Oracle RMAN: Scripts to Monitor Backup Progress

These SQL scripts help monitor and check various aspects of RMAN (Recovery Manager) backups in Oracle databases. Below is a summary of what each script does:

1. Check All Backups

set lines 300
col STATUS format a30
col hrs format 999.99
col START_TIME for a30
col INPUT_TYPE for a30
col END_TIME for a30
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;

2. Check DB Incremental Backup

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
where input_type='DB INCR'
order by session_key;

3. Check DB Archive Backup

set lines 10000
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
where input_type='ARCHIVELOG'
order by session_key;

4. Check Full Backup

set lines 200 pages 100
col STATUS format a25
col start_time for a20
col end_time for a20
col input_bytes_display format a9
col output_bytes_display format a9
col HRS:MIN for a10

select /*+ rule*/ SESSION_KEY,INPUT_TYPE, b.incr_lvl, STATUS,
	to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
	to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
	decode(to_char(start_time, 'd'), 1, 'Sunday', 2, 'Monday',
										3, 'Tuesday', 4, 'Wednesday',
										5, 'Thursday', 6, 'Friday',
										7, 'Saturday') dow,
	trunc(elapsed_seconds/3600) ||':' || trunc(mod((elapsed_seconds/60),60)) "HRS:MIN",
	ceil(((LEAD(START_TIME, 1, START_TIME ) OVER (ORDER BY START_TIME)) - START_TIME)*24 + (elapsed_seconds)/3600 + 6) AS parameter_HRS ,
	input_bytes_display, 
	output_bytes_display,
	OUTPUT_DEVICE_TYPE
	from V$RMAN_BACKUP_JOB_DETAILS r join (select distinct SESSION_RECID,session_stamp, incremental_level incr_lvl from v$backup_set_details where incremental_level = 0) b
	on r.session_stamp = b.session_stamp  and r.SESSION_RECID = b.SESSION_RECID	
	Where input_type in ('DB INCR')
	union all
	select SESSION_KEY,INPUT_TYPE, NULL incr_lvl, STATUS,
	to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
	to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
	decode(to_char(start_time, 'd'), 1, 'Sunday', 2, 'Monday',
										3, 'Tuesday', 4, 'Wednesday',
										5, 'Thursday', 6, 'Friday',
										7, 'Saturday') dow,
	trunc(elapsed_seconds/3600) ||':' || trunc(mod((elapsed_seconds/60),60)) "HRS:MIN",
	ceil(((LEAD(START_TIME, 1, START_TIME ) OVER (ORDER BY START_TIME)) - START_TIME)*24 + (elapsed_seconds)/3600 + 6) AS parameter_HRS ,
	input_bytes_display, 
	output_bytes_display,
	OUTPUT_DEVICE_TYPE
	from V$RMAN_BACKUP_JOB_DETAILS r 
	Where input_type in ('DB FULL')
	order by 1;

5. Monitor RMAN Current Backup Progress

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10           justify left head "DEVICE"
col complete           for 990.00        justify right head "COMPLETE %" 
col compression        for 990.00        justify right head "COMPRESS|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"

select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024 input_mbytes
     , output_bytes/1024/1024 output_mbytes
     , (output_bytes/input_bytes*100) compression
     , (mbytes_processed/dbsize_mbytes*100) complete
     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
  from v$rman_status rs
     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
   and output_device_type is not null
/

6. Check RMAN Backup Logs

set lines 300 pages 1000
select output
from v$rman_output
where session_key = 15       
order by recid;

7. Check Backup Running Sessions

SET LINESIZE 500
SET PAGESIZE 1000
col sid for 9999
col Serial# for 99999
col Program for a30
col "OS User" for a10
col Process for a6
col "Schema Name" for a15
col "SQL Text" for a60
-- SET FEEDBACK OFF
SELECT s.sid,s.serial#,s.LOGON_TIME,
s.status "Status",
p.spid "Process",
s.schemaname "Schema Name",
s.osuser "OS User",
s.program "Program",
a.sql_id,
Substr(a.sql_text,1,120) "SQL Text"
FROM   v$session s,
v$sqlarea a,
v$process p
WHERE  s.sql_hash_value = a.hash_value (+)
AND    s.sql_address    = a.address (+)
AND    s.paddr          = p.addr
## AND    s.SID='&SID'
AND s.program like '%rman%'
AND    s.type <>'BACKGROUND';

Note: if you want to kill any RMAN session then you can pick sid,#serial numbers from the output of above query and then kill the session using below command.

Note: If you need to kill any RMAN session, you can use the SID and serial number from the output of the above query, and then terminate the session using the following command.

ALTER SYSTEM KILL SESSION '12,10124' IMMEDIATE;

These scripts provide comprehensive visibility into the status and progress of RMAN backup operations and can be adjusted based on specific needs, such as filtering by backup type or session key.

Also read:

RMAN Commands Reference