Script to Monitor RMAN Backup Status and Timings

June 30, 2018
Being a DBA, you often asked to check the status of  RMAN backup job details. You can monitor progress and the timing of the backup using v$rman_backup_job_details but also you can use the below script to find the detailed information on RMAN job like completion time of full, incremental & archivelog backups. To check percentage completion, you can use V$SESSION_LONGOPS and v$rman_backup_job_details, to monitor the current executing RMAN jobs and the status of the previously completed backups.

Must Read:

How to Backup Oracle Database in Noarchivelog Mode

Check RMAN Backup Log Files

RMAN STATUS

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

Must Read: