Data Gaurd Gap Detection and resolved gap

When we using Dataguard, physical standby can go out of sync with the primary database. Before doing anything we need to verify why standby is not in sync state with primary database. We can use of v$archived_log and gv$archived_log views for gap detection. In above views we can check status of APPLIED filed for last sequence number that it has YES or NO value. Therefore If APPLIED=YES then archivelog file applied to standby and If APPLIED=NO this means that due to the missing log MRP is not applying the logs on standby database.

1-ON primary and Standby Database:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Or

SQL> SELECT SEQUENCE#, APPLIED FROM gV$ARCHIVED_LOG;

SEQUENCE#                      APPLIED

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

      658                         YES

      659                          NO

      660                          NO

As you see we have gap between primary and standby  this means from sequence number 659 doesn’t apply on the standby.

2-Now we can take an incremental backup of primary from SCN where standby is lagging and apply on standby.

On Standby database query v$database iew and record current SCN of the standby database:

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)

----------------------------------------
15720350562

Stop Redo apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was record.

RMAN>BACKUP INCREMENTAL FROM SCN 15720350562 DATABASE FORMAT '/backup/BackupForStandby_%U' ;

3-Copy taken backup from primary to standby database and do a recovery of standby database using the incremental backup.at first you should catalog your backup piece.

$ rman nocatalog target /

RMAN> CATALOG BACKUPPIECE '/test/BackupForStandby _1re6yu5_1_1';

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 2015-12-14 09:23:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
....
..
..
.
channel ORA_DISK_1: reading from backup piece /backup/BackupForStandby_%U
channel ORA_DISK_1: restored backup piece 1
piece handle/backup/BackupForStandby_%U channel ORA_DISK_1: restore complete, elapsed time: 01:53:08
Finished recover at 2008-07-25 05:20:3

After that start the managed recover :

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Now you can check value of APPLIED for last sequence number on standby and primary database:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Or

SQL> SELECT SEQUENCE#, APPLIED FROM gV$ARCHIVED_LOG;

SEQUENCE#                   APPLIED

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

      658                               YES

      659                               YES

      660                               YES