How To Check Whether Physical Standby is in Sync with the Primary

1. Check for GAP on standby
2. Check redo received on standby
3. Check redo applied on standby

Solution

Execute following queries:

A. On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

Thread     Last Sequence Generated
---------- -----------------------
1          19
2          13
3          11

B. On Physical 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#
ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1          19                     19                    0
2          13                     13                    0
3          11                     11                    0

C. On Physical Standby

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

No rows selected

Now perform following checks:

1. Check for GAP

If query C returns any row then this means there are some archive log missing on standby.

Example:
========

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

Thread     Low Sequence High Sequence
---------- ------------ -------------
1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.
If query C does not returns any row and output is no row selected than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of Last Sequence Generated in query A with Last Sequence Received in query B for all threads.
If both values are same than this means that standby has received the last sequence generated on primary.
If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

Example:
========

If Last Sequence Generated in query A shows value 25 for thread 1 and Last Sequence Received in query B shows value 20 for thread 1 than this means sequence 21 to 25 are missing on standby. Hence standby is not in sync with the primary.

3. Check for redo applied on standby

If value of Difference in query B is 0 than this means all the redo received on primary is applied on standby. Hence we can says standby is in sync with primary.
If value of Difference in query B is not 0 than this means all the redo received on primary is not applied on standby. Hence we can says standby is not in sync with primary

Example:
========

Check for the redo applied and recieved
=======================================

Execute the below query on the Primary database

SQL> SELECT THREAD# Thread,SEQUENCE# Last Sequence Generated FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1;

Thread Last Sequence Generated

1                      21
1                      21

Execute the belowquery on the Standby database

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                     21                    21          0

Compare value of Last Sequence Generated in query executed on Primary with Last Sequence Received in query executed on Standby for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.
If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

Check for GAP
=============

On physical standby,execute the below SQL. If it does not returns any row and output is no row selected than this means there is no archive gap on standby.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected