How To List RMAN Jobs

dbtut

6 years ago

It is often time consuming job to gather detailed information about the backups of Oracle databases with RMAN. You need to collect information from many different environments. However, it is easier to access RMAN backup information with queries.

You can reach detailed information about the backups taken in the last x days with the following query.

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 99,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a20
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col DOW format a10
col START_TIME format a20
col END_TIME format a20
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;

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 99,999,999 heading "OUTPUT|MBYTES"

col session_recid for 999999 heading "SESSION|RECID"

col session_stamp for 99999999999 heading "SESSION|STAMP"

col status for a20

col time_taken_display for a10 heading "TIME|TAKEN"

col output_instance for 9999 heading "OUT|INST"

col DOW format a10

col START_TIME format a20

col END_TIME format a20

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;

Using the session_recid and session_stamp information obtained from the above query, you can access the backupset details created in the corresponding RMAN session by the following query.

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;

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;

By using session_recid and session_stamp information, it is possible to access the outputs of the operations performed in the corresponding RMAN session with the following query.

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;

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;

You can also find detailed information about the running RMAN jobs.

set lines 200 pages 2000
col STATUS format a25
col dev format a10
col START_TIME format a16
col END_TIME format a16
col hrs format 99.99
col INBYTES format a10
col OUTBYTES format a10
select SESSION_KEY, INPUT_TYPE, STATUS, output_device_type DEV,
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_display inbytes, output_bytes_display outbytes
from V$RMAN_BACKUP_JOB_DETAILS
where status='RUNNING'
order by session_key;

set lines 200 pages 2000

col STATUS format a25

col dev format a10

col START_TIME format a16

col END_TIME format a16

col hrs format 99.99

col INBYTES format a10

col OUTBYTES format a10

select SESSION_KEY, INPUT_TYPE, STATUS, output_device_type DEV,

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_display inbytes, output_bytes_display outbytes

from V$RMAN_BACKUP_JOB_DETAILS

where status='RUNNING'

order by session_key;