Script to Monitor Dataguard Log Gap

=> SQL to fetch the log gap

	
[oracle@hostname ~]$ cat /home/oracle/scripts/standby-lag.sql
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') WHEN,': DR is ' || trim(to_char(1440 * (sysdate - max (next_time)),99999.99) ||' minutes behind') LAG from v$archived_log where applied ='YES';
exit;
[oracle@hostname ~]$

    

=> Shell script to email log gap every 4 hours

	

[oracle@hostname ~]$ cat /home/oracle/scripts/standby-lag.sh
#!/bin/bash
##########################################################################################
#
# Purpose     : Script to email standby lag details
# Created on  : 
# Last Update :
#
##########################################################################################
echo -e "Please find the details below : "
echo -e " "
echo -e " "
echo -e "Script Started at `date`"
echo -e " "
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin:/home/oracle/.local/bin:/home/oracle/bin
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/lib
export ORACLE_SID=PRODCDB
/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/sqlplus -s / as sysdba @/home/oracle/scripts/standby-lag.sql

echo -e " "
echo -e " "
echo -e "Script Ended at `date`"
echo -e " "
mailx -r "smtp_email@domain.com" -s "DR Sync Status" -S smtp="smtp.office365.com:587" -S smtp-use-starttls -S smtp-auth=login -S smtp-auth-user="smtp_email@domain.com" -S smtp-auth-password='smtp_email_password' -S ssl-verify=ignore -S nss-config-dir="/etc/pki/nssdb/" -c "to_user1_email@domain.com" "to_user2_email@domain.com" < /home/oracle/scripts/logs/standby-lag.log
exit;
[oracle@hostname ~]$ 


    

=> Schedule the script in crontab 

	

[oracle@hostname ~]$ crontab -l
00 */4 * * * /home/oracle/scripts/standby-lag.sh > /home/oracle/scripts/logs/standby-lag.log
[oracle@hostname ~]$