Resolve Archive gap between PRIMARY and STANDBY

To Check the below query to confirm ARCHIVE GAP Primary and Standby  servers.

A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database.

When the logs are missing on standby difference is huge (Suppose  more than 600 logs), you have to rebuild the standby database from scratch.

Please use below query to find out archive gap on Standby STEP 1:

set time on
set lines 200

prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
;

OUTPUT ARCHIVE:-

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         2                  31852                 31852          50
         1                  49127                 49127          50

prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
exit

Find the SCN on the PRIMARY STEP 2:



SQL> select current_scn from v$database;



 



CURRENT_SCN



———–  



222271661





Find the SCN on the STANDBY:



 



SQL> select current_scn from v$database;



CURRENT_SCN



———–  



223762173


Clearly you can see there is difference








In Primary Server will take increment level  backup with SCN STEP 3:-





run {


allocate channel c1 type disk format ‘/oradata/ora_backup/rman/QTSMMDB/%U.bkp’;


backup  AS COMPRESSED BACKUPSET  incremental from
scn 223762173 database;





}





run


{


allocate channel c1 type disk format



‘/oradata/ora_backup/rman/QTSMMDB/%U.bkp’;


backup current controlfile for standby;






}









Stop and shutdown the managed standby apply process STEP 4:


SQL> alter database recover managed standby database cancel;


Database altered.


Shutdown the standby database


SQL> shut immediate





Step 5:-


Copy the backup dump to  standby instance.






step 6:-






Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands STEP 7:





$ rman target /





RMAN> startup nomount;






RMAN> catalog start with ‘/oradata/ora_backup/Standby/c-3875280703-20150907-02’;





RMAN> restore standby controlfile from


'/oradata/ora_backup/Standby/c-3875280703-20150907-02';





RMAN> sql 'alter database mount standby database';





RMAN> catalog start with ‘/oradata/ora_backup/Standby/’;





PERFORM RECOVER:






RMAN> recover database;





Start managed recovery process STEP 8:





SQL> alter database recover managed standby database disconnect from session;





Database altered.





Check the SCN’s in primary and standby it should be close to each other.