Sunday, March 22, 2009

Multiplexing Control File

Oracle consist of three major physical files, they are :
  • Controlfiles
  • Datafiles
  • Online Redo log files
Among them control files are the most impotent one. Controlfile contains Database name, database creation date, Tablespace names, Physical location of datafiles and Recovery information.

With default installation, Oracle has 3 control files placed in same physical location. According to database availability, It is safe to place the 3 controlfiles in disk.


To see the current physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua1/control03.ctl

Suppose we have two HD and those two are mount as /ua1 and /ua2. So we need to move at list one controlfile in /ua1 and we move the 3rd contronlife. there are several way to do the Control File Multiplexing:

1. Using SPFILE:

The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA

1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.

SQL> ALTER SYSTEM SET control_files='/ua1/control01.ctl'
,'/ua1/control01.ctl',
'/ua1/control01.ctl' scope=spfile;


2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.

SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.

mv /ua1/control01.ct /ua2

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL>STARTUP;


To see the changed physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua2/control03.ctl