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);

Leave a Reply

Your email address will not be published. Required fields are marked *