Skip Headers
Oracle® Database Backup and Recovery Reference
11g Release 1 (11.1)

Part Number B28273-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF



Use the REPORT command to perform detailed analyses of the RMAN repository. RMAN writes the report to standard output or the message log file.

See Also:

Oracle Database Backup and Recovery User's Guide to learn how to create RMAN reports


Execute this command only at the RMAN prompt. Either of the following conditions must be met:



Description of report.gif follows
Description of the illustration report.gif

(needBackupOption::=, atClause::=, reportObject::=, obsOperandList::=, deviceSpecifier::=)


Description of needbackupoption.gif follows
Description of the illustration needbackupoption.gif



Description of reportobject.gif follows
Description of the illustration reportobject.gif



Description of atclause.gif follows
Description of the illustration atclause.gif



This clause specifies the type of report.

Syntax Element Description
Lists files that require backups.

See Also: needBackupOption

OBSOLETE obsOperandList Lists full backups, datafile copies, and archived redo logs recorded in the RMAN repository that can be deleted because they are no longer needed. See Table 2-33 for description of output. The command works in two steps:
  1. For each datafile that has been backed up, RMAN identifies the oldest full backup, level 0 backup, or image copy that is not obsolete under the retention policy. Any backup of the datafile older than the one identified in this step is considered obsolete.

  2. Any archived redo logs and level 1 incremental backups that are older than the oldest nonobsolete full backup are considered obsolete. These files are obsolete because no full or level 0 backup exists to which they can be applied. Incremental level 1 backups or archived redo logs are not considered obsolete if they can be applied to nonobsolete level 0 or full backups.

The subclause obsOperandList describes the criteria that RMAN uses to determine what is obsolete. If you do not specify parameters in obsOperandList, then RMAN uses the options specified in CONFIGURE RETENTION POLICY (see Example 2-110). If you use this option in conjunction with DEVICE TYPE, then RMAN only considers backups and copies created on the specified device. If the retention policy is disabled, then RMAN does not consider any backups as obsolete. Thus, RMAN issues an error when you run REPORT OBSOLETE with no other options and the retention policy is NONE.

Note: A backup made with the KEEP UNTIL TIME clause is obsolete after the KEEP time passes, regardless of the configured retention policy settings.

SCHEMA Lists the names of all datafiles (permanent and temporary) and tablespaces for the target database at the specified point in time. See Table 2-28 for description of output.
Reports the names of all datafiles and tablespaces for the database specified by its DB_UNIQUE_NAME.

You can specify a database with db_unique_name or use ALL for all uniquely named databases recorded in the catalog for a particular DBID. A database is uniquely identified in the recovery catalog by a DBID and the value of the DB_UNIQUE_NAME initialization parameter.

RMAN must be connected to a recovery catalog. RMAN must be connected to a target database or SET DBID must have been run.

See Also: forDbUniqueNameOption for descriptions of the options in this clause

Specifies an SCN, log sequence number, or time.
UNRECOVERABLE reportObject Lists all unrecoverable datafiles. See Table 2-34 for description of output.

A datafile is considered unrecoverable if an unrecoverable operation has been performed against an object residing in the datafile since the last backup of the datafile. In an unrecoverable operation, redo is not generated. Examples are direct load of table data and updates with the NOLOGGING option.

Note: The nonexistence of any backup of a datafile is not sufficient reason to consider it unrecoverable. Such datafiles can be recovered through the use of the CREATE DATAFILE command, if redo logs starting from when the file was created still exist.



Specifies the type of storage device. RMAN only considers backups and copies available on the specified device for its report.


This clause reports only on files that need backups.

Syntax Element Description
NEED BACKUP Lists all datafiles in the specified reportObject that require a new backup.

The report assumes that you will restore the most recent backup. If you do not specify any option, then RMAN uses the current retention policy configuration. If the retention policy is disabled (CONFIGURE RETENTION POLICY TO NONE), then RMAN generates an error.

   DAYS integer Lists all datafiles requiring more than the specified number of days' worth of archived redo log files for complete recovery. For example, REPORT NEED BACKUP DAYS 7 DATABASE shows the datafiles whose recovery requires more than seven days' worth of archived redo logs. See Table 2-29 for description of output.

If the target database control file is mounted and current, then RMAN makes the following optimizations to this report:

  • Files that are offline and whose most recent backup contains all changes to the file are not included.

  • Files that were offline and are now online, and whose most recent backup contains all changes up to the offline time, are only reported if they have been online for more than the specified number of days.

   INCREMENTAL integer Specifies a threshold number of incremental backups required for recovery (see Example 2-109). If complete recovery of a datafile requires more than integer incremental backups, then the datafile requires a new full backup. See Table 2-30 for description of output.

Note: Files for which no backups exist will not appear in this list: issue the REPORT NEED BACKUP REDUNDANCY command to display them.


   OF integer DAYS

Reports datafiles for which there are not sufficient backups to satisfy a recovery window-based retention policy for the specified number of days, that is, datafiles without sufficient backups for point-in-time recovery to any point back to the time SYSDATE - integer. See Table 2-31 for description of output.
   REDUNDANCY integer Specifies the minimum number of backups or copies that must exist for a datafile to be considered not in need of a backup. In other words, a datafile needs a backup if there are fewer than integer backups or copies of this file. For example, REDUNDANCY 2 means that if there are fewer than two copies or backups of a datafile, then it needs a new backup. See Table 2-32 for description of output.
Specifies the object for which you are generating the report.


This subclause specifies the datafiles to be included in the report. The report can include the entire database (optionally skipping certain tablespaces), a list of tablespaces, or a list of datafiles. Note that RMAN includes objects from prior incarnations.

Syntax Element Description
DATABASE Lists backups or datafile copies of all files in the database.

Note: Specify SKIP TABLESPACE tablespace_name to exclude the specified tablespace from the DATABASE specification.

DATAFILE datafileSpec Lists the specified datafiles. RMAN reports on backups or datafile copies that contain at least one of the specified datafiles.
TABLESPACE tablespace_name Lists datafiles in the specified tablespace. RMAN reports on backups or datafile copies that include at least one datafile from a specified tablespace.


This subclause specifies a point in time as a time, SCN, or log sequence number. You must be connected to a recovery catalog when issuing a REPORT SCHEMA command with an AT clause.

Syntax Element Description
AT SCN integer Specifies an SCN.
AT SEQUENCE integer Specifies a log sequence number. The integer indicates the time when the specified log was first opened.
   THREAD integer Specifies a redo THREAD number. The integer indicates the time when the thread was first opened.
AT TIME 'date_string' Specifies a date (see Example 2-108). The NLS_LANG and NLS_DATE_FORMAT environment variables specify the format for the time.

Report Output

The information that appears in the output is described in the following tables:

Table 2-28 Report of Database Schema

Column Indicates


The absolute datafile number.


The size of the file in megabytes.


The tablespace name.

RB segs

For datafiles only. YES if rollback segments exist in the tablespace and NO if they do not (only if connected to the recovery catalog). If RMAN is not connected to the catalog, then *** is displayed.

Datafile Name

For permanent datafiles only. The filename of the datafile.


For tempfiles only. The maximum size of the tempfile.

Tempfile Name

For tempfiles only. The filename of the tempfile.

Table 2-29 Report of Files Whose Recovery Needs More Than n Days of Archived Logs

Column Indicates


The absolute file number of a datafile that requires more than n days of archived redo logs for recovery.


The number of days of archived redo data required for recovery.


The name of the datafile.

Table 2-30 Report of Files That Need More than n Incrementals During Recovery

Column Indicates


The absolute file number of a datafile that requires more than n incrementals for complete recovery.


The number of incremental backups required for complete recovery.


The name of the datafile.

Table 2-31 Report of Files That Must Be Backed Up to Satisfy n Days Recovery Window

Column Indicates


The absolute file number of a datafile that must be backed up to satisfy a recovery window of n days.


The number of days required for complete recovery.


The name of the datafile that requires backup.

Table 2-32 Report of Files with Fewer Than n Redundant Backups

Column Indicates


The absolute datafile number of a datafile with less than n redundant backups.


The number of backups that exist for this file.


The name of the file.

Table 2-33 Report of Obsolete Backups and Copies

Column Indicates


Whether the object is a backup set, backup piece, proxy copy, or datafile copy.


A unique key that identifies this backup in the target database control file.

Completion Time

The time that the backup or copy completed.


The filename or media handle of the backup or datafile copy.

Table 2-34 Report of Files that Need Backup Due to Unrecoverable Operations

Column Indicates


The absolute number of the datafile that needs a new backup due to unrecoverable operations.

Type Of Backup Required

FULL or INCREMENTAL, depending on which type of backup is necessary to ensure the recoverability of all of the data in this file. If FULL, then create a full backup, level 0 backup, or a datafile copy. If INCREMENTAL, then a full or incremental backup will also suffice.


The name of the datafile.


Example 2-108 Reporting a Database Schema

This example, which requires a recovery catalog, reports the names of all datafiles and tablespaces 20 minutes ago.

RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440';

Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    450      SYSTEM               YES     /disk1/oradata/prod/system01.dbf
2    197      SYSAUX               YES     /disk1/oradata/prod/sysaux01.dbf
3    20       UNDOTBS              YES     /disk1/oradata/prod/undotbs01.dbf
4    10       CWMLITE              YES     /disk1/oradata/prod/cwmlite01.dbf
5    10       DRSYS                YES     /disk1/oradata/prod/drsys01.dbf
6    10       EXAMPLE              YES     /disk1/oradata/prod/example01.dbf
7    10       INDX                 YES     /disk1/oradata/prod/indx01.dbf
8    10       TOOLS                YES     /disk1/oradata/prod/tools01.dbf
9    10       USERS                YES     /disk1/oradata/prod/users01.dbf
List of Temporary Files
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    40       TEMP                 32767       /disk1/oradata/prod/temp01.dbf

Example 2-109 Reporting Datafiles Needing Incremental Backups

This example reports all datafiles in the database that require the application of one or more incremental backups to be recovered to their current state:

Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
1    2            /disk1/oradata/prod/system01.dbf
2    2            /disk1/oradata/prod/sysaux01.dbf
3    2            /disk1/oradata/prod/undotbs01.dbf
4    2            /disk1/oradata/prod/cwmlite01.dbf
5    2            /disk1/oradata/prod/drsys01.dbf
6    2            /disk1/oradata/prod/example01.dbf
7    2            /disk1/oradata/prod/indx01.dbf
9    2            /disk1/oradata/prod/users01.dbf

Example 2-110 Reporting Obsolete Backups and Copies

The following example reports obsolete backups and copies that are redundant according to the current retention policy. The retention policy is set to redundancy 1.


RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          1022   19-FEB-07          /disk1/prod/arch/archive1_59_614712405.dbf
Archive Log          1023   19-FEB-07          /disk1/prod/arch/archive1_61_614712405.dbf
Archive Log          1024   19-FEB-07          /disk1/prod/arch/archive1_60_614712405.dbf
Archive Log          1025   19-FEB-07          /disk1/prod/arch/archive1_55_614712405.dbf
Backup Set           1032   19-FEB-07
  Backup Piece       1050   19-FEB-07         
Datafile Copy        1073   19-FEB-07         
Backup Set           1035   19-FEB-07
  Backup Piece       1053   19-FEB-07         
Datafile Copy        1074   19-FEB-07         
Datafile Copy        1075   19-FEB-07         
Datafile Copy        1076   19-FEB-07         
Datafile Copy        1077   19-FEB-07          /disk2/PROD/datafile/o1_mf_drsys_2xmz7wyc_.dbf
Datafile Copy        1078   19-FEB-07         
Datafile Copy        1079   19-FEB-07          /disk2/PROD/datafile/o1_mf_indx_2xmz81jg_.dbf
Datafile Copy        1081   19-FEB-07          /disk2/PROD/datafile/o1_mf_users_2xmz85vo_.dbf
Datafile Copy        1777   20-FEB-07          /disk2/users01.dbf