RMAN Backup Status Report

The below script can be used to find the backup status of Incremental and Full DB backup as well as the archive log backup for over a period of N number of days and displays the result in a tabular format. This will provide the backup report for all the databases registered in RMAN catalog.

Schedule the below in crontab to get the report on daily basis.

Crontab :

#####Backup Status Report#############

00 08 * * * /home/oracle/scripts/Database_Backup_Report.sh rcat1 <Client Name > /tmp/Database_Backup_Report.log

#####END Backup Status Report#########

Actual Script :

xyz.abcd@oracle:/home/oracle/scripts$ cat Database_Backup_Report.sh

#!/bin/bash

export ORACLE_SID=$1

ORACLE_HOME=`cat /etc/oratab | grep -i "${ORACLE_SID}:" | awk -F: '{ print $2}'`

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

$ORACLE_HOME/bin/sqlplus -s /nolog <<!

connect / as sysdba

spool /tmp/RCAT_Backup_Status.html

prompt To: <Your Email>

prompt Subject: $2 : DATABASE BACKUP REPORT

prompt Content-type: text/html

prompt MIME-Version: 1.0

set markup html on entmap off table 'BORDER="1" BGCOLOR="#BDEDFF" style="margin: 0px auto;" cellspacing="8" style="width:100%"'

prompt Dear All,

prompt Please find the Database Backup report for the past 14 days.

prompt NOTE : Please verify the backup status for COMPLETED WITH WARNINGS/ERRORS and RUNNING WITH WARNINGS/ERRORS. It is mostly due to one or more backup channels getting terminated.However, the backup will be successfull upon completion.

prompt

set lines 32767

set pages 32767

set echo off feed off

col OBJECT_TYPE for a15

col STATUS for a20

col START_TIME for a30

col DB_NAME for a10

col STATUS for a27

col DB_NAME heading "DB|NAME" justify center

col OBJECT_TYPE heading "OBJECT|TYPE" justify center

col INCR_LEVEL heading "INCR LEVEL|0=FULL/1=INCR" justify center

col STATUS heading "STATUS" justify center

col DEVICE_TYPE heading "DEVICE|TYPE" justify center

col START_TIME heading "START|TIME" form 99999.99 justify center

col END_TIME heading "END|TIME" form 99999.99 justify center

col ELAPSED_MINS heading "ELAPSED|MINS" form 99999.99 justify center

col BLOCKS_SCANNED_GB heading "BLOCKS|SCANNED_GB" justify center

col BACKUP_SIZE_GB heading "BACKUP|SIZE_GB"justify center

BREAK ON REPORT ON DB_NAME SKIP 2

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT A.DB_NAME,A.OBJECT_TYPE,B.INCR_LEVEL,

case when A.STATUS = 'FAILED' then ''||A.STATUS||''

when A.STATUS = 'COMPLETED' then ''||A.STATUS||''

when A.STATUS LIKE 'COMPLETED WITH%' then ''||A.STATUS||''

when A.STATUS = 'RUNNING' then ''||A.STATUS||''

when A.STATUS LIKE 'RUNNING WITH%' then ''||A.STATUS||''

else ''||A.STATUS||''

end "STATUS",

A.OUTPUT_DEVICE_TYPE DEVICE_TYPE,A.START_TIME,A.END_TIME,A.ELAPSED_MINS,A.BLOCKS_SCANNED_GB,A.BACKUP_SIZE_GB

from (select DB_KEY,DB_NAME, OBJECT_TYPE, STATUS, OUTPUT_DEVICE_TYPE, TO_DATE(START_TIME, 'DD-MON-YYYY HH24:MI:SS') START_TIME, TO_DATE(END_TIME, 'DD-MON-YYYY HH24:MI:SS') END_TIME, ROUND((END_TIME-START_TIME)*24*60,0) ELAPSED_MINS, ROUND(INPUT_BYTES/1024/1024/1024,0) BLOCKS_SCANNED_GB, ROUND(OUTPUT_BYTES/1024/1024/1024,0) BACKUP_SIZE_GB from rman.rc_rman_status where OBJECT_TYPE LIKE '%DB%' AND OPERATION='BACKUP' AND START_TIME > SYSDATE - 14) A, rman.bs B

WHERE A.DB_KEY=B.DB_KEY AND A.START_TIME <= B.START_TIME AND A.END_TIME >= B.COMPLETION_TIME AND ( B.INCR_LEVEL IS NOT NULL OR A.OBJECT_TYPE LIKE '%DB%FULL%' )

GROUP BY A.DB_NAME,A.OBJECT_TYPE,B.INCR_LEVEL,A.STATUS,A.OUTPUT_DEVICE_TYPE,A.START_TIME,A.END_TIME,A.ELAPSED_MINS,A.BLOCKS_SCANNED_GB,A.BACKUP_SIZE_GB

order by

(

CASE WHEN UPPER(A.DB_NAME) LIKE 'P%' THEN '0'||A.DB_NAME

ELSE A.DB_NAME

END

),A.START_TIME DESC ;

set markup html on entmap off table 'BORDER="1" BGCOLOR="#BDEDFF" style="margin: 0px auto;" cellspacing="8" style="width:100%"'

prompt

prompt Please find the Database Archivelog Backup report for the past 3 days.

prompt

set lines 32767

set pages 32767

set echo off feed off

col OBJECT_TYPE for a15

col STATUS for a20

col START_TIME for a30

col DB_NAME for a10

col STATUS for a27

col DB_NAME heading "DB|NAME" justify center

col OBJECT_TYPE heading "OBJECT|TYPE" justify center

col INCR_LEVEL heading "INCR LEVEL|0=FULL/1=INCR" justify center

col STATUS heading "STATUS" justify center

col DEVICE_TYPE heading "DEVICE|TYPE" justify center

col START_TIME heading "START|TIME" form 99999.99 justify center

col END_TIME heading "END|TIME" form 99999.99 justify center

col ELAPSED_MINS heading "ELAPSED|MINS" form 99999.99 justify center

col BLOCKS_SCANNED_GB heading "BLOCKS|SCANNED_GB" justify center

col BACKUP_SIZE_GB heading "BACKUP|SIZE_GB"justify center

BREAK ON REPORT ON DB_NAME SKIP 2

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT A.DB_NAME,A.OBJECT_TYPE,

case when A.STATUS = 'FAILED' then ''||A.STATUS||''

when A.STATUS = 'COMPLETED' then ''||A.STATUS||''

when A.STATUS LIKE 'COMPLETED WITH%' then ''||A.STATUS||''

when A.STATUS = 'RUNNING' then ''||A.STATUS||''

when A.STATUS LIKE 'RUNNING WITH%' then ''||A.STATUS||''

else ''||A.STATUS||''

end "STATUS",

A.OUTPUT_DEVICE_TYPE DEVICE_TYPE,A.START_TIME,A.END_TIME,A.ELAPSED_MINS,A.BLOCKS_SCANNED_GB,A.BACKUP_SIZE_GB

from (select DB_KEY,DB_NAME, OBJECT_TYPE, STATUS, OUTPUT_DEVICE_TYPE, TO_DATE(START_TIME, 'DD-MON-YYYY HH24:MI:SS') START_TIME, TO_DATE(END_TIME, 'DD-MON-YYYY HH24:MI:SS') END_TIME, ROUND((END_TIME-START_TIME)*24*60,0) ELAPSED_MINS, ROUND(INPUT_BYTES/1024/1024/1024,0) BLOCKS_SCANNED_GB, ROUND(OUTPUT_BYTES/1024/1024/1024,0) BACKUP_SIZE_GB from rman.rc_rman_status where OBJECT_TYPE LIKE '%ARC%' AND OPERATION='BACKUP' AND START_TIME > SYSDATE - 3) A

GROUP BY A.DB_NAME,A.OBJECT_TYPE,A.STATUS,A.OUTPUT_DEVICE_TYPE,A.START_TIME,A.END_TIME,A.ELAPSED_MINS,A.BLOCKS_SCANNED_GB,A.BACKUP_SIZE_GB

order by

(

CASE WHEN UPPER( A.DB_NAME ) LIKE 'P%' THEN '0'||A.DB_NAME

ELSE A.DB_NAME

END

),A.START_TIME DESC ;

-- Signature -------

prompt Kind Regards

spool off

/usr/sbin/sendmail -t </tmp/RCAT_Backup_Status.html

exit

OUTPUT :

OracleHunt

Search This Blog

RMAN Backup Status Report

Comments

Post a Comment