One of the major problems being faced by DBAs managing Physical Standby Databases is to keep the standby database in sync with Primary Database.
At times, standby database will go out of sync and there will be hundreds of archive logs to be applied.
It takes huge amount of data transfer and also large amount of archive log apply activity in the standby database.
Also, there is a potential chance of archive log non-availability, which may lead to rebuild of standby database.

The above situation can be tacked, without rebuild in Oracle 10g environment.
Steps to bring the standby database in sync with Primary Database is listed below.

1. Find the current SCN of standby database.

select current_scn from v$database;
CURRENT_SCN
———–
4793543

2. On the primary database – create the needed incremental backup from the above SCN

rman target /
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE FORMAT ‘C:\temp\bkup_%U’;

3. create a new standby controlfile from production

sqlplus> alter database create standby crontrolfile ‘blah’;

4.cancel managed recovery on standby
sqlplus> recover managed standby database cancel;

5. Move your incremental backup from (2) to the standby server (empty folder) and catalog it
rman target /
rman> catalog start with ‘c:\temp\backup\’;

6. recover your standby from the incremental backup
rman> recover database noredo;

7. shutdown the standby and replace the controlfile with the one you backup in (3)

8.startup the standby and put it back into managed recovery mode
sqlplus> startup mount
sqlplus> recover managed standby database disconnect;

3 Responses to “Oracle 10g Dataguard – Sync Standby database”

1. mudhalvan says:

   January 22, 2010 at 2:23 pm

   Nanjunder
   it is very good trick will help most DBAs to avoid rebuilding or spending time on restoring log and searching for missed log with Backup Team.

   Regards
   Mudhalvan M.M
2. Clu Hendrix says:

   August 3, 2010 at 8:52 pm

   What level incremental would this be?
3. Paresh says:

   August 11, 2010 at 8:07 pm

   This is a veru good solution which will help lots of DBAs to overcome their issues.