OCM Disconnected Mode: quick setup guide

Download and move ocm-Production-HP-UX-IA64.zip to $ORACLE_HOME
# unzip ocm-Production-HP-UX-IA64.zip
#cd ccr/config
#vi ccr.properties and add the following line to disable the collection of the host IP address:
ccr.metric.host.ecm_hw_nic.inet_address=false
Add the following line to disable the Network Interface MAC address collection:
ccr.metric.host.ecm_hw_nic.mac_address=false

Configuring Oracle Configuration Manager in Disconnected Mode (so that it does not upload info to Oracle via the internet through the proxy server
$ORACLE_HOME/ccr/bin/setupCCR -s -d

Instrument the Database for Configuration Collections. The following steps must be repeated for every database instance, including multiple instances running from the same ORACLE_HOME.

$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig –s

After Oracle Configuration Manager has been configured, you must run the emCCR
collect command to collect configuration data.

$ORACLE_HOME/ccr/bin/emCCR collect

You can find the disconnected mode collection at the following location:
$ORACLE_HOME/ccr/hosts//state/upload/ocmconfig.jar

To upload this configuration to My Oracle Support, transfer the ocmconfig.jar to a
system that is connected to the Internet and attach it to a Service Request (SR).

Last password change date

Our databases recently went through an audit and we were asked to supply a list of all database users and the date when the last password change occurred. My first reaction was that Oracle does not keep that information, but a little research proofed that I was wrong. The information is kept in sys.user$.ptime
The following query will show when last a password was changed:

select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4
/

DBMS_FILE_TRANSFER

Oracle recommends the use of RMAN to create a physical standby database. But in the manual and various MAA white papers they simply state that you should make the backups available to the target system and that the path to the backup files on the source and standby server must be the same.

Unfortunately they do not recommend how you should do this, especially when you have a FRA configured in ASM.

With the release of version 11 ASM Oracle introduced the ASMCMD cp command. I thought that solves the problem since the white paper (Oracle Database 11G Automatic Storage Management New Features Overview – published June 2007) states “The ASMCMD cp command allows you to copy files between ASM disk groups between 2 ASM servers”, but I could not get the command to copy my RMAN backupsets between ASM disk groups. I eventually logged SR 7173090.993 but even Oracle support was not sure and they wasted 2 days trying to make it work. Only after 2 days, did Oracle support say that the command does not work for copying backupsets between ASM disk groups (only data files).

OK what now?

I had a look around and decided that my only option is the cumbersome (but at least workable) DBMS_FILE_TRANSFER package.

First step is to locate the rman backupsets on the source server:

ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27

Now create the same directory structure on the remote server. On the standby server I first created an ASM Diskgroup with the same name as the FRA on production (+PCASDGF).

ASMCMD> mkdir +PCASDGF/CASOB1
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET/2009_03_27

Use DBMS_FILE_TRANSFER to instantiate an ASM DataGuard database

DBMS_FILE_TRANSFER.GET_FILE contacts a remote database to read a remote file and then creates a copy of the file in the local file system. But before I can use this method I need a "stage" instance on the standby server. (I used DBCA to create a stage database - it took 20 minutes to create)

Prepare the newly created stage database to use DBMS_FILE_TRANSFER:

CONNECT sys/XXX
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27'
CREATE DATABASE LINK SOURCE_DB CONNECT TO system IDENTIFIED BY XXX USING 'CASOB1.VODACOM.CO.ZA';

On Primary:

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27';

Get a list of the backupsets on the source server:

ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
ASMCMD> ls
annnf0_TAG20090327T115944_0.417.682603193
annnf0_TAG20090327T115944_0.418.682603193
...

Use an editor to create a SQL command (one per backupset) which looks like this:

execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'ncsdf0_TAG20090406T092911_0.1
367.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP1',source_database => 'SOURCE_DB');
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'nnndf0_TAG20090406T092911_0.1
364.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP2',source_database => 'SOURCE_DB');
…

Note that the destination_file_name is not the same as the source file name. You will get an ORA-15046 if they are the same, this error is the result of the backupset being an Oracle-Managed file name.

So I execute these commands on the standby server from the stage instance. The progress of the command can be monitored using the V$SESSION_LONGOPS view. Once all the backupsets are copied over I was done with the stage database.

The next step is to create aliases for the new names of the backuppieces, so back on the source server issue:

ALTER DISKGROUP PCASDGF ADD ALIAS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP1' for '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/annnf0_TAG20090327T115944_0.417.682603193';
…

Once done, I can query V$ASM_ALIAS and there are two entries per backuppiece. One for the backuppiece and one for the alias.

The aim of doing this was to create a Data Guard instance, so before I can restore the backup I first have to prepare a Data Guard instance:

Log onto the standby server

# $export ORACLE_SID=casobc
# $orapwd=orapwcasobc password=XXX

sqlplus
SYS@casobc>create spfile from pfile; (I previously created a pfile from production with a few changes necessary for DG)
File created.
SYS@casobc>startup force nomount;
ORACLE instance started.

OK now I am ready to start the restore. First catalog the backuppieces in the source server/primary RMAN

Connect to RMAN on the primary server

RMAN> connect target sys/XXX@casob1.vodacom.co.za;

connected to target database: CASOB1 (DBID=1215042422)

RMAN> connect auxiliary /

connected to auxiliary database: CASOB1 (not mounted)

RMAN> catalog backuppiece '+PCASDGF/ CASOB1/BACKUPSET/2009_03_27/BACKUP1';
RMAN> catalog backuppiece '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP2';
…

Once done, I could list the backup in RMAN and see two entries, one for the backuppiece and one for the alias.
Eventually I am ready to use the RMAN backup to create a data guard instance.

RMAN> duplicate target database for standby ;
...
RMAN> exit

RMAN restore to new host

The mission:

Restore a production 10.2.0.4.0 database to a new host in order to test our recoveries. The production database is part of a Data Guard configuration in Maximum Availability mode and it runs on Unix RAW devices. The restore will be to ASM.

Notes by my colleague Ian Baugaard:

If you perform a test restore only, then do not connect to the recovery catalog when restoring the datafiles. Otherwise, RMAN records information about the restored datafiles to the recovery catalog. This intereferes with future attempts to restore and recover the primary database. If you must use a recovery catalog because the control file is not large enough to contain the RMAN repository data on all of the backups that you need to restore, then export the catalog and import it into a different schema or database and use the copied recovery catalog for the test restore. Otherwise, the catalog considers the restored database as the current target database.

Also confirm all of the following before commencing:

- Ensure that the /etc/oratab file is correctly configured
- Ensure that the following environment variable are set to the values shown
o NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
o NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
- Ensure that the $ORACLE_BASE/admin/$ORACLE_SID/bdump directory is empty
- Ensure that the job_queue_processes parameter is set to 0 when editing the parameter file at step 3
- That the location specified in the log_archive_dest parameter is valid, writable and empty
- Confirm whether or not the source database is using a change tracking file, and if so, try to ensure that the corresponding path exists on the node where the restore is being performed
- Ensure that the tnsnames.ora file does not contain any references whatsoever to a production system

1. Ensure your environment is correctly configured, i.e. ORACLE_SID is set correctly and that the Oracle software installation matches the environment of the source system. When ready, start RMAN and connect to the target without connecting to the recovery catalog.

rman target / NOCATALOG

2. Start the instance without mounting it, using the DBID obtained from the v$database view on the source database. RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file.

RMAN> set DBID dbid ;
RMAN> startup nomount ;

3. Restore and edit the server parameter file. NB: The values for NSR Server and NSR Client will have to be adjusted accordingly, and can be derived from the values of the production script

run {
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
shutdown abort;
}

4. From a second session, edit all appropriate parameters of the restored parameter file to cater for the environment you are restoring on, i.e. to reflect new directory structures, memory differences, etc. Once done, start the instance using the new file

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

5. Proceed to restore the controlfile from an autobackup from your original session

run {
allocate channel c1 device type SBT PARMS=' ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore controlfile from autobackup;
alter database mount;
}

6. Query the database filenames recorded in the control file on the new host by running the following query in SQL*Plus

col name for a60
SELECT file# AS "File/Grp#", name FROM v$datafile
UNION
SELECT group#, member FROM v$logfile;
EXIT

7. Write the RMAN recovery script. The script must include the following steps:
a. For each datafile on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'set newname for datafile '|| file# ||' to '''|| replace( name, 'rabdg', 'rabbcv' ) ||''' ;'
from v$datafile
order by file#

b. For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'SQL "alter database rename file '''''|| member ||''''' to '''''|| replace ( member, 'rabdg', 'rabbcv' ) ||''''' " ;'
from v$logfile
order by member
c. Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.
d. Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
e. Restore and recover the database
NB: By default, RMAN does not restore read-only files when you issue the RESTORE DATABASE command.

run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c2 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c3 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20080310 08:33:12','YYYYMMDD HH24:MI:SS')" ;
# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

# RECOVER THE DATABASE
recover database;
}
EXIT

8. Now perform an OPEN RESETLOGS at the restored database.

RMAN> alter database open resetlogs ;

9. Issue a shutdown and start the restored database in mount mode, and correct the location of all tempfiles

10. If this was a test restore, and it was successful, then you can shut down the test database instance, and delete the test database with all of its files.

SQL> startup force mount restrict pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
SQL> drop database ;

rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora
rm log_archive_dest/*

Scripts and Logfiles

casprd1:/opt/apps/oracle/database/10.2.0.4/rdbms/admin> rman target / NOCATALOG

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 18 14:17:28 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> set dbid 2070848595;

executing command: SET DBID

RMAN> startup nomount ;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/apps/oracle/database/10.2.0.4/dbs/initcasprd1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2054608 bytes
Variable Size                 67110448 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6332416 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2)';
3> restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
4> shutdown abort;
5> }

allocated channel: c1
channel c1: sid=39 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 12:40:16

Change init.ora file to:
*.audit_file_dest='/opt/apps/oracle/admin/casprd1/adump'
*.background_dump_dest='/opt/apps/oracle/admin/casprd1/bdump'
*.compatible='10.2.0'
*.control_file_record_keep_time=21
*.core_dump_dest='/opt/apps/oracle/admin/casprd1/cdump'
*.db_block_size=8192
*.db_cache_size=500000000
*.db_files=500
*.db_name='casprd'
*.db_writer_processes=4
*.instance_name='casprd1'
*.large_pool_size=50000000
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='casprd_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_archive_min_succeed_dest=1
*.log_buffer=16384000
*.os_authent_prefix='ops$'
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='casprd'
*.sga_target=2G
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDO'
*.user_dump_dest='/opt/apps/oracle/admin/casprd1/udump'
*.workarea_size_policy='auto'
*.db_create_file_dest='+TUNXDG_RESTORE'
*.db_recovery_file_dest='+TUNXDG_RESTORE'
*.DB_RECOVERY_FILE_DEST_SIZE=2G
*.PROCESSES=200

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     2057496 bytes
Variable Size                503319272 bytes
Database Buffers            1610612736 bytes
Redo Buffers                  31494144 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2,NSR_DEBUG_FILE=/home/oracle/nsr_icap2.log)';
3> restore controlfile from autobackup;
4> alter database mount;
5> }

allocated channel: c1
channel c1: sid=33 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 13:53:31

channel c1: looking for autobackup on day: 20090219
channel c1: autobackup found: c-2070848595-20090219-04
channel c1: control file restore from autobackup complete
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507
Finished restore at 19-FEB-2009 13:55:17

database mounted
released channel: c1

Edit init.ora file to reflect new controlfile settings:
*.control_files='+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505’,’+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507'


casprd1:/home/oracle/meyert> cat icap_restore.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_restore.log' cmdfile='/home/oracle/meyert/icap_restore.par'

casprd1:/home/oracle/meyert> cat icap_restore.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
…
set newname for datafile 321 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SA_T_CMD_PARAM_0422' ;

SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";
…

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

}
EXIT

casprd1:/home/oracle/meyert> cat icap_restore.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 5 15:44:38 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=26 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=33 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=11 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET NEWNAME

executing command: SET NEWNAME

…

executing command: SET until clause

Starting restore at 05-MAR-2009 15:51:58

channel chnl1: starting datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_12
restoring datafile 00027 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0401
restoring datafile 00156 to +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_ssb
restoring datafile 00193 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0409
channel chnl1: reading from backup piece /full_CASPRD_t680405445_s84501_p1/
…
Finished restore at 07-MAR-2009 01:19:52

datafile 1 switched to datafile copy
input datafile copy recid=665 stamp=680836812 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_system01
datafile 2 switched to datafile copy
input datafile copy recid=666 stamp=680836813 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_undo01
…
datafile 321 switched to datafile copy
input datafile copy recid=985 stamp=680837070 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0422

Starting recover at 07-MAR-2009 01:25:06
channel chnl1: starting incremental datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0418
destination for restore of datafile 00035: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_cmd
destination for restore of datafile 00042: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_event
destination for restore of datafile 00192: +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_05
channel chnl1: reading from backup piece /level1_CASPRD_t680504446_s84621_p1/
…
channel chnl3: reading from backup piece /level1_CASPRD_t680506557_s84697_p1/
channel chnl3: restored backup piece 1
piece handle=/level1_CASPRD_t680506557_s84697_p1/ tag=TAG20090303T050038
channel chnl3: restore complete, elapsed time: 00:17:46

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+TUNXDG_RESTORE/casprd/datafile/casprd_system01'

released channel: chnl1
released channel: chnl2
released channel: chnl3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2009 08:19:08
RMAN-06557: unable to restore archived log thread 1, sequence 12670
RMAN-06558: archived log size of 2043389 kb is bigger than available space of 2020336 kb

Recovery Manager complete.

(My RECOVERY_DEST_SIZE was too small, so I increased it and simply ran a recovery – since the restore was completed)

casprd1:/home/oracle/meyert> cat icap_recovery.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_recovery.log' cmdfile='/home/oracle/meyert/icap_recovery.par'

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT


casprd1:/home/oracle/meyert> cat icap_recovery.log

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 9 07:33:00 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=33 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=12 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=27 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET until clause

Starting recover at 09-MAR-2009 07:33:37

starting media recovery

channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12668
channel chnl1: restoring archive log
archive log thread=1 sequence=12669
channel chnl1: reading from backup piece /arch_CASPRD_t680507135_s84703_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680507135_s84703_p1/ tag=TAG20090303T054533
channel chnl1: restore complete, elapsed time: 00:01:17
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 thread=1 sequence=12668
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 recid=108554 stamp=681032266
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 thread=1 sequence=12669
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 recid=108553 stamp=681032231
channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12670
channel chnl1: reading from backup piece /arch_CASPRD_t680515326_s84705_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680515326_s84705_p1/ tag=TAG20090303T080205
channel chnl1: restore complete, elapsed time: 00:02:06
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 thread=1 sequence=12670
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 recid=108555 stamp=681033249
media recovery complete, elapsed time: 00:05:01
Finished recover at 09-MAR-2009 07:59:13
released channel: chnl1
released channel: chnl2
released channel: chnl3

Recovery Manager complete.

Startup fails with ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR
I found the solution on Stewart F's blog.

Metalink note 245731.1 this was caused by that fact that there was still some kind of reference in my Primary databases data dictionary: "The Primary Database has still the Protection Mode stored in the Data Dictionary. The Protection Mode requires a running connection to a Standby Database with LGWR SYNC as Log-Transportation Mode. Since the standby is not available anymore,LGWR of the primary terminates the instance."

Solution:

SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SYS@casprd1> shutdown immediate
SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> select name from v$tempfile ;

NAME
-----------------------------------------------------------------------------------------------------------------------------
/dev/vx/rdsk/oradg/casprd_TEMP01
/dev/vx/rdsk/oradg/casprd_TEMP02
/dev/vx/rdsk/oradg/casprd_TEMP04
/dev/vx/rdsk/oradg1/casprd_TEMP05
/dev/vx/rdsk/oradg1/casprd_TEMP06
/dev/vx/rdsk/oradg1/casprd_TEMP07
/dev/vx/rdsk/oradg1/casprd_TEMP08

7 rows selected.

SYS@casprd1> alter database rename file '/dev/vx/rdsk/oradg/casprd_TEMP01' to '+TUNXDG_RESTORE/casprd/tempfile/casprd_TEMP01';

Database altered.

Etc for all 7 tempfiles.

SYS@casprd1> alter database open;

Database altered.

Data Guard Lag Time

We use the Broker to administer our Data Guard configurations and we also use OEM to monitor our databases, we have found that every time we change the state of the Physical Standby between ONLINE and READ-ONLY (and back to ONLINE again) we start getting OEM alarms for reaching our set limits for Lag Time (the time in seconds that the Physical Standby is behind the Primary database).This Lag Time can also be found in v$dataguard_stats (in fact that is where OEM gets it from).

What we have found was that we need to bounce the Physical Standby to force v$dataguard_stats to get updated with the correct values. The good news is that the Physical standby does not really have a lag time, it is just that v$dataguard_stats does not update itself when DG STATES change. The bad news is that OEM shows the faulty lag time, generates an alarm and since our alarms are visible to the whole organization we have to explain that it is not really a problem.

To confirm that the problem is in v$dataguard_stats and is not a real reflection of reality, do the following:

On Standby:

SQL>select TIME_COMPUTED from v$dataguard_stats;

TIME_COMPUTED
------------------------------
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13

SQL>select current_scn from v$database;

CURRENT_SCN
--------------------------
9661858803219

On Primary:

SQL>select scn_to_timestamp(9661857384219) from dual;

SCN_TO_TIMESTAMP(9661857384219)
---------------------------------------------------------
12-JAN-09 12.26.57.000000000 PM

SQL>select SCN_TO_TIMESTAMP(current_scn) from v$database;

SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------
12-JAN-09 12.30.00.000000000 PM

So we are only a few seconds behind, but v$dataguard_stats and OEM show otherwise. Bouncing the Physical Standby database fixed this.

KeePass and Putty

KeePass and Putty are software programs that work great together, especially in an enterprise where you have a lot of passwords to remember. Autotmatic Login to a Unix server is simply a “Ctrl+U” away.

First you need to setup your Putty Configuration, for example:

Servers are saved as sessions and Auto-login username is specified


In KeepPass you need to configure 1 entry per server. The URL entry is what makes KeePass more than a list of passwords.

If putty is in your $PATH then the command you typed in the URL field above (putty -load -pw XX) will also work from the command line. In the main KeepPass window you simply need to highlight the entry and press “Ctrl-U”.