Monitoring Recovery Manager Jobs

Sometimes it is useful to identify what a server session performing a backup or copy operation is doing. You have access to several views that can assist in monitoring the progress of or obtaining information about RMAN jobs:

View
V$PROCESS----->Identifies currently active processes
V$SESSION ---->Identifies currently active sessions. Use this view to determine which Oracle database server sessions correspond to which RMAN allocated channels.
V$SESSION_LONGOPS------->Provides progress reports on long-running operations.
V$SESSION_WAIT-------------->Lists the events or resources for which sessions are waiting.

Correlating Server Sessions with Channels
To identify which server sessions correspond to which RMAN channels, use the set command with the command id parameter. The command id parameter enters the specified string into the CLIENT_INFO column of the V$SESSION dynamic performance view. Join V$SESSION with V$PROCESS to correlate the server session with the channel.

To correlate a process with a channel during a backup:

Step:1 Start RMAN and connect to the target database .
Step:2 Set the command id parameter after allocating the channels and then back up the desired object.

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
set command id to 'rman';
backup
incremental level 0
filesperset 5
tablespace 'SYSTEM';
# optionally, issue a host command to access the operating system prompt
host;
sql 'ALTER SYSTEM ARCHIVE LOG ALL';
}

Step:3 Start a SQL*Plus session and then query the joined V$SESSION and V$PROCESS views while the RMAN job is executing.

SELECT sid, spid, client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%id=rman%';

Monitoring Job Progress
Each server session performing a backup, restore, or copy reports its progress compared to the total amount of work required for that particular part of the restore.

For example, if you perform a restore using two channels, and each channel has two backup sets to restore (a total of 4 sets), then each server session reports its progress through a single set. When that set is completely restored, RMAN starts reporting progress on the next set to restore.

Step:1 Start RMAN and connect to the target database and, optionally, the recovery catalog database.
Step:2 Start an RMAN job.:
run {
allocate channel t1 type disk;
backup database;
}

Step:3 While the job is running, execute a script containing the following SQL statement:

SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork
/
If you repeat the query while the backup progresses, then you see output such as the following:

SQL>
SQL> /
SQL> /
SQL> /

no rows selected

NOTE: If you run the script at intervals of two minutes or more and the % Complete column does not increase, then RMAN is encountering a problem.

SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait WHERE
wait_time = 0
ORDER BY sid;

SID SEC_WAIT EVENT

1 368383335 pmon timer

2 1097 rdbms ipc message

3 387928 rdbms ipc message

4 0 rdbms ipc message

5 1408 smon timer

6 386114 rdbms ipc message

7 387626 rdbms ipc message

8 1060 SQL*Net message from client

9 1060 SQL*Net message from client

12 1060 SQL*Net message from client

13 2366 SQL*Net message from client

14 2757 SQL*Net message from client

12 rows selected.

Note: The V$SESSION_WAIT view shows only Oracle events, not media manager events.

Another Query:

COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE '%disk%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;