About Querying the RMAN Metadata

You can obtain information from the RMAN repository in several different ways. The following table describes the basic options.

Method

Catalog Needed?

Description

LIST command

No

Use this command to list backups, copies, and database incarnations. The output displays those files operated on by the CHANGE, CROSSCHECK, and DELETE commands.

REPORT command

No

Use this command to find out which files need a backup, which backups are no longer needed, which files are in the schema, and so forth.

SHOW command

No

Use this command to display persistent RMAN configuration settings.

PRINTSCRIPT command

Yes

Use this command to display the names of the scripts stored in the recovery catalog.

Recovery catalog fixed views

Yes

Query these views to access the catalog itself. Some information, such as the names and contents of the stored scripts, can only be obtained from the catalog views.

V$ fixed views

No

Query these views to access the target database control file. RMAN obtains metadata for the recovery catalog from the control file. Some V$ views such as V$DATAFILE_HEADER, V$PROCESS, and V$SESSION contain information not found in the catalog views.

The main source of information about RMAN is the REPORT and LIST command output. Use these commands to query the RMAN repository and determine what you have backed up as well as what you need to back up. This information is extremely helpful in developing an effective backup strategy.

The LIST command displays all RMAN backups (both backup sets and proxy copies) and copies, while the REPORT command performs more complex analysis. For example, you can generate a report on which datafiles need a backup and which backup pieces are obsolete with the REPORT command. RMAN writes the output from the REPORT and LIST commands to either standard output or a log file.

The SHOW command displays persistent configuration settings. For example, if you allocate automatic channels with the CONFIGURE command, these settings are displayed in the SHOW output.

Listing RMAN Backups, Copies, and Database Incarnations

The LIST command queries the recovery catalog or control file and produces a listing of the backups, copies, archived redo logs, and database incarnations recorded there. You can specify these files when running the CHANGE, CROSSCHECK, and DELETE commands.

About RMAN Lists

You can control how the output is displayed by using the BYBACKUP and BYFILE options and choosing between the SUMMARY and VERBOSE options.

The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository. For example, you can list:

Backups (backup sets and proxy copies) or image copies recorded in the RMAN repository

Backups or image copies of a specified database, tablespace, datafile, archived redo log, or control file

Backups and image copies that have expired

Backups and image copies restricted by options such as time, path name, device type, tag, or recoverability

Incarnations of a specified database

Use the RMAN repository to determine what you need to back up. In particular, ensure that:

The STATUS columns of the output tables list all backups and image copies as AVAILABLE

All datafiles, archived redo logs, and control files that you need backed up are included in the output

The backups and copies recorded in the repository are recent

Listing Backups by Backup

By default, RMAN lists backups by backup, which means that it serially lists each backup set or proxy copy and then identifies the files included in the backup. By default, RMAN lists backups and copies in verbose mode, which means that it provides extensive, multiline information.

To list backups by backup:

After connecting to the target database and recovery catalog (if you use one), execute LISTBACKUP. Specify the desired objects with the listObjList clause. For example, you can enter:

LIST BACKUP; # lists backup sets, backup pieces, and proxy copies

Optionally, specify the EXPIRED keyword to identify those backups that were not found during a crosscheck:

Listing Backups by File

You can list copies of datafiles, control files, and archived logs. Specify the desired objects with the listObjList or recordSpec clause (refer to Oracle9i Recovery Manager Reference). If you do not specify an object, then RMAN displays copies of all database files and archived redo logs. By default, RMAN lists backups in verbose mode, which means that it provides extensive, multiline information.

To list backups by file:

After connecting to the target database and recovery catalog (if you use one), execute LISTBACKUP with the BYFILE option. Specify the desired objects and options. For example, you can enter:

LIST BACKUP BY FILE;

Optionally, specify the EXPIRED keyword to identify those backups that were not found during a crosscheck:

Listing Copies

Besides listing backup sets and proxy copies, you can list image copies. Specify the desired objects with the listObjList, recordSpec, or archivelogRecordSpecifier clauses. If you do not specify an object, then LISTCOPY displays all datafile copies, control file copies, and archived redo logs. Note that RMAN considers both archived redo logs and image copies of archived redo logs as copies. By default, RMAN lists backups in verbose mode which means that it provides extensive, multiline information.

To list image copies:

After connecting to the target database and recovery catalog (if you use one), execute LISTCOPY. Specify the desired objects and options. For example, you can enter:

Listing Backups in Summary Mode

By default the LIST output is highly detailed, but you can also specify that RMAN display the output in summarized form. Specify the desired objects with the listObjectList or recordSpec clause. If you do not specify an object, then LISTBACKUP displays all backups. By default, RMAN lists backups in verbose mode.

To list backups in summary mode:

After connecting to the target database and recovery catalog (if you use one), execute LISTBACKUP. Specify the desired objects and options. For example, you can enter:

LIST BACKUP SUMMARY;

Optionally, specify the EXPIRED keyword to identify those copies that were not found during a crosscheck:

About RMAN Reports

To gain more detailed information from the RMAN repository, generate a report. Use the REPORT command to answer questions such as the following:

Which files need a backup?

Which files have had unrecoverable operations performed on them?

Which backups or copies are obsolete and can be deleted?

What was the physical schema of the database at some previous time?

Which files have not been backed up recently?

Note:

For the report to be accurate, the RMAN repository must be synchronized with the control file and you must have run the CHANGE, UNCATALOG, and CROSSCHECK commands recently to update the status of all backups and copies. To learn how to maintain the RMAN repository refer to Chapter 16, "Managing the Recovery Manager Repository".

The information that you obtain from reports can be extremely important for your backup and recovery strategy. In particular, run the REPORTNEEDBACKUP and REPORTUNRECOVERABLE commands regularly to ensure the following:

The necessary backups are available to perform recovery.

Recovery can be performed within a reasonable length of time, that is, that the mean time to recovery (MTTR) is minimized.

Reporting on Objects Needing a Backup

You can report on objects that require a backup by specifying the NEEDBACKUP keyword. The REDUNDANCY parameter specifies the minimum number of backups or copies that must exist for a datafile to be considered not in need of a backup. If you do not specify the parameter, REDUNDANCY defaults to 1. The DAYS parameter indicates that recovery must begin by using logs more than integer days old. The INCREMENTAL parameter indicates that more than integer incremental backups are required for complete recovery.

Note:

If you disable the retention policy, then REPORTNEEDBACKUP with no other options generates an error message.

To report on objects that need a backup:

After connecting to the target database and recovery catalog (if you use one), run CROSSCHECK commands as needed to update the status of backups and copies. Following is a possible crosscheck session:

To override the retention policy (or if you do not have a retention policy enabled), run REPORTNEEDBACKUPDAYS. Any files older than the DAYS parameter value need a new backup because their backups require the specified number of DAYS worth of archived logs for recovery. For example, run:

To determine which files need an incremental backup, specify the INCREMENTAL parameter. If complete recovery of a datafile requires more than the specified number of incremental backups, then RMAN considers it in need of a new backup. For example, enter:

Reporting on Obsolete Backups and Copies

You can report on objects that are obsolete, that is, superfluous, by specifying the OBSOLETE keyword. If you do not specify any other options, then REPORTOBSOLETE displays the backups and copies that are marked obsolete by the current retention policy. By default, the retention policy is configured to REDUNDANCY of 1.

The REPORTOBSOLETE command supports the RECOVERYWINDOW and REDUNDANCY options at the command level, which have the same meanings as the options with the same names on the CONFIGURE command.

To report on obsolete backups and copies:

After connecting to the target database and recovery catalog (if you use one), issue CROSSCHECK commands as needed to update the status of backups and copies. Following is a possible crosscheck session:

Use the OBSOLETE option to identify which backups are obsolete because they are no longer needed for recovery. For example, enter:

# lists backups or copies that are superfluous because they are not needed to recover
# the database to a random point within the past week
REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
# lists backups or copies that are superfluous because more than 2 copies of the
# files exist on tape
REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

Use the ORPHAN option to list unusable backups and copies belonging to an incarnation that is not a direct predecessor of the current incarnation (refer to "Reports of Orphaned Backups"). For example, enter:

REPORT OBSOLETE ORPHAN;

Optionally, delete those backups that are obsolete. You can automatically delete obsolete backups and copies by issuing the DELETEOBSOLETE command. For example, you can enter:

Note that RMAN prompts you for confirmation before actually deleting the files. To suppress the prompt, specify the NOPROMPT option of the DELETE command. Specify FORCE to delete the files and remove their repository records regardless of whether the files exist. RMAN ignores any I/O errors for the deleted objects.

Reporting on Unrecoverable Backups and Copies

Issue the REPORTUNRECOVERABLE command to determine which datafiles have had an unrecoverable operation performed against an object residing in the datafile after its last backup.

Assume that you perform an unrecoverable operation on the table employee by issuing an ALTERTABLEemployee...NOLOGGING statement. If the employee table is located in datafile 3, then the REPORT command can flag backups of this datafile as unrecoverable.

To report on backups and copies that are unrecoverable:

After connecting to the target database and recovery catalog (if you use one), issue REPORTUNRECOVERABLE. For example, enter:

Reporting on the Database Schema

You do not have to use V$ or recovery catalog views to identify the database files. Issue REPORTSCHEMA to list the files. If you use a recovery catalog, then you also generate historical reports of the database schema at a past time. You do not need a recovery catalog, however, to report the current schema.

To report the database schema at a specified point in time:

After connecting to the target database and recovery catalog (if you use one), issue REPORTSCHEMA for a list of all the datafiles and tablespaces in the target database at the current time:

REPORT SCHEMA;

If you use a recovery catalog, then you can use the atClause to specify a past time, SCN, or log sequence number:

REPORT SCHEMA AT TIME 'SYSDATE-14'; # schema as it existed two weeks ago
REPORT SCHEMA AT SCN 1000; # schema as it existed at scn 1000
REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema as it existed at log sequence 100

Showing All RMAN Configuration Settings

You can use the CONFIGURE command to specify a variety of persistent settings for the RMAN environment. The SHOWALL command displays both the CONFIGURE commands that you have issued as well as RMAN's default configurations. Note that you can return any CONFIGURE command to its default setting by running CONFIGURE...CLEAR.

To show all RMAN configuration settings:

After connecting to the target database and recovery catalog (if you use one), run the SHOWALL command. For example, enter the following:

SHOW ALL; # shows all CONFIGURE settings, both user-entered and default

Note that the output is displayed so that you can paste it into a script and run it as an RMAN command file; hence, you can easily change your entire configuration. You can even run the script on a different target database.

Showing the RMAN Retention Policy Configuration Settings

You can use the CONFIGURERETENTIONPOLICY command to specify either the number of days in the recovery window or the level of redundancy. By default, the retention policy is set to REDUNDANCY=1.

To show the configuration policy:

After connecting to the target database and recovery catalog (if you use one), run the SHOWRETENTIONPOLICY command. For example, enter:

SHOW RETENTION POLICY; # shows the CONFIGURE setting for the retention policy

Showing the Default Device Type

Issue the SHOWDEFAULTDEVICETYPE command to display the settings for the default device type used by the automatic channels. When you issue the BACKUP command, RMAN allocates only default channels of the type set by the CONFIGUREDEFAULTDEVICETYPE command. This default device type setting is not in effect when you use commands other than BACKUP. Note that you cannot disable the default device type: it is always either DISK (default setting) or sbt.

To show the default device type for automatic channels:

After connecting to the target database and recovery catalog (if you use one), run the SHOWDEFAULTDEVICETYPE command. For example, enter:

Showing the Number of Identical Copies of Each Backup

Use the CONFIGURE...BACKUPCOPIES command to set the number of identical copies that RMAN makes of each backup. For example, if the value is 3, RMAN produces a total of three identical copies of each backup piece in a backup set.

To show the number of identical copies of each backup:

After connecting to the target database and recovery catalog (if you use one), run the SHOWARCHIVELOGBACKUPCOPIES or SHOWDATAFILEBACKUPCOPIES commands. For example, enter:

Showing the Default Filenames Configured for Auxiliary Channels

You can use the CONFIGUREAUXNAME command to set persistent filenames for auxiliary channels. For example, you can give new filenames for duplicate or standby datafiles, or datafiles in a TSPITR operation. Issue the SHOWAUXNAME command to display these filenames.

Note:

In releases prior to Oracle9i, the CONFIGUREAUXNAME command was called SETAUXNAME.

To show persistent settings for auxiliary filenames:

After connecting to the target database and recovery catalog (if you use one), issue the SHOWAUXNAME command. For example, enter:

Printing Scripts Stored in the Recovery Catalog

To print the text of a specified stored script, either run the PRINTSCRIPT command or query the RC_STORED_SCRIPT_LINE catalog view. To display a list of RMAN stored scripts, query the RC_STORED_SCRIPT catalog view.

Querying the Recovery Catalog Views

The LIST, REPORT, and SHOW commands should provide you with all the repository information that you require. Nevertheless, you can sometimes also obtain useful information from the recovery catalog views, which are views in the catalog schema prefixed with RC_.

About Queries to the Recovery Catalog Views

The recovery catalog views are not normalized, but are optimized for RMAN usage rather than user queries. RMAN obtains backup and recovery information from the target database control file and stores it in the catalog tables.

In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST, REPORT, and SHOW commands. If you have 10 different target databases registered in the same recovery catalog, then the catalog views show the information for all incarnations of all databases registered in the catalog. You often have to perform joins among the views to distinguish the specific incarnation of the target database that you are interested in.

Most of the catalog views have a corresponding dynamic performance view in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the catalog and server views is that each catalog view contains information about all the databases registered in the catalog, whereas the server view contains information only about itself. The two types of views often use different primary keys to uniquely identify rows.

Distinguishing a Database in the Catalog Views

Most of the catalog views contain the columns DB_KEY and DBINC_KEY. Each target database can be uniquely identified by either the primary key, which is the DB_KEY column value, or the DBID, which is the 32-bit unique database identifier. Each incarnation of each target database is uniquely identified by the DBINC_KEY primary key. When querying information about a specific incarnation of a target database, you should use these columns to specify the database. Then, you can perform joins with most of the other catalog views to obtain the desired information.

Distinguishing a Database Object in the Catalog Views

An important difference between catalog and V$ views is that a different system of unique identifiers is used for backup and recovery objects. For example, many V$ views such as V$ARCHIVED_LOG use the RECID and STAMP columns to form a concatenated primary key. The corresponding catalog view uses a derived value as its primary keys and stores this value in a single column. For example, the primary key in RC_ARCHIVED_LOG is the AL_KEY column. The AL_KEY column value is the primary key that RMAN displays in the LIST command output.

Querying Catalog Views for the Target DB_KEY or DBID Values

The DB_KEY value which is the primary key for a target database, is used only in the recovery catalog. The easiest way is to obtain the DB_KEY is to use the DBID of the target database, which is displayed whenever you connect RMAN to the target database. The DBID, which is a unique system-defined number given to every Oracle database, is what distinguishes one target database from another target database in the RMAN metadata.

Assume that you want to obtain information about one of the target databases registered in the recovery catalog. You can easily determine the DBID from this database either by looking at the output displayed when RMAN connects to the database, or querying a V$ view as in the following:

SELECT DBID
FROM V$DATABASE;
DBID
---------
598368217

You can then obtain the DB_KEY for a target database by running the following query, where dbid_of_target is the DBID that you previously obtained:

SELECT DB_KEY
FROM RC_DATABASE
WHERE DBID = dbid_of_target;

To obtain information about the current incarnation of a target database, specify the target database DB_KEY value and perform a join with RC_DATABASE_INCARNATION by using a WHERE condition to specify that the CURRENT_INCARNATION column value is set to YES. For example, to obtain information about backup sets in the current incarnation of a target database with the DB_KEY value of 1, you can execute this script:

You should use the DB_NAME column to specify a database onlyif you do not have more than one database registered in the recovery catalog with the same DB_NAME. RMAN permits you to register more than one database with the same database name, but requires that the DBID values be different. For example, you can have ten databases with the DB_NAME value of prod1, each with a different DBID. Because the DBID is the unique identifier for every database in the metadata, use this value to obtain the DB_KEY and then use DB_KEY to uniquely identify the database.

The following example lists all copies of datafile 2 with the tag df2__copy that are in the /copy directory:

LIST COPY OF DATAFILE 2 TAG df2_copy LIKE '/copy/%';

Reporting Backups and Copies Not Needed for the Recovery Window: Example

Use the REPORT command to determine which copies and backups are superfluous and so can be deleted. For example, if you only need to be able to recover the database to a point within the last two weeks, then issue this command:

REPORT OBSOLETE RECOVERY WINDOW OF 14 DAYS;

You can then delete these obsolete backups and copies by issuing this command:

DELETE OBSOLETE RECOVERY WINDOW OF 14 DAYS;

Reporting Redundant Backups and Copies: Example

The following command reports all backups and copies on disk that are obsolete because three more recent backups or copies are already available:

REPORT OBSOLETE REDUNDANCY 3 DEVICE TYPE DISK;

The following command reports all backups on tape that are obsolete because at least two backups already exist that were made no more than one week ago:

REPORT OBSOLETE REDUNDANCY 2 UNTIL TIME 'SYSDATE-7' DEVICE TYPE sbt;

Generating Historical Reports of Database Schema: Example

The following commands reports the database schema in the present, a week ago, and on September 20, 2000:

REPORT SCHEMA;
REPORT SCHEMA AT TIME 'SYSDATE-7';
REPORT SCHEMA AT TIME "TO_DATE('09/20/01','MM/DD/YY')";

The following command reports on the database schema at SCN 953:

REPORT SCHEMA AT SCN 953;

The following command reports on the database schema at log sequence number 12 of thread 2:

REPORT SCHEMA AT SEQUENCE 12 THREAD 2;

Listing Database Incarnations: Example

Every time that you perform a RESETLOGS operation on a database, you create a new incarnation. This example lists all database incarnation of trgt registered in the recovery catalog: