Resolving Archived Redo Log Gaps Using Incremental Backups

Some times archive logs may not be shipped to the standby database due to network failure, and might have got deleted from the Primary database. If this happens, even though the subsequent archives has been transferred to the standby, the standby recovery cannot jump in to the next archive before applying the missing archive. In such cases one option is to create the standby database from scratch. But if the database size is in Tera Bytes recreating the standby from scratch is not the best solution.

In such cases there is another solution for recovery of standby. This is to take the incremental backup from Primary from the SCN where Standby database has stopped, and apply it on standby taking the database forward and bypass applying missing archived redo log files.

Below are the high level steps for this recovery.

1. Check the SCN in standby where the recovery has stopped.
2. Take incremental backup from Primary from the scn where standby recovery has stopped.
3. Create standby control file on Primary.
4. Transfer backup files and standby control file to standby server.
5. Check control file location.
6. Cancel Managed Recovery on standby and shutdown the database.
7. Copy standby controlfiles to the controlfile locations.
8. Mount database in standby mode 

9. Recover standby database using incremental backup.
10.After recovery, exit RMAN and start managed recovery process.

Here are the steps in detail.

1. Check the SCN where the recovery has stopped.

On Primary

     SQL> select current_scn from v$database;

     CURRENT_SCN

     -----------

     9713054891 

 On the standby:

     SQL> select current_scn from v$database;

     CURRENT_SCN

     -----------

     9449462485

Here you can see huge difference in SCN between the Primary and the standby database and since the log apply has stopped the SCN of Standby cannot move forward.

2. Take incremental backup from Primary DB from the scn where standby DB recovery has stopped.

$rman connect target /

RMAN>run {
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '/b01/stby_bkup/%d_incr_bkup_for_stby_%u_%s_%p' MAXPIECESIZE = 5G;
ALLOCATE CHANNEL ch2 TYPE DISK FORMAT '/b01/stby_bkup/%d_incr_bkup_for_stby_%u_%s_%p' MAXPIECESIZE = 5G;
ALLOCATE CHANNEL ch3 TYPE DISK FORMAT '/b01/stby_bkup/%d_incr_bkup_for_stby_%u_%s_%p' MAXPIECESIZE = 5G;
backup incremental from scn 9449462486 database;
}

3. Create standby control file on Primary.
SQL> alter database create standby controlfile as '/b01/stby_bkup/%d_stby_ctrl.ctl';

4. Transfer backup files and standby controlfile to standby database.

    scp * root@192.168.2.100:/b01/arch/stby_temp_bkup/

   
    change the ownership and permission of backup files if required.

    
    chown oracle:dba *

    chmod 775 *

5. Check the controlfile location of standby database.
       show parameter control;

6. Cancel Managed Recovery on standby and Shutdown the database.

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

7. Copy the standby controlfile copied from Primary to the standby control file location as specified in the step 5 .

    cp /b01/arch/stby_temp_bkup/ORCL_stby_ctrl.ctl /u02/oradata/data/control01.ctl
    cp /b01/arch/stby_temp_bkup/ORCL_stby_ctrl.ctl /u03/fast_recovery_area/control02.ctl

8. Startup database in nomount stage and then mount as standby database.
    SQL>startup nomount;
    SQL>Alter database mount standby database;

9. Recover standby database using incremental backup.

   $rman target /
    
   Catalog the database if you are copying the backup file to a different directory

           RMAN> catalog start with '/b01/arch/stby_temp_bkup/';

   
      Start Database recovery

          RMAN>recover database;

10. After recovering the database using the backup, the recovery fails searching for current archives.

11. At this point exit RMAN and start managed recovery process:

     SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

     RMAN will look for the next archived redo log files as it generates in primary and start applying it automatically:

12.Now check the SCN’s in primary and standby:

    [Primary] SQL> select current_scn from v$database;

    CURRENT_SCN
    -----------
    9722804556

    [Standby] SQL> select current_scn from v$database;

    CURRENT_SCN
    -----------
    9721051710

Now they are very close to each other. The standby has now caught up.