10 Managing the 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. Note that 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.

Creating the Recovery Catalog 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 with password oracle has SYSDBA privileges on the recovery catalog database catdb.

• A tablespace called tools in the recovery catalog database catdb stores the recovery catalog. Note that to 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.

• The database is configured in the same way as all normal databases, for example, catalog.sql and catproc.sql have successfully run.

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

Creating the Recovery Catalog

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:

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;

Registering a Database in the Recovery Catalog

The first step in using a recovery catalog with a target database is registering the database in the recovery catalog. Use the following procedure:

Verify that the registration was successful by running REPORT SCHEMA:

RMAN> 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

RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';
RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', 
     '/disk1/arch_logs/archive1_732.dbf';
RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

RMAN> CATALOG START WITH '/disk1/backups/';

RMAN> CATALOG START WITH '/disk1/backups'; 

RMAN> CATALOG START WITH '/disk1/backups/';

Unregistering a Target Database from the Recovery Catalog

The UNREGISTER DATABASE command is used to unregister a database from the recover catalog.

To unregister a database:

Resetting the Database Incarnation in the Recovery Catalog

When you run either the RMAN command or the SQL statement ALTER DATABASE OPEN RESETLOGS, you create a new incarnation of the database. You can access a record of the new incarnation in the V$DATABASE_INCARNATION view of the target database.

If you run the RMAN command or the SQL statement ALTER DATABASE OPEN RESETLOGS, 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.

You may need to change the current incarnation of the database in the recovery catalog for some recovery tasks. The steps for changing the incarnation are described in the following procedure.

To reset the recovery catalog to an older incarnation:

Removing DELETED Records From the Recovery Catalog After Upgrade

In Oracle9i and later, RMAN always removes catalog records on deleting files, and never updates them to status DELETED. However, in releases prior to Oracle9i, RMAN updated recovery catalog records to DELETED status after deleting the physical files rather than removing the records. Thus, records with status DELETED can appear in the recovery catalog when you upgrade a recovery catalog created prior to Oracle9i to the current release. For this special case, you can run the prgrmanc.sql script, which is stored in an operating system specific location ($ORACLE_HOME/rdbms/admin on UNIX).

To remove all backup records with status DELETED:

Types of Records Updated When Recovery Catalog is Resynchronized

Table 10-2, "Records Updated During a Resynchronization" describes the types of records that RMAN resynchronizes.

Full and Partial Resynchronization

Resynchronizations can be full or partial. In a partial resynchronization, RMAN reads the current control file to update changed information about new backups, new archived logs, and so forth. However, RMAN does not resynchronize metadata about the database physical schema: datafiles, tablespaces, redo threads, rollback segments (only if the database is open), and online redo logs. In a full resynchronization, RMAN updates all changed records, including those for the database schema.

When to Resynchronize the Recovery Catalog

RMAN automatically performs full or partial resynchronizations in most situations in which they are needed. Most RMAN commands such as BACKUP, DELETE, and so forth perform a full or partial resynchronization (depending on whether the schema metadata has changed) automatically when the target database control file is mounted and the recovery catalog database is available. Thus, you should not need to manually run RESYNC CATALOG very often.

The following sections describe situations in which a manual resynchronization is required.

Forcing a Full Resynchronization of the Recovery Catalog

Use RESYNC CATALOG to force a full resynchronization of the recovery catalog.

Resynchronizing the Recovery Catalog and CONTROL_FILE_RECORD_KEEP_TIME

If you maintain a recovery catalog, then use the RMAN RESYNC CATALOG command often enough to ensure that control file records are propagated to the recovery catalog before they are reused.

Make sure that CONTROL_FILE_RECORD_KEEP_TIME is longer than the interval between backups or resynchronizations. Otherwise, control file records could be reused before they are propagated to the recovery catalog. An extra week is a safe margin in most circumstances.

Creating Stored Scripts: CREATE SCRIPT

Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE SCRIPT command, as shown in this example:

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

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

For a global script, the syntax is similar:

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

You can also 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;
}

Finally, you can create a local or global script, reading its contents from a text file:

CREATE SCRIPT full_backup FROM FILE 'my_script_file.txt';

The file must begin with a { character, contain a series of commands valid within a RUN block, and end with a } character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard.

Running Stored Scripts: EXECUTE SCRIPT

To run a stored script, connect to the target database and recovery catalog, and use EXECUTE SCRIPT. EXECUTE SCRIPT requires a RUN block, as shown:

RUN { EXECUTE SCRIPT full_backup; }

This 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, RMAN will execute 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. Assuming 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; }

Executing a global script only affects the connected target database; to run a global script across multiple databases, you must connect the RMAN client to each one separately and execute the script.

Your script will use 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. Note that, because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will not execute.

Displaying a Stored Script: PRINT SCRIPT

The PRINT SCRIPT command displays a stored script or writes it out to a file. With RMAN connected to the target database and recovery catalog, use the PRINT SCRIPT command as shown here:

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 'my_script_file.txt';

For global scripts, the analogous syntax would be:

PRINT GLOBAL SCRIPT global_full_backup;

and

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

Listing Stored Scripts: LIST SCRIPT NAMES

Use the LIST SCRIPT NAMES command to display the names of scripts defined in the recovery catalog. This command displays the names of all stored scripts, both global and local, that can be executed for the currently connected target database:

LIST SCRIPT NAMES;

If RMAN is not connected to a target database when the LIST SCRIPT NAMES command is run, then RMAN will respond with an error.

To view only global script names, use this form of the command:

LIST GLOBAL SCRIPT NAMES;

To view 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 this form of the command:

LIST ALL SCRIPT NAMES;

The output will indicate for each script listed which target database the script is defined for (or whether a script is global).

Updating Stored Scripts: REPLACE SCRIPT

To update stored scripts, connect to the target database and recovery catalog and use the REPLACE SCRIPT command. If the script does not already exist, then RMAN creates it.

This command updates stored script full_backup with new contents:

REPLACE SCRIPT full_backup 
{
  BACKUP DATABASE PLUS ARCHIVELOG;
}

Global scripts can be updated using the REPLACE GLOBAL SCRIPT command when connected to a recovery catalog, 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 this form of the command:

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

Deleting Stored Scripts: DELETE SCRIPT

To delete a stored script from the recovery catalog, connect to the catalog and a target database, and use the DELETE SCRIPT command:

DELETE SCRIPT 'full_backup';

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

DELETE GLOBAL SCRIPT 'global_full_backup';

If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists. So, if you were to enter the command

DELETE SCRIPT 'global_full_backup';

RMAN would look for a script 'global_full_backup' defined for the connected target database, and if it did not find one, it would search the global scripts for a script called 'global_full_backup' and delete that script.

Starting the RMAN Client and Running a Stored Script

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 SYS/oracle@trgt CATALOG rman/cat@catdb SCRIPT 'full_backup';

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.

Restrictions on Stored Script Names

There are some issues to be aware of about how RMAN resolves script names, especially when a local and global script share the same name.

• RMAN permits but generally does not require that you use quotes around the name of a stored script. However, if the name begins with a digit or if the name is an RMAN reserved word, you will have to put quotes around the name to use it as a stored script name. Consider avoiding stored script names that begin with characters other than A-Z or that are the same as RMAN reserved words.

  See Also:

  Oracle Database Backup and Recovery Reference

• When starting the RMAN client with a SCRIPT argument on the command line, if local and global scripts are defined with the same name, then RMAN will always execute the local script.

• 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, RMAN will look for a global script by the same name to execute, delete or print. For example, if the a stored script global_full_backup is in the recovery catalog as a global script, but no local stored script global_full_backup is defined for the target database, the following command will delete the global script:

  DELETE SCRIPT global_full_backup;
  

Consider using some naming convention to avoid mistakes due to confusion between global stored scripts and local stored scripts.

Backing Up the Recovery Catalog

Here are some general guidelines you should follow when developing a strategy for backing up the recovery catalog.

Choosing the Appropriate Method for Physical Backups

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

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

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

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

See Also:

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

Restoring and Recovering the Recovery Catalog from Backup

Restoring and recovering the recovery catalog is much like restoring and recovering any other database, if you backed it up with RMAN.

You can restore the control file and SPFILE for the recovery catalog database from an autobackup, then restore and perform complete recovery on the rest of the database. The processes required are all described in Oracle Database Backup and Recovery Basics You can also use another recovery catalog to record metadata about backups of this recovery catalog database, if you are in a situation where you are using multiple recovery catalogs.

Re-Creating the Recovery Catalog

If the recovery catalog database is lost or damaged, and 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 these 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. Note that 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 using RMAN as described in "Backing Up the Recovery Catalog".

Considerations When Moving Catalog Data

You should only import the recover catalog into a schema that does not already contain a recovery catalog schema. In other words, the user who will own the imported recovery catalog schema should not already own a recovery catalog schema. For example, if user rman owns the recovery catalog on database catdb, and you want to export the recovery catalog on catdb and import it into database catdb2, then rman should not already own a recovery catalog on catdb2. You should either create a new recovery catalog owner on catdb2, or drop the current user rman on catdb2 and then re-create the user. You cannot merge a recovery catalog into an existing recovery catalog.

The basic steps for exporting a recovery catalog from a primary database and importing the catalog into a secondary database are as follows:

You should not run the CREATE CATALOG command either before or after the Import of the catalog into the secondary database. By importing the catalog data into the new schema, you effectively create the catalog in the secondary database.

Exporting the Recovery Catalog

This example uses the Original Export utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Export utility.

The following procedure creates a logical export of the recovery catalog.

Importing the Recovery Catalog

This example uses the Original Import utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Import utility.

To make a logical import of the recovery catalog from the command line:

1. Create a new user in another database. For the recommended SQL syntax for creating a new user in a recovery catalog database, see "Creating the Recovery Catalog Owner". Be sure to grant the new user the necessary privileges.

   Import the catalog data from the export file. Execute the import at the command line, making sure to do the following:

   Connect as the new owner of the recovery catalog.

   Specify the old owner with the FROMUSER parameter.

   Specify the new owner with the TOUSER parameter.

   Specify the import file.

   For example, assume the following:

   • The old owner of the catalog in database prod1 is rman.

2. The user in the new recovery catalog database catdb2 is rman2.

3. The file containing the export of the catalog is cat.dmp.

   The command is then as follows:

   % imp USERID=rman2/cat2@catdb2 FILE=cat.dmp FROMUSER=rman TOUSER=rman2 
   

   Use the imported catalog data for restore and recovery of your target database.

Identifying Rows for a Database in the Catalog Views

Most of the catalog views contain the columns DB_KEY and DBINC_KEY. Each target database can be uniquely identified by either the primary key, which is the DB_KEY column value, or the DBID, which is the 32-bit unique database identifier. Each incarnation of a target database is uniquely identified by the DBINC_KEY primary key. When querying data about a specific incarnation of a target database, you should use these columns to specify the database. Then, you can perform joins with most of the other catalog views to obtain the desired information.

Identifying Rows for a Database Object in the Catalog Views

An important difference between catalog and V$ views is that a different system of unique identifiers is used for backup and recovery objects. For example, many V$ views such as V$ARCHIVED_LOG use the RECID and STAMP columns to form a concatenated primary key. The corresponding catalog view uses a derived value as its primary keys and stores this value in a single column. For example, the primary key in RC_ARCHIVED_LOG is the AL_KEY column. The AL_KEY column value is the primary key that RMAN displays in the LIST command output.

Querying Catalog Views for the Target DB_KEY or DBID Values

The DB_KEY value, which is the primary key for a target database, is used only in the recovery catalog. The easiest way is to obtain the DB_KEY is to use the DBID of the target database, which is displayed whenever you connect RMAN to the target database. The DBID, which is a unique system-defined number given to every Oracle database, is used to distinguish among databases registered in the RMAN recovery catalog.

Assume that you want to obtain information about one of the target databases registered in the recovery catalog. You can easily determine the DBID from this database either by looking at the output displayed when RMAN connects to the database, querying V$RMAN_OUTPUT, or querying a V$DATABASE view as in the following:

SELECT DBID 
FROM V$DATABASE;

DBID
---------
598368217

You can then obtain the DB_KEY for a target database by running the following query, where dbid_of_target is the DBID that you previously obtained:

SELECT DB_KEY 
FROM RC_DATABASE 
WHERE DBID = dbid_of_target;

To obtain information about the current incarnation of a target database, specify the target database DB_KEY value and perform a join with RC_DATABASE_INCARNATION by using a WHERE condition to specify that the CURRENT_INCARNATION column value is set to YES. For example, to obtain information about backup sets in the current incarnation of a target database with the DB_KEY value of 1, you can execute the following script:

SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME
  FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
  WHERE i.DB_KEY = 1
  AND i.DB_KEY = b.DB_KEY
  AND i.CURRENT_INCARNATION = 'YES';

You should use the DB_NAME column to specify a database only if you do not have more than one database registered in the recovery catalog with the same DB_NAME. RMAN permits you to register more than one database with the same database name, but requires that the DBID values be different. For example, you can have ten databases with the DB_NAME value of prod1, each with a different DBID. Because the DBID is the unique identifier for every database in the metadata, use this value to obtain the DB_KEY and then use DB_KEY to uniquely identify the database.

Using RC_BACKUP_FILES and DBMS_RCVMAN.SETDATABASE

The view RC_BACKUP_FILES can be queried for information about all backups of any database registered in the recovery catalog. However, before querying RC_BACKUP_FILES you must call DBMS_RCVMAN.SETDATABASE , specifying the DBID of one of the databases registered in the recovery catalog, as shown in the following example:

SQL>  CALL DBMS_RCVMAN.SETDATABASE(null,null,null,2283997583,null);

The fourth parameter must be the DBID of a database registered in the recovery catalog. The other parameters must all be NULL.