Scripts to Check RMAN Backup Status and Progress

In this post I wanted to give 2 SQL scripts to check the Oracle RMAN backup status. 

SQL 1

set pages 1000 
set lines 222
col STATUS format a9

col Duration_hrs format 999.99
alter  session SET NLS_DATE_FORMAT = 'dd-mm-yyyy hh:mi:ss';
select  SESSION_KEY, INPUT_TYPE, STATUS,START_TIME, 
 end_time, elapsed_seconds/3600 Duration_hrs 
from  V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

OUTPUT

Session altered.

SESSION_KEY INPUT_TYPE   STATUS    START_TIME  END_TIME  HRS
----------- ------------- --------- ------------------- ------------------- -------
   4 DB FULL   COMPLETED 15-07-2020 12:40:49 15-07-2020 12:41:09 .01
  10 DB FULL   COMPLETED 15-07-2020 02:06:56 15-07-2020 03:07:52    1.02

advertisements

 

SQL 2 

SQL to check the RMAN long running processes This query generally works only for the long running RMAN backup jobs. Otherwise it won’t give any output.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK <> 0 AND SOFAR <> TOTALWORK;

Output

SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 339        3291         1          3476569    20258990   17.16