Configuring the RMAN Catalog Server

Concept behind RMAN Catalog is a centralized repository to maintain and keep all the Database Backup information taken by RMAN. By default whenever any RMAN Backup runs for any particular database then its relevant information will get stored into Control file but control file keeps this information for last 7 days only (default which can be changed). So in order to keep the backup information for long time for multiple databases, oracle has introduced a centralized repository called as Catalog recovery. Catalog recovery is simply a schema into a separate database which stores all the backup information for every database those are registered into that.

Lets see how to create the recovery catalog and register a database to keep the backup information into it.

Server Details:

----------------

Catalog Database : DCCAT

Target Database  : DCPROC

On catalog Server :

1. Create a tablespace which is going to hold all the catalog information into its datafile.

SQL> create tablespace DCCAT datafile ‘/oracle/dbawork/DCCAT/DCCAT01.dbf’

2 Size 30M autoextend on next 30M

3 extent management local

4 segment space management auto uniform size 64K;

Tablespace created.

2. Create a catalog user and assign the default tablespace which is created above.

SQL> create user DCCAT    

2 identified by DCCAT

3 default tablespace DCCAT

4 quota unlimited on DCCAT;

User created.

3. Provide necessary grants to catalog User which inherits all the views to manage the Recovery catalog.

SQL> grant connect, resource, recovery_catalog_owner to DCCAT;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'RECOVERY_CATALOG_OWNER';

PRIVILEGE

----------------------------------------

CREATE SYNONYM

CREATE CLUSTER

ALTER SESSION

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE SESSION

CREATE TYPE

CREATE VIEW

CREATE TRIGGER

11 rows selected.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

4. Create Catalog:

On target Server Server:

------------------------

[oracle@olx785 ~]$ export ORACLE_SID=DCPROC

[oracle@olx785 ~]$ rman catalog DCCAT/DCCAT@DCCAT

Or

RMAN> connect catalog DCCAT/DCCAT@DCCAT;

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Nov 14 20:18:51 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

5. Registered database with RMAN

RMAN> connect target /

Or

[oracle@olx785 ~]$ rman target sys/oracle@DCPROC catalog DCCAT/DCCAT@DCCAT

connected to target database: DCPROC (DBID=1012901700)

/*if it shows RMAN-06004 Error: target database not connected then proceed to fire next step i.e. “RMAN> register database;”*/

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

6. Check Registered Database:

SYS> select DBID, NAME from DCCAT.RC_DATABASE order by 2;

7. Backup Status:

SYS> select DB_ID,TO_CHAR (start_time, ‘dd-mon-yyyy hh24:mi:ss’) start_time ,TO_CHAR (COMPLETION_TIME, ‘dd-mon-yyyy hh24:mi:ss’) COMPLETION_TIME,STATUS from DCCAT.RC_BACKUP_SET;