12 Managing a Recovery Catalog

Purpose of the Recovery Catalog

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.

Basic Concepts for the Recovery Catalog

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:

• Datafile and archived redo log backup sets and backup pieces

• Datafile copies

• Archived redo logs and their copies

• Database structure (tablespaces and datafiles)

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

• Persistent RMAN configuration settings

Basic Steps of Managing a Recovery Catalog

The basic steps for setting up a recovery catalog for use by RMAN are as follows:

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 as well as increase its availability.

The remainder of the chapter explains how to manage the recovery catalog after it is operational. You can perform the following tasks:

• "Managing Stored Scripts" explains how to store RMAN scripts in the recovery catalog and manage them.

• Chapter 10, "Reporting on RMAN Operations" explains how to report on RMAN operations. You can use the LIST and REPORT commands with or without a recovery catalog. "Querying Recovery Catalog Views" explains how to report on RMAN operations by means of fixed views in the recovery catalog.

• "Maintaining a Recovery Catalog" explains a variety of tasks for ongoing recovery catalog maintenance, including how to import one recovery catalog into another recovery catalog.

If you no longer want to maintain a recovery catalog, then see "Dropping a Recovery Catalog".

Configuring the Recovery Catalog Database

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. The SYS user 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.

Creating the Recovery Catalog Schema Owner

After choosing the recovery catalog database and creating necessary space, you are ready to create the owner of the recovery catalog and grant this user necessary privileges. Assume the following background information for the instructions in the following sections:

• User SYS has SYSDBA privileges on the recovery catalog database catdb.

• A tablespace called tools in the recovery catalog database catdb stores the recovery catalog. If you use an RMAN reserved word as a tablespace name, you must enclose it in quotes and put it in uppercase. (Refer to Oracle Database Backup and Recovery Reference for a list of RMAN reserved words.)

• A tablespace called temp exists in the recovery catalog database.

To create the recovery catalog schema in the recovery catalog database:

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 rman IDENTIFIED BY password
     TEMPORARY TABLESPACE temp 
     DEFAULT TABLESPACE tools 
     QUOTA UNLIMITED ON tools;
   

   Note:

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

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 rman;
   

Executing the CREATE CATALOG Command

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.

To create the recovery catalog:

1. Start RMAN and connect to the database that will contain the catalog. Connect to the database as the recovery catalog owner.

2. 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 you want to use for the recovery catalog is an RMAN reserved word, then it must be uppercase and enclosed in quotes. For example:

   CREATE CATALOG TABLESPACE 'CATALOG';
   

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

About Registration of a Database in the Recovery Catalog

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

Registering a Database with the REGISTER DATABASE Command

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.

Use the following procedure:

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

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

   % rman TARGET / CATALOG rman@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
   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
   

About Virtual Private Catalogs

By default, all of the users of an RMAN recovery catalog have full privileges to 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, as well as 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 11g recovery catalog supports virtual private catalogs, but they are not used unless explicitly created. There is no restriction to the number of virtual private catalogs that can 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 creating one or more virtual private catalogs, using the directions that follow, 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.

The basic steps for creating a virtual private catalog are as follows:

1. Create the database user who will own the virtual private catalog (if this user does not already exist) and grant this user access privileges.

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

After the virtual private catalog is created, you can revoke catalog access privileges as necessary. This task is described in "Revoking Privileges from a Virtual Private Catalog Owner". "Dropping a Virtual Private Catalog" explains how to drop a virtual private catalog.

If the recovery catalog is a virtual private catalog, then the RMAN client connecting to it must be at patch level 10.1.0.6 or 10.2.0.3. Oracle9i RMAN clients cannot connect to a virtual private catalog. This version restriction does not affect RMAN client connections to an Oracle Database 11g base recovery catalog, even if it has some virtual private catalog users.

Creating and Granting Privileges to a Virtual Private Catalog Owner

This section assumes that you created the base recovery catalog.

Assume that the following databases are registered in the base recovery catalog: prod1, prod2, and prod3. The database user who owns the base recovery catalog is catowner. You want to create database user vpc1 and grant this user access privileges only to prod1 and prod2. By default, a virtual private catalog owner has no access to the base recovery catalog.

To create and grant privileges to a virtual private catalog owner:

1. Start SQL*Plus and connect to the recovery catalog database with administrator privileges.

2. If the user that will own the virtual private catalog is not yet created, then create the user.

   For example, if you want to create database user vpc1 to own the catalog, then you could 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 database user that will own the virtual private catalog, 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 catowner:

   % rman
   RMAN> CONNECT CATALOG catowner@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;
   

Creating a Virtual Private Catalog

This section assumes that the virtual private catalog owner has been given the RECOVERY_CATALOG_OWNER database role. Also, the base recovery catalog owner used the GRANT command to give the virtual private catalog owner access to metadata in the base recovery catalog.

To create a virtual private catalog:

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;
   

Revoking Privileges from a Virtual Private Catalog Owner

This section assumes that you have already created a virtual private catalog.

Assume that two databases are registered in the base recovery catalog: prod1 and prod2. As owner of the base recovery catalog, you have granted the vpc1 user access privileges to prod1. You have also granted this user the right to register databases in his virtual private catalog. Now you want to revoke privileges from vpc1.

To revoke privileges from a virtual private catalog owner:

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 catowner:

   % rman
   RMAN> CONNECT CATALOG catowner@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;
   

Dropping a Virtual Private Catalog

This section assumes that you have already created a virtual private catalog and now want to drop it. When you drop a virtual private catalog, you do not remove the base recovery catalog itself, but only drop the synonyms and views that refer to the base recovery catalog.

To drop a virtual private catalog:

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;
   

Backing Up the Recovery Catalog

A single recovery catalog is able to store metadata for multiple target databases. Consequently, loss of the recovery catalog can be disastrous. You should back up the recovery catalog frequently. This section provides general guidelines for developing a strategy for protecting the recovery catalog.

Choosing the Appropriate Technique for Physical Backups

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

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

Description of "Figure 12-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.

See Also:

"Performing Disaster Recovery" for more information for recovery with a control file autobackup

Recovering the Recovery Catalog

Restoring and recovering the recovery catalog database is much like restoring and recovering any other database with RMAN. You can restore the control file and server parameter file for the recovery catalog database from an autobackup, then restore and perform complete recovery on the rest of the database. If you are in a situation where you are using multiple recovery catalogs, then you can also use another recovery catalog to record metadata about backups of this recovery catalog database.

If recovery of the recovery catalog database through the normal Oracle recovery procedures is not possible, then you must re-create the catalog. Examples of this worst-case scenario include:

• A recovery catalog database that has never been backed up

• A recovery catalog database that has been backed up, but cannot be recovered because the datafile backups or archived logs are not available

You have the following options for partially re-creating the contents of the missing recovery catalog:

• Use the RESYNC CATALOG command to update the recovery catalog with any RMAN repository information from the control file of the target database or a control file copy. Any metadata from control file records that aged out of the control file is lost.

• Issue CATALOG START WITH... commands to recatalog any available backups.

To minimize the likelihood of this worst-case scenario, your backup strategy should at least include backing up the recovery catalog. This technique is described in "Backing Up the Recovery Catalog".

About Stored Scripts

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.

Creating Stored Scripts

You can use the CREATE SCRIPT command to create a stored script. If GLOBAL is specified, then a global script with this name must not already exist in the recovery catalog. If GLOBAL is not specified, then a local script must not already exist with the same name for the same target database. You can also use the REPLACE SCRIPT to create a new script or update an existing script.

To create a stored script:

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

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.

Replacing Stored Scripts

To update stored scripts, use the REPLACE SCRIPT command. If you are replacing a local script, then you must be connected to the target database that you connected to when you created the script. If the script does not already exist, then RMAN creates it.

To replace a stored script:

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';
   

Executing Stored Scripts

Use the EXECUTE SCRIPT command to run a stored script. If GLOBAL is specified, then a global script with this name must already exist in the recovery catalog; otherwise, RMAN returns error RMAN-06004. If GLOBAL is not specified, then RMAN searches for a local stored script defined for the current target database. If no local script with this name is found, then RMAN searches for a global script by the same name and executes it if one is found.

To execute 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 need to override the configured channels. Because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will 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 is 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; 
   }
   

Creating and Executing Dynamic Stored Scripts

You can specify substitution variables in the CREATE SCRIPT command. When you start RMAN on the command line, the USING clause specifies one or more values for use in substitution variables in a command file. As in SQL*Plus, &1 indicates where to place the first value, &2 indicates where to place the second value, and so on.

To create and use a dynamic stored script:

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 rman@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;
   }
   

Printing Stored Scripts

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

To print stored scripts:

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 would be as follows:

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

Listing Stored Script Names

Use the LIST ... SCRIPT NAMES command to display the names of scripts defined in the recovery catalog. LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES are the only commands that work when RMAN is connected to a recovery catalog without connecting to a target instance; the other forms of the LIST ... SCRIPT NAMES command require a recovery catalog connection.

To list stored script names:

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

Deleting Stored Scripts

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

To delete a stored script:

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';
   

Executing a Stored Script at RMAN Startup

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. For example, you could enter the following command to execute script /tmp/fbkp.cmd:

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

You must connect to a recovery catalog, which contains the stored script, and target database, to which the script will apply, when starting the RMAN client.

If local and global stored scripts are defined with the same name, then RMAN always executes the local script.

About Recovery Catalog Maintenance

After you have created a recovery catalog and registered your target databases, you need to maintain this catalog. For example, you need to run the RMAN maintenance commands, which are explained in Chapter 11, "Maintaining RMAN Backups and Repository Records", to update backup records as well as 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 "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.

Resynchronizing the Recovery Catalog

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.

Updating the Recovery Catalog After Changing a DB_UNIQUE_NAME

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. The CHANGE DB_UNIQUE_NAME command does not actually change the DB_UNIQUE_NAME of the database itself. Instead, it updates the catalog metadata for the database whose unique name has been or will be changed.

The following procedure assumes that the DB_UNIQUE_NAME of the primary database is prodny, and that you have changed the DB_UNIQUE_NAME of a standby database from prodsf1 to prodsf2. You can use the same procedure after changing the DB_UNIQUE_NAME of a primary database, except in step 1 connect RMAN as TARGET to a standby database instead of a primary database.

To update the recovery catalog after a DB_UNIQUE_NAME is changed:

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 catowner@catdb
   
   recovery catalog database Password: password
   connected to recovery catalog database
   
   RMAN> CONNECT TARGET SYS@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;
   

Unregistering a Target Database from the Recovery Catalog

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.

Resetting the Database Incarnation in the Recovery Catalog

As explained in "Database Incarnations", you create a new 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.

If you open the database with the RESETLOGS option, then a new database incarnation record is automatically created in the recovery catalog. The database also implicitly and automatically issues a RESET DATABASE command, which specifies that this new incarnation of the database is the current incarnation. All subsequent backups and log archiving done by the target database is associated with the new database incarnation.

Whenever RMAN returns the database to an SCN before the current RESETLOGS SCN, either by means of RESTORE and RECOVER or FLASHBACK DATABASE, the RESET DATABASE TO INCARNATION command is required. However, you do not need to execute RESET DATABASE TO INCARNATION explicitly in the following scenarios because RMAN runs the command implicitly with Flashback.

• You use FLASHBACK DATABASE to rewind the database to an SCN in the direct ancestral path (see "Database Incarnations" for an explanation of the direct ancestral path).

• You use FLASHBACK DATABASE to rewind the database to a restore point.

The following procedure explains how to reset the database incarnation when recovering through a RESETLOGS.

To reset the recovery catalog to an older incarnation for media recovery:

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-02
   1       582       TRGT      1224038686  CURRENT    59727        10-JUL-02
   
   

   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:

   Oracle Database Backup and Recovery Reference for RESET DATABASE syntax, Oracle Database Backup and Recovery Reference for LIST syntax

Upgrading the Recovery Catalog

This section explains what a recovery catalog upgrade is and when you need to do it.

VERSION
------------
08.01.07
09.00.01
10.02.00

Importing and Moving a Recovery Catalog

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.