Previously we covered How to open Manual Physical Standby Database in READ ONLY mode.
Let us discuss one scenario and then you will realize the use of Incremental backup from SCN.
Consider the production database configured with Manual data guard, and unfortunately any archive log file gets corrupted or lost. Here we solely depend on that corrupted archive log to continue database replication, in this case we can fix the problem with RMAN incremental backup from SCN. Input SCN from standby database current state and backup incremental from that SCN from Primary database, and recover standby database. By this method, we no more need that corrupted archive log file.
There is huge archive log sequence gap between primary and standby database, and we need to recover it in one go.
Before taking Incremental backup from SCN, we need to perform some pre-requisites on primary and standby database server, are as follows:
Check listener status, it should be running and listening to databases.
[oracle@PR ~]$ lsnrctl status
Check database mode and role, On primary site, database should be in READ WRITE mode and role should be PRIMARY.
On standby site, database should be in MOUNTED mode and role should be PHYSICAL STANDBY.
Steps to take incremental backup from SCN:
On primary database, take standby controlfile backup:
SQL> alter database create standby controlfile as '/home/oracle/backup/standby_control.ctl'; Database altered.
Get current SCN from Standby database:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 957835
On primary database, take RMAN incremental backup from SCN, take SCN as an input from Step-II:
[oracle@PR ~]$ rman target / RMAN> backup device type disk incremental from scn 957835 database format '/home/oracle/backup/Inc_backup_%U'; Starting backup at 13-MAY-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK backup will be obsolete on date 20-MAY-16 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf channel ORA_DISK_1: starting piece 1 at 13-MAY-16 channel ORA_DISK_1: finished piece 1 at 13-MAY-16 piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46 using channel ORA_DISK_1 backup will be obsolete on date 20-MAY-16 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 13-MAY-16 channel ORA_DISK_1: finished piece 1 at 13-MAY-16 piece handle=/home/oracle/backup/Inc_backup_06r5f6g7_1_1 tag=TAG20160513T045757 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-MAY-16
Transfer backup to standby database server:
[oracle@DR backup]$ pwd /home/oracle/backup [oracle@DR backup]$ ll total 59728 -rw-r--r--. 1 oracle oinstall 41615360 May 13 04:58 Inc_backup_05r5f6en_1_1 -rw-r--r--. 1 oracle oinstall 9797632 May 13 04:58 Inc_backup_06r5f6g7_1_1 -rw-r--r--. 1 oracle oinstall 9748480 May 13 04:36 standby_control.ctl
On Standby database, Restore Incremental backup:
Shutdown standby database and startup in nomount mode:
SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
SQL> startup nomount; ORACLE instance started. Total System Global Area 755769344 bytes Fixed Size 2217184 bytes Variable Size 448793376 bytes Database Buffers 301989888 bytes Redo Buffers 2768896 bytes
Restore standby controlfile:
[oracle@DR ~]$ rman target / RMAN> restore controlfile from '/home/oracle/backup/standby_control.ctl'; Starting restore at 13-MAY-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/u01/app/oracle/oradata/RTS/control01.ctl output file name=/u01/app/oracle/oradata/RTS/control02.ctl Finished restore at 13-MAY-16
Alter database to MOUNT mode:
SQL> alter database mount; Database altered.
Catalog RMAN incremental backup files to RMAN repository(In my case, i.e. controlfile):
[oracle@DR ~]$ rman target / RMAN> catalog start with '/home/oracle/backup/'; using target database control file instead of recovery catalog searching for all files that match the pattern /home/oracle/backup/ List of Files Unknown to the Database ===================================== File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1 File Name: /home/oracle/backup/standby_control.ctl File Name: /home/oracle/backup/Inc_backup_05r5f6en_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: /home/oracle/backup/Inc_backup_06r5f6g7_1_1 File Name: /home/oracle/backup/standby_control.ctl File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1
Recover standby database:
RMAN> recover database; Starting recover at 13-MAY-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u01/app/oracle/oradata/RTS/system01.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/RTS/sysaux01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/RTS/undotbs01.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/RTS/users01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Inc_backup_05r5f6en_1_1 channel ORA_DISK_1: piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 starting media recovery unable to find archived log archived log thread=1 sequence=0 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/13/2016 05:13:28 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 972205
Above recovery successful and expecting next archive log and starting SCN 972205, which is greater than old current SCN of standby database.( Ref: Step-II )
Ensure archive log gap sequence between primary and standby database, it should be zero or one or two, because logfiles might have switched due to heavy load on your production primary database.
Cheers!! Standby database successfully recovered from incremental backup. Now onward you can continue replication with new archive log files.
Your suggestions and comments are highly appreciated, if any.
Stay tuned with my next article on failover(Activate) of standby database.
Thank you 🙂