RMAN Recovery Catalog Interview Questions

What is the RMAN Catalog Schema, Repository and Database?

The RMAN catalog schema is an Oracle Schema holding the RMAN repository. It is made up of a set of tables, views and pl/sql programs used by RMAN. These objects together form the RMAN catalog or repository. An RMAN catalog database is an Oracle database that the RMAN catalog schema resides in.

The RMAN repository has its own version and can be entirely different from database’s version. For example, this RMAN catalog database is running on 11.2.0.3, but the RMAN catalog version is 10.2.0.5:

$ . oraenv
ORACLE_SID = [ORA112] ? RCAT

$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 18 09:31:10 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: [rman_user]/[password]

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rcver;

VERSION
------------
10.02.00.05

Why Do You Need a Recovery Catalog?

An RMAN recovery catalog is a database schema for RMAN backup metadata. You can certainly take a backup and perform a restore without a recovery catalog. However there are specific operations that are only possible with a recovery catalog. These include stored scripts, long retentions periods, and archival backups.

In general we highly recommend the use of a recovery catalog. The exceptions to these are when you need to perform a test restore of a database; or when there appears to be an issue with the recovery catalog itself.

Long Retention Period

By default the controlfile will keep up to seven days worth of backup history. This is defined by the init.ora parameter CONTROL_FILE_RECORD_KEEP_TIME. Note that this is simply a target rather than a guarantee. Backups records in the controlfile will be reused if required.

So we use a recovery catalog for various reasons including long retention periods (eg. recovery window of x years); and archival (KEEP) backups.

Report schema as at a certain date

If you need to see the database schema as at a certain time, you will need a recovery catalog. Otherwise you will encounter the error below:

$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 19 14:13:00 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA112

RMAN> report schema at time 'sysdate -10';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 04/19/2012 14:13:10
RMAN-06137: must have recovery catalog for REPORT SCHEMA AT TIME

RMAN Stored Scripts

A stored script is a set of RMAN commands stored in the recovery catalog. Any commands that are legal within a RUN block is acceptable in the stored script. The following commands are not legal within stored scripts:RUN,@, and@@.

The main advantage of a stored script is that it is available to any RMAN client that can connect to the target database and recovery catalog.

Dataguard Configuration

A recovery catalog is required when you use RMAN in a Data Guard environment.

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 20 10:57:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA112

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY FOR DB_UNIQUE_NAME <db_unique_name>';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/20/2011 10:57:52
RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode</db_unique_name>

Further, by storing backup metadata for all primary and standby databases, you can use the backup across both environments.

What are the Disk Space and Memory Requirements?

The recovery catalog is a schema within a database. It is made up of tables, views and PL/SQL programs so will occupy disk space and use memory like any other database schema.

It is best to create a new schema and tablespace entirely dedicated for the RMAN recovery catalog. If possible, you should also dedicate a new database for the recovery catalog schema(s). At the very least the target and catalog databases must be two entirely different databases. Do not place your catalog schema within the same target database.

One database can contain multiple RMAN recovery schemas, or you can have a single recovery catalog schema for all target databases’ backup. Each target can be running at a different RDBMS version, and has its own persistent configuration. The decision to share a catalog schema or database is a business decision and will need to be evaluated like any other application schema.

Backup Considerations

You need to backup the recovery catalog schema and database like any other schema and database. So use RMAN to backup this database as well. At the very least you should perform an expdp of the RMAN catalog schema on a regular basis.

Performance Considerations

The RMAN catalog schema is a set of tables, indexes and PL/SQL that resides within a database so normal tuning methodologies apply. The database must be allocated enough resources to run the RMAN PL/SQL code. If there are concurrent backups running then resource contentions may arise.

Implicit vs Explicit Resync

During a resynchronization, RMAN compares the recovery catalog’s metadata to the target database’s controlfile, and the metadata updated accordingly.

The resync happens one way ie oracle pushes the information from control file to recovery catalog. However, there is a one exception for this which is rman configuration. This is bi-directional. That is, if the control file is re-created, then the rman configuration information will be copied from recovery catalog to control file. No other information flows from recovery catalog to controlfile. Likewise, in a Data Guard environment, RMAN can perform a reverse resynchronization to update a database control file with metadata from the catalog.

Most RMAN commands triggers an automatic resync when connect to the catalog. Resynchronizations can be full or partial. In a partial resynchronization, RMAN reads the target’s current control file and updates the catalog’s metadata about new backups, new archived redo logs, and so on. RMAN does not resynchronize metadata about the database physical schema.

In a full resynchronization, RMAN updates all changed records, including those for the database schema. RMAN performs a full resynchronization after structural changes to database (adding or dropping database files, creating new incarnation, and so on) or after changes to the RMAN persistent configuration.

To force a resync:

$ rman target / catalog [rman_user]/[password]@[catalog_tns]

RMAN> resync catalog;

5. How Do You Create The Recovery Catalog?

In this example, we are using the RCAT database, and RMAN schema to host the RMAN catalog.

1. Login to SQL*Plus as the DBA user and create the RMAN catalog schema:

SQL> create user [rman_user]identified by [password] default tablespace users quota unlimited on users;

2. Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog

SQL> grant recovery_catalog_owner to [rman_user];

$ . oraenv
RCAT

$ rman catalog [rman_user]/[password]@[catalog_tns]

RMAN> create catalog;

4. now register the target:

$ . oraenv
ORA112

$ rman target / catalog [rman_user]/[password]@[catalog_tns]

RMAN> register database;

Does a Target Upgrade also Mean a Catalog Upgrade?

When using a single RMAN catalog for multiple target databases, the RMAN catalog must be set at the highest version of the target that you’re trying to backup. For example, if you have targets running on 10.2.0.5 and 11.2.0.3, the catalog version must be 11.2.0.3. In other words, an 11.2.0.3 RMAN catalog can be used for all target databases at 11.2.0.3 and below.

Otherwise you may see messages such as these:

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 2 17:37:50 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect catalog [rman_user]/[password]@[catalog_tns]
connected to recovery catalog database
PL/SQL package RMAN_DD.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is not current
PL/SQL package RMAN_DD.DBMS_RCVMAN version 11.02.00.03 in RCVCAT database is not current

From the above we can see that the catalog version is 11.2.0.3, whereas the target (RMAN executable) is on 11.2.0.4. So a catalog upgrade is required.

Which RMAN Executable to Use

When running RMAN backups, the RMAN executable must be set to the same version as the target database. For example, when backing up a 10.2.0.5 database to the 11.2.0.3 catalog, you must use the RMAN executable from the 10.2.0.5 $ORACLE_HOME.

During a restore, you must use the same executable version involved in the backup. For example, if the backup was taken with the 11.2.0.3’s RMAN executable, you must restore the backuppiece using the RMAN executable from the 11.2.0.3 $ORACLE_HOME. You can certainly use a higher version of the RMAN executable to perform the restore, but this would mean an upgrade to the target database before it can be opened for use.

How do you Upgrade the RMAN Catalog?

Prior to an upgrade, please ensure that you take a valid backup of the recover catalog or schema. During the upgrade itself, no targets should be performing a backup to the catalog. Otherwise you may encounter issues like the below:

RMAN> upgrade catalog;

error creating add_site_tfatt_f2_constraint
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-04020: deadlock detected while trying to lock object [rman_user]_DD.NODE
5:49 PM

To ensure that no other users/processes are connected to the catalog you can consider the following:

1. restart the catalog database.

2. temporarily change the RMAN catalog owner’s password. If any issues are encountered during the upgrade you need to restore the catalog from a valid backup and start again.

Once a backup of the recovery catalog/database is taken, and all sessions are disconnected from the RMAN repository you can proceed with the upgrade.

- Connect to the RMAN catalog using the rman executable from the $ORACLE_HOME that you need to be upgraded to. For example, if upgrading to 11.2.0.2, run the RMAN executable from the 11.2.0.2 $ORACLE_HOME.

$ rman target / catalog [rman_user]/[password]@[catalog_tns]

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 20 12:34:57 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA112
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 10.02.00.04 in RCVCAT database is too old

- now upgrade the catalog:

RMAN> upgrade catalog;

recovery catalog owner is [rman_user]
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.02
DBMS_RCVMAN package upgraded to version 11.02.00.02
DBMS_RCVCAT package upgraded to version 11.02.00.02

When Not to Use a Recovery Catalog

Test Restore

Do not connect to the recovery catalog when performing a test restore otherwise the catalog will get confused. The catalog would assume that the restore is for production rather than test.

If you have accidentally connected to the production recovery catalog during the restore/recovery, you may encounter various errors, such as these:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of resync command at 01/5/2009 08:10:05
RMAN-06004: ORACLE error from recovery catalog database
RMAN-20004: target database name does not match name in recovery catalog

Network Issues

If there are known network issues, either by design (for example, limited network bandwidth) or temporary (for example, network outage, or SQL*Net issues), you can just backup without a recovery catalog and then reysnc to the catalog when the network issue is fixed, or during a quiet time. You may need to increase the parameter CONTROLFILE_RECORD_KEEP_TIME to meet the retention policy as well.

Catalog Issues

If there is a known problem with the catalog, you can operate in NOCATALOG mode until the catalog issue is fixed as long as you do not need to perform any operations listed in the section 2 “Why Do you Need a Recovery Catalog”

For backups, you can just take a backup without the recover catalog, and resync to the catalog when the problem is fixed. You may need to increase the CONTROLFILE_RECORD_KEEP_TIME to meet the retention policy as well.

For restore operations you can restore the correct controlfile first, and continue with the rest of the restore operations in nocatalog mode.

Can You Query the RMAN Backend Views?

The RMAN catalog’s tables and views should only be used by support for debugging purposes. The v$ views themselves are stored in controlfiles sections that get reused as required so your queries may not turn out as expected.

Instead you should use the RMAN commands to interrogate the catalog. RMAN comes with its own extensive set of commands to backup, restore, list and preview. You do not need to write your own SQL scripts based on the views.

For example:

RMAN> list incarnation of database;
RMAN> list backup summary;
RMAN> restore database preview;
RMAN> restore database validate;