9 RMAN Backup and Repository Maintenance

Using the RMAN LIST Command

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.

RMAN Reports

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.

REPORT OBSOLETE REDUNDANCY 2;
REPORT OBSOLETE RECOVERY WINDOW OF 5 DAYS;

Summary of RMAN Methods for Deleting Backups

Table 9-1 describes the functionality of the various RMAN commands that can cause backups to be deleted.

Removal of Backups with the DELETE Command

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.

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

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;

Deletion of Files After Backup Using BACKUP ... DELETE INPUT

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.

RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 123 DELETE ALL INPUT;

How RMAN Deletes Backup Records from the RMAN Repository

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.

Behavior of DELETE Command When the Repository and Media Do Not Correspond

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.

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.

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.

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

rman channel=channel_id

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

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

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.

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:

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, 
       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

Monitoring RMAN Job Performance

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