Backup Recovery   «Prev  Next»

Lesson 8 Data dictionary views
Objective Describe some of the V$ data dictionary views for backup and recovery.

Utilization of V$ARCHIVED_LOG in Backup and Recovery in Oracle

The `V$ARCHIVED_LOG` view in Oracle's data dictionary plays a pivotal role in the processes of database backup and recovery. It provides a comprehensive and detailed inventory of archived redo log files, which are indispensable elements in recovering a database. Understanding the practical application of `V$ARCHIVED_LOG` within backup and recovery operations in Oracle is vital for Database Administrators (DBAs) to ensure data integrity, continuity, and the robustness of backup and recovery procedures.



`V$ARCHIVED_LOG` is a dynamic performance view that presents metadata regarding the archived redo logs in an Oracle database. This view holds crucial information such as the name, location, creation time, and the sequence number of archived logs, all of which are fundamental for successful and efficient database recovery.


The archived redo logs cataloged in `V$ARCHIVED_LOG` are utilized in various recovery scenarios to apply changes made to the database after a backup. The information contained within this view enables DBAs to determine which logs have been archived and are available for recovery purposes.

Role in Backup and Recovery

Log Sequence Number and Recovery

In recovery scenarios, the `SEQUENCE#` column in the `V$ARCHIVED_LOG` view is of paramount importance. It designates the sequence number of the archived redo log, allowing DBAs to identify the chronological order of changes for accurate recovery.

Identification of Available Archived Logs

The `V$ARCHIVED_LOG` view aids in ascertaining the availability of necessary archived redo logs for recovery. By querying this view, DBAs can ensure that all required logs are accessible, thereby ensuring a complete and consistent recovery process.

Determining the Need for Archived Redo Logs

In backup operations, the `V$ARCHIVED_LOG` view assists in determining which archived redo logs should be included in the backup to enable complete recovery. It aids in ensuring that backups are consistent and comprehensive, encompassing all pertinent redo logs.

Backup Optimization

DBAs can utilize the `V$ARCHIVED_LOG` view to optimize backup operations. Information from this view can inform intelligent backup strategies, minimizing backup times while ensuring complete data protection.

Example Query A typical query on the `V$ARCHIVED_LOG` view might look like the following, which retrieves information about all archived redo logs:

DBAs can employ more complex queries to filter and sort results based on specific criteria, enhancing the efficiency and precision of backup and recovery operations.
In essence, the `V$ARCHIVED_LOG` view in Oracle’s data dictionary is an invaluable asset in the realm of database backup and recovery. It provides a comprehensive inventory of archived redo logs, bolstering the efficacy, reliability, and completeness of backup and recovery operations. Effective utilization of the `V$ARCHIVED_LOG` view empowers DBAs to reinforce the resilience and integrity of database systems, safeguarding critical data assets against loss, corruption, and downtime.

Data Dictionary Views

The table below describes some of the system data dictionary views. These views assist in the management of the recovery catalog and its metadata[1].

Performance View Name Description
V$ARCHIVED_LOG This view maintains information about archived redo log files.
V$BACKUP_DATAFILE This view lists information about control files within backup sets.
V$BACKUP_CORRUPTION This view lists corrupt block ranges within datafile backups.
V$BACKUP_DATAFILE This view lists information about datafiles within backup sets.
V$BACKUP_DEVICE This view displays information on supported backup devices.
V$BACKUP_PIECE This view displays information on the backup pieces from control file.
V$BACKUP_REDOLOG This view displays information about archived logs within backup sets from the control file.
V$BACKUP_SET This view displays backup set information from control file.
V$COPY_CORRUPTION This view lists corrupt block ranges within datafile copies.

Purpose of Recovery Catalog Views and Database Data Dictionary Views

Similar to the Oracle database data dictionary tables, there are a number of recovery catalog views that are created when the recovery catalog is created. The naming convention for these views follows the convention of RC_*. For example, there are views called
  1. RC_BACKUP_SET and
along with other views. Keep in mind that almost every RMAN operation is independent of the recovery catalog. Therefore, a number of V$ views in the database provide RMAN metadata similar to what is stored in the database control file. The big difference between the database recovery catalog base tables and views and the related V$ views is the fact that the recovery catalog views will contain information on all the databases contained in the recovery catalog. This can be a security issue, which is addressed by the Virtual Private Catalog features .
You will find slight differences in the V$ views and recovery catalog views. For example, the primary keys for the records in the V$ views are going to be different from the primary key values in the recovery catalog. Again, this is because there are likely to be many more databases in the recovery catalog. You will find that there may well be more records in the recovery catalog than in the V$ views for a given database. This is because some records in the recovery catalog may have a retention requirement that is longer than the control file is able to maintain a record for. The database control file can only hold a year's worth of RMAN backup metadata, whereas the recovery catalog can hold this metadata forever. Therefore, it is quite likely that the recovery catalog records will be much larger than those within the control file.
[1]Metadata: The system tables and views of Oracle are metadata of the database.