REPORT

Purpose

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

Prerequisites

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

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

Syntax

report::=

Description of the illustration report.gif

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

needBackupOption::=

Description of the illustration needbackupoption.gif

(reportObject::=)

reportObject::=

Description of the illustration reportobject.gif

(datafileSpec::=)

atClause::=

Description of the illustration atclause.gif

Semantics

report

This clause specifies the type of report.

Syntax Element	Description
`needBackupOption`	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: 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. 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.
`forDbUniqueNameOption`	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
`atClause`	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.
`DEVICE` `TYPE` `deviceSpecifier`	Specifies the type of storage device. RMAN only considers backups and copies available on the specified device for its report.

needBackupOption

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.
`RECOVERY WINDOW` `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.
`reportObject`	Specifies the object for which you are generating the report.

reportObject

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.

atClause

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"
Table 2-29, "Report of Files Whose Recovery Needs More Than n Days of Archived Logs"
Table 2-30, "Report of Files That Need More than n Incrementals During Recovery"
Table 2-31, "Report of Files That Must Be Backed Up to Satisfy n Days Recovery Window"
Table 2-32, "Report of Files with Fewer Than n Redundant Backups"
Table 2-33, "Report of Obsolete Backups and Copies"
Table 2-34, "Report of Files that Need Backup Due to Unrecoverable Operations"

Table 2-28 Report of Database Schema

Column	Indicates
`File`	The absolute datafile number.
`Size(MB)`	The size of the file in megabytes.
`Tablespace`	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.
`Maxsize(MB)`	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
`File`	The absolute file number of a datafile that requires more than n days of archived redo logs for recovery.
`Days`	The number of days of archived redo data required for recovery.
`Name`	The name of the datafile.

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

Column	Indicates
`File`	The absolute file number of a datafile that requires more than n incrementals for complete recovery.
`Incrementals`	The number of incremental backups required for complete recovery.
`Name`	The name of the datafile.

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

Column	Indicates
`File`	The absolute file number of a datafile that must be backed up to satisfy a recovery window of n days.
`Days`	The number of days required for complete recovery.
`Name`	The name of the datafile that requires backup.

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

Column	Indicates
`File`	The absolute datafile number of a datafile with less than n redundant backups.
`#bkps`	The number of backups that exist for this file.
`Name`	The name of the file.

Table 2-33 Report of Obsolete Backups and Copies

Column	Indicates
`Type`	Whether the object is a backup set, backup piece, proxy copy, or datafile copy.
`Key`	A unique key that identifies this backup in the target database control file.
`Completion Time`	The time that the backup or copy completed.
`Filename/handle`	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
`File`	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.
`Name`	The name of the datafile.

Examples

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:

RMAN> REPORT NEED BACKUP INCREMENTAL 1;
 
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> REPORT OBSOLETE;

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         
 /disk2/PROD/backupset/2007_02_19/o1_mf_nnndf_TAG20070216T173839_2xnpmp0l_.bkp
Datafile Copy        1073   19-FEB-07         
 /disk2/PROD/datafile/o1_mf_system_2xmz5l5m_.dbf
Backup Set           1035   19-FEB-07
  Backup Piece       1053   19-FEB-07         
 /disk2/PROD/backupset/2007_02_19/o1_mf_nnndf_TAG20070219T111434_2xnpozym_.bkp
Datafile Copy        1074   19-FEB-07         
 /disk2/PROD/datafile/o1_mf_sysaux_2xmz6zdg_.dbf
Datafile Copy        1075   19-FEB-07         
 /disk2/PROD/datafile/o1_mf_undotbs_2xmz7rof_.dbf
Datafile Copy        1076   19-FEB-07         
 /disk2/PROD/datafile/o1_mf_cwmlite_2xmz7vrg_.dbf
Datafile Copy        1077   19-FEB-07          /disk2/PROD/datafile/o1_mf_drsys_2xmz7wyc_.dbf
Datafile Copy        1078   19-FEB-07         
 /disk2/PROD/datafile/o1_mf_example_2xmz7y5s_.dbf
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