Why Virtual Private Catalog?

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… Continue Reading →

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:
SQL> create user VPC_USER1 identified by password
default tablespace VPC_USERS TABLESPACE vpc_users
temporary tablespace TEMP;

SQL> create user VPC_USER2 identified by password
default tablespace VPC_USERS TABLESPACE vpc_users
temporary tablespace TEMP;

SQL> grant RECOVERY_CATALOG_OWNER to VPC_USER1;
SQL> grant RECOVERY_CATALOG_OWNER to VPC_USER2;
  1. Connect to the RMAN catalog as catalog owner and grant privileges to the virtual private catalog owner:
SQL> rman CATALOG rman/password@rmancat
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jul 1 10:56:59 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to recovery catalog database
RMAN>
RMAN> grant catalog for database DB1 to VPC_USER1;
RMAN> grant catalog for database DB2 to VPC_USER1;
RMAN> grant catalog for database DB3 to VPC_USER2;
RMAN> grant catalog for database DB4 to VPC_USER2;

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.

  1. Create the virtual private catalog:

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

SQL> rman catalog VPC_USER1/password@RMANCAT
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 1 12:18:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database

RMAN> create VIRTUAL CATALOG;
found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

SQL> rman catalog VPC_USER2/password@RMANCAT
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 1 12:18:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database

RMAN> create VIRTUAL CATALOG;
found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN
  1. Connect to catalog owners and query the registered databases:
  • Connect to catalog owner RMAN and list all registered databases:
RMAN> list db_unique_name all;
List of Databases
DB Key  DB Name DB ID             Database Role   Db_unique_name
------- ------- ----------------- --------------- ------------------
1       DB1     1790162170        PRIMARY         DB1
3419    DB2     3510904891        PRIMARY         DB2
6531    DB3     3510904891        PRIMARY         DB3
89231   DB4     3510904891        PRIMARY         DB4
  • Connect to catalog owner VPC_USER1 and list all registered databases
RMAN> list db_unique_name all;
List of Databases
DB Key  DB Name DB ID             Database Role   Db_unique_name
------- ------- ----------------- --------------- ------------------
1       DB1     1790162170        PRIMARY         DB1
3419    DB2     3510904891        PRIMARY         DB2
  • Connect to catalog owner VPC_USER2 and list all registered databases:
RMAN> list db_unique_name all;
List of Databases
DB Key  DB Name DB ID Database    Role            Db_unique_name
------- ------- ----------------- --------------- ------------------
6531    DB3     3510904891        PRIMARY         DB3
89231   DB4     3510904891        PRIMARY         DB4
  1. Granting/revoking privileges from the virtual private catalog owner:
  • Grant the access to register new target database to virtual private catalog owner:
RMAN> grant register database to VPC_USER1;
RMAN> grant register database to VPC_USER2;
  • Revoke the access to register new target database from virtual private catalog owner
RMAN> revoke register database from VPC_USER1;
RMAN> revoke register database from VPC_USER2;
  • Revoke the access to metadata for DB2 ,DB4 databases from virtual private catalog owners.
RMAN> revoke catalog for database DB2 from VPC_USER1;
RMAN> revoke catalog for database DB4 from VPC_USER2;
  1. Drop the virtual private catalog:
  • Connect to the RMAN catalog as virtual private catalog owner and drop the virtual private catalog:
SQL> rman catalog VPC_USER1/password@RMANCAT
RMAN>
RMAN> drop catalog;
recovery catalog owner is VPC_USER1
enter DROP CATALOG command again to confirm catalog removal
RMAN> drop catalog;
recovery catalog dropped
RMAN>

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:
SQL> execute RMAN.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
  • Drop virtual private catalog:
SQL> execute RMAN.DBMS_RCVCAT.DROP_VIRTUAL_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.

SQL> rman target sys/password@DB1 catalog VPC_USER1/password@RMANCAT
RMAN> create GLOBAL script GLOBAL_BACKUP (backup database plus archivelog;);

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

SQL> rman connect target sys/password@DB2 catalog VPC_USER1/password@RMANCAT
RMAN>RUN {execute script GLOBAL_BACKUP; }

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.

CREATE SCRIPT NON_GLOBACL_backup
{
	BACKUP FORMAT "/rman-backup/DB1/%d_%t_%s_%p.rmn"
		DATABASE PLUS ARCHIVELOG;
}
RUN {EXECUTE SCRIPT NON_GLOBACL_backup;}

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

RMAN> print script GLOBAL_BACKUP;

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.