LLI Wiki — My Digital Knowledge Base

Monitoring RMAN backup status and output with V$ views

SQL queries to check the status, progress, size and output of Oracle RMAN backup jobs.

This guide collects the queries used to follow a running or finished RMAN backup from Oracle dynamic views, without opening the log file on the server.

Principle

RMAN keeps its metadata in the control file, exposed through the V$RMAN_% views (past and running jobs) and the V$BACKUP_% views (backup sets and pieces, added once sets complete). They are no help if the database is lost and must be restored: they are meant for day-to-day monitoring.

Views used:

View Content
V$RMAN_BACKUP_JOB_DETAILS one row per backup job (status, duration, volumes)
V$BACKUP_SET backup sets
V$BACKUP_SET_DETAILS details per backup set
GV$RMAN_OUTPUT text output of RMAN jobs (memory only)
V$SESSION_LONGOPS long operations, including RMAN channel progress

Step 1: job status

set lines 300
set pages 1000
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;

This covers every backup type (full, incremental, archived logs). Note SESSION_RECID and SESSION_STAMP of the job you are interested in: together they identify it uniquely for the next steps.

To list only full and incremental backups, excluding archived log backups, filter on the input type:

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
where input_type='DB INCR'
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: active RMAN channels

This query shows the sessions and server processes behind RMAN channels, with their wait event.

set lines 120
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
SELECT s.sid, p.spid, s.client_info, s.event, s.seconds_in_wait secs, s.p1, s.p2, s.p3
  FROM v$process p, v$session s
 WHERE p.addr = s.paddr
   AND s.client_info LIKE 'rman channel=%';

Step 4: job output (log)

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;

Supply the SESSION_RECID and SESSION_STAMP found in step 1. The output contains, for example, the command run, the allocated channels, the pieces created and the Finished backup lines.

Multi-day summary

The following query combines the information usually needed to verify backups in one result: status, duration, output size, day of week, executing instance and number of pieces per kind.

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col SESSION_KEY for 999999 heading "SESSION|KEY"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col OUTPUT_DEVICE_TYPE for a8 trunc heading "DEVICE|TYPE"
select
  j.session_key,j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type, j.OUTPUT_DEVICE_TYPE,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

&NUMBER_OF_DAYS is prompted at run time (how many days to look back). Computed columns:

Column Meaning
CF number of control file backups in the set
DF number of full datafile backups
I0 number of level-0 incremental backups
I1 number of level-1 incremental backups
L number of archived log backups
OUT INST instance where the job ran and where its output is available

Backup set details for a job

For a given (SESSION_RECID, SESSION_STAMP) pair:

set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

Backed-up volumes

set pages 2000 lines 200
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select 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,
  INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
  OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
  OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY;

Notes

Mastodon LLIMastodon SocialFosstodon