In this post , I will demonstrate synchronization of a standby DB from an incremental RMAN backup. It might be needed in following scenarios:
1-The standby database is considerably lagging behind the primary . The copying and applying of archive logs from primary will be time consuming as it will apply both the COMMITED and the NON COMMITED transactions then will ROLLBACK the non committed transactions. Incremental backup will recover the standby database much faster than applying the archives as it will apply only the COMMITED transactions on the standby database .
2-Some archivelogs on the primary which have not been applied to the standby have been lost. In such cases, you can create an incremental backup of the primary database containing changes since the standby database was last refreshed. This incremental backup can be applied to the standby database to synchronize it with a primary database.
- Stop redo transport on primary
- Switch logs on primary – results in some archived logs on primary which have not been sent to standby
- Rename newly generated archived logs on primary to simulate their loss
- Restart redo transport – gives error as gap cannot be resolved due to missing logs
- Create a control file for standby database on primary
- Take incremental backup on primary starting from the SCN# of standby database
- Copy the incremental backup to the standby host and catalog it with RMAN
- Mount the standby database with newly created standby control file
- Cancel managed recovery of standby database and apply incremental backup to the standby database
- Start managed recovery of standby database
– Check current log sequence on primary
PRI>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 40 Next log sequence to archive 42 Current log sequence 42
- check that all the archived logs prior to the current log have been sent to standby
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 41
– stop redo transport from primary (dg01)
DGMGRL> show database dg01 Database - dg01 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dg01 Database Status: SUCCESS DGMGRL> edit database dg01 set state='Transport-off'; Succeeded. DGMGRL> show database dg01 Database - dg01 Role: PRIMARY Intended State: TRANSPORT-OFF Instance(s): dg01 Database Status: SUCCESS
– switch log on primary
PRI>alter system switch logfile; alter system switch logfile; alter system switch logfile; archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 44 Next log sequence to archive 45 Current log sequence 46
– check that logs after sequence# 42 are not being transported to sby
SBY> select max(sequence#) from v$archived_log MAX(SEQUENCE#) -------------- 42
– Find out names of archived logs generated on primary which have not been transported to standby
PRI>set line 500 col name for a40 select sequence#, name from v$archived_log where sequence# > 42; SEQUENCE# NAME ---------- ---------------------------------------- 43 /u01/app/oracle/flash_recovery_area/DG01 /archivelog/2013_11_02/o1_mf_1_43_9780s1 ch_.arc 44 /u01/app/oracle/flash_recovery_area/DG01 /archivelog/2013_11_02/o1_mf_1_44_9780tl t0_.arc 45 /u01/app/oracle/flash_recovery_area/DG01 /archivelog/2013_11_02/o1_mf_1_45_9780tl vo_.arc
– To simulate loss of archived logs on primary, rename them
PRI>ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_43_9780s1ch_.arc /home/oracle/arch_43.arc ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_44_9780tlt0_.arc /home/oracle/arch_44.arc ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_45_9780tlvo_.arc /home/oracle/arch_45.arc
– Restart redo transport
DGMGRL> edit database dg01 set state='Transport-on';
– check that gap in redo logs on standby cannot be resolved as some logs are missing on primary
DGMGRL> show database dg01 Database - dg01 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dg01 Database Error(s): ORA-16783: cannot resolve gap for database dg02 Database Status: ERROR DGMGRL> show configuration; Configuration - dgconfig1 Protection Mode: MaxPerformance Databases: dg01 - Primary database Error: ORA-16724: cannot resolve gap for one or more standby databases dg02 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ERROR
– check current scn# of standby
SBY>select current_scn from v$database; CURRENT_SCN ----------- 998647
– Create a standby control file:
PRI>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby_control.ctl';
– copy the standby control file to standby host
PRI> !scp /home/oracle/standby_control.ctl node2:/home/oracle/standby_control.ctl
– Take incremental backup on primary starting from scn# of standby database
RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.
• RMAN does not consider the incremental backup as part of a backup strategy at the source database. The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database.
RMAN> Backup incremental from SCN 987005 database tag='FOR_STANDBY' format '/home/oracle/%d_%t_%s_%p'; Starting backup at 02-NOV-13 channel ORA_DISK_1: starting full datafile backup set ... channel ORA_DISK_1: starting piece 1 at 02-NOV-13 channel ORA_DISK_1: finished piece 1 at 02-NOV-13 piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
– Move the backup pieces to the standby host
PRI> !scp /home/oracle/DG01_830395300_22_1 node2:/home/oracle/DG01_830395300_22_1
– Catalog the Incremental Backup Files at the Standby Database
RMAN> catalog backuppiece '/home/oracle/DG01_830395300_22_1'; list backup tag FOR_STANDBY; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Incr 9.38M DISK 00:00:00 02-NOV-13 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: FOR_STANDBY Piece Name: /home/oracle/DG01_830395300_22_1 Keep: NOLOGS Until: 09-NOV-13 List of Datafiles in backup set 9
– Find out names of current control files
SQL> col value for a50 col name for a15 select name, value from v$parameter where upper(name)= 'CONTROL_FILES'; NAME VALUE --------------- -------------------------------------------------- control_files /u01/app/oracle/oradata/dg02/control01.ctl, /u01/a pp/oracle/flash_recovery_area/dg02/control02.ctl
- Shutdown the standby database and rename the original control file of the standby database:
SBY> Shu immediate; !mv /u01/app/oracle/oradata/dg02/control01.ctl /u01/app/oracle/oradata/dg02/control01.bak !mv /u01/app/oracle/flash_recovery_area/dg02/control02.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.bak
– Restore the standby control file we just copied from the primary
SBY> ! cp /home/oracle/standby_control.ctl /u01/app/oracle/oradata/dg02/control01.ctl ! cp /home/oracle/standby_control.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.ctl
– Startup the Standby database with the new controlfile:
SBY> startup mount;
– Apply the Incremental Backup to the Standby Database
Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database.
SBY> recover managed standby database cancel; RMAN> RECOVER DATABASE from tag for_standby NOREDO; Starting recover at 02-NOV-13 using channel ORA_DISK_1 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/dg02/system01.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/dg02/sysaux01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/dg02/undotbs01.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/dg02/users01.dbf destination for restore of datafile 00005: /u01/app/oracle/oradata/dg02/example01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/DG01_830395300_22_1 channel ORA_DISK_1: piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 02-NOV-13
Now your standby database is refreshed from the incremental backup.
You can start the Managed Recovery process on the standby DB:
SBY>recover managed standby database disconnect; Media recovery complete.
You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.
— check that SCN# of the standby database has advanced.
SBY>select current_scn from v$database; CURRENT_SCN ----------- 1005729 DGMGRL> edit database dg01 set state='transport-on'; DGMGRL> show configuration; Configuration - dgconfig1 Protection Mode: MaxPerformance Databases: dg01 - Primary database dg02 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
This technique cannot be used to update a duplicate database.