Steps to sync Standby with Primary Database in Oracle

by

In this tutorials, you will learn Steps to sync Standby with Primary Database in Oracle.

We can perform standby DB sync with primary when:

  • If there is a huge gap between primary and standby DB
  • If archivelogs are missing on primary
  • If archivelogs are corrupt on primary

Steps to Sync Standby with Primary Database in Oracle

Step 1: On standby DB – cancel the recovery on standby database with below command.

Sql>alter database recover managed standby database cancel;

Step 2: On primary DB –  Determine the point from which standby is out of sync

sql>column current _scn for 999999999

sql>select current_scn from  v$database;

Step 3: On primary DB – Determine if any datafile was added on primary DB after the standby DB was out of sync.  If yes, we need to restore that datafile on standby separately. If there is no datafile added then continue standby sync activity with the below steps. 

sql>select  file#, name, creation_change#  from v$datafile_header where creation_change#  >= Step 2's scn;

Step 4: On primary DB – take SCN based incremental backup using RMAN. Here we are using SCN number which we got from step 2. This backup should go on disk. And don’t forget to allocate channels while taking a backup so that backup will be faster.

Also, we are taking control of file backup.

RMAN>

run{

allocate channel ch 15

device type disk ;

backup as  compressed backupset  incremental  from scn ( scn from step 2) database format  '/u002/rman/inc_backup_%U' 

release channel ch 15;

RMAN>backup current controlfile for standby  format  '/u001/tmp/standby_ctrl.bkp';

Step 5: Transfer backup files from primary DB to standby DB

On primary – 

OS> scp  -p /u002/rman/inc_back*  oracle@175.120.678.67:/u002/rman_stdby/

OS> scp  -p /u002/rman/standby_ctrl.bkp  oracle@175.120.678.67:/u002/rman_stdby/

Step 6:  On standby  –  Connect to rman and catalog the copied backup

RMAN> catalog start with  '/u002/rman_stdby/';

Step  7: On standBy DB – Now restore the control file from backup

RMAN>restore standby control file to '/tmp/control.ctl';

Step 8: On standby DB – Now copy the restored standby control file to its original location. Use the below command to get an original location of a control file.

sql>show parameter control_files    

#OR

sql>select name from v$controlfile;

And replace  control file from original location with /tmp/control.ctl

Step  9: On standby – Now shutdown the standby database

sql>shutdown immediate

sql>startup mount

Step 10: On standby – Now restore data file from step 3 using rman.  We are performing this step only if we are getting output in step 3.

RMAN>restore data file datafile_number(from step3);

RMAN>recover database noredo;

Step 11: On standby – Now start recovery with below command.

sql>recover managed standby database disconnect from session;

Step 12: Confirm if the standby database is in sync with primary DB. Check archive log list on  both sides to see current log sequences

Now, you learned the Steps to sync Standby with Primary Database in Oracle real environment.

thanks for reading these articles. you’ll also like the below articles.

Oracle Database Administrators

Oracle 11gR2 RAC

Oracle Database Security Tutorials

Oracle Performance Tuning





Related Posts