7 Reporting on RMAN Operations

The RMAN LIST and REPORT commands, generate reports on backup activities based on the RMAN repository. Use SHOW ALL to display the current RMAN configuration. You can also query the views described in "Backup and Recovery Views".

Listing Backups

Run the LIST BACKUP and LIST COPY commands to display information about backups and datafile copies listed in the repository. You can display specific objects, as in the following examples:

RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cf.cpy";
RMAN> LIST BACKUPSET OF DATAFILE 1;

For backups, you can control the format of LIST output with these options:

Parameter	Example	Explanation
`BY` `BACKUP`	`LIST` `BACKUP` `OF` `DATABASE` `BY` `BACKUP`	Organizes the output by backup set. This is the default mode of presentation.
`BY` `FILE`	`LIST` `BACKUP` `BY` `FILE`	Lists the backups according to which file was backed up.
`SUMMARY`	`LIST` `BACKUP` `SUMMARY`	Displays summary output. By default, the output is `VERBOSE`.

For both backups and copies you have the following additional options:

Parameter	Example	Explanation
`EXPIRED`	`LIST` `EXPIRED` `COPY`	Lists backups that are recorded in the RMAN repository but that were not present at the expected location on disk or tape during the last CROSSCHECK command. Such backups may have been deleted outside of RMAN.
`RECOVERABLE`	`LIST` `BACKUP RECOVERABLE`	Specifies datafile backups or copies that are available and that can be restored and recovered in the current database incarnation.

Reporting on Database Files and Backups

The REPORT command performs more complex analysis than LIST. Some of the main options are:

Parameter	Example	Explanation
`NEED` `BACKUP`	`REPORT` `NEED` `BACKUP DATABASE`	Shows which files need backing up under current retention policy. Use optional `REDUNDANCY` and `RECOVERY` `WINDOW` parameters to specify different criteria.
`OBSOLETE`	`REPORT` `OBSOLETE`	Lists backups that are obsolete under the configured retention policy. Use optional `REDUNDANCY` and `RECOVERY` `WINDOW` parameters to specify criteria.
`UNRECOVERABLE`	`REPORT` `UNRECOVERABLE`	Lists all datafiles for which an unrecoverable operation has been performed against an object in the datafile since the last backup of the datafile.
`SCHEMA`	`REPORT` `SCHEMA`	Reports the tablespaces and datafiles in the database at the current time (default) or a different time.

Monitoring RMAN Through V$ Views

Status information for jobs in progress and completed jobs is stored in V$RMAN_STATUS. V$RMAN_OUTPUT contains the text ouptut of all RMAN jobs.

To see status information on jobs in V$RMAN_STATUS use the following query:

SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from
V$RMAN_STATUS;

To correlate a channel with a process, run the following query in SQL*Plus while the RMAN job is executing:

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

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

To calculate the progress of an RMAN job, run the following query in SQL*Plus while the RMAN job is executing:

SQL> 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;