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:
SQL> SELECT status, name, is_recovery_dest_file FROM V$CONTROLFILE;
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:
[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.
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 files.
[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 instance 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
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.
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.
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
