For this guide, the specifications are:
PRIMARY
Operating System: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Database: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Storage Option: Automatic Storage System (ASM) 10g, Oracle-Managed Files (OMF)
db_name: ORCLA10
db_unique_name: MANILA
Datafiles Location: +VOL1/orcla10/datafile/
PHYSICAL STANDBY
Operating System: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Database: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Storage Option: Automatic Storage System (ASM) 10g, Oracle-Managed Files (OMF)
db_name: ORCLA10
db_unique_name: GENSAN
Datafiles Location: +VOL1/gensan/datafile/
In cases where a physical standby database is far behind the primary database or you need to recover missing archivelogs, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply.
In the example below, archivelogs with sequence numbers 225-239 which are required on the standby are deleted.
The RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.
1. On the physical standby database, check the current SCN which will be used for the incremental backup at the primary database, as the backup must be created from this SCN forward.
SQL> col current_scn for 9999999999999999
SQL> select current_scn from v$database;
Code: Select all
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1311881
SQL> alter database recover managed standby database cancel;
Code: Select all
SQL> alter database recover managed standby database cancel;
Media recovery complete.
RMAN> backup incremental from scn 1311881 database format '/u01/backup/ORCLA10_%U' tag 'FORSTANDBY';
Code: Select all
RMAN> backup incremental from scn 1311881 database format '/u01/backup/ORCLA10_%U' tag 'FORSTANDBY';
Starting backup at 18-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=122 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+VOL1/orcla10/datafile/system.256.771579811
input datafile fno=00003 name=+VOL1/orcla10/datafile/sysaux.257.771579811
input datafile fno=00005 name=+VOL1/orcla10/datafile/example.269.771580081
input datafile fno=00002 name=+VOL1/orcla10/datafile/undotbs1.258.771579813
input datafile fno=00004 name=+VOL1/orcla10/datafile/users.259.771579813
channel ORA_DISK_1: starting piece 1 at 18-FEB-12
channel ORA_DISK_1: finished piece 1 at 18-FEB-12
piece handle=/u01/backup/ORCLA10_0nn3k8ok_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 18-FEB-12
channel ORA_DISK_1: finished piece 1 at 18-FEB-12
piece handle=/u01/backup/ORCLA10_0on3k8r0_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-FEB-12
4. Transfer all backup sets created on the primary system to the physical standby machine. There are many other ways to copy the files but in this case, we use scp (secure copy) to transfer the files.
From primary system,
[dgprim.localdomain /u01/backup]$ scp -rv /u01/backup/* oracle@dgstan.localdomain:/u01/backup/
Code: Select all
[dgprim.localdomain /u01/backup]$ scp -rv /u01/backup/* oracle@dgstan.localdomain:/u01/backup/
Executing: program /usr/bin/ssh host dgstan.localdomain, user oracle, command scp -v -r -d -t /u01/backup/
OpenSSH_3.9p1, OpenSSL 0.9.7a Feb 19 2003
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: Applying options for *
debug1: Connecting to dgstan.localdomain [192.168.218.130] port 22.
debug1: Connection established.
debug1: identity file /home/oracle/.ssh/identity type -1
debug1: identity file /home/oracle/.ssh/id_rsa type -1
debug1: identity file /home/oracle/.ssh/id_dsa type -1
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.9p1
debug1: match: OpenSSH_3.9p1 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.9p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host 'dgstan.localdomain' is known and matches the RSA host key.
debug1: Found key in /home/oracle/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Next authentication method: gssapi-with-mic
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Next authentication method: publickey
debug1: Trying private key: /home/oracle/.ssh/identity
debug1: Trying private key: /home/oracle/.ssh/id_rsa
debug1: Trying private key: /home/oracle/.ssh/id_dsa
debug1: Next authentication method: password
oracle@dgstan.localdomain's password:
debug1: Authentication succeeded (password).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -r -d -t /u01/backup/
Sending file modes: C0640 3629056 ORCLA10_0nn3k8ok_1_1
Sink: C0640 3629056 ORCLA10_0nn3k8ok_1_1
ORCLA10_0nn3k8ok_1_1 100% 3544KB 3.5MB/s 00:01
Sending file modes: C0640 7438336 ORCLA10_0on3k8r0_1_1
Sink: C0640 7438336 ORCLA10_0on3k8r0_1_1
ORCLA10_0on3k8r0_1_1 100% 7264KB 7.1MB/s 00:00
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: fd 0 clearing O_NONBLOCK
debug1: fd 1 clearing O_NONBLOCK
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
RMAN> CATALOG START WITH '/u01/backup/ORCLA10';
Code: Select all
RMAN> CATALOG START WITH '/u01/backup/ORCLA10';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/backup/ORCLA10
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/ORCLA10_0on3k8r0_1_1
File Name: /u01/backup/ORCLA10_0ln3k4ng_1_1
File Name: /u01/backup/ORCLA10_0nn3k8ok_1_1
File Name: /u01/backup/ORCLA10_0kn3k4l5_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/ORCLA10_0on3k8r0_1_1
File Name: /u01/backup/ORCLA10_0ln3k4ng_1_1
File Name: /u01/backup/ORCLA10_0nn3k8ok_1_1
File Name: /u01/backup/ORCLA10_0kn3k4l5_1_1
RMAN> recover database noredo;
Note: We specify NOREDO option in the RECOVER command because online redo logs are lost. Although the online logs are available but the redo cannot be applied to the incrementals.
Code: Select all
RMAN> recover database noredo;
Starting recover at 18-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +VOL1/gensan/datafile/system.281.775556873
destination for restore of datafile 00002: +VOL1/gensan/datafile/undotbs1.319.775556873
destination for restore of datafile 00003: +VOL1/gensan/datafile/sysaux.261.775556873
destination for restore of datafile 00004: +VOL1/gensan/datafile/users.313.775556873
destination for restore of datafile 00005: +VOL1/gensan/datafile/example.320.775556873
channel ORA_DISK_1: reading from backup piece /u01/backup/ORCLA10_0nn3k8ok_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/ORCLA10_0nn3k8ok_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 18-FEB-12
RMAN> COPY CURRENT CONTROLFILE FOR STANDBY TO '/u01/backup/standbyORCLA10.ctl';
Code: Select all
RMAN> COPY CURRENT CONTROLFILE FOR STANDBY TO '/u01/backup/standbyORCLA10.ctl';
Starting backup at 18-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=126 devtype=DISK
channel ORA_DISK_1: starting datafile copy
copying standby control file
output filename=/u01/backup/standbyORCLA10.ctl tag=TAG20120218T100941 recid=6 stamp=775562983
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-FEB-12
[ORCLA10 /u01/backup]$ scp -rv /u01/backup/standbyORCLA10.ctl oracle@dgstan.localdomain:/u01/backup/
Code: Select all
[ORCLA10 /u01/backup]$ scp -rv /u01/backup/standbyORCLA10.ctl oracle@dgstan.localdomain:/u01/backup/
Executing: program /usr/bin/ssh host dgstan.localdomain, user oracle, command scp -v -r -t /u01/backup/
OpenSSH_3.9p1, OpenSSL 0.9.7a Feb 19 2003
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: Applying options for *
debug1: Connecting to dgstan.localdomain [192.168.218.130] port 22.
debug1: Connection established.
debug1: identity file /home/oracle/.ssh/identity type -1
debug1: identity file /home/oracle/.ssh/id_rsa type -1
debug1: identity file /home/oracle/.ssh/id_dsa type -1
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.9p1
debug1: match: OpenSSH_3.9p1 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.9p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host 'dgstan.localdomain' is known and matches the RSA host key.
debug1: Found key in /home/oracle/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Next authentication method: gssapi-with-mic
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Authentications that can continue: publickey,gssapi-with-mic,password
debug1: Next authentication method: publickey
debug1: Trying private key: /home/oracle/.ssh/identity
debug1: Trying private key: /home/oracle/.ssh/id_rsa
debug1: Trying private key: /home/oracle/.ssh/id_dsa
debug1: Next authentication method: password
oracle@dgstan.localdomain's password:
debug1: Authentication succeeded (password).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -r -t /u01/backup/
Sending file modes: C0640 7356416 standbyORCLA10.ctl
Sink: C0640 7356416 standbyORCLA10.ctl
standbyORCLA10.ctl 100% 7184KB 7.0MB/s 00:01
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: fd 0 clearing O_NONBLOCK
debug1: fd 1 clearing O_NONBLOCK
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.5 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
RMAN> shutdown immediate;
RMAN> startup nomount;
10. Connect to the physical standby database using RMAN and restore the standby control file.
Below statement will overwrite your existing standby control file which is:
Code: Select all
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +VOL1/gensan/controlfile/curre
nt.298.775556785Code: Select all
RMAN> restore standby controlfile from '/u01/backup/standbyORCLA10.ctl';
Starting restore at 18-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+VOL1/gensan/controlfile/current.298.775556785
Finished restore at 18-FEB-12
RMAN> shutdown immediate;
RMAN> startup mount;
12a. From standby database, if primary and standby database data file directories are identical, just put the standby database back to managed recovery mode and PROCEED TO Step 13! Else, proceed to step 12b to 12d.
SQL> alter database recover managed standby database using current logfile disconnect;
Code: Select all
SQL> alter database recover managed standby database using current logfile disconnect;
Media recovery complete.
Note: From the alert log, you will find similar error messages.
Sat Feb 18 10:21:42 PHT 2012
Errors in file /u01/app/oracle/admin/ORCLA10/bdump/orcla10_dbw0_24042.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+VOL1/orcla10/datafile/system.256.771579811'
ORA-17503: ksfdopn:2 Failed to open file +VOL1/orcla10/datafile/system.256.771579811
ORA-15012: ASM file '+VOL1/orcla10/datafile/system.256.771579811' does not exist
Sat Feb 18 10:21:42 PHT 2012
Errors in file /u01/app/oracle/admin/ORCLA10/bdump/orcla10_dbw0_24042.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+VOL1/orcla10/datafile/undotbs1.258.771579813'
ORA-17503: ksfdopn:2 Failed to open file +VOL1/orcla10/datafile/undotbs1.258.771579813
ORA-15012: ASM file '+VOL1/orcla10/datafile/undotbs1.258.771579813' does not exist
For example, if the primary database data file directory is +VOL1/orcla10/datafile/ and the standby database data file directory is +VOL1/gensan/datafile/, then run the following statement in RMAN to catalog all standby data files while connected to the physical standby database:
RMAN> catalog start with '+VOL1/gensan/datafile/';
Code: Select all
RMAN> catalog start with '+VOL1/gensan/datafile/';
Starting implicit crosscheck backup at 18-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 18-FEB-12
Starting implicit crosscheck copy at 18-FEB-12
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-FEB-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +vol1/GENSAN/TEMPFILE/TEMP.286.775558769
File Name: +vol1/GENSAN/BACKUPSET/2012_02_18/annnf0_STABLE_0.316.775557159
File Name: +vol1/GENSAN/BACKUPSET/2012_02_18/nnndf0_TAG20120218T083239_0.318.775557161
File Name: +vol1/GENSAN/BACKUPSET/2012_02_18/ncsnf0_TAG20120218T083239_0.262.775557285
File Name: +vol1/GENSAN/DATAFILE/SYSTEM.281.775556873
File Name: +vol1/GENSAN/DATAFILE/SYSAUX.261.775556873
File Name: +vol1/GENSAN/DATAFILE/EXAMPLE.320.775556873
File Name: +vol1/GENSAN/DATAFILE/UNDOTBS1.319.775556873
File Name: +vol1/GENSAN/DATAFILE/USERS.313.775556873
searching for all files that match the pattern +VOL1/gensan/datafile/
no files found to be unknown to the database
RMAN> switch database to copy;
Code: Select all
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+VOL1/gensan/datafile/system.281.775556873"
datafile 2 switched to datafile copy "+VOL1/gensan/datafile/undotbs1.319.775556873"
datafile 3 switched to datafile copy "+VOL1/gensan/datafile/sysaux.261.775556873"
datafile 4 switched to datafile copy "+VOL1/gensan/datafile/users.313.775556873"
datafile 5 switched to datafile copy "+VOL1/gensan/datafile/example.320.775556873"
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
12e. Put the standby database back to managed recovery mode.
SQL> alter database recover managed standby database using current logfile disconnect;
Code: Select all
SQL> alter database recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> alter system switch logfile;
SQL>
set lines 250
select dest_id, status, error
from v$archive_dest
where dest_id in (1,2);
The status should be 'VALID'.
Code: Select all
DEST_ID STATUS ERROR
---------- --------- -----------------------------------------------------------------
1 VALID
2 VALID
SQL>
select max(sequence#) from v$archived_log
where archived = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
Code: Select all
SQL> select max(sequence#) from v$archived_log
where archived = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
2 3
MAX(SEQUENCE#)
--------------
241
SQL>
select max(sequence#) from v$archived_log
where applied = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
Code: Select all
SQL> select max(sequence#) from v$archived_log
where applied = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
2 3
MAX(SEQUENCE#)
--------------
241