Standby Database Archived Redo Logs Gap and Incremental Restore

Archive log gap is a very common issue that DBAs face every now and then. Normally standby instance would automatically fetch the required archived logs in case of any gap, but there could be scenarios when missing archived logs cannot be found, restored or fetched. In some scenarios, standby database me be several weeks behind the primary and restoring and applying huge number of archived logs could take very long time. In such case, we can take incremental backup of primary database and restore it on the standby to make in in sync with primary. I will explain this procedure in this article.

On standby database, find the current SCN that shows the point until which the changes of primary have been applied.

SQL>SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------------------------- 14786186264

Go on primary database and take incremental backup starting from the SCN returned by the above query form the physical standby database.

run{ allocate channel dsk1 type disk; allocate channel dsk2 type disk; allocate channel dsk3 type disk; allocate channel dsk4 type disk; backup as compressed backupset format '/u05/rman_backup/standby_incr_%U.rbf' INCREMENTAL FROM SCN 14786186264 database filesperset 10 tag 'forstandby'; backup current controlfile for standby FORMAT '/u05/rman_backup/standby_ctl.bak'; }

 Move all the backup files to the standby host (suppose you copied files under /u03/backup for this example). Once done, stop managed recovery on standby from SQLPLUS, or DGMGRL. Shutdown the database, and start in mount mode. For RAC, stop all instance and perform these steps on only one instance.

SQL> alter database recover managed standby database cancel; SQL> shutdown immediate SQL> startup mount

 

Start RMAN and perform the recovery after cataloging the backups.

RMAN> catalog start with '/u03/backup/*'; RMAN> recover database;

 

After recovery completes, restore the controlfile backup. You need to shutdown the instance and start in nomount state before doing this.

SQL> shutdown immediate SQL> startup nomount -- Connect to RMAN for restoring controlfile RMAN> restore standby controlfile from '/u03/backup/standby_ctl.bak';

 If the files are located on ASM or youa re using OMF (Oracle Managed Files) that are on the file system, the datafiles’ names would be different in primary and standby, and after restoring controlfile, the datafiles on standby would become unknown to the controlfile. In this case, catalog the datafiles and switch database to copy.

Provide name of diskgroup or directory name where datafiles are located

RMAN> catalog start with ‘+DATA’; RMAN> switch database to copy;

 After this, managed recovery of the database can be started once again.

SQL> alter database recovery managed standby database using current logfile disconnect;

 There could still be one thing missing here and that is datafiles added to the primary database since the standby became out of sync. In such case, you need to find out datafiles added to the primary database after the SCN 14786186264 (for this example, SCN is 14786186264). Once you find the datafiles, a full backup of these datafiles would need to be restore on the standby database.

SQL> select file#,name from v$datafile where creation_change# > 14786186264; FILE#         NAME ---------       ------------ 38               ‘+DATA/MYDB/DATAFILES/tablespace20.345.12334234234’

 
Take full backup of this datafile, transfer backups to the standby.

RMAN> backup as comperssed backupset format '/u05/rman_backup/datafile_38__%U.rbf' datafile 38;

 
After copying backup to the standby host, perform the restoration after ataloging.

RMAN> catalog start with '/u03/backup/*'; RMAN> restore datafile 38;

After that, you can start managed recovery.