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%';

SID SPID CLIENT_INFO
8 21973 id=rman
16 22057 id=rman
17 22068 id=rman,ch=t1
18 22070 id=rman,ch=t2

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>
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
8 19 1 10377 36617 28.34

SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
8 19 1 21513 36617 58.75

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
;