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.
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.
