Oracle Control Files

Hi all,

Every person has a friend, a very good friend. who knows everything about that person. and this friend is very crucial to that person.same way oracle control file is also very crucial to oracle database. It is needed to mount the database.

Oracle control file stores the complete information about the physical structure of database and and it's status. oracle control file contains following information about oracle database, but not limited to it:

• Database information (RESETLOGS SCN and their time stamp)
• Archive log history
• Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
• Redo threads (current online redo log)
• Database's creation date
• database name
• current archive log mode
• Log records (sequence numbers, SCN range in each log)
• RMAN catalog
• Database block corruption information
• Database ID, which is unique to each DB
  

This way control file contain contain almost all information about oracle database. The control file must be available for writing by the oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file is created at the time of database creation on the location specified by initialization parameter file. It is recommended that you should multiplex your control file on same host or another machine, because you might need them in case of recovery.

To backup the control file in running instance, execute following command as sys user:

SQL> alter database backup controlfile to trace;

or if you want to backup controlfile in user readable format then execute

SQL> alter database backup controlfile to '';

you can multiplex control files with operating system copy command, but for this purpose you have to shut down your database. Once the controlfile are multiplexed, all we be synchronized by oracle if some changes are made to database. follow the following steps to multiplex the control files on you oracle host:

1.shut down database.
2. copy existing control files to the multiplexing location with operating system copy command. ('copy' in windows and 'cp' in Linux).
3. make entries in initialization parameter file, edit the control_files parameter specifying path newly multiplexed control files.
4. recreate server parameter (sp file) file from newly edited parameter file.
5. open the database.
6. check with view v$controlfile, whether multiplexed controlfile are there in list.

This way you can multiplexed your control files. If lost in worst situation you can recreate your control file for disaster recovery. following script will explain how to recreate your control file.

startup nomount
create controlfile reuse database 'dbname' noresetlogs noarchivelog/archivelog
maxlogfiles 30
maxlogmembers 5
maxdatafiles 30
maxinstances 1
maxloghistory 500
logfile
group 1 '/uo1/oracle/dbs/redo01.log' size 100m,
group 2 'u01/oracle/dbs/redo02.log' size 100m
datafile
'/u01/oracle/dbs/system01.dbf',
'/u01/oracle/dbs/tools01.dbf',
'/u01/oracle/dbs/index01.dbf',
'/u01/oracle/dbs/users01.dbf',
'/u01/oracle/dbs/undotbs01.dbf',
'/u01/oracle/dbs/fin01.dbf',
add temporary tablespace temp
tempfile ''/u01/oracle/dbs/temp01.dbf',
character set WE8DEC
;

This way you can create your control file in needed. but always backup and multiplex control files for safe reasons.