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
