LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon