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;

NAME
------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL03.CTL

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\
                                                 DATABASE\SPFILESIBL.ORA

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.


*.control_files='F:\oracle\product\10.2.0\oradata\sibl\control01A.ctl','F:\oracle\product\10.2.0\oradata\sibl\control02A.ctl','F:\oracle\product\10.2.0\oradata\sibl\control03A.ctl'

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;

NAME
--------------------------------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL01A.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL02A.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL03A.CTL

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.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: