Virtual Private Catalogues in 11g for RMAN Oracle 11g New Features Tips by Burleson Consulting July 8, 2008

Oracle 11g SQL New Features Tips

If the virtual catalog owner tries to create a virtual catalog and does not have the recovery_catalog_owner role the following error is thrown by rman:

RMAN> create virtual catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user LUTZ

 [oracle@rac11b-pub ~]$ oerr rman 6426

6426, 1, "RECOVERY_CATALOG_OWNER role must be granted to user %s"

// *Cause:  The CREATE CATALOG or UPGRADE CATALOG command was used, but the
//          USERID that was supplied in the CATALOG connect string does not
//          have the RECOVERY_CATALOG_OWNER role granted as a DEFAULT role.
// *Action: Grant the RECOVERY_CATALOG_OWNER role to the recovery catalog
//          owner.

It is necessary as the next step that the master of the base catalog makes sure that nobody but him/herself can gain access to the full catalog by creating the catalog and grant access to parts of the catalog to the virtual catalog owners.

[oracle@rac11b-pub ~]$ rman target sys/oracle1@prod11g2 catalog rcat_master/oracle1@rcat11g

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 01:33:44 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved. 

connected to target database: PROD11G1 (DBID=1350648997)
connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

Then all other target databases are registered in the catalog:

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

[oracle@rac11b-pub ~]$ rman target sys/oracle1@prod11g2 catalog rcat_master/oracle1@rcat11g

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 01:35:26 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PROD11G2 (DBID=1566175465)
connected to recovery catalog database

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

[oracle@rac11b-pub ~]$ rman target sys/oracle1@rac11g1 catalog rcat_master/oracle1@rcat11g

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 01:40:20 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RAC11G1 (DBID=411270766)
connected to recovery catalog database

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

RMAN> GRANT CATALOG FOR DATABASE prod11g1 TO prod_vpc;
Grant succeeded.
RMAN> GRANT CATALOG FOR DATABASE prod11g2 TO prod_vpc;
Grant succeeded.

Now the virtual catalogs can be created by the virtual catalog owners:

[oracle@rac11b-pub ~]$ rman catalog prod_vpc/oracle1@rcat11g
connected to recovery catalog database

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

The previous rman command only works with a catalog database of version 11g and an rman client with version 11g.

Here is the syntax to cerate a virtual catalog for pre 11g rman clients:

SQL> CONNECT prod_vpc/oracle1@rcat11g
SQL> exec rcat_master.dbms_rcvcat.create_virtual_catalog;

Let’s see what we can access in the catalog:

RMAN> LIST INCARNATION;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       2       PROD11G1 1350648997       CURRENT 1          26-JAN-08
31      32      PROD11G2 1566175465       CURRENT 1          26-JAN-08

A user will not be allowed to create a virtual catalog a long as he/she has not been granted access to parts of the base.

[oracle@rac11b-pub ~]$ rman catalog rac_vpc/oracle1@rcat11g

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 02:34:14 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to recovery catalog database
RMAN> CREATE VIRTUAL CATALOG;
found ineligible base catalog owned by RCAT_MASTER
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06801: no base catalog found

After a grant it works:

[oracle@rac11b-pub ~]$ rman catalog rcat_master/oracle1@rcat11g
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 02:34:14 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to recovery catalog database
RMAN> GRANT CATALOG FOR DATABASE rac11g1 TO rac_vpc;
Grant succeeded.

[oracle@rac11b-pub ~]$ rman catalog rac_vpc/oracle1@rcat11g
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 27 02:34:53 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to recovery catalog database
RMAN> CREATE VIRTUAL CATALOG;
found eligible base catalog owned by RCAT_MASTER
created virtual catalog against base catalog owned by RCAT_MASTER
RMAN> LIST INCARNATION;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
61      62      RAC11G1  411270766        CURRENT 1          26-JAN-08

The base catalog contains the entire metadata for all registered targets:

RCAT_MASTER  @ rcat11g SQL>  SELECT COUNT(*), table_type
  2   FROM cat
  3   GROUP BY table_type;

  COUNT(*) TABLE_TYPE
---------- -----------
         1 SEQUENCE
        44 TABLE
        98 VIEW

Let’s have a look at the information about the virtual catalog owners with the table vpc_users...

RCAT_MASTER  @ rcat11g SQL> SELECT * FROM vpc_users;

FILTER_USER                    A VERSION
------------------------------ - ------------
PROD_VPC                       Y 11.01.00.06
RAC_VPC                        N 11.01.00.06

… and the registered target databases via the table vpc_databases

RCAT_MASTER  @ rcat11g SQL>  SELECT * FROM vpc_databases;

FILTER_USER                         DB_ID
------------------------------ ----------
PROD_VPC                       1350648997
PROD_VPC                       1566175465
RAC_VPC                         411270766

These two tables can only be directly read by the base catalog owner.

In the virtual catalog schema vpc_databases is a synonym.

The virtual catalog consists of a number of views and synonyms:

RAC_VPC  @ rcat11g SQL>SELECT  COUNT(*)table_type 

  COUNT(*) TABLE_TYPE
---------- -----------
        46 SYNONYM
        55 VIEW

Other security aspects about backup in 11g

 There are two default archive destinations in an Oracle 11g database.

Log_archive_dest_10 is the set to use_db_recovery_file_dest by default if a flash recovery area exists, as it was already in 10g.

But also log_archive_dest_1 is set by default, which you might not really want to be so!

Here is an example from database control:

In this chapter we have seen that Oracle has put a lot of effort into providing a more secure database environment. Many security risks have been addressed in 11g. This chapter does not make any claim to be complete about the new security features. The problem is that they are not documented in one place and I think that we will find out more about the one or the other little feature which is not mentioned here as time goes by.

Many of these features are only available with the Enterprise Edition in combination with extra cost options like the Advanced Security Option and the Advanced Compression Option.

Some of the new security features can have a big impact on applications when upgrading and enabling them. It is up to the Remote DBA to test thoroughly in front before implementation in production.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr. You can buy it direct from the publisher for 30% off.