Virtual Private Catalog

Virtual Private Rman Catalog

You are most likely using a catalog database for the RMAN repository. If you are not, you should seriously consider using one. There are several advantages, such as reporting, simpler recovery in case the controlfile is damaged, and so on.

Now comes the next question: How many catalogs? Generally, it makes sense to have only one catalog database as the repository for all databases. However, that might not be a good approach for security. A catalog owner will be able to see all the repositories of all databases. Since each database to be backed up may have a separate DBA, making the catalog visible may not be acceptable.

So, what’s the alternative? Of course, you could create a separate catalog database for each target database, which is probably impractical due to cost considerations. The other option is to create only one database for catalog yet create a virtual catalog for each target database. Virtual catalogs are new in Oracle Database 11g. Let’s see how to create them.

First, you need to create a base catalog that contains all the target databases. The owner is, say, “RMAN”. From the target database, connect to the catalog database as the base user and create the catalog.

$ rman target=/ rcvcat rman/rman@catdb
Recovery Manager: Release 11.1.0.6.0 – Production on Sun Sep 9 21:04:14 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ODEL11 (DBID=2836429497) connected to recovery catalog database

RMAN> create catalog;
recovery catalog created

RMAN> register database;
database registered in recovery catalog starting full resync of recovery catalog full resync complete

This is called the base catalog, owned by the user named “RMAN”. Now, let’s create two additional users who will own the respective virtual catalogs. For simplicity, let’s gives these users the same name as the target database. While still connected as the base catalog owner (RMAN), issue these statements:

RMAN> grant catalog for database odel11 to odel11;
Grant succeeded.

Now connect using the virtual catalog owner (odel11), and issue the statement

create virtual catalog:

$ rman target=/ rcvcat odel11/odel11@catdb

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN
Now, register a different database (PRONE3) to the same RMAN repository and create a virtual catalog owner “prone3″ for its namesake database.

RMAN> grant catalog for database prone3 to prone3;

Grant succeeded.

$ rman target=/ rcvcat prone3/prone3@catdb

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN
Now, connecting as the base catalog owner (RMAN), if you want to see the databases registered, you will see:

$ rman target=/ rcvcat=rman/rman@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
——- ————– ————— ——————- ———————
285 PRONE3 1596130080 PRIMARY PRONE3
1 ODEL11 2836429497 PRIMARY ODEL11

As expected, it showed both the registered databases. Now, connect as ODEL11 and issue the same command:

$ rman target=/ rcvcat odel11/odel11@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
———- ————- —————- ——————- ———————
1 ODEL11 2836429497 PRIMARY DEL11

Note how only one database was listed, not both. This user (odel11) is allowed to see only one database (ODEL11), and that’s what it sees. You can confirm this by connecting to the catalog as the other owner, PRONE3:

$ rman target=/ rcvcat prone3/prone3@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
———- ————- ————— —————— ———————-
285 PRONE3 1596130080 PRIMARY PRONE3

Virtual catalogs allow you to maintain only one database for the RMAN repository catalog yet establish secure boundaries for individual database owners to manage their own virtual repositories. A common catalog database makes administration simpler, reduces costs, and enables the database to be highly available, again, at less cost.