Script to Monitor RMAN Backup Status and Timings

Being a DBA, you often asked to check the status of  RMAN backup job details. There are many ways to monitor the progress of the backup but you can use the below scripts to monitor the status of the RMAN job like full, incremental & archivelog backups.

You can use views like v$rman_backup_job_details and V$SESSION_LONGOPS to monitor the current executing RMAN jobs and the status of the previously completed backups.

SQL> desc v$rman_backup_job_details
 Name                                 Null?        Type
 ----------------------------- -------- --------------------
 SESSION_KEY                              NUMBER
 SESSION_RECID                          NUMBER
 SESSION_STAMP                         NUMBER
 COMMAND_ID                             VARCHAR2(33)
 START_TIME                                DATE
 END_TIME                                    DATE
 INPUT_BYTES                             NUMBER
 OUTPUT_BYTES                         NUMBER
 STATUS_WEIGHT                       NUMBER
 OPTIMIZED_WEIGHT                  NUMBER
 OBJECT_TYPE_WEIGHT                    NUMBER
 OUTPUT_DEVICE_TYPE                     VARCHAR2(17)
 AUTOBACKUP_COUNT                       NUMBER
 BACKED_BY_OSB                          VARCHAR2(9)
 AUTOBACKUP_DONE                        VARCHAR2(9)
 STATUS                                 VARCHAR2(69)
 INPUT_TYPE                             VARCHAR2(39)
 OPTIMIZED                              VARCHAR2(9)
 ELAPSED_SECONDS                        NUMBER
 COMPRESSION_RATIO                      NUMBER
 INPUT_BYTES_PER_SEC                    NUMBER
 OUTPUT_BYTES_PER_SEC                   NUMBER
 INPUT_BYTES_DISPLAY                    VARCHAR2(4000)
 OUTPUT_BYTES_DISPLAY                   VARCHAR2(4000)
 INPUT_BYTES_PER_SEC_DISPLAY            VARCHAR2(4000)
 OUTPUT_BYTES_PER_SEC_DISPLAY           VARCHAR2(4000)
 TIME_TAKEN_DISPLAY                     VARCHAR2(4000)


This script will report status of current as well as completed backup details like full, incremental and archivelog backups:

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
        585 ARCHIVELOG    COMPLETED 01/08/15 06:00 01/08/15 06:02     .03
        591 ARCHIVELOG    COMPLETED 01/08/15 12:00 01/08/15 12:01     .02
        596 ARCHIVELOG    COMPLETED 01/08/15 18:01 01/08/15 18:02     .03
        601 DB INCR       FAILED    01/08/15 20:00 01/09/15 01:47    5.79
        603 ARCHIVELOG    COMPLETED 01/09/15 06:00 01/09/15 06:07     .12
        608 ARCHIVELOG    COMPLETED 01/09/15 12:00 01/09/15 12:09     .16
        613 ARCHIVELOG    COMPLETED 01/09/15 15:07 01/09/15 15:25     .29

Below script will report you the percentage of completion along with sid and serial#.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;


 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 22        31         1          8225460    18357770   45.21