Check status for backup and restore RMAN operation

Status for RMAN Backup and Restore operation

You can check the running status for RMAN job with following views. You can also monitored the progress of rman job running.
By query this view, you will get your answers:
How much time the rman job take to completed last time?
How much space last time the backup is made of?
How much percentage the backup or restore operation is done?

Check the backup percentage status RMAN Completed

select sid,start_time, totalwork sofar, (sofar/totalwork) * 100 pct_done
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';

Check the Restore Status completed

set pagesize 100 echo on feedback on
select s.client_info,
sl.message,
sl.sid, sl.serial#, p.spid,
sl.start_time, sl.last_update_time,
sl.time_remaining, sl.elapsed_seconds, sl.time_remaining + sl.elapsed_seconds as total_seconds,
sl.time_remaining/60 "Remaining (Min)", sl.elapsed_seconds/60 "Elapsed (Min)",
round(sl.sofar/sl.totalwork*100,2) || '%' "Completed"
from v$session_longops sl, v$session s, v$process p
where p.addr = s.paddr
and sl.sid=s.sid
and sl.serial#=s.serial#
and opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar totalwork;

Find the last 7 day RMAN backup job details including start time,end time, time taken and status

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI';
col TIME_TAKEN_DISPLAY format a30
select START_TIME, END_TIME, AUTOBACKUP_DONE, INPUT_TYPE, STATUS, TIME_TAKEN_DISPLAY from V$RMAN_BACKUP_JOB_DETAILS where START_TIME>sysdate-7 order by START_TIME;

Space used by RMAN backup

select start_time, END_Time, input_bytes/(1024*1024*1024), output_bytes/(1024*1024*1024), status,elapsed_seconds/60/60
from v$rman_backup_job_details order by 1;

Report for backup status with size

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, bp.handle handle, bp.
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;

Check with the TAG & handle associated with backup detail as incremental, full, archive

select bp.TAG, bp.handle, bp.STAMP, decode(bs.backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
bp.SET_STAMP, to_char(bp.completion_time + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and tag='TAG20121005T233026';

RMAN Backup job status by session

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, 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
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.