Creating a Recovery Catalog in Oracle

In this lesson, we will discuss the steps to take to create your own recovery catalog. If you choose to use a recovery catalog, you must create it before using Recovery Manager. List the steps to create a recovery catalog in Oracle 11g R2.
Creating a "Recovery Catalog" in Oracle 11g R2 involves several steps. The Recovery Catalog is used by RMAN (Recovery Manager) to store metadata about backups and recovery operations. Here’s a step-by-step guide:

1. Set Up the Recovery Catalog Database

   The Recovery Catalog is typically created in a separate database from the target database(s) to ensure reliability during recovery.

   • Create a dedicated database or use an existing database to host the Recovery Catalog.
   • Ensure the database is open and accessible.
2. Create a Recovery Catalog User

   Create a user in the Recovery Catalog database and grant the necessary privileges.

   -- Connect as SYSDBA to the Recovery Catalog database
   sqlplus / as sysdba
   
   -- Create the user
   CREATE USER rman_catalog_user IDENTIFIED BY password;
   
   -- Grant required privileges
   GRANT CONNECT, RESOURCE TO rman_catalog_user;
   GRANT RECOVERY_CATALOG_OWNER TO rman_catalog_user;
           

3. Connect to RMAN

   Connect RMAN to the Recovery Catalog database.

   rman
   RMAN> CONNECT CATALOG rman_catalog_user/password@recovery_catalog_db;
           

4. Create the Recovery Catalog

   Run the following command in RMAN:

   RMAN> CREATE CATALOG;
           

   • This initializes the Recovery Catalog schema in the Recovery Catalog database.
5. Register Target Databases

   Register the target databases with the Recovery Catalog.

   RMAN> CONNECT TARGET target_user/password@target_database;
   RMAN> REGISTER DATABASE;
           

   • This adds the target database's metadata to the Recovery Catalog.
6. Configure Persistent Settings

   Set persistent configurations for backups in RMAN.

   RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
   RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
   RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
           

7. Test the Setup

   Perform a test backup and ensure the metadata is saved in the Recovery Catalog.

   RMAN> BACKUP DATABASE;
           

8. Maintain the Recovery Catalog
   • Periodically resynchronize the Recovery Catalog with the target database.
   
   

   RMAN> RESYNC CATALOG;
   

   • Back up the Recovery Catalog to ensure it can be restored in case of failure.

   RMAN> BACKUP RECOVERY AREA;
               

Best Practices

• Always backup the Recovery Catalog regularly.
• Use separate storage for the Recovery Catalog database to isolate it from the target databases.
• Monitor and prune the Recovery Catalog using RMAN commands like DELETE EXPIRED.

By following these steps, you’ll have a functioning Recovery Catalog for managing RMAN backups in Oracle 11g R2. Normally you will create a new database to store a recovery catalog.

Although it is advisable to create the recovery catalog in the same version as the target database, this is not required in the case of a single recovery catalog serving a mix of 9i, 10g, and 11g databases. In this case, the recovery catalog must be created in a database that is running the same version as the highest version of the target databases. However, a catalog in a 9i or 10g database cannot be used to backup an Oracle 11g target since Oracle 9i and 10g were predecessors to Oracle 11g.
Since the recovery catalog is capable of holding backup-related metadata longer than the target databases control file, this essentially depicts that the storage should be persistent compared to the control file.

• Persistent Storage
  In Oracle, the persistent storage lies only in the form of datafiles managed by means of tablespaces . The same holds true for the recovery catalog, which is going to be managed within a dedicated tablespace specifically created for the recovery catalog. Recovery catalog creation demands the creation of its owner which will be used to manage it.
  Although Oracle does not prevent creating the recovery catalog schema in the target database, such an implementation is unacceptable. It is recommended that you create this schema on a different database that resides at a remote host. If you create this schema on the same database and you lose the database, you also lose all backup metadata as well. If you create this schema using a different database at the same host, due to media failure, you can lose 1) all hard drives and 2) backup metadata. For this reason, it is imperative that you create the schema on a different database that resides at remote host (separate physical server).
  Therefore, it is recommended that the recovery catalog schema be put in a different database that resides on a different server.
  In order to create the recovery catalog, you need to create a user, that is, a separate schema to hold the metadata of backups, and then create a catalog which holds RMAN backup metadata of registered databases.
  Listed below are the main steps in creating the recovery catalog:
  1. Create a tablespace that holds backup metadata
  2. Create a user that owns the catalog
  3. Grant recovery_catalog_owner privilege to that user
  4. Using RMAN executable, connect to the catalog database
  5. Use create catalog command to create a recovery catalog

You are now ready to use Recovery Manager. Most of the information that is stored in the recovery catalog is read from your target database control file. As you will learn later, it is important to maintain the recovery catalog by ensuring that it gets backed up on a regular basis and that it is kept up to date with any structural changes to its schema.

You need a password file. Often this file is created during your Oracle installation. My database is called orc1, and my oracle home is C:\Oracle. Thus, my password file is C:\Oracle\database\pwdorc1.ora. This file is not readable and contains the password for sys and internal.
Follow these steps to make sure that your password file is set up correctly. Assume that this is a new installation with nothing in the password file. We will also use your setup for naming in your examples.

1. Delete the file C:\Oracle\database\pwdorc1.ora.
2. Using a DOS window, execute the orapwd utility:

C:\Oracle\bin>orapwd80 file=c:\Oracle\database\


pwdorc1.ora password=oracle entries=20

This command creates a new password file for your database orc1 in the directory %oracle_home%\database. Continue to follow this format. Now you have set the SYS and INTERNAL user passwords to Oracle. It is recommended that you use something other than the usual default passwords.

1. In your init.ora file, add or modify the following entry:

remote_login_passwordfile = exclusive

By default, this is usually shared. More information on password files can be found in the Oracle Administrator's Guide, the Oracle release 8.0 guide, or online at technet.oracle.com under the documentation sections.
REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file. Values:

1. shared: One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.
2. exclusive: The password file can be used by only one database. The password file can contain SYS as well as non-SYS users.
3. none: Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

The next lesson wraps up this module.