Friday, November 8, 2013

How to check for progress on a long-running RMAN job

If you want to know if your backup is actually doing anything then the following SQL is useful

col INPUT_BYTES format  999,999,999,999
col OUTPUT_BYTES format 999,999,999,999
col OPERATION format a30
col OUTPUT_DEVICE_TYPE format a9
col RECID format 99999
col PARENT_RECID format 99999
col PARENT_RECID heading P_RECID
col SID format 99999
col STATUS format a30

set linesize 300
set pagesize 5000
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
SELECT   ST.SID, 
         LO.SERIAL#,
         ST.RECID, 
         ST.PARENT_RECID, 
         ST.STATUS, 
         ST.START_TIME, 
         ST.END_TIME, 
         ST.OPERATION,
         ST.OBJECT_TYPE, 
         ST.OUTPUT_DEVICE_TYPE, 
         ST.INPUT_BYTES, 
         ST.OUTPUT_BYTES, 
         TY.INPUT_TYPE,
         LO.MESSAGE,
         ROUND(LO.TIME_REMAINING/60) "ETA (MIN)"
FROM V$RMAN_STATUS ST INNER JOIN V$SESSION_LONGOPS LO ON ST.SID = LO.SID
LEFT OUTER JOIN V$RMAN_BACKUP_TYPE TY ON TY.INPUT_TYPE = ST.OBJECT_TYPE
WHERE ST.STATUS = 'RUNNING'
ORDER BY ST.RECID ASC, ST.START_TIME DESC;

Output:
SID SERIAL# RECID PARENT_RECID STATUS START_TIME END_TIME OPERATION OBJECT_TYPE OUTPUT_DEVICE_TYPE INPUT_BYTES OUTPUT_BYTES INPUT_TYPE ETA (MIN)
227 42437 44435   RUNNING 24.01.2014 12:50:36 24.01.2014 16:06:12 RMAN     0 0   0
227 42676 44435   RUNNING 24.01.2014 12:50:36 24.01.2014 16:06:12 RMAN     0 0   0
227 42437 44436 44435 RUNNING 24.01.2014 12:51:11 24.01.2014 16:06:12 BACKUP DB FULL DISK 298811883520 52378599424 DB FULL 0
227 42676 44436 44435 RUNNING 24.01.2014 12:51:11 24.01.2014 16:06:12 BACKUP DB FULL DISK 298811883520 52378599424 DB FULL 0

As long as your INPUT_BYTES and OUTPUT_BYTES bytes are increasing then something is happening!

No comments:

Post a Comment