Check RMAN backup job status in Oracle
SQL queries to follow finished and running RMAN backup jobs, their progress and their output.
RMAN records each backup job in the view V$RMAN_BACKUP_JOB_DETAILS. The queries below show the status of finished jobs, the progress of running jobs and the output of a running job.
Connect to RMAN
rman TARGET /
rman TARGET sys@orcl NOCATALOG
rman TARGET / CATALOG rcat_owner@catdb
The first form uses operating system authentication. The other two prompt for the password.
Step 1: status of the jobs
set lines 300
col STATUS format a22
col hrs format 999.99
select SESSION_KEY, SESSION_RECID, SESSION_STAMP, 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;
Step 2: progress of a running job
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;
Step 3: output of a running job
set lines 200
set pages 1000
select output from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;
Enter the SESSION_RECID and SESSION_STAMP values returned by step 1.
All 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;
Jobs of the last 8 days with sizes
set lines 150
set pages 900
col start for a20
col end for a20
col status format a11
col input_bytes_display format a10
col output_bytes_display format a10
col status_weight format 99999
SELECT
to_char(start_time,'YYYY.MM.DD HH24:MI:SS') "Start",
to_char(end_time,'YYYY.MM.DD HH24:MI:SS') "End",
status,
status_weight,
input_type,
input_bytes_display,
output_bytes_display
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE start_time BETWEEN (SYSDATE - 8) AND SYSDATE
ORDER BY start_time;
Notes
- To list only incremental database backups, filter with
where input_type='DB INCR'. This returns incremental backups only, not full backups and not archivelog backups. - The
V$SESSION_LONGOPSview only shows operations that are still running.