Monday, 21 November 2016

How to Multiplex Control Files in Oracle Database

How to Multiplex Control Files in Oracle Database

How to add one or more control files in oracle database? Why do we require control file multiplexing? Why is it important to have multiple copies of control files in your oracle base?

Control files serve a very important role as they contain all the information regarding database physical structure in binary format so that user neither can read it or can alter it directly. There are ways in which you modify the contents in Control Files which you can read here in my article - Modifying Contents in Control Files in Oracle Database. But since this post is about making multiple copies of Control Files and why that is important.

Importance of Control File Multiplexing in Oracle Database:

As we all know that Control Files are required to start the database (as simple as it is). When you give the database startup command, first of all the parameter file is read (containing info regarding memory parameters and control file location) which helps in running up the instance. After this, the control file is read so that the physical structure of database is recognised and if everything goes well i.e. if everything is found well as per the names and paths defined in Control File, the database goes to open state. Now after the database becomes open, all users are allowed to make connections to the database and access their data respectively.

What if we miss our Control File? The answer is simple. We'll not be able to open our database because we might know where the physical files are but how will you make computer know where the files are. That's why control files are in binary format because only your system is allowed to read that file and get the information out of it.

 The necessity of Control Files is clear. Now it's not like that you copy one control file and keep its copied version somewhere and think that you have saved control file backup. It's not true at all. If you are in Oracle World, you must be knowing that Control Files also contains info regarding SCN (System Change Number) which keeps on updating as the changes are made in the database. With every single change made inside the database, the SCN updates itself. So in order to make things going well, this information is written in Control Files so that the next time you startup the database, it must be able to know the mount point.

So to avoid all the above mentioned issues, we multiplex our Control Files and keep the copies at separate locations. This is just to make sure that we don't all the Control Files simultaneously because of some disk failure or file corruption issue. Like we say, it is always mandatory to have backup and this is our backup strategy in case of Control Files. (but not the only strategy, of course we have Different Backup & Recovery Scenarios in Oracle Database)

Multiplexing a Control File:

Multiplexing a Control File in Oracle database is quite simple and mandatory. Follow the below mentioned steps if you want to perform the same:

1. Shutdown the database
2. Make physical copies of Control File (Just like copy a file and paste it in another location. In this case you are doing the same with the Control File. Also you can copy and paste the Control File in same location naming it as CONTROL01.CTL, CONTROL02.CTL, CONTROL03.CTL etc but that's not a recommended approach because what's the worth of keeping all the control files all together)
3. Make the newly created control files enteries in PFILE (To see the difference between PFILE and SPFILE, please read my article - How PFILE differs from SPFILE in Oracle Database)
4. After making changes to PFILE, save the file.
5. Now Start the database using that PFILE for example:
SQL> Startup PFILE=Mention the complete path and then name here
6. Now create spfile from pfile so that the changes also go to the Binary Parameter file.
SQL> create spfile from pfile;

Once all the above steps are done, you are done with your Control File Multiplexing. I hope you'll like the article.

No comments:

Post a Comment