#!/bin/bash
# This script is to send mail for RMAN Backup monitroing.
# 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}/rman_job_status.txt ]
then
    `rm   ${LOG_DIR}/rman_job_status.txt `
fi
if [ -e ${LOG_DIR}/rman_job_output.log ]
then
    `rm ${LOG_DIR}/rman_job_output.log `
fi

html_head="Subject: RMAN JOB 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>
RMAN Job Run Details for The Past 7 Days
</title>
</head>"
html_tail='<br><h2>Details of Columns:</h2><a><br>
SESSION RECID & SESSIONSTAMP - both columns used to pull the rman job log from V\$RMAN_OUTPUT view.<br>
CF - Number of controlfile backups included in the backup set <br>
DF - Number of datafile full backups included in the backup set <br>
I0 - Number of datafile incremental level-0 backups included in the backup set <br>
I1 - Number of datafile incremental level-1 backups included in the backup set<br>
L - Number of archived log backups included in the backup set<br>
*If a particular report table is missing for a Database. Then no RMAN jobs were taken for the past 3 days<br> </a></body></html>'

touch ${LOG_DIR}/rman_job_status.txt

echo "<br><br>" >> ${LOG_DIR}/rman_job_status.txt
for SID in DBSID1 DBSID2 DBSID3 DBSID4
do
echo $SID
echo "<br><h1>THE RMAN backup report for the Database: ${SID} </h1>" >> ${LOG_DIR}/rman_job_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}/rman_job_output.log
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status entmap off
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
   j.session_recid, j.session_stamp,
   to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
   to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
   (j.output_bytes/1024/1024) output_mbytes,
   case
   when j.status='COMPLETED' then '<font color="green">'||j.status||'</font>'
   else '<font color="red">'||j.status||'</font>'
   end status,
   j.input_type,
   decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                      3, 'Tuesday', 4, 'Wednesday',
                                      5, 'Thursday', 6, 'Friday',
                                      7, 'Saturday') dow,
   j.elapsed_seconds, j.time_taken_display
from V\$RMAN_BACKUP_JOB_DETAILS j
where j.start_time > trunc(sysdate)-7 and j.input_type <> 'ARCHIVELOG'
order by 3 desc
/
spool off
exit
EOF
cat ${LOG_DIR}/rman_job_output.log |grep -v 'SQL>'|grep -v 'SQL&gt' >> ${LOG_DIR}/rman_job_status.txt
done
if [ -e ${LOG_DIR}/RMAN_REPORT.html ]
then
    `rm   ${LOG_DIR}/RMAN_REPORT.html `
fi

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

############
Sample Output

THE RMAN backup report for the Database: DBSID1

SESSION RECID	SESSION STAMP	START_TIME	END_TIME	OUTPUT MBYTES	STATUS	INPUT_TYPE	DOW	ELAPSED SECONDS	TIME TAKEN
17516	853048802	2014-07-16 06:00:53	2014-07-16 06:37:49	32,671	COMPLETED	DB FULL	Wednesday	2216	00:36:56
17413	852962402	2014-07-15 06:00:53	2014-07-15 06:38:52	32,570	COMPLETED	DB FULL	Tuesday	2279	00:37:59
17314	852876002	2014-07-14 06:00:57	2014-07-14 06:42:05	32,489	COMPLETED	DB FULL	Monday	2468	00:41:08
17210	852789602	2014-07-13 06:00:57	2014-07-13 06:40:05	32,572	COMPLETED	DB FULL	Sunday	2348	00:39:08
17108	852703202	2014-07-12 06:00:55	2014-07-12 06:38:11	32,535	COMPLETED	DB FULL	Saturday	2236	00:37:16
17005	852616801	2014-07-11 06:00:43	2014-07-11 06:38:30	32,446	COMPLETED	DB FULL	Friday	2267	00:37:47
16901	852530402	2014-07-10 06:00:58	2014-07-10 06:40:01	32,394	COMPLETED	DB FULL	Thursday	2343	00:39:03