Querying Recovery Catalog Views

The LIST, REPORT, and SHOW commands provide the easiest means of accessing the data in the control file and the recovery catalog. Nevertheless, you can sometimes also obtain useful information from the recovery catalog views, which are views in the catalog schema prefixed with RC_.

See Also:

Oracle Database Backup and Recovery Reference for reference information about the recovery catalog views

RMAN obtains backup and recovery metadata from the target database control file and stores it in the tables of the recovery catalog. The recovery catalog views are derived from these tables. Note that the recovery catalog views are not normalized or optimized for user queries.

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 any query of the recovery catalog views show the information for all incarnations of all databases registered in the catalog. You often have to perform complex selects and joins among the views to extract usable information about a specific database and incarnation.

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

Identifying Rows for 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 a target database is uniquely identified by the DBINC_KEY primary key. When querying data 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.

Identifying Rows for 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 used to distinguish among databases registered in the RMAN recovery catalog.

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, querying V$RMAN_OUTPUT, or querying a V$DATABASE 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 the following script:

SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME
  FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
  WHERE i.DB_KEY = 1
  AND i.DB_KEY = b.DB_KEY
  AND i.CURRENT_INCARNATION = 'YES';

You should use the DB_NAME column to specify a database only if 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.

Using RC_BACKUP_FILES and DBMS_RCVMAN.SETDATABASE

The view RC_BACKUP_FILES can be queried for information about all backups of any database registered in the recovery catalog. However, before querying RC_BACKUP_FILES you must call DBMS_RCVMAN.SETDATABASE , specifying the DBID of one of the databases registered in the recovery catalog, as shown in the following example:

SQL>  CALL DBMS_RCVMAN.SETDATABASE(null,null,null,2283997583,null);

The fourth parameter must be the DBID of a database registered in the recovery catalog. The other paramters must all be NULL.