Recovery Manager Command Syntax

report

Syntax

atClause::=

reportObject::=

Purpose

To perform detailed analyses of the RMAN metadata. Oracle writes the output from the report command to standard output or the message log file (see "connect").

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

Which files need a backup?
Which files have not had a backup in a while?
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?

Requirements

Execute this command only at the RMAN prompt.
You must use a recovery catalog when issuing a report schema command with the at time, at scn, or at logseq 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.

incremental integer

specifies a threshold number of incremental backups. If complete recovery of a datafile requires more than the specified number of 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 of redo log files that need application during recovery of this 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, RMAN makes the following optimizations to this report:

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

Files that were offline and are now online, and whose most recent backup contains all changes up to the offline time, will only be 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.

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 non-existence 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, provided that redo logs starting from when the file was created still exist.

reportObject clause

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.

skip tablespace tablespace_name

excludes the specified tablespaces from the database specification.

obsolete

lists full backups and datafile copies recorded in the RMAN metadata that can be deleted because they are no longer needed. If you do not specify further parameters, redundancy defaults to 1. If you use this option in conjunction with device type, RMAN only considers backups and copies on the specified type.

redundancy integer

specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete. A datafile copy is obsolete if there are at least integer more recent backups or image copies of this file; a datafile backup set is obsolete if there are at least integer more recent backups or image copies of each file contained in the backup set. For example, redundancy 2 means that there must be at least two more recent backups or copies of a datafile for any other backup or copy to be obsolete.

untilClause

specifies that no backup or copy will be considered obsolete if there are at least n (where n is the value for redundancy) backups or copies that are more recent but do not contain changes later than the specified time, SCN, or log sequence number. For example, obsolete redundancy 2 until 'SYSDATE-7' means that a backup or copy is obsolete if there are at least two backups or copies that are more recent and those copies were checkpointed more than a week ago.
This clause is useful if the database must be recoverable to non-current time, SCN, or log sequence number. See "untilClause".

orphan

specifies as obsolete those backups and copies that are unusable because they belong to incarnations of the database that are not direct ancestors of the current incarnation. For an explanation of orphaned backups, see "Reporting on Orphaned Backups".

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 logseq integer

specifies a log sequence number for a specified redo thread. The integer indicates the time when the specified log and thread were first opened.

device type deviceSpecifier

specifies the type of storage device. RMAN only considers backups and copies available on the specified device for its report. See "deviceSpecifier".

Report Output

The fields in each report are described below:

Table 11-9 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.
NAME	the filename of the datafile.

Table 11-10 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 11-11 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 11-12 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 11-13 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 11-14 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

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

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
5    9            /vobs/oracle/dbs/tbs_21.f
6    9            /vobs/oracle/dbs/tbs_22.f
7    9            /vobs/oracle/dbs/tbs_23.f
8    9            /vobs/oracle/dbs/tbs_03.f

Reporting Datafiles Needing Backups

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

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

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-DEC-98         
Backup Piece         839    04-DEC-98          /vobs/oracle/dbs/05aetj6b_1_1
Backup Set           807    04-DEC-98         
Backup Piece         810    04-DEC-98          /vobs/oracle/dbs/03aetj1f_1_1
Backup Set           835    04-DEC-98         
Backup Piece         838    04-DEC-98          /vobs/oracle/dbs/04aetj6b_1_1

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.
	incremental integer	specifies a threshold number of incremental backups. If complete recovery of a datafile requires more than the specified number of 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 of redo log files that need application during recovery of this 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, RMAN makes the following optimizations to this report: Files that are offline and whose most recent backup contains all changes to the file will not be included. Files that were offline and are now online, and whose most recent backup contains all changes up to the offline time, will only be 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.
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 non-existence 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, provided that redo logs starting from when the file was created still exist.
reportObject clause	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.
	skip tablespace tablespace_name	excludes the specified tablespaces from the database specification.
obsolete	lists full backups and datafile copies recorded in the RMAN metadata that can be deleted because they are no longer needed. If you do not specify further parameters, redundancy defaults to 1. If you use this option in conjunction with device type, RMAN only considers backups and copies on the specified type.
	redundancy integer	specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete. A datafile copy is obsolete if there are at least integer more recent backups or image copies of this file; a datafile backup set is obsolete if there are at least integer more recent backups or image copies of each file contained in the backup set. For example, redundancy 2 means that there must be at least two more recent backups or copies of a datafile for any other backup or copy to be obsolete.
	untilClause	specifies that no backup or copy will be considered obsolete if there are at least n (where n is the value for redundancy) backups or copies that are more recent but do not contain changes later than the specified time, SCN, or log sequence number. For example, obsolete redundancy 2 until 'SYSDATE-7' means that a backup or copy is obsolete if there are at least two backups or copies that are more recent and those copies were checkpointed more than a week ago. This clause is useful if the database must be recoverable to non-current time, SCN, or log sequence number. See "untilClause".
	orphan	specifies as obsolete those backups and copies that are unusable because they belong to incarnations of the database that are not direct ancestors of the current incarnation. For an explanation of orphaned backups, see "Reporting on Orphaned Backups".
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 logseq integer	specifies a log sequence number for a specified redo thread. The integer indicates the time when the specified log and thread were first opened.
device type deviceSpecifier	specifies the type of storage device. RMAN only considers backups and copies available on the specified device for its report. See "deviceSpecifier".