rman catalog in Oracle Database

Let’s start with Catalog Database, connect to your desired catalog database as sysdba. Create Tablespace, user and grant appropriate privelleges as shown.



SQL> create tablespace cat_tablespace datafile 'G:\oracle\product\10.2.0\oradata\catdb\cat_datafile.dbf' size 200M autoextend on;

Tablespace created.

SQL> create user rcat identified by rcat default tablespace cat_tablespace temporary tablespace temp quota unlimited on cat_tablespace;

User created.

SQL> grant connect, create session, resource, recovery_catalog_owner to rcat;

Grant succeeded.

SQL> conn rcat/rcat@catdb;
Connected.

When we use rman without catalog Database, it uses target Database's Control File as rman repository. But if we use rman with catalog Database, it uses catalog database along with target database control file as rman repository. Those following step by step command shows how to register a target Database in catalog.



C:\Documents and Settings\A Rahim Khan>rman target sys/sys@orcl1 catalog rcat/rcat@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 3 03:06:23 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL1 (DBID=1163774759)
connected to recovery catalog database

RMAN>

RMAN> create catalog tablespace cat_tablespace;

recovery catalog created

RMAN> register database;

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

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'G:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFORCL1.ORA'; # default

However, we can use rman without catalog database, so why should we go for catalog. Target Database Control File keep records for 7 days by default, it should not be increased as it will degrade total Database performance.

Worst of all, if we lose target Database Control File, we are losing rman repository as well. Here, I am going to demonstrate a scenario where we lost Database Control File recovers it using rman backup with catalog Database.


SQL> show parameter control_file_record_keep_time;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\A Rahim Khan>echo %oracle_sid%
orcl1

C:\Documents and Settings\A Rahim Khan>sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 3 03:22:53 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              88081088 bytes
Database Buffers          192937984 bytes
Redo Buffers                7139328 bytes

SQL> alter system register;

System altered.


Now, it's possible to recover Control File from rman backup using catalog Database repository. You have to reset log after restoring control file.



C:\Documents and Settings\A Rahim Khan>rman target / catalog rcat/rcat@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 3 03:51:21 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: orcl1 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile from autobackup;

Starting restore at 03-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

recovery area destination: G:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2012_05_03\O1_MF_S_782277294_7T4PNZYR_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\CONTROL01.CTL
output filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\CONTROL02.CTL
output filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\CONTROL03.CTL
Finished restore at 03-MAY-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


RMAN> recover database;

Starting recover at 03-MAY-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 23 is already on disk as file G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02A.LOG
archive log filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02A.LOG thread=1 sequence=23
media recovery complete, elapsed time: 00:00:04
Finished recover at 03-MAY-12

Thanks
A Rahim Khan

About these ads
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 29 other followers

%d bloggers like this: