DBA Tips Archive for Oracle

  


The Recovery Catalog

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Setting up the Recovery Catalog
  2. Registering the Target Database
  3. Unregister a Database From the Recovery Catalog



Setting up the Recovery Catalog

To use RMAN, a recovery catalog is not necessary. Remember that RMAN will always use the control file of the target database to store backup and recovery operations. To use a recovery catalog, you will first need to create a recovery catalog database and create a schema for it. The catalog (database objects) will be located in the default tablespace of the schema owner. Please note that the owner of the catalog cannot be the SYS user.

The recovery catalog database should be created on a different host, on different disks, and in a different database from the target databse you will be backing up. If you do not, the benefits of using a recovery catalog are lost if you loose the database and need to restore.

The first step is to create a database for the recovery catalog. For the purpose of this example, I created an Oracle 9.2.0 database named CATDB. The database has the following installed:

Now, let's create the recovery catalog:

  1. Start SQL*Plus and then connect with SYSDBA privileges to the database containing the recovery catalog:
    % sqlplus "sys/change_on_install as sysdba"
  2. Create a user and schema for the recovery catalog:
    SQL> CREATE USER rman IDENTIFIED BY rman
      2  DEFAULT TABLESPACE tools
      3  TEMPORARY TABLESPACE temp
      4  QUOTA UNLIMITED ON tools;
    
    User created.
  3. Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with privileges to maintain and query the recovery catalog:
    SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
    
    Grant succeeded.
  4. Grant other desired privileges to teh RMAN user:
    SQL> GRANT CONNECT, RESOURCE TO rman;
    
    Grant succeeded.
  5. After creating the catalog owner you should now create the catalog itself by using the CREATE CATALOG command within the RMAN interface. This command will create the catalog in the default tablespace of the catalog owner. you will need to connect to the database that will contain the catalog as teh catalog owner as follows:
    % rman catalog rman/rman@catdb
    
    Recovery Manager: Release 9.2.0.1.0 - Production
    
    Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
    
    connected to recovery catalog database
    recovery catalog is not installed
  6. Now, run the CREATE CATALOG command to create the catalog. Note that this process can take several minutes to complete.
    RMAN> create catalog;
    
    recovery catalog created



Registering the Target Database

Before using RMAN using a recovery catalog, you will need to register the taget database(s) in the recovery catalog. RMAN will obtain all information it needs to register the target database from the database itself.

As long as each target database has a distinct DBID, you can register more than one target database in the same recovery catalog. Each database registered in a given catalog must have a unique database identifier (DBID), but not necessarily a unique database name.

You can use either the command-line utilities provided by RMAN or the Oracle Enterprise Manager GUI to register the target database. For the purpose of this example, I will be using the command-line utilities. I will be registering a database named TARGDB to a recovery catalog within a database named CATDB. The target database must be either mounted or opened in order to register it.

% . oraenv
ORACLE_SID = [TARGDB] ? TARGDB

% rman target backup_admin/backup_admin catalog rman/rman@catdb

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TARGDB (DBID=2457750772)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete



Unregister a Database From the Recovery Catalog

Taken from Metalink: Doc ID: Note:1058332.6

This section describes the steps on how to remove (unregister) a database (the target database) from the recovery catalog.

You can unregister a database by running the following procedure from the while logged into the recovery catalog:

SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)

To unregister a database, do the following:

NOTE: If the target database does not exists anymore, the only steps to execute are (1) and (3). Because the backupsets cannot be deleted from the catalog (requires to connect to the target database) they are not be deleted from disk or tape either. So you have to remove these backupsets manually. A list of the related backupsets are:
SQL> select handle from rc_backup_piece where db_id = <see step (1)>;

  1. Identify the database that you want to unregister. Run the following query from the recovery catalog using Server Manager or SQL*Plus (connected as the RMAN user):
    SQL> select * from rc_database;
    
        DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
    ---------- ---------- ---------- -------- ----------------- ---------
             1          2 2498101982 TARGDB                   1 15-JAN-04
           105        106 2457750772 OIDDB                    1 14-DEC-03
           128        129 2351019032 OMSDB                    1 15-JAN-04
           301        302 2498937635 TARGDB              140831 25-JAN-04
    For this example, I want to unregister all databases with this catalog.

  2. Remove the backupsets that belong to the database that you want to unregister.

    • Find the backupsets of the database that you want to unregister.
      RMAN> list backupset of database;

    • Remove the backupsets that belongs only to the database you want to unregister.
      RMAN> allocate channel for delete type disk;
      RMAN> change backupset XXX delete;
      NOTE: You need to allocate a channel for the delete. In this example a disk drive is being used and not a tape. The procedure for a backup done to tape is the same except you have to allocate a different channel for tape. Example:
      RMAN> allocate channel for delete type 'sbt_tape';
      The XXX value is the 'list of key' value from the 'list backupset of database' command

  3. Unregister the database by executing the following procedure from the recovery catalog:
    SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)
    The "db_key" and "db_id" values you will get by running the following query from the recovery catalog:
    SQL> select * from rc_database;
        DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
    ---------- ---------- ---------- -------- ----------------- ---------
             1          2 2498101982 TARGDB                   1 15-JAN-04
           105        106 2457750772 OIDDB                    1 14-DEC-03
           128        129 2351019032 OMSDB                    1 15-JAN-04
           301        302 2498937635 TARGDB              140831 25-JAN-04
    Make sure you are using the correct values by looking at the 'NAME' column of the "rc_database" table. Here is an example of how to unregister all databases within this catalog:
    SQL> execute dbms_rcvcat.unregisterdatabase(1, 2498101982)
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_rcvcat.unregisterdatabase(105, 2457750772)
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_rcvcat.unregisterdatabase(128, 2351019032)
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_rcvcat.unregisterdatabase(301, 2498937635)
    
    PL/SQL procedure successfully completed.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 18-Nov-2010 18:29:31 EST
Page Count: 9849