Resolving Archive Gap using incremental SCN RMAN backup

An archive gap is a range of archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary database. For example, an archive gap occurs when the network becomes unavailable and automatic archiving from the primary database to the standby database stops. When the network is available again, automatic transmission of the redo data from the primary database to the failed standby database resumes.
Causes:-
The steps in this section can used to resolve problems if a physical standby database has
1) Loss of Archive log
2) Corrupted Archive Redo Data
3) Unresolvable Archive GAP’s
4) Out of Retention policy

WorkArounds:-
1) If archive exists on primary Troubleshoot the issue, It will automatically shipped if the archive is not corrupted.
2) If archive is corrupted at OS level, if we do have backup, Then Restore archive logs from Backup.
3) Incremental SCN for standby/ Roll Forward.

 Let us assume that the archive log deleted by the user accidentally from primary database, which is not shipped to standby database.
In this case either we need to use workaround 2 or 3.
In this post we will learn about workaround 3 which will take less time to resolve this issue.

Incremental SCN for standby/ Roll Forward :

1. Check if any sequence gap present in standby database
        SQL> Select * from v$archive_gap;

THREAD#  LOW_SEQUENCE#  HIGH_SEQUENCE#

-------------  -------------------------  ----------------------------

                        1          69320                          69331

Note: If there are no gaps then it will show "no rows selected".

     2.       From the above we see the archive log gap is present then our next step is to find the  SCN no. form standby database;

            SQL> select current_scn from v$database;

            CURRENT_SCN
            -----------
            287279617

     3.       On the primary database we need to take the incremental backup from the above SCN no.

RMAN>backup incremental from SCN "287279617" database format 'I: \oradata  

             \Incremental\bkup_%U';

The above RMAN command will backup the data starting from the SCN 287279617which we need to apply on standby database.

4.    Backup current control file for standby.

RMAN>Backup current controlfile for standby format 'I:   

              \oradata\Incremental\control_stndby.ctl';

      5.       Cancel managed recovery at the standby database

SQL>recover managed standby database cancel;

we need to stop the MRP process before we start to apply the incremental backup.

6.   Shutdown the standby database and startup at no mount.

SQL>Shudown immediate;

SQL>Startup nomount;

7.   Restore the standby control file which you have taken backup.

RMAN>restore  standby  controlfile from 'I:   

              \oradata\Incremental\control_stndby.ctl';

8.    Mount the standby database

RMAN>sql ‘alter database mount standby database’;

9.       Copy the backup files to a new folder in standby server so that the folder contains only backup files. Now Catalog the Incremental Backup Files at the Standby Database

RMAN> catalog start with 'I:\oradata\Incremental’;

10.       Apply the Incremental Backup to the Standby Database

 RMAN> recover database noredo;

      11.       Put the standby database back to managed recovery mode.

           SQL> recover managed standby database current log disconnect;

That’s it! You have successfully resolved the archive log gap issue.
Please make sure the remaining archive logs are applied by checking the view v$archive_gap.

SQL> Select * from v$archive_gap;

The out put should be “no rows selected.”