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

The following tips will help you in monitoring sync between primary and standby databases.

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

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

1 2 3 4 5 6 7 8 9 10

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 19 2 13 3 11

B. On Physical Standby

1 2 3 4 5 6 7 8 9 10 11 12 13

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 19 19 0 2 13 13 0 3 11 11 0

C. On Physical Standby

1 2	SQL> 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:

1 2 3 4 5

SQL> 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

Cheers,
Sakthivel G