The Geek Diary

Oracle Interview Questions : Recovery catalog for RMAN backup

by

Why use a recovery catalog?

The RMAN catalog has several benefits. It makes restore if all the target database is lost, including the controlfile. As that controlfile contains all backup information, the catalog would then be used to identify a controlfile backup. If an RMAN catalog is not used, the controlfile autobackup should be configured to ON. There are some features which are only supported using an RMAN catalog. Primarily the ability to store scripts which would be available to all target databases using the catalog.

It is recommended that the RMAN recovery catalog schema be created in a database which would be available if the target database is lost. It is not recommended to create the recovery catalog in the target database itself. Ideally, the catalog is created in a database on a different server. If it is not possible to create the recovery catalog on a different server, ensure that the recovery catalog and target databases do not reside on the same disks. You do not want a failure to affect both the recovery catalog and the target database. Therefore, if possible, take other measures as well to eliminate common points of failure between your recovery catalog database and the target databases using the catalog.

The recovery catalog contains information about RMAN operations, including:

  • Datafile and archived redo log backup sets and backup pieces
  • Datafile copies
  • Archived redo logs and their copies
  • Tablespaces and datafiles on the target database
  • Stored scripts, which are named user-created sequences of RMAN commands
  • Persistent RMAN configuration settings

Creating a recovery catalog is a 3 step process. The recovery catalog is stored in the default tablespace of the recovery catalog owner/schema. SYS cannot be the owner of the recovery catalog.

  1. Creating the recovery catalog owner/schema
  2. Creating the recovery catalog
  3. Registering the target database(s)

Creating the Recovery Catalog Owner/Schema

Determine the size of recovery catalog schema:

– Size of recovery catalog schema depends on

  • The number of databases monitored by the catalog.
  • The rate at which archived redo log generates in the target database
  • The number of backups for each target database
  • RMAN stored scripts stored in the catalog

Creating the recovery catalog schema

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. For example:

$ sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
     DEFAULT TABLESPACE tools  
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON tools; 

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

How to create Recovery Catalog?

Using RMAN, connect to the recovery catalog schema and create the catalog schema. For example:

In the below example “catdb” is the catalog database connection string. Before creating the recovery catalog make sure to have the tnsnames.ora entry for the catalog database in the target server and the listener must be up and running in the catalog database server. You must be able to connect to the catalog database from sqlplus from the target server.

$ rman catalog rman/rman @catadb

RMAN> CREATE CATALOG;

How to register target database?

Connect RMAN to the target database and recovery catalog database and register the database. The target database must be either open or in mounted. For example:

$ rman TARGET / CATALOG rman/rman @catdb

RMAN> REGISTER DATABASE;

RMAN creates rows in the catalog tables to contain information about the target database. It copies all the pertinent data from the controlfile into the catalog tables, synchronizing the catalog with the control file. You can register multiple target databases in a single recovery catalog. RMAN uses the DBID to distinguish one database from another. Therefore do NOT register cloned databases created without using RMAN duplicate or whose DBID wasn’t changed using NID within the same catalog schema.

How to Upgrade recovery catalog Schema?

The catalog schema must support the highest target version it supports. Therefore, when a target database is upgraded, you need to also upgrade the RMAN catalog schema. Connect to RMAN from the target database (with highest version) and connect to the catalog schema to upgrade the schema version. For example:

$ rman target / catalog rman/rman @ catdb
RMAN> UPGRADE CATALOG;
RMAN-06435: recovery catalog owner is rman 
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade 
RMAN> UPGRADE CATALOG;
NOTE: Issuing ‘upgrade catalog’ will only upgrade the catalog schema. It does not upgrade the catalog database in any way.

How to upgrade recovery catalog database?

Upgrading the recovery catalog database is same as the any other database upgrade steps. Upgrading the catalog database does not upgrade the catalog database schema.

How to remove a catalog?

The “drop catalog;” command to removes an RMAN catalog. This command needs to be entered twice to confirm the operation. For example:

RMAN> DROP CATALOG;

How to unregister the target database from the recovery catalog?

From 10G onwards, to unregister the target database, connect to that particular target and the catalog schema and issue ‘unregister database…‘. For example:

RMAN> UNREGISTER DATABASE [database_name] ;

This command removes ONLY information for that target database from the catalog schema. This is different than ‘drop catalog’ which drops all the tables.

Prior to release 10G, in order to unregister the target database you need to execute the following statement in the recovery catalog database within sqlplus connected as recovery catalog schema owner. For example: 

$ sqlplus rman/rman@catdb

SQL > DBMS_RCVCAT.UNREGISTERDATABASE(db_key, db_id);

How to backup of the Recovery Catalog?

Recovery catalog database is just like any other database. This database backup need to be taken every time after the target database backup. You can take physical backup using rman or logical backup of the catalog database using datapump or export.

Few guideline for 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.
  • Do not use another recovery catalog as the repository for the backups.
  • Configure the control file autobackup feature to ON.

How to restore and Recover recovery catalog from Backup?

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

Compatibility of the Recovery Catalog

When RMAN with a recovery catalog is used in an environment running different Oracle versions, check the compatibility between the RMAN client, recovery catalog database, recovery catalog schema version, and target databases.

How to identify recovery catalog schema version?

The schema version of the recovery catalog is stored in the recovery catalog itself. This does not necessarily equal the version fo the catalog database. This information is useful when checking compatibility. To determine the schema version of the recovery catalog connect to catalog database from the recovery catalog user and then query RCVER table. Look at the example :

% sqlplus rman/rman @catdb 

SQL > SELECT * FROM rcver;   

VERSION   
------------   
11.01.00

If the table displays multiple rows, then the highest version in the RCVER table is the current catalog schema version. For example, the following catalog schema version is 10.2.0:

VERSION 
------------ 
08.01.07  
09.02.00 
10.02.00

You May Also Like