1.First create the shell script mentioning all environmental variables adjusted to your environment.
vi dataguard_dr_sync_check.sh #!/bin/bash cd /u01/app/oracle/shells for k in ORCL do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$k $ORACLE_HOME/bin/sqlplus -S monitor@"$k"/xxx << EOF >> dataguard_dr_sync_check_orcl_`date +\%d\%m\%Y\%H`.log @dataguard_dr_sync_check_orcl.sql exit EOF done
2.For 2 node RAC,below will be dataguard_dr_sync_check_orcl.sql script .For 4 nodes,just add another 2 sql with thread and union it.
SET HEADING OFF SELECT 'SYNC CHECK OF '||NAME||' & '||SUBSTR(NAME,0,LENGTH(NAME)-3)||'EXA1' FROM V$DATABASE; SET HEADING ON PROMPT =================================================== SELECT THREAD, LOG_ARCHIVED,LOG_APPLIED, LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM ( SELECT THREAD# THREAD FROM V$ARCHIVED_LOG WHERE THREAD#=1), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=3 AND APPLIED='YES' and THREAD#=1 ) UNION SELECT THREAD, LOG_ARCHIVED,LOG_APPLIED, LOG_ARCHIVED-LOG_APPLIED LOG_GAP from ( SELECT THREAD# THREAD FROM V$ARCHIVED_LOG WHERE THREAD#=2 ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2 ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2 ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=3 AND APPLIED='YES' and THREAD#=2);
0 comments on “Script to sync check of applied archive log between 2 RAC databases”
//khelraja-india.com/download-app/