Managing Oracle Database Controlfile
Control file management
The control file 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 is read to determine if the database is in a valid state to use. Oracle server updates the control file continuously during 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 is not accessible, the database does not function properly. If all copies of database’s control files are lost, the database must be recovered before it can be opened.
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 data file 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:
- Oracle database can identify the data files and redo log files to open during database startup
- Oracle database can identify files that are required or available in case if database recovery is necessary.
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 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 file 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 operation, then the instance is aborted and media recovery is required.
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.
- Shut down the database in a normal state.
- Copy the existing control file to a new name and location
$cp /u01/oradata/orcl/control01.ctl /u02/oradata/orcl/control02.ctl
- Add the new control file to initorcl.ora
Control_files = (/u01/oradata/orcl/control01.ctl, /u02/oradata/orcl/control02.ctl)
- Start up the database
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 control files present in the database are lost or corrupted.
2. We are restoring a backup in which 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.
For recreating the control file, we need to follow the below steps:
- At database open state, we need to fire the ‘alter the database backup control file to trace;’ command as shown in 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;
- 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 udump location.
- 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 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 “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 restlog set, other wise you can take no rest log case also. And remove above line up to create controlfile command)
Create controlfile reuse database “wisodm” resetlogs noarchivelog
Max log files 16
Max log members 2
Max data files 30
Max instances 1
Max log history 292
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
Character set us7ascii
:wq! (save and quite the file)
- 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 controlfiels.
$rm –f control*
- Now, export the oracle_sid value as cricket and connect to the database as sys user by using the following command.
$sqlplus / as sysdba
- 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 temptbs.
Sql> alter tablespace temptbs add tempfile '/u01/cricket/temp01.dbf'
Size 500m reuse autoextend off;
Control file views:
- Show parameter control_files