Controlfile Recovery

Controlfile is crucial for database to operate cause it stores status of physical database structure. One of the most important obligations for DBA's is multiplexing controlfiles on different storage devices to create safe backups. By default controlfile is multiplexed but often backups are located on the same disk in the same directory as original controlfile.
Beside that CONTROLFILE AUTOBACKUP is OFF as default value of RMAN parameters.

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST11 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 COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/11.0.1/dbs/snapcf_test11.f'; # default

If you enable autobackup of controlfiles, backup of controlfile will be initiated after any operation on physical structure (adding datafiles, change size, etc...)

Cause of great importance of controlfile I would suggest turning autobackup on. You can do this changing bolded RMAN parameters:

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/backup/cf%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/backup/cf%F';
new RMAN configuration parameters are successfully stored

You can chose different device - this '/oracle/backup/cf%F' is just for testing proposes.


I'll create datafile just to show autobackup in action:

SQL> create tablespace test123;

Tablespace created.

Backup of controlfile is created:

[oracle@ciciban backup]$ ls -l
total 9616
-rw-r----- 1 oracle dba 9830400 Jul 26 19:35 cfc-3345968494-20080726-00

From that filename maybe most important number is 3345968494 which is DBID unique identifier. DBID is required during disaster recovery.


TEST CASE

Suppose you have all three controlfiles located on one disk in the same directory and someone deletes that directory or disk crashes. All you have is backup made by autobackup. How to make recovery of controlfiles.

I'll just remove controlfiles on my test machine and shutdown database.

[oracle@ciciban test11]$ rm -f control*

I'am not using recovery catalog so I'll just recover my controlfiles from autobackup.

I cannot start database in MOUNT mode without controlfile so I'll start database in NOMOUNT mode.

[oracle@ciciban ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 26 20:59:01 2008

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1299624 bytes
Variable Size 180357976 bytes
Database Buffers 125829120 bytes
Redo Buffers 6373376 bytes

Now I'll restore controlfile from RMAN interface.

RMAN> SET DBID 3345968494;

executing command: SET DBID

RMAN> run
2> {
3> set controlfile autobackup format FOR DEVICE TYPE DISK TO '/oracle/backup/cf%F';
4> restore controlfile from autobackup;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 26-JUL-08
using channel ORA_DISK_1

recovery area destination: /oracle/flash_recovery_area
database name (or database unique name) used for search: TEST11
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20080726
channel ORA_DISK_1: AUTOBACKUP found: /oracle/backup/cfc-3345968494-20080726-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /oracle/backup/cfc-3345968494-20080726-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/oracle/oradata/test11/control01.ctl
output file name=/oracle/oradata/test11/control02.ctl
output file name=/oracle/oradata/test11/control03.ctl
Finished restore at 26-JUL-08

I've restored all controlfiles and now I have controlfile with information from time that autobackup was executed.

Next I have to recover database and open with resetlogs opinion.

SQL> recover database using backup controlfile until cancel
ORA-00279: change 1392697 generated at 07/26/2008 19:30:08 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_42_637967666.dbf
ORA-00280: change 1392697 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/oradata/test11/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs
2 ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/oradata/test11/system01.dbf'

Something is wrong here. Oracle needs to find proper SCN for sucessfull recovery, and usually finds it from logs automatically, but now we have to show Oracle what redo's to apply during recovery process.

SQL> set lines 150
SQL> columun member format a50
SQL> select a.group#, a.sequence#, a.members, a.status, b.member from v$log a, v$logfile b
2 where a.group#=b.group#;

GROUP# SEQUENCE# MEMBERS STATUS MEMBER
---------- ---------- ---------- ---------------- ----------------------------------------
1 40 1 INACTIVE /oracle/oradata/test11/redo01.log
3 42 1 CURRENT /oracle/oradata/test11/redo03.log
2 41 1 INACTIVE /oracle/oradata/test11/redo02.log

We should execute query above to find out what redo is necessary to apply. From query above I've found that I should apply /oracle/oradata/test11/redo03.log. This was the current redo logfile used when autobackup was executed.

SQL> recover database using backup controlfile
ORA-00279: change 1392697 generated at 07/26/2008 19:30:08 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_42_637967666.dbf
ORA-00280: change 1392697 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/oradata/test11/redo03.log
Log applied.
Media recovery complete.

Now try to open database.

SQL> alter database open resetlogs;

Database altered.

Our database is up and running without data loss.