Thursday 14 July 2011

Control file backup and recovery

Control File backup and Recovery:
Control file is repository of Oracle Database. Every information about Database structure is stored here. This can be called as Metadata of Database. Control file stores SCN information (Checkpoint SCN for consistent opening of DB), Data file information, REDO information, RMAN setting information (if configured),Archive log mode ,DBID,DBNAME, Temporary Table space information. By now you would have understood that Control file is actually everything that makes Database working and without control file there will not be any database.
All of the above information makes it very crucial to have your Control file intact. And prepare flawless strategy of Backup and Recovery of Control file.  Given proper consideration and well thought planning about safety of Control file makes life of DBA  easy. Following are scenarios of Control file Backup and recovery using RMAN utility:
Environment:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 - Production
"CORE   10.2.0.1.0             Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production

In scenario 1, I have used RMAN with Recovery Catalog database connectivity.

C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:36:57 2011

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

connected to target database: SEED (DBID=837440993)
connected to recovery catalog database

RMAN> Show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'D:\bkup\seed\SEED_BKUP_%U.BKP';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'D:\bkup\seed\SEED_BKUP_%U.BKP';
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 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSEED.ORA'; # default

These two Control file backup specific configuration(Configuration setting Yellow Colored) makes it sure that whenever Database is Backed up then current control file goes along with Backup.
Secondly , Whenever we make change in Database , eg. Tablespace added, Tablespace Dropped (2 changes in Database), Then two backup of control file goes automatically to 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';  location.

Now I shutdown the database and remove the Control files to demonstrate Recovery of Control file.
After removing Control files(s) on OS level when I try to open the Database, I get following error:
SQL> Startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             230689668 bytes
Database Buffers          373293056 bytes
Redo Buffers                7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

Our database now is in NOMOUNT stage.
Now to recover it I connect to RMAN
RMAN> exit

Recovery Manager complete.

Scenario 1 :-
Control file is lost but we have Recovery Catalog Database connectivity and we have all the backup piece(s) in tact and valid. Backup piece(s) are at their default location (mentioned in RMAN configuration).
C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:56:43 2011

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

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

We  list out Backup Piece of Control file :

RMAN> List backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Full    1.03M      DISK        00:00:01     11-JUL-11
        BP Key: 54   Status: AVAILABLE  Compressed: YES  Tag: TAG20110711T110624
        Piece Name: D:\BKUP\SEED\SEED_BKUP_01MH5P1G_1_1.BKP
  Control File Included: Ckp SCN: 662627       Ckp time: 11-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47      Full    6.80M      DISK        00:00:01     11-JUL-11
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20110711T110627
        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110711-00
  Control File Included: Ckp SCN: 662633       Ckp time: 11-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    6.80M      DISK        00:00:01     12-JUL-11
        BP Key: 61   Status: AVAILABLE  Compressed: NO  Tag: TAG20110712T162012
        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110712-00
  Control File Included: Ckp SCN: 759685       Ckp time: 12-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
336     Full    6.80M      DISK        00:00:03     13-JUL-11
        BP Key: 337   Status: AVAILABLE  Compressed: NO  Tag: TAG20110713T175948
        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00
  Control File Included: Ckp SCN: 806881       Ckp time: 13-JUL-11

Now! We have seen Backup piece(s) containing  Control file. All we need to do is Restore the Control File. As database is connected to Recovery Catalog, So not much problem is there. Comparatively easy. All we need to do is Restore control from Auto backup , Mount the Database and Recover database and in end Open Database in RESETLOGS mode.

RMAN> Restore Controlfile;

Starting restore at 13-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00 tag=TAG20110713T175948
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL
Finished restore at 13-JUL-11

RMAN> Alter Database Mount;

database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2

RMAN> Recover Database;

Starting recover at 13-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG
archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG thread=1 sequence=1
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=2
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JUL-11

RMAN> Alter Database Open Resetlogs;

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

Now! Control file is recovered and Database is opened . Restart the database and take backup.  Recovering Control file  using Recovery Catalog Database is pretty simple and straight forward. We have all the information required for Restoration of File.


Scenario 2:

Control file as well as Recovery Catalog Database is lost. Backup piece(s) are at their default location and available.

To demonstrate I delete all the Control File from OS  using OS utility. Now when I try to open the Database , I get following error (Same like scenario 1):

SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             247466884 bytes
Database Buffers          356515840 bytes
Redo Buffers                7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

Now problem is, When I connect to RMAN using Catalog Database, Due to unavailability of Database, I get following error:
C:\>rman target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:13:18 2011

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

connected to target database: seed (not mounted)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-12518: TNS:listener could not hand off client connection.

Now! I start RMAN without  Recovery Catalog Database.
C:\>rman target sys/seed@seed_54 nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:47:33 2011

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

connected to target database: seed (not mounted)
using target database control file instead of recovery catalog

Unlike scenario 1, When I try to see backup piece(s) containing Control File(s), I get following message.

RMAN> list backup of controlfile;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/14/2011 18:31:48
ORA-01507: database not mounted

It is understandable; Database is not in MOUNT stage so we cannot see the Backup information from Control file. As we are unable to see Backup information,  cannot proceed with restoration of Control File. If we use any of “RESTORE CONTROLFILE” Command, than statement fails.
RMAN> restore controlfile;

Starting restore at 14-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/14/2011 18:39:15
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> restore controlfile from autobackup;

Starting restore at 14-JUL-11
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: looking for autobackup on day: 20110708
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/14/2011 18:39:28
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Even if, We SET proper FORMAT of Control File auto backup using following RUN Block, We fail.

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';
4> RESTORE CONTROLFILE;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2011 10:36:18
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';
4> RESTORE CONTROLFILE FROM AUTOBACKUP;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11
using channel ORA_DISK_1

autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2011 10:36:32
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

There is no problem within RUN block, It is just that Oracle is not aware of Backup Piece location  of the Database. Here we need to provide the DBID of the Database. In case we have lost Control file and Recovery Catalog Database connectivity, we must know the DBID of Database. Because using DBID of the Database, Oracle comes to know the where about of Database Backup Piece(s) and once it knows the location of backup piece(s), it uses them for restoration and recovery.

RMAN> set dbid=837440993

executing command: SET DBID

Now after providing , DBID procedure of Control file recovery is simple. But unlike scenario 1, We cannot simply use “RECOVER CONTROLFILE” or “RECOVER CONTROLFILE FROM AUTOBACKUP” . Both commands produce error.
RMAN> Restore Controlfile;

Starting restore at 15-JUL-11
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2011 10:45:20
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> Restore Controlfile from Autobackup;

Starting restore at 15-JUL-11
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: looking for autobackup on day: 20110713
channel ORA_DISK_1: looking for autobackup on day: 20110712
channel ORA_DISK_1: looking for autobackup on day: 20110711
channel ORA_DISK_1: looking for autobackup on day: 20110710
channel ORA_DISK_1: looking for autobackup on day: 20110709
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/15/2011 10:45:32
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

To Restore control file in such situation, We need to provide FORMAT of Control File AUTOBACKUP.

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';
4> RESTORE CONTROLFILE FROM AUTOBACKUP;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715
channel ORA_DISK_1: looking for autobackup on day: 20110714
channel ORA_DISK_1: autobackup found: D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_c-837440993-20110714-02
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL
Finished restore at 15-JUL-11

As we can see that, using RUN block our restoration of Control file is successful.

RMAN> Alter Database Mount;

database mounted
released channel: ORA_DISK_1

RMAN> Recover database;

Starting recover at 15-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK

starting media recovery

archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG thread=1 sequence=3
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=4
media recovery complete, elapsed time: 00:00:05
Finished recover at 15-JUL-11

RMAN> Alter database open Resetlogs;

database opened

It is pretty simple and straight. All you need to provide DBID and location of Control File backup piece. 
The procedure of recovering Control file from its default AUTOBACK FORMAT location or any other location where user manually has transferred is simple. All we need to do is  SET CONTOLFILE AUTOBACKUP FORMAT             to user defined location. Eg. In scenario 2 I used following RUN block

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';  ß Default Location
4> RESTORE CONTROLFILE FROM AUTOBACKUP;
5> }

In case, User has transferred backup piece(s) to any other location lets say D:\bkup  and Then all we need to do is change the RUN block eg.

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup \SEED_CTL_FILE_%F';  ß changed Location
4> RESTORE CONTROLFILE FROM AUTOBACKUP;
5> }



Any comment/Query/Correction is most welcome....