Oracle database without control file is just like a human body without a backbone. Control file plays the role of the backbone of the database . Being an oracle dba we must know about controlfile . The control file is a file that the Oracle DBMS maintains to manage the state of the database, and it is probably the single most important file in the Oracle database. Every database has one control file, but due to the file’s importance, multiple identical copies (usually three) are maintained—when the database writes to the control file, all copies of the file get written to. The control file is critical to the functioning of the database, and recovery is difficult without access to an up-to-date control file. Oracle creates the control file (and the copies) during the initial database creation process. The control file contains the names and locations of the datafiles, redo log files, current log sequence numbers, backup set details, and the all-important system change number(SCN), which indicates the most recent version of committed changes in the database—information that is not accessible by users even for reading purposes. Only Oracle can write information to the control file, and the Oracle server process continually updates the control file during the operation of the database. This post is about all steps which are used by dba to restore from a loss of a subset of the current control files.

Due to its obvious importance, Oracle recommends that you keep multiple copies of the control file. The V$CONTROLFILE dynamic view gives you the names of all the control files. The STATUS column will be NULL if the name can be determined, which is the case always. If the name can’t be determined (which shouldn’t happen), you’ll see the value INVALID in the STATUS column. The IS_RECOVER_DEST_FILE column shows YES if the control file was created in the flash recovery area and a value of NO otherwise. Here’s the output of a query on the V$CONTROLFILE view:

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:
    copy one of the valid multiplexed control files to the same location of the lost control file;
  • 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);
  • copy one of the valid multiplexed control file to a different location (you have a valid disk alternative), adding the reference to 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:

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

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.
You could even query the V$DATAFILE view to obtain the same… “infamous” ORA27041 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:

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.

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.

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

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

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

No Oracle instance is running.

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

Going through the steps covered by a STARTUP command, after every process successfully attaches to the shared memory, the instance tries 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.

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

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

Bounce the instance.

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

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.

Start your instance in NOMOUNT mode.

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

Bounce the instance.

Your database is available and open to the users.


Thank you for giving your valuable time to read the above information.


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply