Viewing RMAN jobs status and output
extracts of this post have been taken from
Backup jobs’ status and metadata
A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views.
These views show past RMAN jobs as well as jobs currently running. Once the jobs complete backup sets,
metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.
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_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" select 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, 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;
CF: Number of controlfile backups included in the backup set
DF: Number of datafile full backups included in the backup set
I0: Number of datafile incremental level-0 backups included in the backup set
I1: Number of datafile incremental level-1 backups included in the backup set
L: Number of archived log backups included in the backup set
OUT INST: Instance where the job was executed and the output is available (see below)
Another important thing to note is that in a RAC environment some fields for a RUNNING backup job may contain invalid
information until the backup job is finished. To get consistent information, run this query on the node where the backup is running.
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB" from (select trunc(bp.completion_time) ctime , backup_type , round(sum(bp.bytes/1024/1024),2) bsize from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = 'A' group by trunc(bp.completion_time), backup_type) order by 1, 2;
Backup set details
Once you found the general information about the backup sets available, you may need to get more information about
the backup sets for one particular backup job. Each backup job is uniquely identified by (SESSION_RECID, SESSION_STAMP), which are listed by the query above.
The query below retrieves details for a backup job, given a pair of values for (SESSION_RECID, SESSION_STAMP):
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;
From Recovery Catalog
RMAN obtains backup and recovery metadata from a target database control file and stores it in the tables of the recovery catalog. The recovery catalog views are derived from these tables. Note that the recovery catalog views are not normalized or optimized for user queries.In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST, REPORT, and SHOW commands.
If you have ten different target databases registered in the same recovery catalog, then any query of the catalog views show the metadata for all incarnations of all ten databases. You often have to perform complex selects and joins among the views to extract usable information about a database incarnation.
Most of the catalog views have a corresponding V$ view in the database. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding V$ view is that each recovery catalog view contains metadata about all the target databases registered in the recovery catalog. The V$ view contains information only about itself.
set pages 2000 lines 200 COL STATUS FORMAT a9 COL hrs FORMAT 999.99 select DB_NAME, 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 RC_RMAN_BACKUP_JOB_DETAILS –where DB_NAME=’SBLTPS’ order by DB_NAME,SESSION_KEY;