Step by Step - Creating RMAN Virtual Private Catalog - Oracle Database 11g New Feature

About Virtual Private Catalogs

By default, all of the users of an RMAN recovery catalog have full privileges to insert, update, and delete any metadata in the catalog. For example, if the administrators of two unrelated databases share the same recovery catalog, each administrator could, whether inadvertently or maliciously, destroy catalog data for the other's database. In many enterprises, this situation is tolerated because the same people manage many different databases and also manage the recovery catalog. But in other enterprises where clear separation of duty exists between administrators of various databases, as well as between the DBA and the administrator of the recovery catalog, you may desire to restrict each database administrator to modify only backup metadata belonging to those databases that they are responsible for, while still keeping the benefits of a single, centrally-managed, RMAN recovery catalog. This goal can be achieved by implementing virtual private catalogs.  - source - docs.oracle.com

What is Virtual Private Catalog?

A subset of the metadata in a base recovery catalog to which a database user is granted access. The owner of a base recovery catalog can grant or revoke restricted access to the recovery catalog to other database users. Each restricted user has full read/write access to his own virtual private catalog.

Follow these steps to create virtual private catalog on RMAN.

1. Create a Recovery Catalog at RCDB database. [DB Server 3]

1.  SQL> create tablespace vpc_ts datafile 'C:\vpc_ts01.dbf' size 100m ;  
   

2.  SQL> create user vpc_owner identified by vpc default tablespace vpc_ts quota unlimited on vpc_ts;
   

3.  SQL> grant recovery_catalog_owner to vpc_owner;
   

4.  C:\rman catalog vpc_owner/vpc

    RMAN> create catalog;

    RMAN> exit;

2. Now we need to create two more database user on [DB Server 3] which will act as virtual user for [DB Server 2] and [DB Server 1]

1.  SQL> create user vpc_orcl identified by orcl default tablespace vpc_ts quota unlimited on vpc_ts;

    SQL> create user vpc_test identified by test default tablespace vpc_ts quota unlimited on vpc_ts;

2.  SQL> grant recovery_catalog_owner to vpc_test,vpc_orcl;
   

3. Now register both test and orcl to the recovery catalog using user vpc_owner.[DB Server 3] Make sure you have net service name configure on [DB Server 3] to connect to [DB Server 2] and [DB Server 1]

1.  C:\ rman catalog vpc_owner/vpc target sys/admin@orcl

    RMAN> register database; 

    RMAN> exit;

2.  C:\ rman catalog vpc_owner/vpc target sys/admin@test

    RMAN> register database;

    RMAN> exit; 

4. Now we need to grant privileges to vpc_orcl and vpc_test to accesss RMAN metadata stored on RCDB from vpc_owner
   [DB Server 3]

1.  C:\ rman catalog vpc_owner/vpc
   

2.  RMAN> grant catalog for database orcl to vpc_orcl;

    RMAN> exit; 

3.  C:\ rman catalog vpc_owner/vpc

4.  RMAN> grant catalog for database test to vpc_test;

    RMAN> exit; 

5. Now we need to connect our target database with the newly created users and create virtual catalog so that they can only access RMAN metadata stored on RCDB for there respective databases only. E.g vpc_orcl cannot view matadata of test database and vice-versa.[DB Server 3]

1.  C:\ rman catalog vpc_orcl/orcl@orcl <--- orcl is net service name.

2.  RMAN> create virual catalog;

    RMAN> exit; 

3.  C:\ rman catalog vpc_test/test@test <--- test is net service name.

4.  RMAN> create virual catalog;

    RMAN> exit; 

6. Now you can connect to respective virtual catalog normal user for your respective databases to query recovery catalog views. E.g vpc_test can only see RMAN meta database generated from his own test database and he will not be able to see any metadata generated by other databases registered on the catalog server i.e [DB Server 3].

1.  C:\sqlplus vpc_test/test@test <--- test is net service name.

    SQL> select * from tab;

    SQL> select * from rc_database;

2.  C:\sqlplus vpc_orcl/orcl@orcl <--- orcl is net service name.

    SQL> select * from tab;

    SQL> select * from rc_database;

7. Now if you connect to recovery from vpc_owner you will be to see all the RMAN metadata generated from both orcl and test databases.

1.  C:\sqlplus vpc_owner/vpc

    SQL> select * from tab;

    SQL> select * from rc_database;

8. You can also grant the user the ability to register new target databases in the recovery catalog. E.g :- Suppose on [DB Server 1] you have one database called orcl1 and you want to register it with your virtual private catalog then you need one more privielge granted to you.

1.  C:\rman catalog vpc_owner/vpc

    RMAN> grant register database to vpc_orcl;

    RMAN> exit;

2.  C:\ rman target sys/admin@orcl1 catalog vpc_orcl/orcl@orcl <--- orcl is net service name.

    RMAN> register database;