A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. Typically, you store the catalog in a dedicated database.

A recovery catalog provides the following benefits:

• A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database. The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog.

• A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform.

• A recovery catalog can store metadata history much longer than the control file. This capability is useful if you must do a recovery that goes further back in time than the history in the control file. The added complexity of managing a recovery catalog database can be offset by the convenience of having the extended backup history available.

Some RMAN features function only when you use a recovery catalog. For example, you can store RMAN scripts in a recovery catalog. The chief advantage of a stored script is that it is available to any RMAN client that can connect to the target database and recovery catalog. Command files are only available if the RMAN client has access to the file system on which they are stored.

A recovery catalog is required when you use RMAN in a Data Guard environment. By storing backup metadata for all primary and standby databases, the catalog enables you to offload backup tasks to one standby database while enabling you to restore backups on other databases in the environment.

The recovery catalog contains metadata about RMAN operations for each registered target database. When RMAN is connected to a recovery catalog, RMAN obtains its metadata exclusively from the catalog.

The catalog includes the following types of metadata:

• Data file and archived redo log backup sets and backup pieces

• Data file copies

• Archived redo logs and their copies

• Database structure (tablespaces and data files)

• Stored scripts, which are named user-created sequences of RMAN commands

• Persistent RMAN configuration settings

Managing a recovery catalog consists of creating the catalog and then registering your target databases with the catalog.

1. Create the recovery catalog.

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

2. Register your target databases in the recovery catalog.

   This step enables RMAN to store metadata for the target databases in the recovery catalog. "Registering a Database in the Recovery Catalog" explains this task.

3. If needed, catalog any older backups whose records are no longer stored in the target control file.

   "Cataloging Backups in the Recovery Catalog" explains how to perform this task.

4. If needed, create virtual private catalogs for specific users and determine the metadata to which they are permitted access.

   "Creating and Managing Virtual Private Catalogs" explains how to perform this task.

5. Protect the recovery catalog by including it in your backup and recovery strategy.

   "Protecting the Recovery Catalog" explains how to back up and recover the catalog, and increase its availability.

When you use a recovery catalog, RMAN requires that you maintain a recovery catalog schema. The recovery catalog is stored in the default tablespace of the schema. Privileged users such as SYS cannot be the owner of the recovery catalog.

Decide which database you will use to install the recovery catalog schema, and also how you will back up this database. Also, decide whether to operate the catalog database in ARCHIVELOG mode, which is recommended.

After choosing the recovery catalog database and creating the necessary space, you are ready to create the owner of the recovery catalog and grant this user necessary privileges.

1. Start SQL*Plus and connect with administrator privileges to the database containing the recovery catalog. In this example, the database is catdb.
2. Create a user and schema for the recovery catalog. For example, you could enter the following SQL statement (replacing password with a user-defined password):

   CREATE USER rco IDENTIFIED BY password
     TEMPORARY TABLESPACE temp 
     DEFAULT TABLESPACE tools 
     QUOTA UNLIMITED ON tools;

   Note:

   Create a password that is secure.

3. 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.

   GRANT RECOVERY_CATALOG_OWNER TO rco;

4. (Optional) Enable the VPD model for the recovery catalog by running the dbmsrmanvpc.sql script with the –vpd option.
   The following command enables the VPD model for the recovery catalog owned by the user rco:

   SQL> @/$ORACLE_HOME/rdbms/admin/dbmsrmanvpc.sql -vpd rco;

After creating the catalog owner, create the catalog tables with the RMAN CREATE CATALOG command. The command creates the catalog in the default tablespace of the catalog owner.

1. Enable Oracle Partitioning for the recovery catalog database.
2. Start RMAN and connect to the database that will contain the catalog. Connect to the database as the recovery catalog owner.
3. Run the CREATE CATALOG command to create the catalog. The creation of the catalog can take several minutes. If the catalog tablespace is this user's default tablespace, then you can run the following command:

   RMAN> CREATE CATALOG;
   

   You can specify the tablespace name for the catalog in the CREATE CATALOG command. For example:

   RMAN> CREATE CATALOG TABLESPACE cat_tbs;
   

   Note:

   If the tablespace name for the recovery catalog is an RMAN reserved word, then it must be uppercase and enclosed in quotes. For example:

   CREATE CATALOG TABLESPACE 'CATALOG';

4. You can check the results by using SQL*Plus to query the recovery catalog to see which tables were created:

   SQL> SELECT TABLE_NAME FROM USER_TABLES;

The process of enrolling of a target database in a recovery catalog is called registration.

If a target database is not registered in the recovery catalog, then RMAN cannot use the catalog to store metadata for operations on this database. You can still perform RMAN operations on an unregistered database: RMAN always stores its metadata in the control file of the target database.

If you are not using the recovery catalog in a Data Guard environment, then use the REGISTER command to register each database. Each database must have a unique DBID. If you use the RMAN DUPLICATE command or the CREATE DATABASE statement in SQL, then the database is assigned a unique DBID automatically. If you create a database by other means, then the copied database may have the same DBID as its source database. You can change the DBID with the DBNEWID utility so that you can register the source and copy databases in the same catalog.

You can use the UNREGISTER command to unregister a database from the recovery catalog.

The first step in using a recovery catalog with a target database is registering the target database in the recovery catalog. If you use the catalog in a Data Guard environment, then you can only register the primary database in this way.

1. Start RMAN and connect to a target database and recovery catalog. The recovery catalog database must be open.

   For example, issue the following command to connect to the catalog database with the net service name catdb as user rco (who owns the catalog schema):

   % rman TARGET / CATALOG rco@catdb;
   

2. If the target database is not mounted, then mount or open it:

   STARTUP MOUNT;
   

3. Register the target database in the connected recovery catalog:

   REGISTER DATABASE;
   

   RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.

4. Verify that the registration was successful by running REPORT SCHEMA:

   REPORT SCHEMA;
   
   Report of database schema for database with db_unique_name TRGT
   
   List of Permanent Datafiles===========================
   File Size(MB)   Tablespace       RB segs Datafile Name
   ---- ---------- ---------------- ------- -------------------
   1        307200 SYSTEM             NO    /oracle/oradata/trgt/system01.dbf
   2         20480 UNDOTBS            YES   /oracle/oradata/trgt/undotbs01.dbf
   3         10240 CWMLITE            NO    /oracle/oradata/trgt/cwmlite01.dbf
   4         10240 DRSYS              NO    /oracle/oradata/trgt/drsys01.dbf
   5         10240 EXAMPLE            NO    /oracle/oradata/trgt/example01.dbf
   6         10240 INDX               NO    /oracle/oradata/trgt/indx01.dbf
   7         10240 TOOLS              NO    /oracle/oradata/trgt/tools01.dbf
   8         10240 USERS              NO    /oracle/oradata/trgt/users01.dbf
   
   List of Temporary Files
   =======================
   File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
   ---- -------- -------------------- ----------- --------------------
   1    200      TEMP                 32767       /oracle/oradata/trgt/tbs_tmp.dbf
   

By default, all of the users of an RMAN recovery catalog have full privileges to read, select, insert, update, and delete any metadata in the catalog. For example, if the administrators of two unrelated databases share the same recovery catalog, each administrator could, whether inadvertently or maliciously, destroy catalog data for the other's database. In many enterprises, this situation is tolerated because the same people manage many different databases and also manage the recovery catalog. But in other enterprises where clear separation of duty exists between administrators of various databases, and between the DBA and the administrator of the recovery catalog, you may desire to restrict each database administrator to modify only backup metadata belonging to those databases that they are responsible for, while still keeping the benefits of a single, centrally-managed, RMAN recovery catalog. This goal can be achieved by implementing virtual private catalogs.

Every RMAN recovery catalog starting with Oracle Database 11g supports virtual private catalogs, but they are not used unless explicitly created. There is no restriction on the number of virtual private catalogs that can be created beneath one recovery catalog. Each virtual private catalog is owned by a database schema user which is different than the user who owns the recovery catalog.

After you set up a virtual private catalog user, the administrator for the recovery catalog grants each virtual private catalog the privilege to use that catalog for one or more databases that are currently registered in the recovery catalog. The administrator of the recovery catalog can also grant the privilege to register new databases while using a virtual private catalog.

After you create a virtual private catalog, you can revoke catalog access privileges as necessary.

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.

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

   REVOKE CATALOG FOR PLUGGABLE DATABASE hr_pdb FROM vpc1;

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

   REVOKE REGISTER DATABASE FROM vpc1;

A single recovery catalog can store metadata for multiple target databases. Consequently, loss of the recovery catalog can be disastrous. You must back up the recovery catalog frequently.

This section provides general guidelines for developing a strategy for protecting the recovery catalog.

Restoring and recovering the recovery catalog database is much like restoring and recovering any other database with RMAN.

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.

Stored scripts can be local or global. A local 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 that target database. A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.

The commands allowable within the brackets of the CREATE SCRIPT command are the same commands supported within a RUN block. Any command that is legal within a RUN command is permitted in the stored script. The following commands are not legal within stored scripts: RUN, @, and @@.

When specifying a script name, RMAN permits but generally does not require that you use quotes around the name of a stored script. If the name begins with a digit or is an RMAN reserved word, however, then you must put quotes around the name to use it as a stored script name. Consider avoiding stored script names that begin with nonalphabetic characters or that are the same as RMAN reserved words.

Consider using a naming convention to avoid confusion between global and local stored scripts. For the EXECUTE SCRIPT, DELETE SCRIPT and PRINT SCRIPT commands, if the script name passed as an argument is not the name of a script defined for the connected target instance, then RMAN looks for a global script by the same name. For example, if the global script global_backup is in the recovery catalog, but no local stored script global_backup is defined for the target database, then the following command deletes the global script:

DELETE SCRIPT global_backup;

To use commands related to stored scripts, even global scripts, you must be connected to both a recovery catalog and a target database instance.

You can use the CREATE SCRIPT command to create a stored script.

1. Start RMAN and connect to a target database and recovery catalog (if used).

   See Also:

   Making Database Connections with RMAN

2. Run the CREATE SCRIPT command.

   The following example illustrates creation of a local script:

   CREATE SCRIPT full_backup 
   {     
     BACKUP DATABASE PLUS ARCHIVELOG;
     DELETE OBSOLETE;
   }

   For a global script, the syntax is similar:

   CREATE GLOBAL SCRIPT global_full_backup 
   {     
     BACKUP DATABASE PLUS ARCHIVELOG;
     DELETE OBSOLETE;
   }
   

   Optionally, you can provide a COMMENT with descriptive information:

   CREATE GLOBAL SCRIPT global_full_backup 
   COMMENT 'use only with ARCHIVELOG mode databases'
   {     
     BACKUP DATABASE PLUS ARCHIVELOG;
     DELETE OBSOLETE;
   }

   You can also create a script by reading its contents from a text file. The file must begin with a left brace ({) character, contain a series of commands valid within a RUN block, and end with a right brace (}) character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard.

   CREATE SCRIPT full_backup 
     FROM FILE '/tmp/my_script_file.txt';
   

3. Examine the output.

   If no errors are displayed, then RMAN successfully created the script and stored in the recovery catalog.

To update stored scripts, use the REPLACE SCRIPT command.

1. Start RMAN and connect to a target database and recovery catalog (if used).
2. Execute REPLACE SCRIPT.

   This following example updates the script full_backup with new contents:

   REPLACE SCRIPT full_backup 
   {
     BACKUP DATABASE PLUS ARCHIVELOG;
   }
   

   You can update global scripts by specifying the GLOBAL keyword as follows:

   REPLACE GLOBAL SCRIPT global_full_backup 
   COMMENT 'A script for full backup to be used with any database'
   {
     BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
   }
   

   As with CREATE SCRIPT, you can update a local or global stored script from a text file with the following form of the command:

   REPLACE GLOBAL SCRIPT global_full_backup 
     FROM FILE '/tmp/my_script_file.txt';

Use the EXECUTE SCRIPT command to run a stored script.

1. Start RMAN and connect to a target database and recovery catalog (if used).
2. If needed, use SHOW to examine your configured channels.

   Your script uses the automatic channels configured at the time you execute the script. Use ALLOCATE CHANNEL commands in the script if you must override the configured channels. Because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script do not execute.

3. Run EXECUTE SCRIPT . This command requires a RUN block, as shown in the following example:

   RUN 
   { 
     EXECUTE SCRIPT full_backup; 
   }
   

   The preceding command invokes a local script if one exists with the name specified. If no local script is found, but there is a global script with the name specified, then RMAN executes the global script.

   You can also use EXECUTE GLOBAL SCRIPT to control which script is invoked if a local and a global script have the same name. If there is no local script called global_full_backup, the following two commands have the same effect:

   RUN 
   {
     EXECUTE GLOBAL SCRIPT global_full_backup;
   }
   
   RUN
   { 
     EXECUTE SCRIPT global_full_backup; 
   }

You can specify substitution variables in the CREATE SCRIPT command.

1. Create a command file that contains a CREATE SCRIPT statement with substitution variables for values that must be dynamically updated.

   The following example uses substitution variables for the name of the tape set, for a string in the FORMAT specification, and for the name of the restore point.

   CREATE SCRIPT quarterly { 
     ALLOCATE CHANNEL c1
       DEVICE TYPE sbt
       PARMS 'ENV=(OB_MEDIA_FAMILY=&1)';
     BACKUP
       TAG &2
       FORMAT '/disk2/bck/&1%U.bck'
       KEEP FOREVER
       RESTORE POINT &3
       DATABASE;
   }
   

2. Connect RMAN to a target database (which must be mounted or open) and recovery catalog, specifying the initial values for the recovery catalog script.

   For example, enter the following command:

   % rman TARGET / CATALOG rco@catdb USING arc_backup bck0906 FY06Q3
   

   A recovery catalog is required for KEEP FOREVER, but is not required for any other KEEP option.

3. Run the command file created in the first step to create the stored script.

   For example, run the /tmp/catscript.rman command file as follows:

   RMAN> @/tmp/catscript.rman
   

   This step creates but does not execute the stored script.

4. Every quarter, execute the stored script, passing values for the substitution variables.

   The following example executes the recovery catalog script named quarterly. The example specifies arc_backup as the name of the media family (set of tapes), bck1206 as part of the FORMAT string and FY06Q4 as the name of the restore point.

   RUN
   { 
     EXECUTE SCRIPT quarterly 
       USING arc_backup
             bck1206
             FY06Q4;
   }

The PRINT SCRIPT command displays a stored script or writes it out to a file.

1. Start RMAN and connect to a target database and recovery catalog.
2. Run the PRINT SCRIPT command as follows:

   PRINT SCRIPT full_backup;
   

   To send the contents of a script to a file, use this form of the command:

   PRINT SCRIPT full_backup 
     TO FILE '/tmp/my_script_file.txt';
   

   For global scripts, the analogous syntax is as follows:

   PRINT GLOBAL SCRIPT global_full_backup;
   PRINT GLOBAL SCRIPT global_full_backup 
     TO FILE '/tmp/my_script_file.txt';

Use the LIST ... SCRIPT NAMES command to display the names of scripts defined in the recovery catalog.

1. Start RMAN and connect to a target database and recovery catalog.
2. Run the LIST ... SCRIPT NAMES command.

   For example, run the following command to list the names of all global and local scripts that can be executed for the currently connected target database:

   LIST SCRIPT NAMES;
   

   The following example lists only global script names:

   LIST GLOBAL SCRIPT NAMES;
   

   To list the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use the following form of the command:

   LIST ALL SCRIPT NAMES;
   

   For each script listed, the output indicates which target database the script is defined for (or whether a script is global).

Use the DELETE GLOBAL SCRIPT command to delete a stored script from the recovery catalog.

1. Start RMAN and connect to a target database and recovery catalog.
2. Enter the DELETE SCRIPT command.

   If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, then RMAN looks for a global stored script by the specified name and deletes the global script if it exists. For example, suppose you enter the following command:

   DELETE SCRIPT 'global_full_backup';

   In this case, RMAN looks for a script global_full_backup defined for the connected target database, and if it did not find one, it searches the global scripts for a script called global_full_backup and delete that script.

   To delete a global stored script, use DELETE GLOBAL SCRIPT:

   DELETE GLOBAL SCRIPT 'global_full_backup';
   

To run the RMAN client and start a stored script in the recovery catalog on startup, use the SCRIPT argument when starting the RMAN client.

% rman TARGET / CATALOG rco@catdb SCRIPT '/tmp/fbkp.cmd';

After you have created a recovery catalog and registered your target databases, you must maintain this catalog.

For example, you must run the RMAN maintenance commands, which are explained in "Maintaining RMAN Backups and Repository Records", to update backup records and to delete backups that are no longer needed. You must perform this type of maintenance regardless of whether you use RMAN with a recovery catalog. Other types of maintenance, such as upgrading a recovery catalog schema, are specific to use of RMAN with a recovery catalog.

If you use a recovery catalog in a Data Guard environment, then special considerations apply for backups and database files recorded in the catalog. See "About RMAN File Management in a Data Guard Environment" for an explanation of when backups are accessible to RMAN and how RMAN maintenance commands work with accessible backups.

When RMAN performs a resynchronization, it compares the recovery catalog to either the current or backup control file of the target database and updates the catalog with metadata that is missing or changed.

Most RMAN commands perform a resynchronization automatically when the target control file is mounted and the catalog is available. In a Data Guard environment, RMAN can perform a reverse resynchronization to update a database control file with metadata from the catalog.

You may decide to change the DB_UNIQUE_NAME of a database in a Data Guard environment. In this case, you can run the CHANGE DB_UNIQUE_NAME command to associate the metadata stored in recovery catalog for the old DB_UNIQUE_NAME to the new DB_UNIQUE_NAME.

1. Connect RMAN to the primary database as TARGET and also to the recovery catalog. For example, enter the following commands:

   % rman
   RMAN> CONNECT CATALOG rco@catdb
   
   recovery catalog database Password: password
   connected to recovery catalog database
   
   RMAN> CONNECT TARGET sbu@prodny
   
   target database Password: password
   connected to target database: PRODNY (DBID=39525561)
   

2. List the DB_UNQUE_NAME values known to the recovery catalog.

   Run the following LIST command:

   RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
   

3. Change the DB_UNIQUE_NAME in the RMAN metadata.

   The following example changes the database unique name from standby database prodsf1 to prodsf2:

   RMAN> CHANGE DB_UNIQUE_NAME FROM prodsf1 TO prodsf2;

You can use the UNREGISTER DATABASE command to unregister a database from the recovery catalog.

When a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the time of reregistration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost. Stored scripts, which are not stored in the control file, are also lost.

You create an incarnation of the database when you open the database with the RESETLOGS option. You can access a record of the new incarnation in the V$DATABASE_INCARNATION view.

1. Determine the incarnation key of the desired database incarnation. Obtain the incarnation key value by issuing a LIST command:

   LIST INCARNATION OF DATABASE trgt;
   
   List of Database Incarnations
   DB Key  Inc Key   DB Name   DB ID       STATUS     Reset SCN    Reset Time
   ------- -------   -------   ------      -------    ----------   ----------
   1       2         TRGT      1224038686  PARENT     1            02-JUL-12
   1       582       TRGT      1224038686  CURRENT    59727        10-JUL-12
   

   The incarnation key is listed in the Inc Key column.

2. Reset the database to the old incarnation. For example, enter:

   RESET DATABASE TO INCARNATION 2;
   

3. If the control file of the previous incarnation is available and mounted, then skip to Step 6 of this procedure. Otherwise, shut down the database and start it without mounting. For example:

   SHUTDOWN IMMEDIATE
   STARTUP NOMOUNT
   

4. Restore a control file from the old incarnation. If you have a control file tagged, then specify the tag. Otherwise, you can run the SET UNTIL command, as in this example:

   RUN 
   {
     SET UNTIL 'SYSDATE-45';
     RESTORE CONTROLFILE; # only if current control file is not available
   }
   

5. Mount the restored control file:

   ALTER DATABASE MOUNT;
   

6. Run RESTORE and RECOVER commands to restore and recover the database files from the prior incarnation, then open the database with the RESETLOGS option. For example, enter:

   RESTORE DATABASE;
   RECOVER DATABASE;
   ALTER DATABASE OPEN RESETLOGS;

   See Also:

   • About Database Incarnations

   • Oracle Database Backup and Recovery Reference for RESET DATABASE syntax

   • Oracle Database Backup and Recovery Reference for LIST syntax

You can use the IMPORT CATALOG command in RMAN to merge one recovery catalog schema into another.

This command is useful in the following situations:

• You have multiple recovery catalog schemas for different versions of the database. You want to merge all existing schemas into one without losing backup metadata.

• You want to move a recovery catalog from one database to another database.