Thursday, October 27, 2011

Synchronize | Refresh Standby Database From The Primary Database Using RMAN Incremental Backup

You need to synchronize a standby DB from an incremental RMAN backup in these two cases:

1-There is a big gap between the primary database and the standby database, whereas copying and applying the archives from Production to DR site will take a long time.

2-You lost one or more archive log file on the primary site that needed for the recovery of the standby database or nologging transactions have been run on the primary database.

In general the advantages of recovering the standby database from an incremental backup over the recovery from arvhivelogs is that Incremental backup will recover the standby database much faster than applying the archives, incremental backup apply only the COMMITTED transactions on the standby database while archives will apply the COMMITED & NON-COMMITTED transactions then ROLLBACK the non-committed transactions the thing makes the process much longer than recovering from RMAN incremental backup.

Note: The following demonstration done on 11g database [Linux OS platform]...

On 11g and older versions:

Step1: On The DR Site:

------  ------------------
Check the current SCN on the standby DB:

DEV > select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
------------------------------
1552405397


Step 2: On The Primary Site:
-------   ----------------------
Create a standby control file:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control/standby_control.ctl';

Move that new standby controlfile to the DR site:
#scp /home/oracle/control/standby_control.ctl oracle@dr:/ora_dr1/backup_inc

Create an incremental backup:

Take an incremental backup starting from the standby SCN -The one we checked in Step1-:
RMAN> Backup incremental from SCN 1552405397 database tag='FOR_STANDBY' format '/backupdisk/%d_%t_%s_%p';

Move the backup pieces to the DR site:
#scp /backupdisk/* oracle@dr:/ora_dr1/backup_inc


Step 3: On The DR Site:
-------  ------------------
Shutdown the standby database:

SQL> Shu immediate;

Rename the original control file of  the standby database:
# cp /ora_dr1/control/control01.ctl /ora_dr1/control/control01.ctl.sav

Restore the standby control file we just copied from the primary site:
Make sure to copy it to the right path of the standby DB control file and duplicate it if you have more than one control file path in the standby DB spfile:

# cp /ora_dr1/backup_inc/standby_control.ctl   /ora_dr1/control/control01.ctl

Startup the Standby database with the new controlfile:

SQL> startup mount;

Catalog the incremental backup pieces -which we moved from the Primary site- in the standby DB RMAN catalog to let the standby DB consider that backup piece during recovery process:

#rman target /

RMAN> catalog start with '/ora_dr1/backup_inc/' noprompt;

Recover the standby database from the incremental backup: -From RMAN-


RMAN> RECOVER DATABASE noredo;
Or:
RMAN> RECOVER DATABASE FROM TAG for_standby;

Now your standby database is refreshed from the incremental backup. You can start the Managed Recovery process on the standby DB:

SQL> recover managed standby database disconnect;

Done.

 
[Update: On 07-08-2014]

on 18c and above:

With one command, you can get the job done:

On standby DB: [From mount mode]
 
RMAN> RECOVER STANDBY DATABASE FROM SERVICE primary_tns USING COMPRESSED BACKUP;
 
Notes: 
- Service "primary_tns" should exist in the standby server tnsnames.ora file pointing to the primary DB.
- Including "USING COMPRESSED BACKUP" will be wise, to send the packets in a compressed format to save the bandwidth and transfer the packets faster through the network.