11g R2 – Creating a Recovery Catalog

Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Creating a Recovery Catalog                              

The first thing to do is to configure the Recovery Catalog.  RMAN requires that you maintain a Recovery Catalog Schema.  The SYS user cannot be the owner of the recovery catalog.  The Recovery Catalog is stored in the default tablespace of the schema.

The first thing to decide is which database to use to create the Recovery Catalog in.  You also need to decide what the backup strategy for this database is going to be, because you need to protect your recovery catalog.  Oracle recommends that you run the Recovery Catalog database in ARCHIVELOG mode.  The Recovery Catalog should be stored on a different database, to the Target database.

Sizing the Recovery Catalog Schema

You must allocate space to be used by the Recovery Catalog Schema’s tablespace.  If your target database has around 100 datafiles, and fifty backup sets with 1 backup piece, every time you back up.  Then the space used would be less than 200 KB per backup.  SO if you did around 350 backups per year, you would need around 70MB per year.  With a small database you ld even get away with around 15MB for a year’s worth of data.  Then if you included the archived log files, you could easily double this to 140MB per year of space that you need.  So if you registered five databases in this recovery catalog, then you would need five times that space or around 700MB per year.  So if you allowed 1GB per year, it would be more than enough.  10GB would be enough for  around 10 years.

If you are creating your Recovery Catalog in an existing database, then make sure that allocate sufficient space to the default tablespace of the recovery catalog schema.  If you are creating a database to hold your recovery catalog, then you should first calculate the space required for the database, and add the space for the recovery catalog to that.  Otherwise you can calculate the exact space required for such a database for one year:

Type of Space	Space Needed
SYSTEM tablespace	100MB
SYSAUX tablespace	100MB
TEMP tablespace	5MB
UNDO tablespace	5MB
Online Redo Logs	150MB
Recovery Catalog Tablespace	140MB

  

Total space needed is: 500MB.  This is an absolute minimal size, and it would probably be better to allow quite a bit more space for the database.  The above has not taken into account the Archived Redo Log Files, space requirement.  Also you will need space for the Oracle Database Software etc.

If your target database and recovery catalog database are on the same server, then make sure that they are at least not on the same disk or file system.

Creating the Recovery Catalog Schema Owner

Assume the following information is true for this Recaovery Catalog database:

·         User SYS has SYSDBA privileges on this database “catdb”

·         There is an available tablespace called “tools”, that you can use for the default tablespace

·         The default temporary tablespace is “temp”

1.       Start SQL*Plus and connect to the catdb database as SYSDBA

2.       SQL> create user rman identified by <password>

Temporary tablespace temp

Default tablespace tools

Quota unlimited on tools;

3         SQL> grant RECOVERY_CATALOG_OWNER to rman;

After you have created the Catalog user, you must create the catalog tables with the RMAN CREATE CATALOG command:

·         Start RMAN, and connect to the database that will contain the recovery catalog, as the Recovery Catalog user.

Rman catalog rman/<password>@<dbname>

RMAN>

·         RMAN> create catalog;

This will take a few minutes to run through, so you can leave it to run.  You can also specify the tablespace to create the catalog in:

RMAN> create catalog tablespace <tablespace_name>;

If the tablespace name is a RMAN reserved work, then you must list it in upper case, inclosed in single quotes, for example:

RMAN> create catalog tablespace ‘CATALOG’;

·         Check the results:

SQL> connect rman/<password>@<dbname>

SQL> select table_name from user_tables;

You may have more or less 44 tables created in the RMAN schema.

Mark Tiger,

Need a Database Health Check, Remote Monitoring, Support, Maintenance, or a Security Audit?

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.