Securefile Lobs in 11g at Work with OEM Oracle 11g New Features Tips by Burleson Consulting July 8, 2008

Oracle 11g SQL New Features Tips

Oracle Enterprise Manager fully supports the creation of securefile lob columns.

With the Encryption Options button you reach the page where you can specify which encryption algorithm to use for the column:

The Set Default LOB Attributes button leads you to the place where you can specify the lob type securefile or basicfile and the tablespace to use for the log segment as well as caching options:

Virtual Private Catalogues for RMAN

The recovery catalog owner had full access to all backup information in the recovery catalog and  before Oracle’s version 11g there was no way to restrict access to limited parts of the catalog for users who have the recovery_cotalog_owner privileges.

Either a user had full access or no access to the catalog.

Oracle 11g now comes with the possibility to create virtual private catalogs which makes it easy to limit access to backup information selectively for multiple users and thus separate responsibilities by restrictive grants on clearly defined parts of the catalog.

A virtual private catalog is a subset of the base recovery catalog.

The catalog owner is the master of the base catalog and has access to all the metadata of the catalog. This user has full control over the privileges on all backups for all registered databases.

The metadata in the catalog includes the following information:

• Backup sets and backup pieces containing datafiles and archived redo logfiles

• Datafile copies

• Archived redo logs and their copies

• Structure of the target database including historical information

• Stored scripts, which can be target specific or global scripts since 10g

• Persistent RMAN configurations

The owner of the base catalog can use the new rman commands grant and revoke to limit access on subsets of the catalog to certain users of the same recovery catalog database.

These users are called virtual catalog owners. In their schemas a set of views and synonyms is stored.

In the following section of this chapter I will demonstrate what virtual private catalogs are and how to use them.

In my example I will use the following 4 databases:

-       rcat11g              recovery catalog database

-       prod11g1           single instance target

-       prod11g2           single instance target

-       rac11g               cluster database target

As a first step let’s create the recovery catalog master and two virtual catalog owners:

LUTZ AS SYSRemote DBA @rcat11g SQL> CREATE USER rcat_master IDENTIFIED BY ORACLE1
  2  DEFAULT TABLESPACE rman_ts
  3  QUOTA UNLIMITED ON rman_ts;

User created.

LUTZ AS SYSRemote DBA @rcat11g SQL> CREATE USER rac_vpc IDENTIFIED BY oracle1
  2   DEFAULT TABLESPACE rman_vpcs
  3   QUOTA UNLIMITED ON   rman_vpcs;

User created.

LUTZ AS SYSRemote DBA @rcat11g SQL> CREATE USER prod_vpc IDENTIFIED BY oracle1
  2   DEFAULT TABLESPACE rman_vpcs
  3   QUOTA UNLIMITED ON   rman_vpcs;

User created.

LUTZ AS SYSRemote DBA @rcat11g SQL> GRANT recovery_catalog_owner TO rcat_master;

Grant succeeded.

LUTZ AS SYSRemote DBA @rcat11g SQL> GRANT recovery_catalog_owner TO rac_vpc;

Grant succeeded.

LUTZ AS SYSRemote DBA @rcat11g SQL> GRANT recovery_catalog_owner TO  prod_vpc;

Grant succeeded.

As you can see I have used two different tablespaces for the base catalog and the virtual catalog owners. I want to be independent for possible upgrading in the future and for export.

Also please note that all the users have been granted full access to the base catalog at the beginning.

 

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.