DR Log Apply Gap Detection

Hi Everyone,

I was working on physical standy recently to get synched up with Primary database. I have used scripts to find the apply gap between PRD and Standby. very handy script. This is working in my 12.1.0.2 DB nicely. 

This script can be run in Primary DB.

 

SQL> 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# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  38336                 38336          0

         2                  35649                 35649          0

SQL>

Below Script can be run from Standby to find the apply and transport lag lag.

col name format a20

SQL> select NAME,value from V$DATAGUARD_STATS where name in ('apply lag','transport lag');

NAME                 VALUE

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

transport lag        +00 00:00:00

apply lag            +00 00:00:00

SQL>

Let me know your thoughts on this.