30
B.3 How to monitor RMAN wait states
COLUMN EVENT FORMAT a20
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, sw.EVENT, s.SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, s.CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE '%sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;
SQL> @RMAN_MML_monitor.sql
SPID EVENT SEC_WAIT STATE CLIENT_INFO
------------ -------------------- ---------- -------------------- ------------------------------
4795 Backup: sbtwrite2 3 WAITED SHORT TIME rman channel=dev_1
4793 Backup: sbtwrite2 3 WAITING rman channel=dev_0
B.4 How to monitor RMAN channels current speed
In the output below, we can see two RMAN channels running on one instance, each over 70MB/s. In
this view, it can also be verified which the current MAXOPENFILES setting is (the value maxopenfiles
is only present when type is AGGREGATE)
col type format a10;
select TYPE,maxopenfiles,ELAPSED_TIME,EFFECTIVE_BYTES_PER_SECOND from v$backup_async_io where type =
'AGGREGATE' order by open_time, close_time
/
SQL> @backup_aggregate_speed
TYPE MAXOPENFILES ELAPSED_TIME EFFECTIVE_BYTES_PER_SECOND
---------- ------------ ------------ --------------------------
AGGREGATE 1 8500 77829012
AGGREGATE 1 8600 70010737
