Control File in Database

Control file in oracle database:

As I have already discussed about the database startup process and steps involved for database to open. You can also get the same from below URL:

http://appsdbaraz.blogspot.com/2013/12/database-startup-options.html

While we start database, first it uses the parameter file and nomount the database, then using control file mount the database and after checking if recovery is required and performs the required step and then opens the database.

Control file is compulsory for every database to mount. Control file is a binary file which records the physical structure of database. While we think of control file one question arises in our mind i.e.

What control file contains?

Control file contains information like:

• The Database name.
• Names and locations of data files and redo log files.
• The timestamp of database creation.
• The current log sequence number.
• Checkpoint information.
• Backup set and backup pieces information.

Control file is the critical file, which is required for database to startup. So to avoid loss of control file, we manage to have multiple copies of control file.

When we create database at least one control file is created with default values and for some operating systems, by default multiple copies of control files are created.

How to get the location of control file?

SQL> Show Parameter Control_Files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string     /u02/oracle/prod/control01.ctl
                                                      ,  /u02/oracle/prod/control02.ctl
                                                      ,  /u02/oracle/prod/control03.ctl

             
Create control file:

Creating Additional Copies, Renaming, and Relocating Control Files:

Multiplexing copy of control file or renaming a control file steps:

1.     Shut down the database.

2.     Using operating system command, copy the existing control file to new location.

3.     Edit the CONTROL_FILES parameter in the initialization parameter file to add new control file name or to change the existing control file name.

4.     Restart the database.

Manually Creating New Control Files:

Create Control file statement can be used to create a new control file for a database and also we can use the parameter values as per our requirement. We can give the database name whatever we want.

CREATE CONTROLFILE
 SET DATABASE prod
 LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
 '/u01/oracle/prod/redo01_02.log'),
 GROUP 2 ('/u01/oracle/prod/redo02_01.log',
 '/u01/oracle/prod/redo02_02.log'),
 GROUP 3 ('/u01/oracle/prod/redo03_01.log',
 '/u01/oracle/prod/redo03_02.log')
 RESETLOGS
 DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
 '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
 '/u01/oracle/prod/users01.dbs' SIZE 5M,
 '/u01/oracle/prod/temp01.dbs' SIZE 5M
 MAXLOGFILES 50
 MAXLOGMEMBERS 3
 MAXLOGHISTORY 400
 MAXDATAFILES 200
 MAXINSTANCES 6
 ARCHIVELOG;

  

Back Up Control Files:

Back up of control file is very important and It happens by default when you change your physical structure of your database. Some examples of structure changes are:

1.     Adding, dropping, or renaming datafiles.

2.     Adding or dropping a tablespace.

3.     Altering the read-write state of the tablespace.

4.     Adding or dropping redo log files or groups.

Back up control file using the below command from SQL prompt and stores it into binary format:
SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

We can also backup our control file to trace location:

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

When to create control file?

1. When you have lost your all control files.
2. When you want to rename your database.

Dropping Control Files:

Sometimes the location of cotrol file is changed, in such situation need to drop the existing control file and use the correct one. Control file can be dropped or renamed very easily as below steps.

1.     Edit the CONTROL_FILES parameter in the initialization parameter file to delete old control file name.

2.     Shut down the database.

3.     Start the database.

Reusable section of control file (control_file_record_keep_time) :

Control file has reusable section and it tracks ARCHIVE logs and BACKUP sets. control_file_record_keep_time is the initialization parameter which determines the reussable data to be stored for number of days. Default value of control_file_record_keep_time is 7 days. It can also be increased to any value whatever we want.

We can check the reusable period from SQL prompt as below:
SQL> show parameter Keep_time;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

Changing the value of control_file_record_keep_time using SQL command:
SQL> alter system set control_file_record_keep_time=14 scope=spfile;

System altered

Now RMAN can keep records of BACKUPS and ARCHIVE logs for 14 days.

Also need to configure control file autobackup on.
CONFIGURE CONTROLFILE AUTOBACKUP ON;