Control File in Oracle

The control file in oracle is a small binary file necessary for the database to start and operate successfully. Each control file is associated with only one oracle database. Before a database is opened, the control file in oracle is read to determine if the database is in a valid state to use.

Oracle server updates the control file continuously during the database is in use, so it must be available for writing whenever the database is open. Only the oracle server can modify the information in the control file; no database administrator or end-user can edit the control file.

If for some reason, the control file in oracle is not accessible, the database does not function properly. If all copies of the database’s control files are lost, the database must be recovered before it can be opened.

Control File in Oracle - Table of Contents

• How to Find Control Files in Oracle?
• Multiplexed Control Files
• Recreating the Control Files
• Control File Views

If you want to enrich your career and become a professional in Oracle DBA, then enroll in "Oracle DBA Training". This course will help you to achieve excellence in this domain.

How to Find Control Files in Oracle?

A Control File contains information such as:

• The database name
• The timestamp of database creation
• The names and locations of associated data files and redo log files
• Tablespace information
• Data file offline ranges
• The log history
• Archived log information
• Backup set and backup piece information
• Backup datafile and REDO LOG information
• Data file copy information
• The current log sequence number
• Checkpoint information.

The database name and time stamp originate at database creation. The database name is taken from either the name specified by the db_name initialization parameter or the name used in the create database statement.

Each time that a data file or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:

1. Oracle database can identify the data files and redo log files to open during database startup.
2. Oracle database can identify files that are required or available in case if database recovery is necessary.

[ Learn Complete Oracle DBA Tutorial ]

Therefore, if you make a change to the physical structure of your database (using alter database statements), then you should immediately make a backup of your control file.
Control files also record information about checkpoints.

Every three seconds, the checkpoint process (ckpt) records information in the control file about the checkpoint position in the redo log. This information is used during database recovery to tell the oracle database that all redo entries recorded before this point in the redo log group are not necessary for database recovery; they were already written to the data files.

 

Multiplexed Control Files

As a DBA, it’s your responsibility to multiplex control files to protect your organization in case of possible data loss due to media failure or control file corruption.
Oracle enables multiple, identical control files to be open concurrently and written for the same database.

By storing multiple control files for a single database on different disks, you can safeguard against a single point of failure with respect to control files. If a single disk that contained a control file crashes, then the current instance fails when oracle attempts to access the damaged control file.

However, when other copies of the current control file are available on different disks, an instance can be restarted without the need for database recovery. If all control files of a database are permanently lost during the operation, then the instance is aborted and media recovery is required.

[ Related Article: Managing The Redo Log - Oracle DBA ]

Steps for Multiplexing the Control Files

Every Oracle Database should have at least two control files, each stored on a different physical disk. If a control file is damaged due to a disk failure, the associated instance must be shut down.

Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

1. Shut down the database in a normal state:

Sql>shutdown normal

2. Copy the existing control file to a new name and location:

$cp /u01/oradata/orcl/control01.ctl  /u02/oradata/orcl/control02.ctl

3. Add the new control file to initorcl.ora:

Control_files = (/u01/oradata/orcl/control01.ctl, /u02/oradata/orcl/control02.ctl)

[ Check out How to Create TableSpaces in Oracle DBA? ]

4. Startup the database

Sql>startup;

To obtain the location and name of the control files, you can use the following commands:

Sql>select name from v$controlfile;

Sql>select name,value from v$parameter wher name = ‘control_files’;

Sql>show parameter control_files

Recreating the Control Files

The size of the control file is influenced by the following keywords in the create database or create control file commands:

• Max log files
• Max log members
• Max log history
• Max data files
• Max instances.

So, if ever we have to change the values of the above parameters we have to recreate the control files. Also, when we want to change the name of the database.

Steps for Recreating the Control File

We should only recreate our control file when we are under certain circumstances:

1. All copies of the control files present in the database are lost or corrupted.
2. We are restoring a backup in which the control file is corrupted or missing.
3. We need to change a hard limit database parameter in the control file.
4. If we are moving our database to another server, our files are present in different locations. 
5. Oracle customer support advises us to do so.

[ Learn Managing User Roles in Oracle DBA ]

For recreating the control file, we need to follow the below steps:

1. At database open state, we need to fire the ‘alter the database backup control file to trace;’ command as shown in the below example:

Sys@rao>show parameter trace_

Name                                                        type           value                         
———                                                   ———–         ——-
Sec_protocol_error_trace_action          string             trace                         
Trace_enabled                                      boolean          true                          
Tracefile_identifier                                 string    

Sys@rao>alter session set tracefile_identifier=cricket;

Session altered.

Sys@rao>alter database backup controlfile to trace; 

2. The above command creates one trace file in a trace directory location. To know the trace directory, we need to fire the following command.

Sys@rao>show parameter diag

Name                                        type              value                         
———                                     ———         ———–
Diagnostic_dest                      string          /u01/cricket  

Select * from v$diag_info;

If it is 10g database, The trace file store in a dump location.              

3. Now shut down the database and copy the most recent trace file to the oracle user home directory or some other location with a different name and extension must and should be .sql.

Sql>shut immediate

Sql>exit

Here my trace location is /u01/cricket/diag/rdbms/cricket/cricket/trace.

Now move to the trace directory by using the following command.

$cd /u01/cricket/diag/rdbms/cricket/cricket/trace (press enter here)

$ls –lrt

Search the recent trace file that contains the extension with cricket.trc file, and copy this trace to some other location by using:

$ cp  cricket_ora_21971_cricket.trc /home/oracle/recreatecontrol.sql(press enter here)

Now move to /home/oracle directory location and remove unnecessary lines in the “recreatecontrolfile.sql” file.

$cd /home/oracle(press enter here)

$ls –lrt

$vi recreatecontrolfile.sql (press enter here)

( In this file, we have two sets, one is no rest log case and the second one is reset log case, here I am choosing the rest log set, otherwise you can take no rest log case also. And remove the above line up to create control file command)

Create controlfile reuse database “wisdom” resetlogs noarchivelog

 Max log files 16
                        Max log members 2
                        Max data files 30
                        Max instances 1
                        Max log history 292
Logfile
                        Group 1 ‘/u01/cricket/redolog01.log’ size 512m,
                        Group 2 ‘/u01/cricket/redolog02.log’ size 512m,
                        Group 3 ‘/u01/cricket/redolog03.log’ size 512m
— standby logfile
Datafile
                        ‘/u01/rao/system.dbf’,
                        ‘/u01/rao/sysaux.dbf’,
                        ‘/u01/rao/undo01.dbf’,
                        ‘/u01/rao/user01.dbf’
Character set us7ascii
;
:wq! (save and quite the file)

Learn Oracle DBA Interview Questions and Answers that help you grab high-paying jobs

4. Now remove the existing control file that is related to cricket database. For example, here my control files are available in /u01/cricket directory. Move to that directory location and remove the existing control files.

$cd /u01/cricket
$ls –lrt
$rm  –f  control*

5. Now, export the oracle_sid value as cricket and connect to the database as sys user by using the following command.

$export oracle_sid=cricket
$sqlplus / as sysdba

6. Now, start up the database at nomount state and run the edited trace file i.e., “createcontrol.sql”  file at the SQL prompt.

Sql>startup nomount;

Here createcontrol.sql file is saved in /home/oracle location.

Sql>@/home/oracle/createcontrol.sql

After running the file, we need to open the database by using resetlogs option by using the following command.

Sql>alter database open resetlogs;

Then, we need to add the temporary files for existing temporary tablespaces by using the following command.

Here my temporary tablespace is tempts.

Sql> alter tablespace temptbs add tempfile '/u01/cricket/temp01.dbf'
            Size 500m  reuse autoextend off;

Control File Views

• V$controlfile
• V$database
• V$parameter
• V$controlfile_record_section
• Show parameter control_files.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!