13 Managing a Recovery Catalog

The process of enrolling of a database in a recovery catalog for RMAN use is called registration. The recommended practice is to register every target database in your environment in a single recovery catalog. For example, you can register databases prod1, prod2, and prod3 in a single catalog owned by rco in the database catdb.

The owner of a centralized recovery catalog, which is also called the base recovery catalog, can grant or revoke restricted access to the catalog to other database users. Each restricted user has full read/write access to his own metadata, which is called a virtual private catalog. The RMAN metadata is stored in the schema of the virtual private catalog owner. The owner of the base recovery catalog determines which objects each virtual private catalog user can access.

You can use a recovery catalog in an environment in which you use or have used different versions of Oracle Database. As a result, your environment can have different versions of the RMAN client, recovery catalog database, recovery catalog schema, and target database. "Importing and Moving a Recovery Catalog" explains how to merge multiple recovery catalog schemas into one.

For RMAN operations such as backup, restore, and crosscheck, RMAN always first updates the control file and then propagates the metadata to the recovery catalog. This flow of metadata from the mounted control file to the recovery catalog, which is known as recovery catalog resynchronization, ensures that the metadata that RMAN obtains from the control file is current.

You can use a stored script as an alternative to a command file for managing frequently used sequences of RMAN commands. The script is stored in the recovery catalog rather than on the file system.

A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to this target database. A global stored script can be run against any database registered in the recovery catalog. A virtual private catalog user has read-only access to global scripts. Creating or updating global scripts must be done while connected to the base recovery catalog.

As explained in "About RMAN in a Data Guard Environment", you must use a recovery catalog to manage RMAN metadata for all physical databases, both primary and standby databases, in the Data Guard environment. RMAN uses the recovery catalog as the single source of truth for the Data Guard environment.

RMAN can use the recovery catalog to update a primary or standby control file in a reverse resynchronization. In this case, the metadata flows from the catalog to the control file rather than the other way around. RMAN automatically performs resynchronizations in most situations in which they are needed. Thus, you do not need to use the RESYNC command to manually resynchronize very often.

You must allocate space to be used by the catalog schema. The size of the recovery catalog schema depends upon the number of databases monitored by the catalog. The schema also grows as the number of archived redo log files and backups for each database increases. Finally, if you use RMAN stored scripts stored in the catalog, some space must be allocated for those scripts.

For example, assume that the trgt database has 100 files, and that you back up the database once a day, producing 50 backup sets containing 1 backup piece each. If you assume that each row in the backup piece table uses the maximum amount of space, then one daily backup consumes less than 170 kilobytes in the recovery catalog. So, if you back up once a day for a year, then the total storage in this period is about 62 megabytes. Assume approximately the same amount for archived logs. Thus, the worst case is about 120 megabytes for a year for metadata storage. For a more typical case in which only a portion of the backup piece row space is used, 15 MB for each year is realistic.

If you plan to register multiple databases in your recovery catalog, then remember to add up the space required for each one based on the previous calculation to arrive at a total size for the default tablespace of the recovery catalog schema.

If you are creating your recovery catalog in an existing database, then add enough room to hold the default tablespace for the recovery catalog schema. If you are creating a new database to hold your recovery catalog, then in addition to the space for the recovery catalog schema itself, allow space for other files in the recovery catalog database:

• SYSTEM and SYSAUX tablespaces

• Temporary tablespaces

• Undo tablespaces

• Online redo log files

Most of the space used in the recovery catalog database is devoted to supporting tablespaces, for example, the SYSTEM, temporary, and undo tablespaces. Table 13-1 describes typical space requirements.

In a Data Guard environment, the primary and standby databases share the same DBID and database name. To be eligible for registration in the recovery catalog, each database in the Data Guard environment must have different DB_UNIQUE_NAME values. The DB_UNIQUE_NAME parameter for a database is set in its initialization parameter file.

If you use RMAN in a Data Guard environment, then you can use the REGISTER DATABASE command only for the primary database. You can use the following techniques to register a standby database in the recovery catalog:

• When you connect to a standby database as TARGET, RMAN automatically registers the database in the recovery catalog.

• When you run the CONFIGURE DB_UNIQUE_NAME command for a standby database that is not known to the recovery catalog, RMAN automatically registers this standby database if its primary database is registered.

The process of creating and managing virtual private catalogs depends on the Oracle Database version being used.

• To create and manage virtual private catalogs using Oracle Database 12c Release 1 (12.1.0.1), use the instructions in "Creating and Managing Virtual Private Catalogs with Oracle Database 12c Release 1 (12.1.0.1)".

• To create virtual private catalogs using Oracle Database 12c Release 1 (12.1.0.2), use the steps described in "Creating and Managing Virtual Private Catalogs with Oracle Database 12c Release 1 (12.1.0.2)".

• To migrate existing virtual private catalogs that were created using Oracle Database 12c Release 1 (12.1.0.1) or earlier, you must upgrade these virtual private catalogs to Oracle Database 12c Release 1 (12.1.0.2) using the steps described in "Upgrading Virtual Private Catalogs to Oracle Database 12c Release 1 (12.1.0.2)".

1. Create the database user who will own the virtual private catalog and grant access privileges to this user.

   This task is described in "Creating and Granting Privileges to a Virtual Private Catalog Owner".

2. Create the virtual private catalog.

   This task is described in "Creating a Virtual Private Catalog".

1. Start SQL*Plus and connect to the recovery catalog database with administrator privileges.
2. Create the user who will own the virtual private catalog.

   For example, if you want database user vpc1 to own the virtual private catalog, then execute the following command (replacing password with a user-defined password):

   SQL> CREATE USER vpc1 IDENTIFIED BY password
     2  DEFAULT TABLESPACE vpcusers
     3  QUOTA UNLIMITED ON vpcusers;
   

   Note:

   Create a password that is secure. See Oracle Database Security Guide for more information.

3. Grant the RECOVERY_CATALOG_OWNER role to the user, and then exit SQL*Plus.

   The following example grants the role to user vpc1:

   SQL> GRANT recovery_catalog_owner TO vpc1;
   SQL> EXIT;
   

4. Start RMAN and connect to the recovery catalog database as the base recovery catalog owner (not the virtual private catalog owner).

   The following example connects to the base recovery catalog as rco:

   % rman
   RMAN> CONNECT CATALOG rco@catdb;
   
   recovery catalog database Password: password
   connected to recovery catalog database
   

5. Grant desired privileges to the virtual private catalog owner.

   The following example gives user vpc1 access to the metadata for prod1 and prod2 (but not prod3):

   RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1;
   RMAN> GRANT CATALOG FOR DATABASE prod2 TO vpc1;
   

   You can also use a DBID rather than a database name. The virtual private catalog user does not have access to the metadata for any other databases registered in the recovery catalog.

   You can also grant the user the ability to register new target databases in the recovery catalog. For example:

   RMAN> GRANT REGISTER DATABASE TO vpc1;
   

1. Start RMAN and connect to the recovery catalog database as the virtual private catalog owner (not the base recovery catalog owner).

   The following example connects to the recovery catalog as vpc1:

   % rman
   RMAN> CONNECT CATALOG vpc1@catdb;
   

2. Create the virtual private catalog.

   The following command creates the virtual private catalog:

   RMAN> CREATE VIRTUAL CATALOG;
   

3. If you intend to use a 10.2 or earlier release of RMAN with this virtual private catalog, then execute the following PL/SQL procedure (where base_catalog_owner is the database user who owns the base recovery catalog):

   SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
   

1. Start RMAN and connect to the recovery catalog database as the recovery catalog owner (not the virtual private catalog owner).

   The following example connects to the recovery catalog as rco:

   % rman
   RMAN> CONNECT CATALOG rco@catdb;
   

2. Revoke specified privileges from the virtual private catalog owner.

   The following command revokes access to the metadata for prod1 from virtual private catalog owner vpc1:

   REVOKE CATALOG FOR DATABASE prod1 FROM vpc1;
   

   You can also specify a DBID rather than a database name. The catalog vpc1 retains all other granted catalog privileges.

   You can also revoke the privilege to register new target databases in the recovery catalog. For example:

   REVOKE REGISTER DATABASE FROM vpc1;
   

1. Start RMAN and connect to the recovery catalog database as the virtual private catalog owner (not the base recovery catalog owner).

   The following example connects to the recovery catalog as user vpc1:

   % rman
   RMAN> CONNECT CATALOG vpc1@catdb;
   

2. Drop the catalog.

   If you are using an Oracle Database 11g or later RMAN executable, then drop the virtual private catalog with the DROP CATALOG command:

   RMAN> DROP CATALOG;
   

   If you are using an Oracle Database 10g or earlier RMAN executable, then you cannot use the DROP CATALOG command. Instead, connect SQL*Plus to the catalog database as the virtual private catalog user, then execute the following PL/SQL procedure (where base_catalog_owner is the database user who owns the base recovery catalog):

   SQL> EXECUTE base_catalog_owner.DBMS_RCVCAT.DELETE_VIRTUAL_CATALOG;
   

1. Start RMAN and connect to the recovery catalog database as the virtual private catalog owner (not the base recovery catalog owner). Connect to the database that you want to register as TARGET.

   %rman
   RMAN> CONNECT TARGET /
   RMAN> CONNECT CATALOG vpc1@catdb;
   

2. Register the database whose metadata must be stored in the virtual private catalog.

   The following example registers the database with the virtual private catalog owner vpc1.

   RMAN> REGISTER DATABASE;
   

3. Back up the database using the BACKUP command with the required clauses.

   Metadata related to the backup is stored in the virtual private catalog.

The recovery catalog database is a database like any other, and is also a key part of your backup and recovery strategy. Protect the recovery catalog like any other part of your database, by backing it up. The backup strategy for your recovery catalog database is part of an overall backup and recovery strategy.

Back up the recovery catalog with the same frequency that you back up a target database. For example, if you make a weekly whole database backup of a target database, then back up the recovery catalog after the backup of the target database. This backup of the recovery catalog can help you in a disaster recovery scenario. Even if you must restore the recovery catalog database with a control file autobackup, you can use the full record of backups in your restored recovery catalog database to restore the target database.

When backing up the recovery catalog database, you can use RMAN to make the backups. As illustrated in Figure 13-1, start RMAN with the NOCATALOG option so that the repository for RMAN is the control file in the catalog database.

Figure 13-1 Using the Control File as the Repository for Backups of the Recovery Catalog

Description of "Figure 13-1 Using the Control File as the Repository for Backups of the Recovery Catalog"

Follow these guidelines when developing an RMAN backup strategy for the recovery catalog database:

• Run the recovery catalog database in ARCHIVELOG mode so that you can do point-in-time recovery if needed.

• Set the retention policy to a REDUNDANCY value greater than 1.

• Back up the database to two separate media (for example, disk and tape).

• Run BACKUP DATABASE PLUS ARCHIVELOG at regular intervals, to a media manager if available, or just to disk.

• Do not use another recovery catalog as the repository for the backups.

• Configure the control file autobackup feature to ON.

With this strategy, the control file autobackup feature ensures that the recovery catalog database can always be recovered, so long as the control file autobackup is available.

A recovery catalog is only effective when separated from the data that it is designed to protect. Thus, you must never store a recovery catalog containing the RMAN repository for a database in the same database as the target database. Also, do not store the catalog database on the same disks as the target database.

To illustrate why data separation is advised, assume that you store the catalog for database prod1 in prod1. If prod1 suffers a total media failure, and if the recovery catalog for prod1 is also stored in prod1, then if you lose the database you also lose the recovery catalog. At this point the only option is to restore an autobackup of the control file for prod1 and use it to restore and recover the database without the benefit of any information stored in the recovery catalog.

Logical backups of the RMAN recovery catalog created with the Data Pump Export utility can be a useful supplement for physical backups. For damage to a recovery catalog database, you can use Data Pump Import to quickly reimport the exported recovery catalog data into another database and rebuild the catalog.

Resynchronization of the recovery catalog ensures that the metadata that RMAN obtains from the control file stays current. Resynchronizations can be full or partial.

In a partial resynchronization, RMAN reads the current control file of the target database to update changed 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.

RMAN creates a snapshot control file, which is a temporary backup control file, when it performs a full resynchronization. The database ensures that only one RMAN session accesses a snapshot control file at any point in time. RMAN creates the snapshot control file in an operating system-specific location on the target database host. You can specify the name and location of the snapshot control file, as explained in "Configuring the Snapshot Control File Location".

This snapshot control file ensures that RMAN has a consistent view of the control file. Because the control file is intended for short-term use, it is not registered in the catalog. RMAN records the control file checkpoint in the recovery catalog to indicate the currency of the catalog.

RMAN automatically resynchronizes the recovery catalog when RMAN is connected to a target database and recovery catalog and you have executed RMAN commands. Thus, you do not need to manually run the RESYNC CATALOG command very often.

1. Start RMAN and connect to a target database and recovery catalog.
2. Mount or open the target database:

   STARTUP MOUNT;
   

3. Resynchronize the recovery catalog.

   Run the RESYNC CATALOG command at the RMAN prompt as follows:

   RESYNC CATALOG;
   

   The following example resynchronizes the control file of standby1:

   RESYNC CATALOG FROM DB_UNIQUE_NAME standby1;
   

   The following variation, when connected to the target database as the SYS user and using password file authentication, resynchronizes the control files for all databases in the Data Guard environment:

   RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
   

   See Also:

   • Oracle Database Backup and Recovery Reference for RESYNC CATALOG command syntax

   • Oracle Data Guard Concepts and Administration to learn how to configure the RMAN environment for use with a standby database

1. Start RMAN and connect as TARGET to the database to unregister. Also connect to the recovery catalog.

   It is not necessary to connect to the target database, but if you do not, then you must specify the name of the target database in the UNREGISTER command. If multiple databases have the same name in the recovery catalog, then you must create a RUN block around the command and use SET DBID to set the DBID for the database.

2. Make a note of the DBID as displayed by RMAN at startup.

   For example, RMAN outputs a line of the following form when it connects to a target database that is open:

   connected to target database: PROD (DBID=39525561)
   

3. As a precaution, it may be useful to list all of the backups recorded in the recovery catalog using LIST BACKUP SUMMARY and LIST COPY SUMMARY. This way, you can recatalog backups not known to the control file if you later decide to reregister the database.
4. If your intention is to actually delete all backups of the database completely, then run DELETE statements to delete all existing backups. Do not delete all backups if your intention is only to remove the database from the recovery catalog and rely on the control file to store the RMAN metadata for this database.

   The following commands illustrate how to delete backups:

   DELETE BACKUP DEVICE TYPE sbt;
   DELETE BACKUP DEVICE TYPE DISK;
   DELETE COPY;
   

   RMAN lists the backups that it intends to delete and prompts for confirmation before deleting them.

5. Run the UNREGISTER DATABASE command. For example:

   UNREGISTER DATABASE;
   

   RMAN displays the database name and DBID, and prompts you for a confirmation:

   database name is "RDBMS" and DBID is 931696259
    
   Do you really want to unregister the database (enter YES or NO)? yes
   

   When the process is complete, RMAN outputs the following message:

   database unregistered from the recovery catalog
   

1. Start RMAN and connect as TARGET to the primary database. Also, connect RMAN to a recovery catalog.

   For example, enter the following commands:

   % rman
   RMAN> CONNECT TARGET "sbu@lnx3 AS SYSBACKUP";
   
   target database Password: password
   connected to target database: LNX3 (DBID=781317675)
   
   RMAN> CONNECT CATALOG rco@catdb;
   

2. List the database unique names.

   For example, execute the LIST DB_UNIQUE_NAME command as follows:

   RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
   
   List of Databases
   DB Key  DB Name DB ID             Database Role   Db_unique_name
   ------- ------- ----------------- --------------- ------------------
   1       LNX3    781317675         STANDBY         STANDBY1
   1       LNX3    781317675         PRIMARY         LNX3
   

3. Run the UNREGISTER DB_UNIQUE_NAME command.

   For example, execute the UNREGISTER command as follows to unregister database standby:

   RMAN> UNREGISTER DB_UNIQUE_NAME standby1;
   

   RMAN displays the database name and DBID, and prompts you for a confirmation:

   database db_unique_name is "standby1", db_name is "LNX3" and DBID is 781317675
    
   Do you really want to unregister the database (enter YES or NO)? yes
   

   When the process is complete, RMAN outputs the following message:

   database with db_unique_name standby1 unregistered from the recovery catalog
   

1. Obtain the DB_NAME of the protected database that you want to unregister.
2. (Optional) To delete all the backups associated with this protected database, perform the following steps:

   • Connect to the protected database as a user with the SYSDBA or SYSBACKUP privilege.

   • Use the following commands to delete all backups:

     DELETE BACKUP DEVICE TYPE sbt;
     DELETE BACKUP DEVICE TYPE DISK;
     DELETE COPY;
     

     RMAN lists the backups that it intends to delete and prompts for confirmation before deleting them.

3. Start SQL*Plus and connect to the Recovery Appliance metadata database as RASYS (Recovery Appliance catalog owner).
4. Unregister the protected database from Recovery Appliance using the DBMS_RA.DELETE_DB procedure.

   begin
         DBMS_RA.DELETE_DB('TEST_DB');
   end;
   /
   

   RMAN prompts you to confirm the unregister database operation.

If you are upgrading to Oracle Database 12c Release 1 (12.1.0.2), then the recovery catalog database must use the Enterprise Edition of Oracle Database.

If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. The compatibility matrix in Oracle Database Backup and Recovery Reference explains which schema versions are compatible with which versions of RMAN. For example, you must upgrade the catalog if you use an Oracle Database 11g RMAN client with a release 10.2 version of the recovery catalog schema.

The Oracle Database 10g Release 1 version of the recovery catalog schema requires the CREATE TYPE privilege. If you created the recovery catalog owner in a release before 10gR1, and if you granted the RECOVERY_CATALOG_OWNER role when it did not include the CREATE TYPE privilege, then you must grant CREATE TYPE to this user explicitly before upgrading the catalog.

You receive an error when issuing UPGRADE CATALOG if the recovery catalog is at a version greater than that required by the RMAN client. RMAN permits the UPGRADE CATALOG command to be run if the recovery catalog is current and does not require upgrading, however, so that you can re-create packages at any time if necessary. Check the message log for error messages generated during the upgrade.

1. Start SQL*Plus and connect to the recovery catalog database as the catalog owner.
2. Query the RCVER table to obtain the schema version, as in the following example (sample output included):

   SELECT *
   FROM   rcver;
   
   VERSION
   ------------
   12.01.00.01 
   

VERSION
------------
10.02.00
11.02.00
12.01.00.01

When using IMPORT CATALOG, the source catalog schema is the catalog schema to import into a different schema. The destination catalog schema is the catalog schema into which you intend to import the source catalog schema.

By default, RMAN imports metadata from all target databases registered in the source recovery catalog. Optionally, you can specify the list of database IDs to be imported from the source catalog schema.

By default, RMAN unregisters the imported databases from the source catalog schema after a successful import. To indicate whether the unregister was successful, RMAN prints messages before and after unregistering the merged databases. You can also specify the NO UNREGISTER option to specify that the databases is not unregistered from the source catalog.

A stored script is either global or local. It is possible for global scripts, but not local scripts, to have name conflicts during import because the destination schema already contains the script name. In this case, RMAN renames the global script name to COPY OF script_name. For example, RMAN renames bp_cmd to COPY OF bp_cmd.

If the renamed global script is still not unique, then RMAN renames it to COPY(2) OF script_name. If this script name also exists, then RMAN renames the script to COPY(3) OF script_name. RMAN continues the COPY(n) OF pattern until the script is uniquely named.

In a Recovery Appliance environment, a single, centrally-managed Recovery Appliance catalog residing on the Recovery Appliance is shared by all the protected databases. This catalog must be used by all protected databases that send backups to Recovery Appliance.

When you move protected databases to a data protection strategy that uses Recovery Appliance, you can choose to migrate existing backups and backup metadata to Recovery Appliance. To migrate backup metadata, you must import the RMAN recovery catalog into the Recovery Appliance catalog.

As shown in compatibility matrix in Oracle Database Backup and Recovery Reference, a target database, recovery catalog database, and recovery catalog schema can be at different database versions. The recommended practice is to import all existing recovery catalogs into a single recovery catalog at the latest version of the recovery catalog schema. "Determining the Schema Version of the Recovery Catalog" explains how to determine the catalog version. Check the compatibility matrix to determine which schema versions are compatible in your environment.

When using IMPORT CATALOG, the version of the source recovery catalog schema must be equal to the current version of the RMAN executable with which you run the command. If the source catalog schema is a lower version, then upgrade it to the current version before importing the schema. "Upgrading the Recovery Catalog" explains how to upgrade. If the source recovery catalog schema is a higher version, then retry the import with a higher version RMAN executable.

No database can be registered in both the source and destination catalog schema. If a database is currently registered in both catalog schemas, then unregister the database from source catalog schema before performing the import.

1. Start RMAN and connect as CATALOG to the destination recovery catalog schema. For example:

   % rman
   RMAN> CONNECT CATALOG 111cat@destdb;
   

2. Import the source recovery catalog schema, specifying the connection string for the source catalog.

   For example, enter the following command to import the catalog owned by 102cat on database srcdb:

   IMPORT CATALOG 102cat@srcdb;
   

   A variation is to import metadata for a subset of the target databases registered in the source catalog. You can specify the databases by DBID or database name, as shown in the following examples:

   IMPORT CATALOG 102cat@srcdb DBID=1423241, 1423242;
   IMPORT CATALOG 102cat@srcdb DB_NAME=prod3, prod4;
   

3. Optionally, connect to a target database to check that the metadata was successfully imported. For example, the following commands connect to database prod1 as TARGET and list all backups for this database:

   CONNECT TARGET "sbu@prod1 AS SYSBACKUP";
   LIST BACKUP;
   

   sbu is a user who is granted the SYSBACKUP privilege in the target database.

1. Create a recovery catalog on the destination database, but do not register any databases in the new catalog.

   "Creating a Recovery Catalog" explains how to perform this task.

2. Import the source catalog into the catalog created in the preceding step.

   "Importing a Recovery Catalog" explains how to perform this task.