Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


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

    Share

    Mary
    Guest

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

    Post  Mary on Sat Apr 24, 2010 8:21 pm

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

    Execute following queries:

    On Primary
    Code:

    set pages 1000
    set lines 120
    column DEST_NAME format a20
    column DESTINATION format a35
    column ARCHIVER format a10
    column TARGET format a15
    column status format a10
    column error format a15
    select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest
    where DESTINATION is NOT NULL
    /


    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
    /


    At Physical Standby

    Code:

    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#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP
    /


    select process,status,sequence# from v$managed_standby
    /



    Compare value of "Last Sequence Generated" in query at primary with "Last Sequence Received" in query at 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.

      Current date/time is Sat Aug 19, 2017 7:55 pm