RMAN Backup and Restore Status in Oracle Database

If you start long operations such as RMAN Backup and Datapump Export in Oracle database, probably you want to see progress of these operations.

RMAN is designed to complete a backup or restore as quikly and efficiently as possible. But if a job takes longer than expected to complete it can be confusing trying to determine:

· What precisely is RMAN doing at any particular time?

· Is RMAN hung or just slow?

· How far into the backup/restore has it got?

· How much more work is there to go?

 

Method:1

 

SQL> col STATUS format a9SQL> col hrs format 999.99SQL> 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_DETAILSorder by session_key;

 

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

 ----------- ------------- --------- -------------- -------------- -------

 29             DB FULL       RUNNING   28/09/20 10:28  28/09/20 10:28      .00

SQL> /

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

 ----------- ------------- --------- -------------- -------------- -------

 29             DB FULL       RUNNING   28/09/20 10:28  28/09/20 10:28     .01

 

SQL> /

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

 ----------- ------------- --------- -------------- -------------- -------

 29            DB FULL       COMPLETED 28/09/20 10:28  28/09/20 10:29      .03

 

 

Method:2

 

session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 2222;

set pages 2222;

set long 6666;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) done_by

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

 

Method:3

 

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;

 

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE

---------- ---------- ---------- ---------- ---------- ----------

 18        29         1          9115569    19258880   47.33

 

Channel progress – v$session_longops

 only ACTIVE channels are reported

 check that % Complete is increasing

Session progress – CURRENT wait events and time in wait

Waits on ‘RMAN backup & recovery I/O‘ events are considered normal during backup and restore operations and indicate IO to disk.

Use this query to identify excessive waits on any Oracle or Media manager related resources.only CURRENT wait events are reported.

The session with NULL CH value is the RMAN client – this is always idle when the physical backup or restore is in progress.

 if SEQ# and EVENT do not change this is the same wait, check seconds in wait.

if EVENT does not change and SEQ# has increased this is a different wait on the same event.