Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
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.
The following aspects of RMAN performance can be monitored through these views:
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.
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
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.
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
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.
See Also:
Oracle Database Backup and Recovery Reference forSET
COMMAND
ID
syntax, and Oracle Database Reference for more information on V$SESSION
and V$PROCESS
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 |
---|---|
|
The server session ID corresponding to an RMAN channel. |
|
The server session serial number. This value changes each time a server session is reused. |
|
A text description of the row. Examples of details rows include Note: |
|
For backup output rows, this value is |
|
The meaning of this column depends on the type of operation described by this row:
|
|
The meaning of this column depends on the type of operation described by this row:
|
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.
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, sw.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:
TheV$SESSION_WAIT
view shows only database events, not media manager events.Monitor backup and restore performance by querying V$BACKUP_SYNC_IO
and V$BACKUP_ASYNC_IO
.
See Also:
Oracle Database Reference for more information on theseV$
views, and "Step 5: Query V$ Views to Identify Bottlenecks" to learn how to use these views to tune backup performance