RMAN Commands , 45 of 60

REPORT

Purpose

To perform detailed analyses of the RMAN repository. Oracle writes the output from the REPORT command to standard output or the message log file.

Use the REPORT command to answer questions such as the following:

Which files need a backup?
Which files have not had a backup for some time?
Which files are not recoverable due to unrecoverable operations?
Which backup files can be deleted?
What was the physical schema of the database at a previous time?

See Also:
Oracle9i Recovery Manager User's Guide to learn how to use RMAN's reporting functionality

Restrictions and Usage Notes

Execute this command only at the RMAN prompt.
You must connect to a recovery catalog when issuing a REPORT SCHEMA command with the AT TIME, AT SCN, or AT SEQUENCE options. Otherwise, a recovery catalog is not required for the REPORT command.

Keywords and Parameters

`NEED` `BACKUP`	Lists all datafiles in need of a new backup. The report assumes that you will use the most recent backup for restore operations. 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`), RMAN generates an error.
	`INCREMENTAL` `=` integer	Specifies a threshold number of incremental backups required for recovery. If complete recovery of a datafile requires more than integer incremental backups, then the datafile requires a new full backup. The `REPORT` command, like the RECOVER command, uses the lowest level of incremental backup whenever there is a choice. This is the same strategy that RMAN would use if the file were actually being recovered by the `RECOVER` command. Note: Files for which no backups exist will not appear in this list: issue the `REPORT` `NEED` `BACKUP` `REDUNDANCY` command to display them.
	`DAYS` `=` integer	Specifies a threshold number of days worth of logs needed to recover the file. For example, `REPORT` `NEED` `BACKUP` `DAYS` `7` `DATABASE` shows the datafiles whose recovery requires more than one week's worth of archived redo logs. 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.
	`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.
	`RECOVERY WINDOW` `OF` integer `DAYS`	Specifies a time window in which RMAN should be able to recover the database. The window stretches from the current time (`SYSDATE`) to the point of recoverability, which is the earliest date to which you want to recover. The point of recoverability is integer days in the past, that is, `SYSDATE` `-` integer.
`UNRECOVERABLE`	Lists all unrecoverable datafiles. 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. 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.
reportObject	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.
	`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.
	`DATABASE`	Lists backups or datafile copies of all files in the current database. Specify `SKIP` `TABLESPACE` tablespace_name to exclude the specified tablespace from the `DATABASE` specification.
`SCHEMA`	Lists the names of all datafiles and tablespaces at the specified point in time.
atClause	Specifies a point in time as a time, an SCN, or a log sequence number.
	`AT` `TIME` `=` `'`date_string`'`	Specifies a date. The `NLS_LANG` and `NLS_DATE_FORMAT` environment variables specify the format for the time.
	`AT` `SCN` `=` integer	Specifies an SCN.
	`AT` `SEQUENCE` `=` integer `THREAD` `=`integer	Specifies a log sequence number for a specified redo `THREAD` number. The integer indicates the time when the specified log and thread were first opened.
`OBSOLETE` obsOperandList	Lists full backups and datafile copies recorded in the RMAN repository that can be deleted because they are no longer needed. 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`. If you use this option in conjunction with `DEVICE` `TYPE`, then RMAN only considers backups and copies created on the specified device.
`DEVICE` `TYPE` deviceSpecifier	Specifies the type of storage device. RMAN only considers backups and copies available on the specified device for its report.

Report Output

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

Table 2-16, "Report of Database Schema"
Table 2-17, "Report of Obsolete Backups and Copies"
Table 2-18, "Report of Files that Need Backup Due to Unrecoverable Operations"
Table 2-19, "Report of Files with Less Than n Redundant Backups"
Table 2-20, "Report of Files Whose Recovery Needs More Than n Days of Archived Logs"

Table 2-21, "Report of Files That Need More than n Incrementals During Recovery"

Table 2-16 Report of Database Schema

Column	Indicates
`File`	The absolute datafile number.
`K-bytes`	The size of the file in kilobytes.
`Tablespace`	The tablespace name.
`RB segs`	`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`	The filename of the datafile.

Table 2-17 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-18 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.

Table 2-19 Report of Files with Less 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-20 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-21 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.

Examples

Reporting Database Schema: Example

This example reports the names of all datafiles and tablespaces in the database one week ago:

REPORT SCHEMA AT TIME 'SYSDATE-7';

Report of database schema
File K-bytes    Tablespace           RB segs Name
---- ---------- -------------------- ------- -------------------
1         47104 SYSTEM               YES     /vobs/oracle/dbs/tbs_01.f
2           978 SYSTEM               YES     /vobs/oracle/dbs/tbs_02.f
3           978 TBS_1                NO      /vobs/oracle/dbs/tbs_11.f
4           978 TBS_1                NO      /vobs/oracle/dbs/tbs_12.f
5           978 TBS_2                NO      /vobs/oracle/dbs/tbs_21.f
6           978 TBS_2                NO      /vobs/oracle/dbs/tbs_22.f
7           500 TBS_3                NO      /vobs/oracle/dbs/tbs_31.f
8           500 TBS_3                NO      /vobs/oracle/dbs/tbs_32.f
9          5120 SYSTEM               YES     /vobs/oracle/dbs/tbs_03.f

Reporting Datafiles Needing Incremental Backups: Example

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

REPORT NEED BACKUP INCREMENTAL 5 DATABASE;

Report of files that need more than 5 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
1    9            /vobs/oracle/dbs/tbs_01.f
2    9            /vobs/oracle/dbs/tbs_02.f
3    9            /vobs/oracle/dbs/tbs_11.f
4    9            /vobs/oracle/dbs/tbs_12.f

Reporting Datafiles Needing Backups: Example

The following example reports all datafiles from tablespace SYSTEM that will need more than two days of archived redo logs to be applied during recovery after being restored from the most recent backup:

REPORT NEED BACKUP DAYS 2 TABLESPACE SYSTEM; 

Report of files whose recovery needs more than 2 days of archived logs
File Days  Name
---- ----- -----------------------------------------------------
1    3     /vobs/oracle/dbs/tbs_01.f
2    3     /vobs/oracle/dbs/tbs_02.f
16   3     /vobs/oracle/dbs/tbs_03.f

Reporting Unrecoverable Datafiles: Example

The following example reports all datafiles that cannot be recovered from existing backups because redo may be missing:

REPORT UNRECOVERABLE;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4     FULL                   /vobs/oracle/dbs/tbs_12.f

Reporting Obsolete Backups and Copies: Example

The following example reports obsolete backups and copies with a redundancy of 1:

REPORT OBSOLETE;

Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           836    04-OCT-00         
Backup Piece         839    04-OCT-00          /vobs/oracle/dbs/05aetj6b_1_1
Backup Set           807    04-OCT-00         
Backup Piece         810    04-OCT-00          /vobs/oracle/dbs/03aetj1f_1_1