How to Monitor RMAN Backup and Long Operations in Oracle

If you have big database about more than 1 TB, then your backup and export operations are taking long times.

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

You can see and monitor RMAN Backup progress with following script.

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 order by 6 desc;

Step1:To check any RMAN Backup progress.

SQL> startup

ORACLE instance started.

Total System Global Area 2046820352 bytes

Fixed Size 8622480 bytes

Variable Size 587206256 bytes

Database Buffers 1442840576 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL>

SQL> 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 order by 6 desc; 2 3 4 5 6 7

no rows selected

Step2:Now the RMAN Backup progressing and check process details.

RMAN> connect target /

connected to target database: TEST (DBID=2343286063)

RMAN> backup database plus archivelog;

Starting backup at 27-SEP-20

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=66 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=108 RECID=108 STAMP=1049353353

input archived log thread=1 sequence=109 RECID=109 STAMP=1049407361

SQL> 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 order by 6 desc; 2 3 4 5 6 7

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE

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

66 15044 1 494584 4727734 10.46

You can monitor Datapump Export ,Import and other long operations with following script.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK order by 6 desc;