ORACLE DATABASE ADMINISTRATION

A base recovery catalog is a database schema that stores RMAN metadata for one or more target databases, serving as a central repository for backup and recovery information.

This post covers how to create a Recovery Catalog, register a database, and optionally set up a Virtual Private Catalog (VPC) for multi-user backup environments.

---

⚙️ Step 1: Create a Recovery Catalog User

First, connect to the recovery catalog database (a separate database from your target) and create a dedicated user.

CREATE USER reco_user IDENTIFIED BY password
  DEFAULT TABLESPACE cat_tbs
  QUOTA UNLIMITED ON cat_tbs;

GRANT recovery_catalog_owner TO reco_user;

Next, execute the dbmsrmansys.sql script to grant additional privileges required for the RECOVERY_CATALOG_OWNER role:

@$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

---

🧩 Step 2: Create the Recovery Catalog

Start RMAN and connect to the recovery catalog database as the new user:

RMAN> CONNECT CATALOG reco_user@catdb

When prompted, enter the password. Once connected, create the catalog:

RMAN> CREATE CATALOG;

✅ This command creates the recovery catalog schema objects in the reco_user schema.

---

🔗 Step 3: Register the Target Database

In the same RMAN session, connect to your target database (the one to be backed up) and register it with the catalog.

RMAN> CONNECT TARGET /
RMAN> REGISTER DATABASE;
RMAN> EXIT;

This associates the target database with the recovery catalog and enables RMAN to store backup metadata in it.

---

🔒 Step 4: Creating a Virtual Private Catalog (Optional)

A Virtual Private Catalog (VPC) allows separate users (such as backup operators) to manage RMAN metadata for specific databases — ideal for multi-database or shared environments.

Create the VPC user and grant privileges:

CREATE USER vpc1 IDENTIFIED BY password
  DEFAULT TABLESPACE vpcusers;

GRANT CREATE SESSION TO vpc1;

Now connect to RMAN as the catalog owner and grant VPC privileges for a specific database:

RMAN> 
CONNECT CATALOG reco_user@catdb
RMAN> 
GRANT CATALOG FOR DATABASE prd1 TO vpc1;

When the GRANT command is issued, Oracle automatically creates the virtual private catalog for user vpc1.

---

💾 Step 5: Using the Virtual Private Catalog

The backup operator (vpc1) can now connect and register the database for use:

RMAN> 
CONNECT CATALOG vpc1@catdb
RMAN> 
REGISTER DATABASE prod1;

✅ The virtual catalog is now ready. The user vpc1 has access to metadata only for the granted database (prod1), ensuring isolation and security.

---

🧾 Summary

Task	Command / Description
Create Recovery Catalog User	CREATE USER reco_user ...
Grant Catalog Owner Role	GRANT recovery_catalog_owner TO reco_user;
Create Recovery Catalog	RMAN> CREATE CATALOG;
Register Database	RMAN> REGISTER DATABASE;
Create VPC User	CREATE USER vpc1 IDENTIFIED BY password;
Grant Catalog Access	GRANT CATALOG FOR DATABASE prd1 TO vpc1;

---

💡 Key Benefits

• Centralized RMAN metadata repository
• Simplified backup and restore management
• Easier monitoring for multiple databases
• Secure isolation with Virtual Private Catalogs