Home > Oracle > Viewing RMAN jobs status and output

Viewing RMAN jobs status and output

extracts of this post have been taken from

http://www.pythian.com/blog/viewing-rma-jobs-status-and-output/


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.

V$BACKUP_SET
V$BACKUP_SET_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
GV$RMAN_OUTPUT


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, 'Monday', 2, 'Tuesday',
                                     3, 'Wednesday', 4, 'Thursday',
                                     5, 'Friday', 6, 'Saturday',
                                     7, 'Sunday') 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.

Backup sizes


 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;
About these ads
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 121 other followers

%d bloggers like this: