How to Multiplex Control Files in Oracle

As a DBA, you know that control files play a very important role in oracle database.

If the control files are corrupt, can you start your database?

No, you can’t do it. So what should be done to avoid such type of corruption?

As a DBA, it’s your responsibility to multiplex control files to protect your organization in case of possible data loss due to media failure or control file corruption.

multiplex control file


Follow below steps to multiplex control file.

First backup your control file with a trace option. You can use following command to backup of your control file with the TRACE option.


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Step-1: Connect to a database

Let’s connect to SQL*Plus as the system/manager@yourdb user.

SQL> CONNECT system/manager@testdb AS SYSDBA

Step-2: List all the controlfiles

Query V$CONTROLFILE view to list all the control files in the database.

SQL> Select * from v$controlfile;

STATUS  NAME                                        
------- --------------------------------------------
        C:\ORACLE\ORADATA\TESTDB\CONTROL01.CTL 
        C:\ORACLE\ORADATA\TESTDB\CONTROL02.CTL 
        C:\ORACLE\ORADATA\TESTDB\CONTROL03.CTL 

SQL>

Step-3 Shutdown the database

SQL> SHUTDOWN IMMEDIATE

Step-4: Add one more controlfile

Copy a controlfile and name it CONTROL04.CTL.
In real practice, Make sure to copy the controlfile into a different hard disk. So, in the case of hard disk failure other controlfile be secure.

SQL> HOST COPY C:\ORACLE\ORADATA\TESTDB\CONTROL03.CTL E:\Multiplexing_Control_Files\CONTROL04.CTL

Step-5: Edit init.ora file and add new file path

Open init.ora file from admin folder and add CONTROL04.CTL path to the location.

Step-6: Create SPFILE using PFILE

SQL> CREATE SPFILE='C:\oracle\..........\SPFILETESTDB.ORA' FROM PFILE='C:\oracle\admin\TESTDB\pfile\init.ora.7282007113213';

File created.

SQL>

Step-7: START the database

SQL> STARTUP;
ORACLE instance started.

Total System Global Area  135338868 bytes  
Fixed Size                   453492 bytes  
Variable Size             109051904 bytes  
Database Buffers           25165824 bytes  
Redo Buffers                 667648 bytes  
Database mounted.
Database opened.
SQL>

Step-8: Check controlfile using show parameter command

SQL> SHOW PARAMETER CONTROL_FILES;

NAME                                 TYPE        VALUE               
------------------------------------ ----------- ------------------------------ 
control_files                        string      C:\oracle\oradata\TESTDB\CONTRO 
                                                 L01.CTL, C:\oracle\oradata\JVV 
                                                 NL\CONTROL02.CTL, C:\oracle\or 
                                                 adata\TESTDB\CONTROL03.CTL, D:\ 
                                                 Multiplexing_Control_Files\CON 
                                                 TROL04.CTL
SQL>

How to Multiplex Redo Log Files in Oracle

There are many other methods to multiplex control file.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)