- 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, --session_id
- 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, --状态(WAITING 、WAITED UNKNOWN TIME、WAITED SHORT TIME 、WAITED KNOWN TIME )
- 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, --文件当前发送的IO请求数量
- round(BYTES/TOTAL_BYTES*100,2) "% Complete" ,
- a.type, --类型 (INPUT, OUTPUT, or AGGREGATE)
- 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, --(NOT STARTED, IN PROGRESS, or FINISHED)
- buffer_size bsz, --使用的buffer大小
- buffer_count bfc, --使用的buffer 数量
- open_time open, --文件被打开的时间
- io_count ----文件当前发送的IO请求数量
- 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 -------------------------------
监控rman执行操作的脚本
最新推荐文章于 2021-01-19 18:20:28 发布