Monitoring RMAN Through V$ Views

When LIST, REPORT and SHOW do not provide all the information you need on RMAN activities, there are a number of useful V$ views that can provide more details.

Sometimes it is useful to identify exactly what a server session performing a backup or recovery task is doing, or view the results of recent RMAN backup activities. The views described in the following table are useful in monitoring the progress of or obtaining information about RMAN jobs.

View	Description
`V$PROCESS`	Identifies currently active processes.
`V$SESSION`	Identifies currently active sessions. Use this view to determine which database server sessions correspond to which RMAN allocated channels.
`V$SESSION_LONGOPS`	Provides progress reports on RMAN backup and restore jobs.
`V$SESSION_WAIT`	Lists the events or resources for which sessions are waiting.
`V$BACKUP_SYNC_IO`	Displays rows when the I/O is synchronous to the process (or thread on some platforms) performing the backup.
`V$BACKUP_ASYNC_IO`	Displays rows when the I/O is asynchronous to the process (or thread on some platforms) performing the backup. Note: Where asynchronous I/O is not supported by the host operating system, it may be implemented using slave I/O processes.

The following aspects of RMAN performance can be monitored through these views:

Correlating Server Sessions with RMAN Channels
Monitoring RMAN Job Progress
Monitoring RMAN Interaction with the Media Manager
Monitoring RMAN Job Performance

Correlating Server Sessions with RMAN Channels

To identify which server sessions correspond to which RMAN channels, you can query V$SESSION and V$PROCESS. The SPID column of V$PROCESS identifies the operating system ID number for the process or thread. For example, on UNIX the SPID column shows the process ID, whereas on Windows the SPID column shows the thread ID. You have two basic methods for obtaining this information, depending on whether you have multiple RMAN sessions active concurrently.

Matching Server Sessions with Channels When One RMAN Session Is Active

When only one RMAN session is active, the easiest method for determining the server session ID for an RMAN channel is to execute the following query on the target database while the RMAN job is executing:

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
;

If you do not run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:

rman channel=channel_id

For example, the following shows sample output:

SID SPID         CLIENT_INFO
---- ------------ ------------------------------
  14 8374         rman channel=ORA_SBT_TAPE_1

Matching Server Sessions with Channels in Multiple RMAN Sessions

If more than one RMAN session is active, it is possible for the V$SESSION.CLIENT_INFO column to yield the same information for a channel in each session. For example:

SID SPID         CLIENT_INFO
---- ------------ ------------------------------
  14 8374         rman channel=ORA_SBT_TAPE_1
   9 8642         rman channel=ORA_SBT_TAPE_1

In this case, you have the following methods for determining which channel corresponds to which SID value.

Obtaining the Channel ID from the RMAN Output

In this method, you must first obtain the sid values from the RMAN output and then use these values in your SQL query.

To correlate a process with a channel during a backup:

In one of the active sessions, run the RMAN job as normal and examine the output to get the sid for the channel. For example, the output may show:
```
Starting backup at 21-AUG-01
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=14 devtype=SBT_TAPE
```

Start a SQL*Plus session and then query the joined V$SESSION and V$PROCESS views while the RMAN job is executing. For example, enter:

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
/

Use the sid value obtained from the first step to determine which channel corresponds to which server session:

SID SPID         CLIENT_INFO
---------- ------------ ------------------------------
        14 2036         rman channel=ORA_SBT_TAPE_1
        12 2066         rman channel=ORA_SBT_TAPE_1

Correlating Server Sessions with Channels by Using SET COMMAND ID

In this method, you specify a command ID string in the RMAN backup script. You can then query V$SESSION.CLIENT_INFO for this string.

To correlate a process with a channel during a backup:

In each session, set the COMMAND ID to a different value after allocating the channels and then back up the desired object. For example, enter the following in session 1:
```
RUN 
{
  ALLOCATE CHANNEL c1 TYPE disk;
  SET COMMAND ID TO 'sess1';
  BACKUP DATABASE;
}
```
Set the command ID to a string such as sess2 in the job running in session 2:
```
RUN 
{
  ALLOCATE CHANNEL c1 TYPE sbt;
  SET COMMAND ID TO 'sess2';
  BACKUP DATABASE;
}
```
Start a SQL*Plus session and then query the joined V$SESSION and V$PROCESS views while the RMAN job is executing. For example, enter:
```
SELECT SID, SPID, CLIENT_INFO 
  FROM V$PROCESS p, V$SESSION s 
  WHERE p.ADDR = s.PADDR 
  AND CLIENT_INFO LIKE '%id=sess%';
```
If you run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:
```
id=command_id,rman channel=channel_id
```
For example, the following shows sample output:
```
SID SPID         CLIENT_INFO
---- ------------ ------------------------------
  11 8358         id=sess1
  15 8638         id=sess2
  14 8374         id=sess1,rman channel=c1
   9 8642         id=sess2,rman channel=c1
```
The rows that contain the string rman channel show the channel performing the backup. The remaining rows are for the connections to the target database.

Monitoring RMAN Job Progress

Monitor the progress of backups and restores by querying the view V$SESSION_LONGOPS. RMAN uses two types of rows in V$SESSION_LONGOPS: detail and aggregate rows. Detail rows describe the files being processed by one job step, while aggregate rows describe the files processed by all job steps in an RMAN command. A job step is the creation or restore of one backup set or datafile copy. Detail rows are updated with every buffer that is read or written during the backup step, so their granularity of update is small. Aggregate rows are updated when each job step completes, so their granularity of update is large.

Table 9-2 describes column in V$SESSION_LONGOPS that are most relevant for RMAN. Typically, you will view the detail rows rather than the aggregate rows to determine the progress of each backup set.

Table 9-2 Columns of V$SESSION_LONGOPS Relevant for RMAN

Column	Description for Detail Rows
`SID`	The server session ID corresponding to an RMAN channel.
`SERIAL#`	The server session serial number. This value changes each time a server session is reused.
`OPNAME`	A text description of the row. Examples of details rows include `RMAN:` `datafile` `copy`, `RMAN:` `full` `datafile` `backup`, and `RMAN:` `full` `datafile` `restore`. Note: `RMAN:` `aggregate` `input` and `RMAN:` `aggregate` `output` are the only aggregate rows.
`CONTEXT`	For backup output rows, this value is `2`. For all other rows except proxy copy (which does not update this column), the value is `1`.
`SOFAR`	The meaning of this column depends on the type of operation described by this row: For image copies, the number of blocks that have been read. For backup input rows, the number of blocks that have been read from the files being backed up. For backup output rows, the number of blocks that have been written to the backup piece. For restores, the number of blocks that have been processed to the files that are being restored in this one job step. For proxy copies, the number of files that have been copied.
`TOTALWORK`	The meaning of this column depends on the type of operation described by this row: For image copies, the total number of blocks in the file. For backup input rows, the total number of blocks to be read from all files processed in this job step. For backup output rows, the value is `0` because RMAN does not know how many blocks that it will write into any backup piece. For restores, the total number of blocks in all files restored in this job step. For proxy copies, the total number of files to be copied in this job step.

Each server session performing a backup or restore reports its progress compared to the total amount of work required for a job step. For example, if you perform a database restore that uses two channels, and each channel has two backup sets to restore (a total of four sets), then each server session reports its progress through a single backup set. When that set is completely restored, RMAN begins reporting progress on the next set to restore.

To monitor job progress:

Before starting the job, create a script file (called, for this example, longops) 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
;

After connecting to the target database and, if desired, the recovery catalog database, start an RMAN job. For example, enter:
```
RESTORE DATABASE;
```

While the job is running, start SQL*Plus connected to the target database, and execute the longops script to check the progress of the RMAN job. If you repeat the query while the restore progresses, then you see output such as the following:

SQL> @longops
       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
         8         19          1      10377      36617      28.34

SQL> @longops
       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK % COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
         8         19          1      21513      36617      58.75

SQL> @longops
       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK % COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
         8         19          1      29641      36617      80.95

SQL> @longops
       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK % COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
         8         19          1      35849      36617       97.9

SQL> @longops
no rows selected

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. Refer to "Monitoring RMAN Interaction with the Media Manager" to obtain more information.

If you frequently monitor the execution of long-running tasks, you could create a shell script or batch file under your host operating system that runs SQL*Plus to execute this query repeatedly.

Monitoring RMAN Interaction with the Media Manager

You can use the event names in the dynamic performance event views to monitor RMAN calls to the media management API. The event names have one-to-one correspondence with sbt functions, as shown in the following examples:

Backup: sbtinit
Backup: ssbtopen
Backup: ssbtread
Backup: ssbtwrite
Backup: ssbtbackup
.
.
.

To obtain the complete list of sbt events, you can use the following query:

select name from v$event_name where name like '%sbt%';

Before making a call to any of functions in the media management API, the server adds a row in V$SESSION_WAIT, with the STATE column including the string WAITING. The V$SESSION_WAIT.SECONDS_IN_WAIT column shows the number of seconds that the server has been waiting for this call to return. After an sbt function is returned from the media manager, this row disappears.

A row in V$SESSION_WAIT corresponding to an sbt event name does not indicate a problem, because the server updates these rows at runtime. The rows appear and disappear as calls are made and returned. However, if the SECONDS_IN_WAIT column is high, then the media manager may be hung.

To monitor the sbt events, you can run the following SQL 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, 
       STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 's%bt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
;

Examine the SQL output to determine which sbt functions are waiting. For example, the following output indicates that RMAN has been waiting for the sbtbackup function to return for ten minutes:

SPID EVENT             SEC_WAIT   STATE                CLIENT_INFO
---- ----------------- ---------- -------------------- ------------------------------
8642 Backup: sbtbackup 600        WAITING              rman channel=ORA_SBT_TAPE_1

Note:

The V$SESSION_WAIT view shows only database events, not media manager events.

Monitoring RMAN Job Performance

Monitor backup and restore performance by querying V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO.