Query RMAN Recovery Catalog Data Dictionary Views

Oracle AI Database 26ai provides Oracle-supplied RC_* recovery catalog views. You can query these views from a SQL client after an RMAN recovery catalog has been created and populated with metadata for registered target databases. The views support custom reports about databases, incarnations, tablespaces, data files, backups, archived redo logs, catalog resynchronizations, and stored RMAN scripts.

A recovery catalog can contain information for multiple target databases and multiple incarnations of each database. A query that omits the database and incarnation identifiers can therefore mix unrelated records. Begin with RC_DATABASE, identify the required DBID, DB_KEY, and DBINC_KEY, and then use those identifiers to filter or join the other catalog views.

The RC_* views are not the same as the general USER_*, ALL_*, and DBA_* data dictionary families. They are also different from the dynamic performance views in the target database. This lesson focuses specifically on querying RMAN metadata stored in a recovery catalog.

Choose the Appropriate Reporting Source

RMAN always records repository metadata in the target database control file. When a recovery catalog is used, RMAN also maintains repository metadata in the catalog schema. Oracle supplies several ways to read this information, and the appropriate method depends on the report you need.

Most recovery catalog views correspond to a target V$ view, but the families are not identical. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE, while RC_STORED_SCRIPT has no target control-file counterpart. Catalog views can also retain longer history than the target control file and can expose records for every database registered in the catalog.

For a routine question such as “Which backups are available for the connected target?”, LIST BACKUP or LIST BACKUP SUMMARY is usually easier to read. Use SQL against the RC_* views when you need columns, filters, joins, or cross-database reporting that the standard RMAN display does not provide.

Connect to the Recovery Catalog Database

The views reside in the recovery catalog schema. Connect a SQL client to the catalog database with the catalog owner or another account that has authorized access to the required catalog metadata. The following SQL*Plus pattern prompts for the password instead of exposing it in a command, script, shell history, or article:

SQL> CONNECT rco@catdb
Enter password:

In this example, rco represents the catalog account and catdb represents the catalog service. Substitute the approved names from your environment. A base recovery catalog can serve multiple databases. A virtual private catalog can restrict a user to a subset of that base catalog, so the rows visible to one authorized user may differ from those visible to another.

Do not expect these RC_* views to appear automatically in every target database. They become available through the recovery catalog created with RMAN. The supported catalog workflow creates and maintains the catalog schema; administrators should not copy the obsolete lesson's catrman.sql explanation into a current procedure.

Understand the Catalog Identifiers

Names alone are not reliable join keys. Two registered databases can share a DB_NAME, and one database can have several incarnations after OPEN RESETLOGS operations. Catalog keys preserve the required identity:

DBID

The database identifier obtained from the target's V$DATABASE view or displayed when RMAN connects to the target.

DB_KEY

The recovery catalog primary key for a registered database. This key is commonly used to filter and join catalog views.

DBINC_KEY

The recovery catalog primary key for one database incarnation. Use it when the selected view exposes incarnation-specific records.

Object keys

Keys such as BS_KEY, BP_KEY, BDF_KEY, and AL_KEY identify backup sets, backup pieces, data file backups, and archived log records inside the catalog.

RC_DATABASE contains one row for each registered database and identifies its current incarnation. Start a reporting session by listing the available targets:

SELECT db_key,
       dbid,
       name,
       dbinc_key,
       resetlogs_time
FROM   rc_database
ORDER  BY name, dbid;

The query deliberately selects named columns rather than using SELECT *. Explicit columns make the report easier to review and less sensitive to changes in a view definition. Actual rows depend on the databases visible to the connected catalog account.

Find the Catalog Key for One Target

Determine the target's DBID before building a report for a specific database. You can obtain it from RMAN connection output or query V$DATABASE.DBID while connected to the target. Then use that value in the catalog:

SELECT db_key,
       dbinc_key,
       name
FROM   rc_database
WHERE  dbid = &target_dbid;

SQL*Plus prompts for target_dbid. Save the returned DB_KEY for subsequent queries. This two-step approach is safer than filtering only by NAME, especially when the recovery catalog manages cloned, recreated, or similarly named databases.

Review Database Incarnations

Oracle creates a new database incarnation when a database is opened with RESETLOGS. The catalog can retain parent and orphaned incarnations as well as the current one. Query RC_DATABASE_INCARNATION to understand that history:

SELECT db_key,
       dbinc_key,
       dbid,
       name,
       current_incarnation,
       status,
       resetlogs_time
FROM   rc_database_incarnation
WHERE  db_key = &db_key
ORDER  BY resetlogs_time;

CURRENT_INCARNATION = 'YES' identifies the current incarnation. The STATUS column can identify a row as CURRENT, PARENT, or ORPHAN. Do not assume that every other catalog view automatically limits its rows to this current incarnation. Consult the view's documented columns and use DBINC_KEY when that view supports it.

Use a Task-Based Set of Recovery Catalog Views

Oracle AI Database 26ai supplies more catalog views than the fixed totals claimed by the legacy article. The catalog evolves as RMAN and Enterprise Manager reporting requirements change. Instead of memorizing a count or an unsupported “most used” ranking, select a view according to the object or operation being investigated.

This table is representative rather than exhaustive. Some views whose names end in _DETAILS or _SUMMARY are primarily intended for Enterprise Manager. For a hand-written report, begin with the simplest documented view that exposes the required columns.

Report Tablespaces and Data Files in a CDB

Current catalog views account for the multitenant architecture. Depending on the view, columns can include CON_ID, PDB_NAME, PDB_KEY, and PDBINC_KEY. In general, CON_ID = 0 describes data that applies to the entire CDB, CON_ID = 1 refers to the root, and higher values identify applicable containers.

The following query replaces the obsolete Oracle8 example with a report that identifies both the tablespace and the container for each registered data file:

SELECT db_name,
       con_id,
       pdb_name,
       tablespace_name,
       file#,
       name
FROM   rc_datafile
WHERE  db_key = &db_key
ORDER  BY con_id, file#;

The NAME column can contain an operating-system path, an Oracle Managed Files name, or another storage identifier. Treat production output as operationally sensitive. Sanitize paths, service names, and storage handles before publishing a report or screenshot.

RC_TABLESPACE also contains records for dropped tablespaces and tablespaces from older incarnations. When the report must represent only the current incarnation, obtain the current DBINC_KEY from RC_DATABASE and filter accordingly:

SELECT con_id,
       pdb_name,
       ts#,
       name,
       bigfile,
       temporary
FROM   rc_tablespace
WHERE  db_key = &db_key
AND    dbinc_key = &current_dbinc_key
ORDER  BY con_id, ts#;

Report Backup Sets and Backup Pieces

RC_BACKUP_SET reports backup sets for all incarnations of a registered database. A row is inserted after a backup completes successfully. The BACKUP_TYPE value identifies a full or level 0 backup, a level 1 incremental backup, or an archived redo log backup. The STATUS value indicates whether all pieces are available, deleted, or only partly available.

SELECT bs_key,
       backup_type,
       incremental_level,
       pieces,
       status,
       completion_time
FROM   rc_backup_set
WHERE  db_key = &db_key
ORDER  BY completion_time DESC;

This query intentionally describes catalog history for the selected DB_KEY. It does not claim to isolate one incarnation because RC_BACKUP_SET does not expose DBINC_KEY. To report data file backups for the current incarnation, use the incarnation key in RC_BACKUP_DATAFILE:

SELECT bdf.bs_key,
       bdf.file#,
       bdf.backup_type,
       bdf.incremental_level,
       bdf.completion_time
FROM   rc_backup_datafile bdf
JOIN   rc_database_incarnation di
  ON   di.dbinc_key = bdf.dbinc_key
WHERE  di.db_key = &db_key
AND    di.current_incarnation = 'YES'
ORDER  BY bdf.completion_time DESC, bdf.file#;

Each backup set contains one or more backup pieces. Join RC_BACKUP_SET to RC_BACKUP_PIECE through BS_KEY, which is the catalog key shared by the two views:

SELECT bs.bs_key,
       bp.bp_key,
       bp.piece#,
       bp.copy#,
       bp.status,
       bp.device_type,
       bp.encrypted,
       bp.handle
FROM   rc_backup_set bs
JOIN   rc_backup_piece bp
  ON   bp.bs_key = bs.bs_key
WHERE  bs.db_key = &db_key
ORDER  BY bs.bs_key, bp.piece#, bp.copy#;

Multiple copies of one backup piece can produce separate catalog rows. The HANDLE identifies the piece and can reveal storage layout, media names, or cloud-related naming conventions. Restrict report access and omit that column when the storage location is not required.

Review Backup Job History

RC_RMAN_BACKUP_JOB_DETAILS reports backup activity by RMAN session. A backup job consists of the BACKUP commands executed within one RMAN job, so a session that backs up the database and archived redo logs can represent one backup job. The SESSION_KEY identifies the RMAN session and can support joins to related status or output views.

SELECT session_key,
       command_id,
       input_type,
       status,
       start_time,
       end_time,
       time_taken_display,
       output_device_type
FROM   rc_rman_backup_job_details
WHERE  db_key = &db_key
ORDER  BY start_time DESC, session_key DESC;

The STATUS column distinguishes running, completed, warning, error, and failed states. A failed job does not prove that RMAN created no usable backup sets. The failure may have occurred after one or more sets completed, so investigate the session and its related backup records before deciding what must be rerun or removed.

This view is primarily intended for Enterprise Manager, but its documented columns can support a concise catalog report. When measuring backup throughput, use the input rate to represent how quickly RMAN read source data. Compression often causes the amount written to be smaller than the amount read, so the output rate alone does not describe backup scanning performance.

Review the Stored Scripts from Earlier Lessons

Lessons 6 and 7 created and executed local and global scripts in the recovery catalog. RC_STORED_SCRIPT contains one row for each stored script, including its name and optional comment. The following query creates a concise inventory for one registered target:

SELECT db_key,
       db_name,
       script_name,
       script_comment
FROM   rc_stored_script
WHERE  db_key = &db_key
ORDER  BY script_name;

This report can confirm that local definitions such as backup_users remain in the catalog. Global scripts are catalog-wide objects, so their ownership and visibility differ from target-specific local scripts. For day-to-day inspection, RMAN LIST SCRIPT NAMES and LIST SCRIPT are usually more convenient. Query RC_STORED_SCRIPT_LINE only when a custom report genuinely needs the stored command text.

Interpret Catalog Results Carefully

A catalog view reports repository metadata, not an independent examination of every backup file at query time. If a file was moved or deleted outside RMAN, the repository can disagree with storage until RMAN reconciles it. Commands such as CROSSCHECK, CHANGE, and DELETE maintain repository status. A row that says a piece is available should therefore be interpreted in the context of the most recent crosscheck and the organization's backup verification procedure.

Catalog recency also depends on resynchronization. RMAN performs resynchronization automatically in many situations, but structural changes, archived redo activity, or an earlier catalog outage can require attention. RC_RESYNC helps report synchronization history, while the operational decision to run RESYNC CATALOG should follow the established recovery-catalog procedure.

Data Guard adds another identity dimension. Primary and standby databases can share a DBID and DB_KEY while using different DB_UNIQUE_NAME values. Some catalog views expose DB_UNIQUE_NAME; others expose SITE_KEY, which can be joined to RC_SITE. Include the site identity when a report must distinguish where a backup or copy was created.

Build Reliable Catalog Reports

Before relying on a recovery catalog query for operational or audit decisions, apply a consistent reporting process:

1. Identify the required target by DBID and obtain its DB_KEY.
2. Determine whether the question concerns the current incarnation, all incarnations, one PDB, or the entire CDB.
3. Check the selected view's 26ai column definition instead of assuming that similarly named views contain the same keys.
4. Join with documented catalog keys such as DBINC_KEY and BS_KEY, not only with database or object names.
5. Select explicit columns and include a deterministic ORDER BY clause when report order matters.
6. Protect output that contains file handles, host paths, device information, or other storage details.
7. Confirm repository currency through normal resynchronization, crosscheck, monitoring, and restore-testing procedures.

Recovery catalog views are valuable because they make centralized RMAN history available to SQL. Their breadth also demands precise filtering. Starting with the registered database, preserving incarnation and container identity, and joining with documented keys turns a broad catalog query into a dependable administrative report.

This lesson completes the Module 5 technical sequence: creating a recovery catalog, registering targets, managing stored scripts, executing those scripts, and querying the resulting catalog metadata. The Module 5 conclusion will review how these capabilities work together as a maintainable RMAN repository and reporting workflow.