Oracle Database Control File Multiplexing

First of all, keep a backup of your existing Control File on other disk. If, you don’t know location of existing Control File then use this following query.

SQL> select name from v$controlfile;


This Database already has 3 multiplexed Control Files but on the same Location. Now, try to find out the pfile of Database, we need to edit INITsibl.ORA (my database name is "sibl") to change/multiplex Control File Location.

However, if you don't find inti[databasename].ora file, create pfile form spfile;

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      F:\ORACLE\PRODUCT\10.2.0\DB_1\

SQL> create pfile from spfile;

File created.

we need to modify pfile init[databasename].ora to reallocate Control Files. Modifying spfile is always forbidden, you may end up losing data. Obviously, Control File Copies should be in place.


Now, shutdown Database and start up with your modified pfile.

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

SQL> startup nomount pfile=F:\oracle\product\10.2.0\db_1\database\INITsibl.ORA
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  1294584 bytes
Variable Size             931139336 bytes
Database Buffers          109051904 bytes
Redo Buffers                7090176 bytes

SQL> select name from v$controlfile;

no rows selected

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> alter database mount;

Database altered.

SQL> select name from v$controlfile;


SQL> create spfile from pfile;

File created.

At your next Database bounce, it will retrieve reallocated Control Files from spfile as it is created again from our modified pfile.

A Rahim Khan

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: