Hi all,

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

One Response to “How To Check Whether Physical Standby is in Sync with the Primary or Not?”

  1. sunil kumar Says:

    by using shell script u can monitor standby this script run on production(primary)
    ——————————————————————
    Simply what this shell script does: Check if there is a latency of more then 20 archive logs in log transport or log apply services by querying the V$ARCHIVE_DEST_STATUS view. If there is, send a mail to DBA’s mentioning the problem with the following information.

    “Last Archive Log Number produced on the primary side”
    “Last Archive Log Number that was sent to the standby side”
    “Last Archive Log Number that was applied on the standby side”
    ———————————————————————-
    #check_dg.sh script (works on primary DB), monitors Data Guard log Apply and log Transport services..
    #if local arc sequence is 20 greater than stby arc sequence, then there is a problem in log Transport..
    #if stby arc sequence is 20 greater than stby apply sequence, then there is a problem in log Apply..

    #set Oracle environment for Sql*Plus
    ORACLE_HOME=/oracle/product/10.2.0 ; export ORACLE_HOME
    ORACLE_SID=usagedb ; export ORACLE_SID
    PATH=$PATH:/oracle/product/10.2.0/bin

    #set working directory. script is located here..
    cd /oracle/scripts

    #Problem statement is constructed in message variable
    MESSAGE=”"

    #hostname of the primary DB.. used in messages..
    HOST_NAME=`/usr/bin/hostname`

    #who will receive problem messages.. DBAs e-mail addresses seperated with space
    DBA_GROUP=’dba1@company.com dba2@company.com

    #SQL statements to extract Data Guard info from DB
    LOCAL_ARC_SQL=’select archived_seq# from V$ARCHIVE_DEST_STATUS where dest_id=1; \n exit \n’
    STBY_ARC_SQL=’select archived_seq# from V$ARCHIVE_DEST_STATUS where dest_id=2; \n exit \n’
    STBY_APPLY_SQL=’select applied_seq# from V$ARCHIVE_DEST_STATUS where dest_id=2; \n exit \n’

    #Get Data guard information to Unix shell variables…
    LOCAL_ARC=`echo $LOCAL_ARC_SQL | sqlplus -S / as sysdba | tail -2|head -1`
    STBY_ARC=`echo $STBY_ARC_SQL | sqlplus -S / as sysdba | tail -2|head -1`
    STBY_APPLY=`echo $STBY_APPLY_SQL | sqlplus -S / as sysdba | tail -2|head -1`

    #Allow 20 archive logs for transport and Apply latencies…
    let “STBY_ARC_MARK=${STBY_ARC}+20″
    let “STBY_APPLY_MARK= ${STBY_APPLY}+20″
    if [ $LOCAL_ARC -gt $STBY_ARC_MARK ] ; then
    MESSAGE=${MESSAGE}”Error on $HOST_NAME Standby -log TRANSPORT- service! \n local_Arc_No=$LOCAL_ARC but stby_Arc_No=$STBY_ARC \n”
    fi

    if [ $STBY_ARC -gt $STBY_APPLY_MARK ] ; then
    MESSAGE=${MESSAGE}”Error on $HOST_NAME Standby -log APPLY- service! \n stby_Arc_No=$STBY_ARC but stby_Apply_no=$STBY_APPLY \n”
    fi

    if [ -n "$MESSAGE" ] ; then
    MESSAGE=${MESSAGE}”\This problem may cause the archive directories to get full!!! \n .\n ”
    echo $MESSAGE | mailx -s “$HOST_NAME DataGuard Problem” $DBA_GROUP
    fi

Leave a Reply

You must be logged in to post a comment.