This chapter provides conceptual and procedural information related to maintenance tasks associated with using the Recovery Manager (RMAN) utility, including maintaining the RMAN repository and deleting backups.
This chapter contains the following topics:
The RMAN repository for a database contains extensive records of backups of the database, as well as other useful information such as database schema and configuration settings. You can use RMAN commands LIST
, REPORT
, and SHOW
to access this repository information.
In addition to these general reporting commands, you can also make use of the RESTORE
... PREVIEW
command to see which backup files are required to restore specific database objects from backup. See Oracle Database Backup and Recovery Basics for more details on RESTORE
... PREVIEW
.
The LIST
command is used to query the RMAN repository and obtain data about:
Backup sets and image copies generated by the RMAN BACKUP
command;
Specified objects contained in the BACKUP
-generated files, that is, archived logs, datafiles, control files, and server parameter files;
Incarnations of a specified database, or of all databases known to a recovery catalog.
RMAN LIST
output is sent either to standard output or to the message log (though not to both at the same time). You can also control how the output is organized as well as the level of detail in the output.
You can also list backups by querying V$BACKUP_FILES
and the RC_BACKUP_FILES
recovery catalog view. These views provide access to the same information as the LIST
BACKUPSET
command.
The LIST
command displays the same files that the CROSSCHECK
and DELETE
commands operate on. Consequently, you can issue LIST
to see what is in the repository, and then run CROSSCHECK
to ensure that these files exist on disk or tape.
See Also:
Oracle Database Backup and Recovery Basics to learn how to use LIST
"Querying Recovery Catalog Views" to learn how to use views as an alternative to LIST
Oracle Database Backup and Recovery Reference for LIST
command syntax
Oracle Database Backup and Recovery Reference for LOG
command-line syntax
RMAN reports are intended to provide analysis of your backup and recovery situation. An RMAN report can answer questions such as:
Which datafiles need a backup?
Which backups are obsolete because they are redundant or because they are not needed for recovery within a recovery window?
Are any datafiles now unrecoverable because they have been the target of unrecoverable operations?
What is the current physical schema of the database, or what was it at some previous time?
RMAN's reporting can be used to monitor and validate your ongoing backup strategy. The REPORT
NEED
BACKUP
and REPORT
UNRECOVERABLE
commands let you ensure that the necessary backups are available for media recovery, and that you can perform media recovery within a reasonable amount of time.
Note:
A datafile that does not have a backup is still considered recoverable by RMAN, as long as a complete set of archived redo logs is available, from the time the datafile was created to the present. During recovery, an empty datafile is created, and then all of the changes to the datafile from the archived redo logs are applied to reconstruct the full contents of the file.The REPORT
OBSOLETE
command displays backups of datafiles, control files, and archived redo logs that can be deleted because they are no longer needed. Backups are determined to be obsolete with respect to a retention policy, based either on redundancy or a required recovery window, as described in the following table:
Retention Policy | Meaning |
---|---|
REDUNDANCY integer |
At least integer more recent backups of this file already exist. |
RECOVERY WINDOW integer |
The backup is not needed for recovery to any point within the recovery window of integer days. For each datafile, one backup that is older than the recovery window must exist, because point-in-time recovery to the beginning of the recovery window requires must begin with restoring the database from such a backup.
In other words, one backup of each datafile must satisfy the condition |
In addition to obsolete datafile backups, RMAN reports obsolete archived logs and archived log backups. When backups of datafiles from a point in time are obsolete, then archived logs that can only be applied to those datafiles are also no longer needed and become obsolete according to the specified retention policy.
Note:
Note that if a datafile has never been backed up, then all archived redo logs back to the creation time of the file are required, and none of them are considered obsolete. With a full set of logs, the file can be completely re-created during media recovery. An empty datafile is automatically created during recovery, and the same changes applied to the original datafile after it was created are re-applied to the newly created file.By default, the REPORT
OBSOLETE
command reports which files are obsolete under the currently configured retention policy. To generate reports of which files are obsolete according to different retention policies by using REDUNDANCY
or RECOVERY WINDOW
retention policy options with the REPORT
OBSOLETE
command. For example, if you run any of these commands:
REPORT OBSOLETE REDUNDANCY 2; REPORT OBSOLETE RECOVERY WINDOW OF 5 DAYS;
RMAN displays backups that are obsolete according to those retention policies, regardless of the actual configured retention policy.
If you disable the retention policy completely (that is, if you run CONFIGURE
RETENTION
POLICY
TO
NONE
), then RMAN does not consider any backups to be obsolete. If you run REPORT
OBSOLETE
with no options and no retention policy is configured, then RMAN issues an error message.
You can also query V$BACKUP_FILES
and RC_BACKUP_FILES
, using the OBSOLETE
column to identify backup sets, datafile copies, and archived logs that are obsolete according to the configured retention policy.
If you are managing backup storage yourself instead of using a flash recovery area, then you should run REPORT OBSOLETE
regularly to identify backups no longer needed to meet your retention policy, and delete these backups with DELETE OBSOLETE
. If you use a flash recovery area, then backups stored there that are obsolete according to the configured retention policy are deleted automatically as space is needed. You do not have to perform DELETE OBSOLETE
to reclaim space used for such backups.
RMAN's record of backups can become inconsistent with the actual backups that exist on tape or disk. For example, a user may delete backup pieces from disk using operating system commands, or a tape containing backups may be lost or may become corrupted.
To ensure that data about backups in the recovery catalog or control file is synchronized with actual files on disk or in the media management catalog, perform a crosscheck. The CROSSCHECK
command operates only on files that are recorded in the RMAN repository.
Figure 9-1 illustrates a crosscheck of the media manager. RMAN queries the RMAN repository for the names and locations of the four backup pieces to be checked. RMAN sends this information to the target database server, which queries the media management software about the backups. The media management software then checks its media catalog and reports back to the server that backup set 3
is missing. RMAN updates the status of backup set 3
to EXPIRED
in the repository. The record for backup set 3
will now be deleted if you run DELETE
EXPIRED
.
Figure 9-1 Crosschecking the Media Manager
Crosschecks are useful because they can
Update outdated information about backups that disappeared from disk or tape or became corrupted
Update the repository if you delete archived redo logs or other files with operating system commands
Use the crosscheck feature to check the status of a backup on disk or tape. If the backup is on disk, then CROSSCHECK
checks whether the header of the file is valid. If a backup is on tape, then the command checks that the backups exist in the media management software's catalog.
Backup pieces and image copies can have the status AVAILABLE
, EXPIRED
, or UNAVAILABLE
. You can view the status information in the output of the LIST
command and the recovery catalog views.
You can issue the DELETE
EXPIRED
command to delete all expired backups. RMAN removes the record for the expired file from the repository. If for some reason the file still exists on the media, then RMAN issues warnings and lists the mismatched objects that cannot be deleted.
Note:
TheCROSSCHECK
command does not delete operating system files or remove repository records. You must use the DELETE
command for these operations.See Also:
Oracle Database Backup and Recovery Basics to learn how to perform crosschecks
Oracle Database Backup and Recovery Reference for CROSSCHECK
syntax and a description of the possible status values
Oracle Database Backup and Recovery Reference for DELETE
syntax
Several RMAN commands can cause the deletion of backups or archived logs, either directly or as a consequence of a backup of those files.
This section contains the following topics:
Table 9-1 describes the functionality of the various RMAN commands that can cause backups to be deleted.
Table 9-1 Maintenance Commands and Scripts
Command or Script | Purpose |
---|---|
|
To delete backups, update the control file records to status You can specify that |
|
To back up archived logs, datafile copies, or backup sets, then delete the input files from the operating system after the successful completion of the backup. RMAN also deletes and updates repository records for the deleted input files. If you specify |
|
To delete recovery catalog records for specified backups and change their control file records to status |
See Also:
"Crosschecks of RMAN Backups"The DELETE
command can remove any file that the LIST
and CROSSCHECK
commands can operate on. For example, you can delete backup sets, archived redo logs, and datafile copies. The DELETE
command removes both the physical file and the catalog record for the file.
Always use DELETE
command within RMAN to remove RMAN backups, rather than an operating system or media manager utility or command. Otherwise, the RMAN repository can contain records of backups that are no longer available for use in restore operations.
If you delete backups without using RMAN, you can use one of the following methods within RMAN to update the RMAN repository directly without performing a crosscheck:
Use CROSSCHECK
to change the status of these files to EXPIRED
and then run DELETE
EXPIRED
to delete the records from the RMAN repository
Use CHANGE
...
UNCATALOG
to remove the catalog records
The DELETE
OBSOLETE
command provides a convenient way to delete backups that are no longer needed. It uses the same REDUNDANCY
and RECOVERY
WINDOW
options as the REPORT
OBSOLETE
command.
If you have configured a retention policy, then you can run DELETE
OBSOLETE
periodically to delete all backups considered obsolete by this policy. For example, you can run DELETE
OBSOLETE
in a script every night with a scheduling utility, freeing disk and tape space used by backups that are no longer needed.
Note that using a flash recovery area as the destination for all backups eliminates the need to manage obsolete backups. Obsolete backups will be deleted from the flash recovery area automatically as disk space is needed to store backup-related files.
See Also:
"Reports of Obsolete Backups"The CROSSCHECK
command updates the repository status for a backup to EXPIRED
when it cannot locate it at the location to which it was backed up. This condition could occur if, for example, a backup was deleted from disk at the operating system level. You can identify expired backups by running the CROSSCHECK
command as in the following example:
RMAN> CROSSCHECK BACKUP; crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=0ad8d32i_1_1 recid=10 stamp=445025363 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=c-1334876723-20011105-00 recid=11 stamp=445025367 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=0cd8d361_1_1 recid=12 stamp=445025473 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=c-1334876723-20011105-01 recid=13 stamp=445025475 Crosschecked 4 objects
If you run CROSSCHECK
while some backup device is temporarily not accessible, this can mark backups as expired when in fact they are still present on the backup device, and are available again once the device is accessible. For example, this can happen if a disk is unmounted or if RMAN does not correctly connect to a media manager. In such a case, fix the problem that prevented RMAN from finding the backups and rerun CROSSCHECK
.
The DELETE
EXPIRED
command removes the record of expired backups from the RMAN repository, by actually deleting the recovery catalog records for expired backups, and updates their control file records to status DELETED
.
This command is especially useful if a user inadvertently deletes RMAN backups or archived logs from disk with an operating system utility. In such a case, the RMAN repository is not synchronized with the actual contents of disk. By running the CROSSCHECK
command, RMAN marks the backups that it cannot find as expired. Then, you can run DELETE
EXPIRED
to remove the records for these files.
You may want to delete files such as archived logs only if they have been backed up a specified number of times to tape. The DELETE
command supports this behavior. The following example deletes all archived redo logs that have already been backed up at least two times to tape:
RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
The BACKUP
...
DELETE
INPUT
command can delete archived redo logs, datafile copies, and backup sets after backing them up. This functionality is especially useful when backing up archived logs on disk to tape. RMAN backs up one copy of each log sequence number, and then deletes the file that it backs up. For example, assume that you issue:
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
In this command, RMAN backs up one copy of each log for each available sequence number, and then deletes only the archived redo log file that it actually backs up. If you have multiple redo log archiving destinations, the other copies of the same log sequence number are not deleted.
If you specify the DELETE
ALL
INPUT
option, then RMAN deletes whichever files match the criteria that you specify, even if there are several files of the same log sequence number in different archiving destinations. For example, assume that you archive to three different directories. Then, you issue this command:
RMAN> BACKUP ARCHIVELOG ALL FROM SEQUENCE 1200 DELETE ALL INPUT;
In this case, RMAN backs up only one copy of each log sequence between 1200 and the most recent sequence, but deletes all logs with these sequence numbers from all three archive destinations.
Note:
If you want to delete only a subset of the logs being backed up, you can useBACKUP
without the DELETE
INPUT
clause, and then use a separate DELETE
command with a BACKED
UP
n
TIMES
TO
DEVICE
TYPE
clause.
In particular, in a Data Guard environment, it is not recommended to use BACKUP... DELETE INPUT to delete archived logs, as RMAN cannot delete the logs generated on another node. A separate DELETE
command with a BACKED
UP
n
TIMES
TO
DEVICE
TYPE
SBT
command provides more direct control over deleting logs in this environment.
During backup of archived redo logs, RMAN checks the file being backed up for corruption. If corruption is found, RMAN automatically switches to reading another copy of the same archived redo log, if one exists. For example, assume that /log1
and /log2
are the only enabled archiving destinations, and that they contain logs with sequence number up through 150. You run this command:
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 123 DELETE ALL INPUT;
RMAN can start reading from any copy of a given log, and switch to reading a different copy if it is unable to read the first copy in its entirety. For example, if RMAN starts reading the copy of log sequence 123 from /log1
and discovers corruption in the file, it continues reading from the copy in /log2
. Because DELETE
ALL
INPUT
is specified, RMAN deletes all copies of logs on disk of sequence 123 and higher.
Every RMAN backup produces a corresponding record in the RMAN repository. This backup record is stored in the control file. In addition, if a recovery catalog is used, the record is also stored in the recovery catalog once it has been resynced with the control file.
For example, if you generate a full database backup set, then you can view the record for this backup set in the V$BACKUP_SET
control file view. If you use a recovery catalog, then you can also access the record in the RC_BACKUP_SET
catalog view.
The V$
control file views and recovery catalog tables differ in the way that they store information, and this affects how RMAN handles repository records. The recovery catalog RMAN repository is stored in actual database tables, while the control file version of the repository is stored in an internal structure in the control file.
When you use an RMAN command to delete a backup, RMAN performs the following steps:
Removes the physical file from the operating system (if the file is still present)
Updates the backup records in the control file to status DELETED
Removes the backup records from the recovery catalog tables (if RMAN is connected to a recovery catalog)
Because of the way that control file data is stored, RMAN cannot remove the record from the control file, only update it to DELETED
status. However, because the catalog tables are ordinary database tables, RMAN removes rows from them in the same way that rows are deleted from any table.
The RMAN repository record for an object can sometimes fail to reflect the physical status of the object. For example, you backup an archived redo log to disk and then use an operating system utility to delete the object. If you do not run the CROSSCHECK
command to update the repository, and if you then run DELETE
against the object, then the repository shows that the object is AVAILABLE
while the object is in fact missing. The following table indicates the behavior of DELETE
in such situations.
Repository Status | Physical Status | Behavior of DELETE Command |
---|---|---|
AVAILABLE |
Not found on media | Does not delete the object and reports the list of mismatched objects at the end of the job. RMAN does not update the repository status. |
EXPIRED |
Found on media | Does not delete the object and reports the list of mismatched objects at the end of the job. RMAN does not update the repository status. |
UNAVAILABLE |
Any | Removes repository record and deletes object if it exists. All I/O errors are ignored. |
If you use the FORCE
option of DELETE
, RMAN will remove the repository record and delete the file if it exists. All I/O errors are ignored, and RMAN displays the number of objects deleted at the end of the job.
RMAN can update the repository to show backups as AVAILABLE
or UNAVAILABLE
. An unavailable backup is one that cannot be accessed at a particular moment but that has not been deleted. For example, you may have backups on tapes that are temporarily stored offsite and are inaccessible. You can use the CHANGE
...
UNAVAILABLE
command to update the repository status for these backups to UNAVAILABLE
so that RMAN will not select them for use in recovery operations.
When the tapes become available again, you can issue the CHANGE
...
AVAILABLE
command to update the RMAN repository to show that these backups now can be used. After setting the files back to status AVAILABLE
, you can also run a CROSSCHECK
to verify that RMAN can access the files.
See Also:
Oracle Database Backup and Recovery Basics to learn how to make backups available or unavailable
Oracle Database Backup and Recovery Reference for CHANGE
syntax
Use CHANGE... KEEP
or CHANGE... NOKEEP
to specify whether a backup should be subject to the configured retention policy or kept until a different date or even indefinitely.
The KEEP
option exempts a backup from the current retention policy either indefinitely or until the specified UNTIL
time. RMAN does not mark the files as obsolete even if they would be considered obsolete under the retention policy. Such backups are called long-term backups. CHANGE
...
NOKEEP
is used to undo the effects of CHANGE
... KEEP
, so that the configured retention policy applies to the backup.
For example, the following command blocks RMAN from considering backupsets with the tag 'year_end_2002'
as obsolete under the retention policy:
RMAN> CHANGE BACKUPSET TAG year_end_2002 KEEP FOREVER NOLOGS;
To allow backupsets with the tag year_end_2002
to be marked as obsolete based on the retention policy, use this command:
RMAN> CHANGE BACKUPSET TAG year_end_2002 NOKEEP;
If you want to prevent the use of a backup marked with KEEP
in restore and recovery operations, then mark these backups as UNAVAILABLE
. RMAN will not delete the records for these backups from the RMAN repository, but will not try to use them in restore and recovery until they are marked AVAILABLE
again.
See Also:
Oracle Database Backup and Recovery Reference for CHANGE...KEEP
and CHANGE...NOKEEP
syntax
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