How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

A slightly different approach to roll-forward standby database procedure

Roll-forwarding a physical standby database is a procedure, technique and method typically used to resolve the huge archive gap between the primary and standby database that is caused by various reasons. It’s a faster and recommended approach to resolve the huge gap and make the standby database in line with the primary database in a quick fashion.

Most of the existing material in this context, explains only one approach, i.e. taking a fresh RMAN incremental back up on the primary database and then applying it on a standby database to fill the archive gap. In this article, I will be sharing an interesting scenario that I have confronted while building a standby database of 2TB sized database and how I resolved the issue taking a slight different approach to the problem.

The following is the typical roll-forward method is used to resolve the gap between the primary and standby databases. For example, when a huge gap, let’s say over 500 archives logs, is found on a physical standby database, you will follow the steps mentioned below to resolve the gap:

1. Cancel the automatic media recovery process (MRP) on the standby database, if in progress:
   
   SQL> alter database recover managed standby database cancel;
    
2. Take a note of current System Change Number (SCN) on the standby database using the following SQL statement:
   
   SQL> SELECT current_scn FROM V$DATABASE;
    
3. Perform an incremental RMAN back up and create a new standby control file on the primary database:
   
   $ export ORACLE_SID=<primary database SID>

$ rman target /

RMAN> BACKUP INCREMENTAL FROM SCN <value from the above query> DATABASE FORMAT ‘/u00/tmp/incr_db_%U’;

 --- create a new standby control-file 

RMAN> backup current controlfile for standby format ‘/u00/tmp/standby.ctl’;
 

4. Copy the files generated under the /u00/tmp directory to the same location on the standby database site. You can use any copy/FTP option to transfer the backup files from primary to the standby database site.
    
5. Once files are moved/copied on the standby site, execute the following sequence of commands on the standby database:
   
   $ rman target / 

RMAN> shutdown; 

RMAN> startup nomount;

RMAN> restore standby controlfile from ‘/u00/tmp/standby.ctl’

RMAN> alter database mount;

RMAN> catalog start with ‘+DG_PRIM/STDBY/DATAFILE’;

--- Respond with YES when the following is prompted:Do you really want to catalog the above files (enter YES or NO)? YES
 Note: the above step is necessary as the location of the primary and standby data files are likely to be a different one.

RMAN> switch database to copy; 

RMAN> recover database delete archivelog;
 

6. Once the recovery is completed on the standby database, re-create the standby redo logs and restart the MRP on the standby database using the following SQL statement:
   
   For 10g database:
   SQL> alter database recover managed standby database disconnect from session;
    For 11g database:
   SQL> alter database recover managed standby database using current logfile disconnect from session;