Oracle Add Controlfile Tips

Question:  How can I add another control file to my database?   

Answer:  Adding and changing the control files is easy.  All you do is copy the file and change the value of your control_files parameter.

Let's say I have this setting:

 CONTROL_FILES = (/u01/appl/oracle/prod/control01.ctl,
                  /u02/app/oracle/prod/control02.ctl,
                  /u03/app/oracle/prod/control03.ctl)

If I want to add a new control file, I follow these steps:

1.  Make sure that the new control file resides on a SEPARATE physical disk.  The purpose of multiple control files is to protect the database in case of a disk failure.

2.  Check to ensure that the new disk exists (the mount point or Windows drive letter) and that Oracle has permission to create the control file on that mount point.
   
   If not, you may get this ORA-00344 unable to re-create log:
   
   ORA-00344: unable to re-create online log 'D:\ORADATA\PROD\REDO01B.ORA'
   ORA-27040: skgfrcre: create error, unable to create file
   OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.

3. Shutdown Oracle and use the cp command to copy one of the old control files to the new location

4.  Change the control_files parameter for your instance:
   
   CONTROL_FILES = (/u01/appl/oracle/prod/control01.ctl,
   /u02/app/oracle/prod/control02.ctl,
   /u03/app/oracle/prod/control03.ctl,
   /u04/app/oracle.prod/control04.ctl)

5.  Start your instance.

There are some SQL*Plus commands that will allow you to see your current control files.

Rampant Author, Kamran Agayev A. provides additional information on control file use when adding a hard disk on Linux.  He also provides specific information on multiplexing the control file as part of the process.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.