Oracle 26ai Backup and Recovery Metadata Views

Oracle AI Database 26ai provides SQL views that help a database administrator inspect archived redo, RMAN backup records, job results, and current recovery conditions. These views support the reports and lists introduced in the preceding lessons. They let you build focused operational reports, investigate an unexpected backup result, and explain which database files a backup contains. A useful report answers a specific question: which jobs finished, where their pieces reside, which redo belongs to a recovery interval, or whether the fast recovery area is approaching its quota.

The lesson uses the familiar expression data dictionary views, but the names beginning with V$ are dynamic performance views. Some expose backup metadata recorded in the target database control file; others describe runtime activity or current storage conditions. They do not all share the same source or history. Recovery catalog views beginning with RC_ provide another reporting surface in the separate catalog database. Choose the source according to the question and the database identity you need to report.

All SQL and RMAN examples below are illustrative. They have been checked against Oracle 26ai documentation, but are not results from an executed backup or recovery. Run them through an appropriately authorized account in your own environment and interpret the actual returned rows.

Choose the reporting context first

Connect to the intended target database when querying its V$ views. For reports covering an entire container database, use an authorized connection to the CDB root. A connection to a pluggable database does not automatically expose all root or other PDB records. Where a view supplies CON_ID, interpret it using that view's documentation: zero can represent the entire CDB, one represents the root, and other values identify individual containers. Do not discard zero-valued rows just because your application runs in a PDB.

Access to dynamic performance views requires suitable privileges. Use the established monitoring account and approved grants rather than granting broad administrative privileges merely to run a report. Record the connection context with exported reports, especially when similarly named databases exist in development and production. A report without database identity, observation time, and query criteria can be misleading even when its SQL is correct.

Prefer explicit columns and a purposeful filter to SELECT *. A seven-day job report and a two-day archived-log report below are convenient examples, not retention requirements. Adjust these intervals to the investigation. Older rows may have aged out of the repository, and a short reporting interval can hide backups needed for recovery. Dates are database DATE values in these examples; format them consistently when exporting evidence.

SELECT resetlogs_change#, thread#, sequence#, dest_id,
       first_time, next_time, status, backup_count, name
FROM   v$archived_log
WHERE  first_time >= SYSDATE - 2
ORDER  BY resetlogs_change#, thread#, sequence#, dest_id;

A reference map for backup and recovery views

The legacy table listed V$BACKUP_DATAFILE twice, once for control files and once for datafiles. Both describe valid uses of that view, so they are consolidated here. Additional specialized views include V$BACKUP_SPFILE for server parameter file backups, V$BACKUP_CONTROLFILE_DETAILS for control-file backup details, and V$CONTROLFILE_COPY for control-file copies. A backup-set record and an image-copy record describe different storage forms.

Inspect recent jobs, then examine their backup pieces

Begin a daily review with a bounded job query. It gives a quick account of RMAN backup activity and distinguishes input types such as database and archived-log backups. Examine warnings and failures, and compare the times with the expected schedule. An absent row needs investigation: the scheduler may not have started RMAN, the connection may be to another database, or the required history may no longer be present.

SELECT session_key, input_type, status, start_time, end_time
FROM   v$rman_backup_job_details
WHERE  start_time >= SYSDATE - 7
ORDER  BY start_time DESC;

A completed job is useful evidence that the backup operation finished, but it does not prove every recovery objective is satisfied. A successful archived-log backup is not a replacement for an appropriate datafile backup. Review the job's purpose and the corresponding RMAN log, then inspect content and retention through the repository. Preserve the distinction between successful execution and the ability to restore to a chosen point.

Use V$BACKUP_SET with V$BACKUP_PIECE to report logical sets and physical outputs together. Join on both SET_STAMP and SET_COUNT. The set and piece views have their own record identifiers; joining their RECID values would not express this relationship.

SELECT s.set_stamp, s.set_count, s.backup_type,
       s.incremental_level, p.piece#, p.copy#,
       p.device_type, p.status, p.bytes,
       p.completion_time, p.handle
FROM   v$backup_set s
JOIN   v$backup_piece p
       ON p.set_stamp = s.set_stamp
      AND p.set_count = s.set_count
WHERE  p.completion_time >= SYSDATE - 7
ORDER  BY p.completion_time DESC, p.piece#;

BACKUP_TYPE distinguishes archived logs (L), full datafile backups (D), and incremental backups (I). Read INCREMENTAL_LEVEL alongside it. Piece STATUS and BYTES come from V$BACKUP_PIECE, not V$BACKUP_SET. The handle identifies the piece for restore; a media-manager handle need not be an ordinary filesystem pathname.

This query returns piece rows, including distinct copy numbers where applicable. If you later join datafile-content rows, one piece can appear repeatedly because the set contains several files. Summing piece bytes after that join can inflate a storage total. Aggregate pieces separately before combining summaries with set contents, and decide whether duplexed copies should count separately in the report.

Monitor progress, space, and recovery conditions

During an operation, V$SESSION_LONGOPS can help monitor qualifying work. Select relevant RMAN operations and interpret SOFAR, TOTALWORK, and UNITS together. A percentage describes that reported operation, not necessarily the complete backup job. RMAN can perform several phases and concurrent tasks, so a progress row reaching its total is not the same evidence as final job completion.

Storage reporting answers another question. The fast recovery area can contain archived redo and other recovery-related files competing for a configured quota. The following query makes the quota and current accounting visible without attempting to delete anything:

SELECT name, space_limit, space_used, space_reclaimable,
       number_of_files
FROM   v$recovery_file_dest;

The space values are bytes. SPACE_RECLAIMABLE represents space Oracle identifies as reclaimable under its rules; it is not permission to remove arbitrary files through operating-system commands. SPACE_USED includes files created in current and previous fast recovery areas, so changing the destination does not reset this accounting to zero. Review RMAN policies and recovery requirements when diagnosing pressure.

V$RECOVER_FILE helps investigate files that need media recovery. Interpret it with the control-file state and RMAN diagnostics, particularly when a restored or recreated control file cannot supply a complete picture. Corruption views also report recorded findings, rather than certifying that every unlisted block has been checked. Relate findings in V$BACKUP_CORRUPTION, V$COPY_CORRUPTION, and V$DATABASE_BLOCK_CORRUPTION to the actual validation or backup operation that discovered them.

Finally, V$BACKUP concerns user-managed backup mode. Seeing ACTIVE is a reason to investigate that mode and the associated procedure; it does not mean an RMAN backup is running successfully. Using the wrong view for a dashboard can turn an abnormal operational condition into a false success signal.

Turn reports into recovery evidence

A practical review starts by confirming the database and observation time, checking recent jobs, locating backup pieces and contents, and examining the redo needed for the recovery objective. RMAN LIST provides repository inventories, while REPORT evaluates questions such as backup need or obsolescence. These commands complement SQL reports with RMAN's own interpretation of its repository and policies.

Use an appropriately scoped CROSSCHECK when you need to reconcile recorded availability with accessible storage; it updates repository status and is not a full block-reading validation. Plan RESTORE ... VALIDATE for the relevant restore inputs when stronger evidence is required. A recovery rehearsal in an isolated environment tests the broader process, including access, required files, and the selected target. Keep the report, RMAN output, and rehearsal result together so their limits remain clear.

The purpose of these views is to make backup and recovery decisions traceable. A good report shows which repository was queried, what the rows mean, and which subsequent check supports a claim of recoverability. Avoid deletion rules based solely on a positive backup count, a completed job, or an empty corruption view. Use the established RMAN policies and verified recovery requirements to guide operational action.

Oracle 26ai documentation

• V$ARCHIVED_LOG: redo identities, duplicate records, backup counts, and application status.
• V$BACKUP_SET and V$BACKUP_PIECE: set contents and physical piece metadata.
• V$RMAN_BACKUP_JOB_DETAILS and V$RECOVERY_FILE_DEST: job reports and recovery-area accounting.
• CONTROL_FILE_RECORD_KEEP_TIME, Reporting on RMAN Operations, and Managing a Recovery Catalog: repository history and operational reporting.