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:
Below script will report you the percentage of completion along with sid and serial#.
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
