This article outlines how to locate the control file in an Oracle database. The control file is a binary file that contains information about the physical database's present status.
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.
|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.|
A Control File contains information such as:
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:
[ 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.
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 ]
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.
$cp /u01/oradata/orcl/control01.ctl /u02/oradata/orcl/control02.ctl
Control_files = (/u01/oradata/orcl/control01.ctl, /u02/oradata/orcl/control02.ctl)
[ Check out How to Create TableSpaces in Oracle DBA? ]
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
The size of the control file is influenced by the following keywords in the create database or create control file commands:
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.
We should only recreate our control file when we are under certain circumstances:
[ 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
Sys@rao>alter session set tracefile_identifier=cricket;
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.
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)
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)
$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.
Here createcontrol.sql file is saved in /home/oracle location.
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;
|Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!|
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
|Oracle DBA Training||Jun 06 to Jun 21|
|Oracle DBA Training||Jun 10 to Jun 25|
|Oracle DBA Training||Jun 13 to Jun 28|
|Oracle DBA Training||Jun 17 to Jul 02|
Yamuna Karumuri is a content writer at Mindmajix.com. Her passion lies in writing articles on IT platforms including Machine learning, PowerShell, DevOps, Data Science, Artificial Intelligence, Selenium, MSBI, and so on. You can connect with her via LinkedIn.
Copyright © 2013 - 2023 MindMajix Technologies