## Modified by Sugivaradhan
#!/bin/ksh

#This script monitors the Data Guard standby gap and sends an alert if it exceeds the threshold.
#Set variables
ORACLE_SID=$1
ORACLE_HOME=`ps -ef |grep pmon|grep $1|awk {'print "pwdx " $2 '}|sh|awk {'print $2 '}|sed 's/....$//'`
export PATH=$PATH:$ORACLE_HOME/bin
STANDBY_GAP_THRESHOLD=$2
export ORACLE_HOME PATH ORACLE_SID STANDBY_GAP_THRESHOLD

#Set output file and remove any existing log file
out_file=/home/oracle/monitor_gap_NEW.log
rm $out_file 2>/dev/null
touch $out_file

#Run SQL script to check standby gap
standby_gap()
{
sqlplus -s "/ as sysdba" > $out_file <<sqlconn
set lines 160 pages 0
col GAP for a20
select (select sysdate from dual)"TIMESTAMP",s.thread#,s.MAX_SEQ#, a.MAX_APP_SEQ#, ' '||(s.MAX_SEQ#-a.MAX_APP_SEQ#)||' ' "GAP" from
( select thread#,max(sequence#) "MAX_SEQ#" from v\$archived_log where activation#=(select activation# from v\$database) group by thread# ) s,
(select thread#,max(sequence#) "MAX_APP_SEQ#" from v\$archived_log where activation#=(select activation# from v\$database )and applied='YES' group by thread#) a where a.thread#=s.thread#;
exit
sqlconn
}
standby_gap

#Check for standby gap exceeding threshold and set alert flag if necessary
grep -v '^[[:space:]]*$' $out_file | while read LINE
do
T1=`echo $LINE | cut -f5 -d' '`
if [ $T1 -gt $STANDBY_GAP_THRESHOLD ]
then
echo "Dataguard Error: Not in Sync"
else
echo "Dataguard is in Sync"
fi
done