下面是一个用于监控 Oracle RMAN 备份和恢复操作的 SQL 脚本。你可以在 Oracle SQL*Plus 环境中运行这个脚本，并定期检查 RMAN 操作的进度。
REM -------------------------------
REM Script to monitor rman backup/restore operations
REM To run from sqlplus: @monitor '<yyyy-mm-dd hh24:mi:ss>'
REM Example:
--SQL>@monitor '2024-06-20 14:03:48'
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='yyyy-mm-dd 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', 'yyyy-mm-dd 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', 'yyyy-mm-dd hh24:mi:ss') ;
REM -------------------------------
输出样例：
14:05:03 SYS@orcl> @monitor '2024-06-20 14:03:48'
Session altered.
Elapsed: 00:00:00.00
SYSDATE
-------------------
2024-06-20 14:05:04
Elapsed: 00:00:00.00
Channel progress - gv$session_longops:
INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete
---------- ---------- -------------------- ---------- ---------- ---------- ----------
1 1717 rman channel=ORA_DIS 1 147582 3121280 4.73
K_1
1 1720 rman channel=ORA_DIS 1 193404 1182720 16.35
K_2
1 12 rman channel=ORA_DIS 1 448126 917504 48.84
K_3
Elapsed: 00:00:00.00
Session progess - CURRENT wait events and time in wait so far:
INST_ID SID CH SEQ# EVENT STATE SECONDS
---------- ---------- -------------------- ---------- ---------------------------------------- ------------------- ----------
1 579 rman channel=ORA_DIS 592 SQL*Net message from client WAITING 16.73
K_4
1 1150 969 SQL*Net message from client WAITING 2.72
Elapsed: 00:00:00.01
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', 'yyyy-mm-dd hh24:mi:ss') order by 2,7
new 7: and a.sid=s.sid and open_time > to_date('2024-06-20 14:03:48', 'yyyy-mm-dd hh24:mi:ss') order by 2,7
INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME
---------- ---------- -------------------- ----------- ------------------- ---------- ---------- ---------- ---------- --------- -----------------------------------------------------------------
1 12 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 1.5 1024 4 .15 INPUT /oradata/ORCLPRI/datafile/o1_mf_users
K_3 _m19cm7df_.dbf
1 12 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 16 2048 19 .78 INPUT /oradata/ORCLPRI/datafile/o1_mf_undot
K_3 bs1_kc5zmqdr_.dbf
1 12 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:04:45 429.99 4096 430 10.50 INPUT /oradata/ORCLPRI/datafile/o1_mf_simtr
K_3 ade_lsd26xdr_.dbf
1 12 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:05:02 24 25 OUTPUT /rman/Full_ORCL_20240620_3n2tr8ns_2_1.bak
K_3
1 12 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 98.05 99 OUTPUT /rman/Full_ORCL_20240620_3n2tr8ns_1_1.bak
K_3
1 579 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 22.97 22.97 24 100.00 INPUT /product/11.2/dbs/snapcf_orcl.f
K_4
1 579 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 2.06 3 OUTPUT /rman/Full_ORCL_20240620_3o2tr8ns_1_1.bak
K_4
1 579 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:47 .08 1 OUTPUT /rman/Full_ORCL_20240620_3p2tr8nv_1_1.bak
K_4
1 1717 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 37.5 100 40 37.50 INPUT /oradata/ORCLPRI/datafile/zhongchao/z
K_1 c01.dbf
1 1717 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 198 300 201 66.00 INPUT /oradata/ORCLPRI/datafile/o1_mf_ts_go
K_1 lde_ky3v32wd_.dbf
1 1717 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:04:45 686.99 23985 687 2.86 INPUT /oradata/ORCLPRI/datafile/o1_mf_users
K_1 _kc5zmqds_.dbf
1 1717 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:05:02 16 17 OUTPUT /rman/Full_ORCL_20240620_3l2tr8ns_2_1.bak
K_1
1 1717 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 98.09 99 OUTPUT /rman/Full_ORCL_20240620_3l2tr8ns_1_1.bak
K_1
1 1720 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 1 800 3 .13 INPUT /oradata/ORCLPRI/datafile/o1_mf_orcl
K_2 _kc5zmqdl_.dbf
1 1720 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:04:45 355.99 960 356 37.08 INPUT /oradata/ORCLPRI/datafile/o1_mf_syste
K_2 m_kc5zmqdo_.dbf
1 1720 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:04:45 355.99 7480 356 4.76 INPUT /oradata/ORCLPRI/datafile/o1_mf_sysau
K_2 x_kc5zmqd3_.dbf
1 1720 rman channel=ORA_DIS FINISHED 2024-06-20 14:04:45 98.26 99 OUTPUT /rman/Full_ORCL_20240620_3m2tr8ns_1_1.bak
K_2
1 1720 rman channel=ORA_DIS IN PROGRESS 2024-06-20 14:05:02 23 24 OUTPUT /rman/Full_ORCL_20240620_3m2tr8ns_2_1.bak
K_2
18 rows selected.
Elapsed: 00:00:00.00
Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
old 6: open_time > to_date('&1', 'yyyy-mm-dd hh24:mi:ss')
new 6: open_time > to_date('2024-06-20 14:03:48', 'yyyy-mm-dd hh24:mi:ss')
no rows selected
Elapsed: 00:00:00.00
「欢迎关注我们的公众号，获取更多技术分享与经验交流。」
文章转载自数据库驾驶舱，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
