Monitor RMAN backup and restore progress
The following script will allow you to monitor progress of an RMAN backup or Restore from 4 different perspectives (channel, session wait events, datafiles, backuppieces). The script is run from SQLPlus and takes a date input value in the format ‘dd-mon-rr hh24:mi:ss’. The date supplied does not have to be precise and can be taken from the rman log of the job that is running.
REM
REM Script to monitor rman backup/restore operations
REM To run from sqlplus: @monitor '[dd-mon-rr hh24:mi:ss]'
REM Example:
--SQL>spool monitor.out
--SQL>@monitor '06-aug-12 16:38:03'
REM where [date] is the start time of your rman backup or restore job
REM Run monitor script periodically to confirm rman is progessing
REM
alter session set nls_date_format='dd-mon-rr hh24:mi:ss';
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10
select sysdate from dual;
REM gv$session_longops (channel level)
prompt
prompt Channel progress - gv$session_longops:
prompt
select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM gv$session_longops o, gv$session s
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND o.sid=s.sid
AND totalwork != 0
AND sofar <> totalwork;
REM Check wait events (RMAN sessions) - this is for CURRENT waits only
REM use the following for 11G+
prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt
select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;
REM use the following for 10G
--select inst_id, sid, CLIENT_INFO ch, seq#, event, state, seconds_in_wait secs
--from gv$session where program like '%rman%' and
--wait_time = 0 and
--not action is null;
REM gv$backup_async_io
prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece
prompt if backup_tape_io_slaves=TRUE:
prompt
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 a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7;
REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
select s.inst_id, a.sid, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,
open_time open, io_count
from gv$backup_sync_io a, gv$session s
where
a.sid=s.sid and
open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;
REM
From RMAN log, you can see the follwoing timestamp.
Starting restore at 16-DEC-2019 12:38:04
You can run this script at any time if you suspect that rman is taking longer than expected - simply spool the results to a file and rerun the script periodically to check that the job is progressing.
Sample output:
$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 16 14:08:34 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @monitor "16-DEC-2019 12:38:04"
Session altered.
SYSDATE
16-dec-19 14:08:45
Channel progress - gv$session_longops:
no rows selected
Session progess - CURRENT wait events and time in wait so far:
INST_ID SID CH SEQ# EVENT STATE SECONDS
1 217 35823 SQL*Net message from client WAITING 115.75
1 225 rman channel=ch00 3647 Backup: MML restore backup piece WAITING 115.94
1 229 rman channel=ch01 32765 Backup: MML restore backup piece WAITING 115.88
1 233 rman channel=ch02 51706 Backup: MML restore backup piece WAITING 115.81
1 237 rman channel=ch03 62302 Backup: MML restore backup piece WAITING 115.75
Disk (file and backuppiece) progress - includes tape backuppiece
if backup_tape_io_slaves=TRUE:
old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7
new 7: and a.sid=s.sid and open_time > to_date('16-DEC-2019 12:38:04', 'dd-mon-rr hh24:mi:ss') order by 2,7
INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME
1 225 rman channel=ch00 FINISHED 16-dec-19 13:03:03 31 31 9 100.00 OUTPUT +DATA/tpcc/iitem_0_0
1 225 rman channel=ch00 FINISHED 16-dec-19 12:47:56 31900 31900 7977 100.00 OUTPUT +DATA/tpcc/hist_0_0
1 229 rman channel=ch01 FINISHED 16-dec-19 12:57:30 30600 30600 7652 100.00 OUTPUT +DATA/tpcc/stok_0_6
1 229 rman channel=ch01 FINISHED 16-dec-19 12:53:37 30890 30890 7724 100.00 OUTPUT +DATA/tpcc/ordr_0_9
1 229 rman channel=ch01 FINISHED 16-dec-19 12:50:21 31280 31280 7822 100.00 OUTPUT +DATA/tpcc/cust_0_6
1 233 rman channel=ch02 FINISHED 16-dec-19 13:02:39 90 90 24 100.00 OUTPUT +DATA/tpcc/ware_0_0
1 233 rman channel=ch02 FINISHED 16-dec-19 13:01:49 933 933 235 100.00 OUTPUT +DATA/tpcc/tpccaux
1 233 rman channel=ch02 FINISHED 16-dec-19 12:58:16 30600 30600 7652 100.00 OUTPUT +DATA/tpcc/stok_0_11
1 233 rman channel=ch02 FINISHED 16-dec-19 12:55:07 30890 30890 7724 100.00 OUTPUT +DATA/tpcc/ordr_0_14
1 233 rman channel=ch02 FINISHED 16-dec-19 12:51:07 30890 30890 7724 100.00 OUTPUT +DATA/tpcc/ordr_0_4
1 237 rman channel=ch03 FINISHED 16-dec-19 13:02:48 33.21 33.21 10 100.00 OUTPUT +DATA/tpcc/iware_0_0
1 237 rman channel=ch03 FINISHED 16-dec-19 13:01:59 400 400 102 100.00 OUTPUT +DATA/tpcc/system_1
1 237 rman channel=ch03 FINISHED 16-dec-19 13:00:34 7490.45 7490.45 1874 100.00 OUTPUT +DATA/tpcc/icust1_0_0
1 237 rman channel=ch03 FINISHED 16-dec-19 12:55:15 30600 30600 7652 100.00 OUTPUT +DATA/tpcc/stok_0_0
1 237 rman channel=ch03 FINISHED 16-dec-19 12:53:02 30890 30890 7724 100.00 OUTPUT +DATA/tpcc/ordr_0_8
1 237 rman channel=ch03 FINISHED 16-dec-19 12:48:06 31280 31280 7822 100.00 OUTPUT +DATA/tpcc/cust_0_1
16 rows selected.
Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')
new 6: open_time > to_date('16-DEC-2019 12:38:04', 'dd-mon-rr hh24:mi:ss')
INST_ID SID CH FILENAME TYPE STATUS BSZ BFC OPEN IO_COUNT
1 225 rman channel=ch00 bk_dTPCC_ucuuj5dpp_s414_p1_t1026733881 INPUT FINISHED 262144 4 16-dec-19 12:47:56 65392
1 225 rman channel=ch00 bk_dTPCC_uehuj5udh_s465_p1_t1026750897 INPUT FINISHED 262144 4 16-dec-19 13:03:03 8
1 229 rman channel=ch01 bk_dTPCC_ud7uj5i8g_s423_p1_t1026738448 INPUT FINISHED 262144 4 16-dec-19 12:50:21 116172
1 229 rman channel=ch01 bk_dTPCC_udtuj5q1d_s445_p1_t1026746413 INPUT FINISHED 262144 4 16-dec-19 12:57:30 113036
1 229 rman channel=ch01 bk_dTPCC_udhuj5l9e_s433_p1_t1026741550 INPUT FINISHED 262144 4 16-dec-19 12:53:37 63372
1 233 rman channel=ch02 bk_dTPCC_ueeuj5u99_s462_p1_t1026750761 INPUT FINISHED 262144 4 16-dec-19 13:02:39 312
1 233 rman channel=ch02 bk_dTPCC_udmuj5mm0_s438_p1_t1026742976 INPUT FINISHED 262144 4 16-dec-19 12:55:07 63776
1 233 rman channel=ch02 bk_dTPCC_ue0uj5rik_s448_p1_t1026747988 INPUT FINISHED 262144 4 16-dec-19 12:58:16 113444
1 233 rman channel=ch02 bk_dTPCC_ueauj5u3a_s458_p1_t1026750570 INPUT FINISHED 262144 4 16-dec-19 13:01:49 3220
1 233 rman channel=ch02 bk_dTPCC_udauj5jb1_s426_p1_t1026739553 INPUT FINISHED 262144 4 16-dec-19 12:51:07 64180
1 237 rman channel=ch03 bk_dTPCC_ueguj5uc3_s464_p1_t1026750851 INPUT FINISHED 262144 4 16-dec-19 13:02:48 0
1 237 rman channel=ch03 bk_dTPCC_uecuj5u6f_s460_p1_t1026750671 INPUT FINISHED 262144 4 16-dec-19 13:01:59 1184
1 237 rman channel=ch03 bk_dTPCC_ue7uj5trl_s455_p1_t1026750325 INPUT FINISHED 262144 4 16-dec-19 13:00:34 28188
1 237 rman channel=ch03 bk_dTPCC_udnuj5mut_s439_p1_t1026743261 INPUT FINISHED 262144 4 16-dec-19 12:55:15 113444
1 237 rman channel=ch03 bk_dTPCC_udfuj5knj_s431_p1_t1026740979 INPUT FINISHED 262144 4 16-dec-19 12:53:02 63372
1 237 rman channel=ch03 bk_dTPCC_ud1uj5f5c_s417_p1_t1026735276 INPUT FINISHED 262144 4 16-dec-19 12:48:06 115768
16 rows selected.