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
No trackbacks yet.