How to check if Oracle Physical Standby is in sync with Primary

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

 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$ARCHI
VED_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#) “Differenc
e” 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#)) ARC
H, (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

Advertisements

About mpoojari
Oracle Database/Applications Administrator

2 Responses to How to check if Oracle Physical Standby is in sync with Primary

  1. is there a way you can check the status of the standby if it is at synch with primary from primary database ?

    Regards
    Sai

    • mpoojari says:

      Run the following queries,

      set feedback off
      COL host_name FOR a20
      set lines 100
      SELECT
      d.name, i.host_name, d.open_mode, d.database_role, d.log_mode,TO_CHAR(startup_time,’DD-MON-YYYY HH24:MI:SS’) startup_time
      FROM
      v$database d, v$instance i
      WHERE
      d.name=i.instance_name
      /
      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
      /
      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
      /

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: