Thursday, August 4, 2011

Multiplexing of controlfile using spfile


Why multiplex a controlfile?

One of the biggest reasons why we multiplex control files is to create backups of the primary control file, which “records the physical structure of the database.” When the control file is damaged there is no way you can bring it back, unless you have the backup. Backup of control files must also be stored in a different physical disk for high availability. The Control File should always be multiplexed/backed up, as a best practice, each time that Tablespaces and Redo Log Files are dropped or added.

Below are the steps to multiplex the controlfile:

Sql> shutdown immediate

Copy the required controlfile and rename it to the desired location using os utility:

'c:\control01.ctl' 

Sql> Startup nomount

Sql> ALTER SYSTEM SET control_files =
‘D:\oracle\product\10.2.0\oradata\control01.ctl’,
‘D:\oracle\product\10.2.0\oradata\control02.ctl’,
‘D:\oracle\product\10.2.0\oradata\control03.ctl’,
‘c:\control04.ctl’ SCOPE=SPFILE;

Sql> shutdown immediate

Sql> startup

Sql> select name from v$controlfile; 

Sql> create PFILE from SPFILE;

Enjoy:-)