There are many questions about multiplexing of control file that I am going to address in this tutorial. Questions like, why multiplexing is important and different ways of multiplexing control files in Oracle database. And at the end I will demonstrate how to multiplex a control file using SPFILE in Oracle Database.
What is multiplexing of Control Files?
Copying and maintaining an existing control file at different locations and informing the database about these copies by modifying CONTROL_FILE parameter is called Multiplexing of control file.
Why Multiplexing a Control File?
Let’s say your database uses only one control file, and that control file gets damaged. In this case you will either restore the control file from the backup and perform the recovery or you will recreate the control file.
But what if you don’t have a good backup of your control file. Now you are stuck. In such scenarios, having more than one control file can come in handy.
If you have multiple control files and one of them gets damaged then you can use the other control files to get your database operational in no time.
That is why multiplexing of control file is important.
Different ways of control file multiplexing
In Oracle Database there are two ways to multiplex a control file.
- Using SPFILE, and
- Using PFILE also know as ‘INIT.ORA’
How to Multiplex The Control File In Oracle Database
In this tutorial I will demonstrate the steps of multiplexing control file using SPFILE. In the next tutorial I will cover the multiplexing using PFILE.
Step 1: Connect With the Database
C> SQLPLUS / as SYSDBA
Step 2: Check if the database is using SPFILE or PFILE
SQL> SHOW PARAMETER spfile;
If the database is using SPFILE then there will be the name along with the location in the VALUE column and if the database is using PFILE then there will be nothing in this column.
Since we have a name and location of the SPFILE in the VALUE column, this signifies that my database is using SPFILE.
Step 3: Find Out The Control File
First find out the control file. You can refer to my tutorial on how to find the location of control file in Oracle. But for now
SQL> show parameter control_files
This will show you the name with the location of your control file. Now we are going to create a copy of this control file. So, COPY the entire path of the control file with the name.
Step 4: Alter the CONTROL_FILES parameter. To make the database aware of the changes we will configure the CONTROL_FILES parameter.
SQL> ALTER SYSTEM set CONTROL_FILES='C:\APP\ORADATA\ORCL\CONTROL01.CTL',
First write the name along with the location of the first control file which in my case is CONTROL01.CTL. Next write the name and location of the second control file. Make sure both the names are separated by comma.
SQL> ALTER SYSTEM set CONTROL_FILES='C:\APP\ORADATA\ORCL\CONTROL01.CTL', 'C:\APP\ORADATA\ORCL\CONTROL02.CTL' SCOPE = spfile;
Also, make sure to end this ALTER SYSTEM statement with SCOPE parameter which is set to SPFILE;
STEP 5: SHUTDOWN the DATABASE
Now we have to shutdown the database so that we can make a consistent copy of the control file for multiplexing
SQL> SHUTDOWN IMMEDIATE;
STEP 6: Make a copy of the control file
Now we will make a copy of the control file whose name and location we copied earlier. For this I will open up a new command prompt.
C> copy C:\APP\ORADATA\ORCL\CONTROL01.CTL C:\APP\ORADATA\ORCL\CONTROL02.CTL
You can name your control file whatever you want, just make sure to end its name with .CTL extension.
Step 7: Start the database
Now we will start our database. If everything goes well then our database will Mount and open for all the operations.
You can even check whether you have successfully multiplexed your control file or not. Simply by checking the value of control_files parameter, like this:
SQL> show parameter control_files
This time we should see both our control files in VALUE column. If that happens then it means multiplexing of control files has been done successfully. So that is how we multiplex control file in Oracle Database using SPFILE. In the next tutorial I will show you how to multiplex control file using PFILE. So Stay tuned! Thanks and have a great day!