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.ctlThen 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.ctlTo 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.ctlAt 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.dbfWhen 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.ctlIn 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: 23No Oracle instance is running.
[oracle@localhost orcl]$ ps -ef|grep smon oracle 10229 4972 0 07:47 pts/4 00:00:00 grep smonThe 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 infoGoing 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.ctlModify 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.ctlI 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.ctlStart 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 bytesReplace 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.