Search This Blog

Monday, February 27, 2012

Script to Check Physical Standby in Sync with the Primary

http://shrikantrao.wordpress.com/2011/08/03/script-to-check-physical-standby-in-sync-with-the-primary/
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=861595.1

A simple script, on single click will provide us the status of DR sync with primary database.

Script 1) .bat file

sqlplus "sys/sys@prod as sysdba" @E:\DRSTATUS\onproduction.sql
sqlplus "sys/sys@standby as sysdba" @E:\DRSTATUS\onstandby.sql >> E:\cloverdba1_Clover_report\DRSTATUS\drsysn.out

I am appending the second output (for standby) to the same file. So that the output for both will be displayed in a single .out file.

Script 2) onproduction.sql file (On Primary)

set time on
set lines 200

spool E:\DRSTATUS\drsysn.out

prompt +++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++

select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Date from dual;
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

spool off
exit

Script 3) onstandby.sql file (On Physical Standby)

set time on
set lines 200

prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON 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#
;

prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

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

Explanation:

1. To check availability of redo on standby. This can be done, by checking the output of “last Sequence Generated” in script 2(at primary) with “Last Sequence Received” in script 3(at standby). If the value differs that shows the archive is missing on standby.
2. To Check for redo applied on standby. The column “difference” in script 3(at standby) should be 0. This specifies that the primary is in sync with physical standby.
3. Second query of 3rd script will provide detail on archive gap, which redo is still missing at standby.

To demo the archive lag, I have cancelled managed recovery. This will show the number of lag in “difference” column (7 in my case). The Output file will be displayed as,

+++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++

TO_CHAR(SYSDATE,'DD-MON-YYYYH
---------------------------------------------------------------------------
02-aug-2011 19:03:58

Elapsed: 00:00:00.00

Last Sequence Generated Thread
----------------------- ----------
582535 1

Elapsed: 00:00:00.09

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Aug 2 19:08:05 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

++++++++++++++++++++LAST SEQUENCE RECEIVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 582535 582528 7

Elapsed: 00:00:02.01
++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

no rows selected

Elapsed: 00:00:34.06
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Ref: How To Check Whether Physical Standby is in Sync with the Primary? [ID 861595.1]

I found out a nice shell script while googling, would like to share,

#
# Script to check if standby database is in sync and in read only with apply mode
#
# Purpose script:
# This script checks if standby and primary are in sync and if standby is in mode "READ ONLY WITH APPLY"
# violations will be mailed to recepients defined in this script.
#
# Usage script:
# Place this script on the standby database server in /home/oracle/scripts
# The following changes will be necessary when implementing this script
# 1: CONNECTRSTR refers to the connectstring to connect to the primary db from the standby db
# 2: ORACLE_SID referts to the oracle SID of the standby database
# 3: ORACLE_HOME refers to the oracle HOME of the standby database
#

#!/usr/bin/ksh
. /etc/profile

DBSID=PROD # Database sid
CONNECTSTR=scott/tiger@PROD # Connect string prod
MAILADR=scott.tiger@everywhere.com # mail
NIETERG=2 # allowed difference between standby log and primary log sequence#

# set the environment to the standby database
export ORACLE_SID=PROD
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

# declare functions to query v$archived_log and other checks

runsqlstb()
{
sqlplus -S << TOTZOVER / as sysdba set heading off select max(sequence#) from v\$archived_log where applied='YES' and dest_id=2 ; exit TOTZOVER } runsqlprd() { sqlplus -S ${CONNECTSTR} <> mailtempl
echo logseq ${DBSID} standby database is $NUMBERSTB >> mailtempl
echo
mailx -s "Standby database out of sync " ${MAILADR} mailtempl
mailx -s "Fout in script " ${MAILADR} mailtempl
errtrap
fi

NUMBERPRD=`runsqlprd`
if [ $? -ne 0 ]
then
echo Something wrong with ${DBSID} > mailtempl
errtrap
fi

echo standby database is ${NUMBERSTB}
echo productie database is ${NUMBERPRD}

VERSCHIL=`expr ${NUMBERPRD} - ${NUMBERSTB}`
echo Standby is $VERSCHIL sequence achter
mailme

Advertisement
Related Posts with Thumbnails