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
- The
CF,DF,I0,I1andLaggregations are only available for recent jobs, because they are purged from the catalog after a few days. - On RAC, some fields of a running job may be invalid until it finishes: run the query on the node where the backup is running.
- The RMAN output view exists in memory only: the output is stored neither in the control file nor elsewhere in the database, and is lost when the instance restarts.
- These queries were not tested on Oracle 10g or earlier.