Check 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;