Control File multiplexing in Oracle

I. Control File multiplexing features

The principle of multiplexing is actually very simple, that is, the control file is stored in multiple disk partitions or multiple hard disks on the database server. When the database system needs to update the control file, it will automatically update multiple control files at the same time. In this case, when one of the control files is corrupted, the system will automatically enable another control file. Only when the database administrator is lucky enough, that is, all the control files are damaged, and the database cannot be started normally. However, as long as the control file is reused on multiple hard disks, the damage to the control file is usually very small. Therefore, the use of Multiplexing Control files can greatly improve the security of control files. Most importantly, there will be no downtime in the Process of controlling file conversion. Therefore, by storing control files on different hard disks, the database administrator can avoid the risk of spof in the database. When multiple control files are enabled using multiplexing technology, the database updates these control files when updating control files. Although sometimes this may allow the database to spend more time modifying the control file, the investment in this time is worthwhile relative to the risk. In addition, as long as the performance of the hard disk is good, the impact of this time is very small, or even negligible. However, when multiplexing is adopted, it is best not to prevent control files from being placed on servers on the network. Sometimes, if the system encounters poor network performance or network interruptions when updating the control file, it takes a long time to update the control file.

For example, if you install an Oracle database in a Windows operating system, the multiplexing technology is enabled by default. However, this multiplexing technology is not reasonable. It saves copies of the remaining two control files in the same directory of the same partition. In case of a failure in the server's hard disk, the multiplexing becomes meaningless because the control file is stored in the same hard disk. Therefore, it is best to save the control file in Different Hard disks to improve the security of the control file.

Ii. How to achieve control file multiplexing on Windows Platforms

The Oracle database has three control files stored in the same directory. I have now placed a control file on another hard disk partition to achieve multiplexing. In this case, even if the first two control files are corrupted, the database will immediately enable the third control file to start the database. In Windows, it is relatively simple to implement Multiplexing Control files. You only need to take a few simple steps.

Step 1: modify the system parameter control_files. In the Oracle database system, this initialization parameter is used to open the control file. That is, the number of Control Files specified in this initialization parameter, which are respectively stored there and the database will update the number of control files. However, you must note that when using a database, only one control file is opened. To enable multiplexing, you must first use the ALTERSYSTEM command to set this initialization parameter so that you can add a control file at a location specified by the Administrator. The specific format is ALTERSYSTEMcontrol_files "Control File 1", "control file 2 ". Note that all the control files here must use absolute paths.

Step 2: Shut down the database and related services. After setting this initialization parameter, you must close the database and related services before proceeding to the next step. Therefore, it is best to prepare control file multiplexing before the database is put into production and use. Otherwise, you will have to pay the cost of database downtime. After you use the shutdown command to close the database, you also need to close related services in the Service Management window of the operating system.

Step 3: Copy and rename the control file. To ensure that all control files are images and identical to each other, it is best to copy the original control file to a new location and rename it when the database is closed. Note that the location and control file name must be the same as the path specified in step 1.

Step 4: restart the database and related services. After starting the database, pay attention to the related options in the Manual start service window. If you are in trouble, restart the operating system and the system will automatically enable the relevant Oracle Database Service during the restart process. After the database is restarted, The Multiplexing Control file can be used. If you want to confirm whether the original settings work, you can use the query statement shown in. After you use the ALTERSYSTEM parameter to change the initialization parameter, because this control file does not actually exist, the changed changes cannot be found when you use this statement to query. After the control file is copied and the database system is restarted, the changes take effect. In this case, you can use this query statement to find the control file that works. In other words, if the administrator actually copies the control file, but cannot find the corresponding results here, it indicates the location where the problem occurs. At this time, the system engineers should start from scratch to eliminate the possible causes of the fault.

3. Notes during implementation

Obviously, it is easier to achieve control file multiplexing on Windows operating systems. However, in the specific configuration process, some details should be paid attention to by administrators. First, when you use ALTERSYSTEM to change the initialization parameters, you must include the original control file information. That is to say, by default, the Oracle database already has three control files. If the database administrator still needs to use two more control files on other hard disks, add five pieces of information to the ALTERSYSTEM statement. That is to say, the original control file information must also be included. If the database administrator only includes two statements for the new control file in this statement, the database system considers that all the three control files are missing, instead of using the two control files added later. Therefore, the author reminds database administrators not to make this low-level error when adjusting settings. Next, you need to consider the storage location of Multiplexing Control files. Like the default settings of the database, the control component is stored in the same directory.

This is obviously not safe. At least place the control file on a different hard disk or partition. Specifically, each copy of the control file should be stored on a different disk drive. That is to say, a copy of the control file can be stored on the hard drive where each storage has a redo day to the members of the file group. However, it should be noted that it is best not to save copies of control files on the network host. This is mainly because the system updates multiple database control files at the same time. If the network is busy, updating the control on the network host may take a lot of time. It can be seen that the selection of this location is not only related to the control file update speed, but also to the security of the control file copy. Therefore, when planning the database, you also need to think about where the copy of the control file should be stored. Third, it should be noted that the default storage location of this control file is different in different operating systems.

For example, in Linux and Windows, although three control files are used by default, one is a copy of the control file. However, their storage paths are different. Therefore, to reuse control files on different operating systems, you must use the preceding query statement to query the control files currently in effect. Never look for it by feeling. Even if you find the control file, you cannot ensure that the control file is valid or contains the latest content. If not, all control files cannot be identical, and all control files cannot be mirrored to each other.

1. Manage Oracle constraints and partition tables
2. Three criteria for creating an Oracle database index
3. Oracle performance optimization achieved through Partitioning technology
4. Three methods to simplify the management of Oracle table columns
5. What Oracle DBA must understand in the new environment