As an Oracle DBA, we might come across a situation
where the Oracle database has only one control file, which is considered
dangerous to the database, as failure or corruption of a single control file
cause issue to database availability.
For example, in the below database we have only one
control file
SQL>
select name from v$controlfile;
NAME
-----------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL
As standard, we should have at least 2 control
files. converting one control file to multiple control files termed as
multiplexing of control files. We will perform multiplexing of control
activity.
We will convert this single control to two control
files, that will provide security to the database.
Step 1 : Check existing control file name and
location using either of the below command
SQL>
select name from v$controlfile;
NAME
-----------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL
SQL>
show parameter control_file
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time
integer 7
control_files
string C:\APP\ORACLE\ORADATA\ASGARD\C
ONTROL01.CTL
Step 2 : check whether database is running on pfile
or spfile using below command
SQL>
show parameter pfile
NAME
TYPE
VALUE
------------------
----------- ------------------------------
spfile string C:\APP\ORACLE\PRODUCT\11.2.0.4 \DATABASE\SPFILEASGARD.ORA
SQL>
If this command gives output, then consider oracle
database is running with spfile (server parameter pfile)
Step 3 : Create pfile with spfile, so that we
can add new control file to the database
SQL>
create pfile from spfile;
File
created.
SQL>
Step 4 : Shutdown database and copy existing
control file to new location and add location in pfile as well
SQL>
shu immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
C:\Users>copy
"C:\app\oracle\oradata\asgard\CONTROL01.CTL"
C:\app\oracle\oradata\ctrl\CONTROL02.CTL
1 file(s) copied.
*.control_files='C:\app\oracle\oradata\asgard\control01.ctl','C:\app\oracle\oradata\ctrl\CONTROL02.CTL'
Step 5 : Once all above steps are successfully.
create spfile from pfile using below command, while database is down
SQL>
create spfile from pfile;
File
created.
SQL>
Once spfile is created, start the database using
startup command
Once database is started, you will be able to see
two control files, and we are done with our task, i.e. multiplexing of control
file
SQL>
startup
ORACLE
instance started.
Total
System Global Area 3156725760 bytes
Fixed
Size 2179896
bytes
Variable
Size 1795165384 bytes
Database
Buffers 1342177280 bytes
Redo
Buffers 17203200 bytes
Database
mounted.
Database
opened.
SQL>
select name from v$controlfile;
NAME
-----------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL
C:\APP\ORACLE\ORADATA\CTRL\CONTROL02.CTL
SQL>
This completes DBA’s task of multiplexing of control file.