Multiplexing Control File

Control File is parameter that defines connection between instance and database. If control file is corrupted, missing or there is mismatch in database names, database won't mount. Within the control file there are pointers to other database files so it's obvious that control file is very important and essential file.

Because of it's importance DBA's should always think about multiplexing that file on several storage locations besides backup.

By default, we usually have 2 or 3 control files situated in the same directory, but it is advisable to relocate those files on several drives, or create copies.


I'll show in test case how to multiplex control file.



First I will connect as sysdba and create parameter file.

[oracle@dibidus ~]$ sqlplus sys@test11 as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 30 11:28:51 2008

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password: ******

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

With this command I've created parameter file in my $ORACLE_HOME/dbs directory.
So location is: $ORACLE_HOME/dbs/inittest11.ora.


Now to query how many control files I have on what locations.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/oracle/product/11.1.0/dbs/test11.ctl

I have just one control file what is bad practice (I can afford this to myself cause this is "test" database), so I'll multiplex it on another location.


I will shutdown my database and copy control file to backup location.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit


$ cd /ora/test11/data/TEST11/datafile
$ cp /oracle/product/11.1.0/dbs/test11.ctl .

Then I'll edit my parameter file adding location of another control file.

*.control_files='/oracle/product/11.1.0/dbs/test11.ctl','/ora/test11/data/TEST11/datafile/test11.ctl'

Startup database using newly edited parameter file.

SQL> startup pfile = '/oracle/product/11.1.0/dbs/inittest11.ora';
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1313092 bytes
Variable Size             226494140 bytes
Database Buffers           79691776 bytes
Redo Buffers                6361088 bytes
Database mounted.
Database opened.

To check my controlfiles now.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------
/oracle/product/11.1.0/dbs/test11.ctl
/ora/test11/data/TEST11/datafile/test11.ctl

Apply changes to server parameter file and woila, now we have multiplexed control file on another location.

SQL> create spfile from pfile;

File created.