Difference between revisions of "RMAN"

Revision as of 16:16, 26 March 2013

Check the status of the database backups for the past 7 days

Run this one from sys on the database server

set pages 100
set lines 200
col operation   for a12
col backup_type for a27
col start_time  for a17
col end_time    for a17
col duration    for 90.99
col days_ago    for 90

select vrbsd.operation
,      vrbsd.command_id                              backup_type
,      to_char(vrbsd.start_time,'DD-MON-YY HH24:MI') start_time
,      to_char(vrbsd.end_time,'DD-MON-YY HH24:MI')   end_time
,      vrbsd.status                                  status
,      (vrbd.elapsed_seconds/3600)                   duration
,      trunc(sysdate)-trunc(vrbsd.start_time)        days_ago
from   v$rman_backup_job_details     vrbd
,      v$rman_backup_subjob_details  vrbsd
where  1=1
and    vrbd.session_key = vrbsd.session_key
and    vrbd.input_type='DB INCR'
and    vrbsd.start_time > sysdate -8
order  by vrbsd.start_time desc

Check the status of the database backups from the RMAN catalog

Run this one from the RMAN catalog server

select s.db_name
,      s.status
,      max(s.start_time) start_time
,      max(round(s.mbytes_processed/1024))  "processed_data(GB)"
,      max(round((end_time-start_time)*60*24)) "duration(min)"
,      trunc(sysdate)-trunc(s.start_time)   days_since_last_backup
from   rc_rman_status s 
where  1=1
and    lower(s.operation)   = 'backup'
and    lower(s.object_type) ='db full'
and    s.start_time =
       (
       select max(md.start_time)
       from   rc_rman_status md 
       where  1=1
       and    lower(md.operation)   = 'backup'
       and    lower(md.object_type) ='db full'
       and    md.db_name = s.db_name
       )
group  by s.db_name
,      s.object_type
,      s.operation
,      s.status
,      trunc(sysdate)-trunc(s.start_time)
order  by trunc(sysdate)-trunc(s.start_time) desc

Which tapes are the backups on?

Not really necessary as RMAN can work it out but if you need to know...

select media
, to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') backed_up
from   rc_backup_piece
where  1=1
and    db_id           = 4053457814
and    completion_Time > sysdate - 2
order  by 2

Delete archivelogs that have been backed up twice

delete noprompt archivelog all backed up 2 times to sbt_tape;

Backup the archivelogs to tape then delete the ones on disk

backup device type sbt_tape archivelog all delete all input;

Show all the configured options

show all;

What needs backing up?

report need backup;

To remove all rows from v$rman_status

SYS.DBMS_BACKUP_RESTORE.resetCfileSection(28);

RMAN reporting by André Araujo

Reference: pythian.com Reproduced here in case it disappears from the internets.

• Query 1

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;

• Query 2

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;

• Query 3

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;