Rman Backup Report in HTML Format

Running Rman backup is one of our regular DBA job, but to check if the backups are running fine on daily basis, below script will generate a report and send mails with the report in HTML format. 

SQL Script:

-----------------------Start of rman_status.sql----------------------------------

--Script Location :/app/oracle/scripts/rman_report/rman_status.sh

set pagesize 0

set line 120

set heading off

set feedback off

set verify off

spool /app/oracle/scripts/rman_report/rman_out.txt

select db_name||'|'||INPUT_TYPE||'|'||STATUS||'|'||to_char(START_TIME,'mm/dd/yy hh24:mi')||'|'|| to_char(END_TIME,'mm/dd/yy hh24:mi')||'|'||round((end_time-start_time)*24*60)||'|'||round((INPUT_BYTES/1024/1024/1024),2)||'|'||round((OUTPUT_BYTES/1024/1024/1024),2)||'|'

from rman.rc_RMAN_BACKUP_JOB_DETAILS where input_type in ('DB INCR','DB FULL') and (db_name,start_time) in (select db_name,max(START_TIME) from rman.rc_RMAN_BACKUP_JOB_DETAILS where input_type in ('DB INCR','DB FULL') group by db_name)order by status desc,start_time;

spool off

-----------------------End of rman_status.sql----------------------------------

Shell Script:

----------------------Start of rman_report.sh -------------------------------

#Script Location :/app/oracle/scripts/rman_report/rman_report.sh

export ORACLE_HOME=/app/oracle/product/11.2.0.3.1

export ORACLE_SID=RMANCAT

export PATH=$ORACLE_HOME/bin:$PATH

cd /app/oracle/scripts/rman_report

$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF

@/app/oracle/scripts/rman_report/rman_status.sql

exit

EOF

rm /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo " " >> /app/oracle/scripts/rman_report/rman_report.html

echo "

" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "RMAN Backup Summary   DB_NAMEBACKUP_TYPE STATUS

START_TIME END_TIME   NET_TIME(MIN) DB_SIZE(GB)   BKP_PIECE(GB) " >> /app/oracle/scripts/rman_report/rman_report.html

cat /app/oracle/scripts/rman_report/rman_out.txt|awk  -F '|' '{ if ($3 == "COMPLETED" || $3 == "RUNNING") print " "$1""$2"  "$3"  "$4"  "$5" "$6" "$7" "$8"   "; else print " "$1""$2" "$3" "$4" "$5" "$6" "$7" "$8" " }' >> /app/oracle/scripts/rman_report/rman_report.html

outputFile="/app/oracle/scripts/rman_report/rman_report.html"

(

echo "From: "

echo "To: "

echo "MIME-Version: 1.0"

echo "Subject: RMAN Backup Summary Report"

echo "Content-Type: text/html"

cat $outputFile

) | sendmail -t

This script is proven working in AIX Server.

----------------------End of rman_report.sh -------------------------------