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. Let’s review the RMAN Backup and Restore Status in Oracle in this post.

 

RMAN Backup and Restore Status in Oracle

 

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

 

alter 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
/

 

Or You can use the following script to monitor Long running Backup & Restore

 

SET HEAD OFF
SELECT 'RMAN Throughput : '||
ROUND(SUM(v.value/(power(2,30))),1) || ' GB so far ---> Per Second Throughput = ' ||
ROUND(SUM(v.value /(power(2,30)))/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /(power(2,30)))),2) || ' GB'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE '[email protected]%'
GROUP BY n.name
/

 

Example output is as follows.

 

SQL> SET HEAD OFF
SQL> SELECT 'RMAN Throughput : '||
2 ROUND(SUM(v.value/(power(2,30))),1) || ' GB so far ---> Per Second Throughput = ' ||
3 ROUND(SUM(v.value /(power(2,30)))/NVL((SELECT MIN(elapsed_seconds)
4 FROM v$session_longops
5 WHERE opname LIKE 'RMAN: aggregate input'
6 AND sofar != TOTALWORK
7 AND elapsed_seconds IS NOT NULL
8 ),SUM(v.value /(power(2,30)))),2) || ' GB'
9 FROM gv$sesstat v, v$statname n, gv$session s
10 WHERE v.statistic# = n.statistic#
11 AND n.name = 'physical write total bytes'
12 AND v.sid = s.sid
13 AND v.inst_id = s.inst_id
14 AND s.program LIKE '[email protected]%'
15 GROUP BY n.name
16 /

RMAN Throughput : 70952.9 GB so far ---> Per Second Throughput = 1.04 GB

SQL>

 

Or you can also use the following script to monitor RMAN.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM gV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR != TOTALWORK order by 6 desc;

 

 

You can use the following script to see details of RMAN Channels’ status.

 select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a, gv$session s
where not a.STATUS in ('UNKNOWN') and s.status='ACTIVE' and a.STATUS <> 'FINISHED'
and a.sid=s.sid order by 6 desc,7;

 

 

 

 

Do you want to learn more details about RMAN, then Click this Link and read the articles.

RMAN Tutorial | Backup, Restore and Recovery Tutorials For Beginner Oracle DBA

 1,282 views last month,  7 views today

About Cihan Gedik

I am currently working as Senior Oracle Application Database Administrator. I have experienced many EBS database upgrade, migrations, Fresh EBS installations, Solaris to Linux, Windows to Linux replatform migration projects in medium and large companies and also experienced core database migration projects for one of the biggest bank of Katar.With my colleagues we decided to run this platform to share our knowledge

Leave a Reply

Do NOT follow this link or you will be banned from the site!