<\/p>\n
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.<\/p>\n
You can reach detailed information about the backups taken in the last x days with the following query.<\/p>\n
set lines 220\r\nset pages 1000\r\ncol cf for 9,999\r\ncol df for 9,999\r\ncol elapsed_seconds heading \"ELAPSED|SECONDS\"\r\ncol i0 for 9,999\r\ncol i1 for 9,999\r\ncol l for 9,999\r\ncol output_mbytes for 99,999,999 heading \"OUTPUT|MBYTES\"\r\ncol session_recid for 999999 heading \"SESSION|RECID\"\r\ncol session_stamp for 99999999999 heading \"SESSION|STAMP\"\r\ncol status for a20\r\ncol time_taken_display for a10 heading \"TIME|TAKEN\"\r\ncol output_instance for 9999 heading \"OUT|INST\"\r\ncol DOW format a10\r\ncol START_TIME format a20\r\ncol END_TIME format a20\r\nselect\r\nj.session_recid, j.session_stamp,\r\nto_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,\r\nto_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,\r\n(j.output_bytes\/1024\/1024) output_mbytes, j.status, j.input_type,\r\ndecode(to_char(j.start_time, 'd'), 1, 'SUNDAY', 2, 'MONDAY',\r\n3, 'TUESDAY', 4, 'WEDNESDAY',\r\n5, 'THURSDAY', 6, 'FRIDAY',\r\n7, 'SATURDAY') dow,\r\nj.elapsed_seconds, j.time_taken_display,\r\nx.cf, x.df, x.i0, x.i1, x.l,\r\nro.inst_id output_instance\r\nfrom V$RMAN_BACKUP_JOB_DETAILS j\r\nleft outer join (select\r\nd.session_recid, d.session_stamp,\r\nsum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,\r\nsum(case when d.controlfile_included = 'NO'\r\nand d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,\r\nsum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,\r\nsum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,\r\nsum(case when d.backup_type = 'L' then d.pieces else 0 end) L\r\nfrom\r\nV$BACKUP_SET_DETAILS d\r\njoin V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count\r\nwhere s.input_file_scan_only = 'NO'\r\ngroup by d.session_recid, d.session_stamp) x\r\non x.session_recid = j.session_recid and x.session_stamp = j.session_stamp\r\nleft outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id\r\nfrom GV$RMAN_OUTPUT o\r\ngroup by o.session_recid, o.session_stamp)\r\nro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp\r\nwhere j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS\r\norder by j.start_time;<\/pre>\nUsing 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.<\/p>\n
set lines 220\r\nset pages 1000\r\ncol backup_type for a4 heading \"TYPE\"\r\ncol controlfile_included heading \"CF?\"\r\ncol incremental_level heading \"INCR LVL\"\r\ncol pieces for 999 heading \"PCS\"\r\ncol elapsed_seconds heading \"ELAPSED|SECONDS\"\r\ncol device_type for a10 trunc heading \"DEVICE|TYPE\"\r\ncol compressed for a4 heading \"ZIP?\"\r\ncol output_mbytes for 9,999,999 heading \"OUTPUT|MBYTES\"\r\ncol input_file_scan_only for a4 heading \"SCAN|ONLY\"\r\nselect\r\nd.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,\r\nto_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,\r\nto_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,\r\nd.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes\/1024\/1024) output_mbytes, s.input_file_scan_only\r\nfrom V$BACKUP_SET_DETAILS d\r\njoin V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count\r\nwhere session_recid = &SESSION_RECID\r\nand session_stamp = &SESSION_STAMP\r\norder by d.start_time;<\/pre>\nBy 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.<\/p>\n
set lines 200\r\nset pages 1000\r\nselect output\r\nfrom GV$RMAN_OUTPUT\r\nwhere session_recid = &SESSION_RECID\r\nand session_stamp = &SESSION_STAMP\r\norder by recid;<\/pre>\nYou can also find detailed information about the running RMAN jobs.<\/p>\n
set lines 200 pages 2000\r\ncol STATUS format a25\r\ncol dev format a10\r\ncol START_TIME format a16\r\ncol END_TIME format a16\r\ncol hrs format 99.99\r\ncol INBYTES format a10\r\ncol OUTBYTES format a10\r\nselect SESSION_KEY, INPUT_TYPE, STATUS, output_device_type DEV,\r\nto_char(START_TIME,'mm\/dd\/yy hh24:mi') start_time,\r\nto_char(END_TIME,'mm\/dd\/yy hh24:mi') end_time,\r\nelapsed_seconds\/3600 hrs,\r\ninput_bytes_display inbytes, output_bytes_display outbytes\r\nfrom V$RMAN_BACKUP_JOB_DETAILS\r\nwhere status='RUNNING'\r\norder by session_key;<\/pre>\n\n<\/div>\n