Monitoring Data Guard Log Shipping using Shell Script with Mail alert

Version:- Oracle 11g
All the below scripts are stored and will run from oracle user & from Primary Database.

[oracle@server1 ~]$ vi /home/oracle/dg.sql
Set linesize 222
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ),
(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
exit;

:wq

Now lets create the shell-script which will monitor the log gap in between of primary and standby database:-
From oracle user.
[oracle@server1 ~]$ vi /home/oracle/dgmonitor.sh

#!/bin/bash

. .bash_profile

sqlplus -S / as sysdba @/home/oracle/dg.sql > /home/oracle/loggap.txt

loggap=`cat /home/oracle/loggap.txt | awk '{print $6}'`

if [[ $loggap -gt 1 ]]; then

cat /home/oracle/loggap.txt|mailx -s "Attention!! There is a loggap between Primary and Standby Database at `date` BETWEEN CCUINE23 AND CCUINE103 DATABASE" support@xyz.com

fi

rm -r /home/oracle/loggap.txt

exit 0

:wq

[oracle@server1 ~]$ chmod 777 dgmonitor.sh

Now schedule it on crontab to execute the script for every 15mins :-
[oracle@server1 ~]$  crontab -e
*/15 * * * * /home/oracle/dgmonitor.sh > /dev/null