Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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; |
