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....
