Recovery Manager (RMAN) is the preferred tool to backup and recover the Oracle Database. You can use recovery catalog or the control file of the target database for RMAN Repository, but Recovery Catalog is the preferred method as it offers several advantages over the others, like reporting operations, simple recovery in case of control file damage, and more.

Generally in any company there will be one separate database for Recovery Catalog to store a repository of all Application databases. The catalog owner should be able to see all application database repositories. The Recovery Catalog is managed by Database Administrator (DBA) and in the case of large database environments there will be more than one DBA managing the same repository. In versions of Oracle Database before 11g, a user cannot restrict access on RMAN Repository for security reasons or segregate the duties between DBAs. All users of an RMAN Recovery Catalog have full privileges to insert, update, and delete any metadata in the Recovery Catalog. If you need to secure the repository then you need to create separate recovery catalog for each target database.

The virtual private catalog was introduced in Oracle 11g. This new feature lets you grant restricted access on RMAN Catalog to some users so that they can access a limited set of application databases that are registered in the recovery catalog. This feature is very useful if you need to separate the duties between administrators of various databases or between DBAs and the administrator of the Recovery Catalog. For example, imagine there are around 400 application databases registered in Recovery Catalog, out of which 300 are business-critical, and handled by senior DBAs. The remaining 100 will be managed by junior DBAs. In this case you can create a virtual private catalog and limit read/write access on RMAN metadata by creating multiple recovery catalog users.

Diagram key:

RCAT – Recovery Catalog Database

DB1,DB2,DB2,DB4 – User/Application Databases

RMAN is the Catalog owner who has full access on Recovery Catalog. The virtual private catalog user1 (VPC_USER1) has access to the DB1 and DB2 catalog metadata. Virtual private catalog user2 (VPC_USER2) has access to the DB3, DB4 catalog metadata. User1 and user2 have limited access to the catalog and can manage database backups individually.

Oracle 11g recovery catalog supports virtual private catalog, but they are not used unless explicitly created and each virtual private catalog is owned by a database schema user. The Recovery Catalog owner is different to the virtual private catalog user in that they control user privileges for the Recovery Catalog for one or more databases that are registered with Recovery Catalog. There is no restriction on number of private virtual catalogs that can be created on a RMAN recovery catalog.

Steps To Create And Manage Virtual Private Catalog

1. Create the database user VPC_USER1, VPC_USER2 in the Recovery Catalog database and grant the RECOVERY_CATALOG_OWNER privilege to them:

2. Connect to the RMAN catalog as catalog owner and grant privileges to the virtual private catalog owner:

The virtual private catalog users VPC_USER1, VPC_USER2 don’t have access to the metadata as the virtual private catalog is not yet created. The user can also use DBID instead of DB_NAME.

3. Create the virtual private catalog:

Connect to the RMAN catalog as virtual private catalog owner and create a virtual private catalog.

4. Connect to catalog owners and query the registered databases:

• Connect to catalog owner RMAN and list all registered databases:

• Connect to catalog owner VPC_USER1 and list all registered databases

• Connect to catalog owner VPC_USER2 and list all registered databases:

5. Granting/revoking privileges from the virtual private catalog owner:

• Grant the access to register new target database to virtual private catalog owner:

• Revoke the access to register new target database from virtual private catalog owner

• Revoke the access to metadata for DB2 ,DB4 databases from virtual private catalog owners.

6. Drop the virtual private catalog:

• Connect to the RMAN catalog as virtual private catalog owner and drop the virtual private catalog:

If you are planning to use a 10.2 or older release of RMAN with virtual private catalog then you need to execute below procedures to create/drop virtual private catalogs:

• Create virtual private catalog:

• Drop virtual private catalog:

Where RMAN is the BASE CATALOG OWNER in the above command.

The stored scripts play an important role between virtual private catalogs. All virtual private catalog users have “read” access to all global stored scripts, and the scripts can be run across the environment or are common across the environment need to be created as global scripts. Also, each virtual private catalog user has non-global stored scripts that belong to the databases to which they have privileges. The virtual private catalog user cannot access non-global stored scripts that belong to databases where they don’t have the correct privileges.

For example, use the below script to create global backup script. You might want to connect target database DB1 and recovery catalog owner VPC_USER1.

Now the user can connect to a new target database (DB2) and run the global stored script “global_backup” to back up the database.

If the user created a non-global script by connecting to DB1 and catalog owner as VPC_USER1 then it is not accessible to the virtual private catalog owner VPC_USER2.

The user can run the PRINT SCRIPT command to display stored script:

Summary

Virtual private catalog will allow you to maintain only one recovery catalog repository by securing boundaries between administrators of various databases or between DBAs, as well as allowing you to separate their duties.