How to restore from a loss of a subset of the current control files

Tweet

The following scenario simulates a loss of a critical component of the database: the control file.
Among other things, it keeps track of names and locations of the datafiles and redo logs, the name of the database, its default block size, the characters set and RMAN recovery information.

Control files should always be multiplexed to different locations and until your database lose just one of the multiplexed control files the recovery process is straightforward.

As you can see in the next lines you can simply:
A) copy one of the valid multiplexed control file to the same location of the lost control file;
B) temporarily update your init.ora file removing the reference of the missing control file (you are experiencing a permanent media failure and need to open your database, but still don't have a valid disk alternative);
C) copy one of the valid multiplexed control file to a different location (you have a valid disk alternative), adding the reference of the copied control file to the init.ora file and removing the inaccessible one;

Let's begin looking at where my control files are located:

[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> set pagesize 999
SQL> col name format a70;
SQL> select status, name from V$CONTROLFILE;

STATUS  NAME
------- ---------------------------------------------------
        /home/oracle/app/oracle/oradata/orcl/control01.ctl
        /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

In my case the database has only two multiplexed control files.
One of them is located in the flash recovery area and I'm going to remove that one under the /home/oracle/app/oracle/oradata/orcl/ directory

[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl

Then I try to query the V$DATAFILE_HEADER view to display datafile information from the datafile headers: document "Oracle Database Reference 11g Release 2 (11.2)" states two columns are directly related to control file and they are:

Column  Datatype  Description
FILE#  NUMBER          Datafile number (from control file)
STATUS  VARCHAR2(7)  ONLINE | OFFLINE (from control file)
...

You could even query the V$DATAFILE view to obtain the same... "infamous" ORA-27041 error.
In this example my instance didn't crash after the loss of the control file and I was just able to run the query:

SQL> select file#, status, error, name from V$DATAFILE_HEADER;
select file#, status, error, name from V$DATAFILE_HEADER
          *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Let's have a look at the CONTROL_FILES parameter.
This initialization parameter specifies one or more names of control files (up to 8) separated by commas. The database knows its control files are located there: one control file (control01.ctl) is inaccessible, but you still have a valid copy (control02.ctl) into the flash recovery area.

SQL> show parameter control_files;

NAME             TYPE     VALUE
---------------- -------- ------------------------------
control_files    string   /home/oracle/app/oracle/oradata/orcl/control01.ctl,                          /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

To let your database be available and open again, following the option A) you can copy the good multiplexed control file (control02.ctl) to the location of the missing one (control01.ctl) while your instance is down.

[oracle@localhost orcl]$ cp /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

At this time you can open again your database, be able to use the instance and successfully execute your queries such as:

SQL> select file#, status, error, name from V$DATAFILE_HEADER;

FILE# STATUS  ERROR NAME
---------- ------- -----------------------------------------------------------
 1 ONLINE /home/oracle/app/oracle/oradata/orcl/system01.dbf
 2 ONLINE /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
 3 ONLINE /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
 4 ONLINE /home/oracle/app/oracle/oradata/orcl/users01.dbf
 5 ONLINE /home/oracle/app/oracle/oradata/orcl/example01.dbf
 6 ONLINE /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

When you are experiencing a permanent media failure you can adopt option B) described below.
Remove again one of your control file.

[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl

In my case attempting to create a new tablespace caused to know my istance is down and crashed.

SQL> create tablespace t1 datafile
'/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M;
create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf'
size 1M
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9996
Session ID: 34 Serial number: 23

No Oracle instance is running.

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   10229  4972  0 07:47 pts/4    00:00:00 grep smon

The instance doesn't start if a control file is inaccessible.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             356518584 bytes
Database Buffers           92274688 bytes
Redo Buffers                6008832 bytes
ORA-00205: error in identifying control file, check alert log for more info

G‌oing through the steps covered by a STARTUP command, after every processes successfully attach to the shared memory, the instance try to read the control file to know the structure of the database: if it doesn't happen due to some media failure the instance won't open and will remain in NOMOUNT mode.

SQL> select OPEN_MODE from V$DATABASE;
select OPEN_MODE from V$DATABASE
       *
ERROR at line 1:
ORA-01507: database not mounted

Use the CONTROL_FILES initialization parameter to know where your control files are located.

SQL> show parameter control_files;

NAME           TYPE     VALUE
-------------- -------- ------------------------------
control_files  string   /home/oracle/app/oracle/oradat a/orcl/control01.ctl,/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Modify the CONTROL_FILES parameter removing the entry of the unavailable control file.

SQL> alter system set control_files='/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

Bounce the instance.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             356518584 bytes
Database Buffers           92274688 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

The instance is now open to the users and the CONTROL_FILES parameter shows where your multiplexed control files are located.

SQL> show parameter control_files;

NAME           TYPE        VALUE
-------------- ----------- ------------------------------
control_files  string      /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

I want to underline the above steps were made on a test environment.
I originally had only two multiplexed control files, before removing one: so, for my specific test case, implementing option B) meant to have a database working with only a control file. Don't even think to use only a control file on a production database: Oracle recommends that you multiplex multiple control files (using DBCA three control files are created) on different devices or mirror the file at the operating system level.

Next option to restore from a loss of one control file is C), that is copy one of the valid multiplexed control file to a new and accessible location, replacing the reference of the inaccessible file with the new one in the CONTROL_FILES initialization parameter.

[oracle@localhost orcl]$ cp /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl

Start your instance in NOMOUNT mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes

Replace the old reference to the inaccessible file with the new one copied to a different location.

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

Bounce the instance.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

Your database is available and open to the users.

That's all.