Crontab - Physical standby Archive sync details on mail

Crontab - Physical standby Archive sync details on mail 

Crontab Details : 

Check in every 6 hrs and send mail 

0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check1.sh

Script Output:

oracle@test01:~> cat /u04/MasterDB/scripts/DR_Check/dr_check1.sh

#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/DR_check/DR_Status.log
DR_log=/u04/MasterDB/scripts/DR_Check/dr_1.log
printf "to:abc@tech.com\n" > $DR_log
#printf "cc:abc@tech.com\n" >> $DR_log
printf "subject:  Server  DR HOURLY STATUS REPORT " >> $DR_log

printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log

sqlplus -s "/as sysdba" <<EOF >> $DR_log

set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
PROMPT
PROMPT
select process,status,sequence# from v\$managed_standby;
PROMPT
PROMPT

PROMPT
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V\$LOG_HISTORY GROUP BY THREAD#;
PROMPT

PROMPT
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#;

EOF
/usr/sbin/sendmail -t < $DR_log

oracle@test01:~>

OUTPUT :

DR Status report for test01 at Tue Nov 12 00:00:01 CET 2019

NAME     INSTANCE_NAME    DB_UNIQUE_NAME                      OPEN_MODE        DATABASE_ROLE            CURRENT_SCN               FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

PRODB  PRODB_DR     PRODB_DR                                  READ WRITE         PRIMARY               YES                           469372332

PROCESS   STATUS     SEQUENCE#

--------- ------------ ----------

ARCH     OPENING            28118

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

LNS        OPENING            28119

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

PROCESS   STATUS     SEQUENCE#

--------- ------------ ----------

ARCH     CONNECTED              0

ARCH     CLOSING            28122

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

PROCESS   STATUS     SEQUENCE#

--------- ------------ ----------

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CONNECTED              0

ARCH     CLOSING            28118

ARCH     CONNECTED              0

31 rows selected.

   THREAD# LAST_APPLIED_LOG

---------- ----------------

             1               28125

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

             1                         28125                          28125              0