#!/bin/bash
# This script is to send mail for alerting Data gaurd DR sync status of given database.
# Assumption smtb confirgured,TNS entry of db in tnsname.ora,created DIR's

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:$PATH
export SCRIPT_DIR=/u01/app/oracle/util/scripts
export LOG_DIR=/u01/app/oracle/util/log
export TNS_ADMIN=/u01/app/oracle/util/scripts

CURR_DATE=`date '+%m/%d/%y_%H:%M'`

if [ -e ${LOG_DIR}/DR_Sync_status.txt ]
then
    `rm   ${LOG_DIR}/DR_Sync_status.txt `
fi
if [ -e ${LOG_DIR}/DR_Sync_output.log ]
then
    `rm ${LOG_DIR}/DR_Sync_output.log `
fi

html_head="Subject: DR SYNC REPORT for ${CURR_DATE}
\nMIME-Version: 1.0 \nContent-Type: text/html \nContent-Disposition: inline \n
<html>
<head>
<meta http-equiv=\"Content-Type\" content=text/html;
charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
DR SYNC STATUS REPORT ${CURR_DATE}
</title>
</head>"
html_tail='<br><a><br>
<br> </a></body></html>'

touch ${LOG_DIR}/DR_Sync_status.txt

echo "<br><br>" >> ${LOG_DIR}/DR_Sync_status.txt
for SID in DBSID1 DBSID2 DBSID3 DBSID4
do
echo $SID
echo "<br><h1>DR SYNC STATUS REPORT FOR DATABASE: ${SID} </h1>" >> ${LOG_DIR}/DR_Sync_status.txt
sqlplus -s username/password@${SID} << EOF
set feed off term off trims on linesize 300 pages 300 echo off underline off heading on
set markup html on
spool ${LOG_DIR}/DR_Sync_output.log
col name for a10
set und off
col INST_NAME for a10
col TIME_APPLIED for a25
SELECT INST_NAME,LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED, LOG_ARCHIVED - LOG_APPLIED LOG_GAP FROM
(SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME FROM GV\$INSTANCE ORDER BY INST_ID) NAME, (SELECT   INST_ID,
PROTECTION_MODE, SYNCHRONIZATION_STATUS FROM GV\$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2 ORDER BY INST_ID) STAT,
             (SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED FROM GV\$ARCHIVED_LOG WHERE DEST_ID = 1
AND ARCHIVED = 'YES' AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID) FROM GV\$ARCHIVED_LOG WHERE DEST_ID = 1
AND ARCHIVED = 'YES') GROUP BY THREAD# ORDER BY THREAD#) ARCH, (SELECT   THREAD#,MAX (SEQUENCE#) LOG_APPLIED,
TO_CHAR (MAX (COMPLETION_TIME), 'DD-Mon, HH24:MI:SS') TIME_APPLIED FROM GV\$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES'
AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID) FROM GV\$ARCHIVED_LOG WHERE DEST_ID = 1 AND ARCHIVED = 'YES') GROUP BY THREAD#
              ORDER BY THREAD#) APPL WHERE NAME.INST_ID = STAT.INST_ID AND NAME.INST_ID = ARCH.THREAD# AND NAME.INST_ID = APPL.THREAD#
/
spool off
exit
EOF
cat ${LOG_DIR}/DR_Sync_output.log |grep -v 'SQL>'|grep -v 'SQL&gt' >> ${LOG_DIR}/DR_Sync_status.txt
done
if [ -e ${LOG_DIR}/DR_REPORT.html ]
then
    `rm   ${LOG_DIR}/DR_REPORT.html `
fi

echo -e $html_head > ${LOG_DIR}/DR_REPORT.html
cat ${LOG_DIR}/DR_Sync_status.txt >> ${LOG_DIR}/DR_REPORT.html
echo $html_tail >> ${LOG_DIR}/DR_REPORT.html
rm ${LOG_DIR}/DR_Sync_output.log
rm ${LOG_DIR}/DR_Sync_status.txt
sendmail -v username@mailid.com < ${LOG_DIR}/DR_REPORT.html

################
Sample Out put