Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to multiplex Control Files in Oracle

Control File is a physical component of every Oracle Database, it is a small binary file, which records database structure. Control File includes information like database name, name and location of data files and redo log files, database creation timestamp, current log sequence number, checkpoint information. Additionally if we use RMAN for backup and we do use RMAN catalog, information about RMAN backups is stored in control file. Oracle Database requires at least one Control File to operate. Control Files must be accessible all the time the database is open.

Control File is crucial physical component of every Oracle database. Multiplexing Control Files to several different file systems decreases the probability of losing Control Files. The procedure used to multiplex Control Files can also be used to move or rename Control Files. It is worth to remember that each control file is exact copy of primary Control File. The locations and names of control files is stored in instance parameter named CONTROL_FILES.

The first step in this procedure is to identify the current CONTROL_FILES.

SQL> column is_recovery_dest_file format a25
SQL> column name format a60
SQL> set linesize 160
SQL>
SQL> select status, name, is_recovery_dest_file
2 from v$controlfile
3 /

STATUS NAME IS_RECOVERY_DEST_FILE
------- ------------------------------------------------------------ -------------------------
/u01/app/oracle/oradata/orcl/control01.ctl NO
/u01/app/oracle/oradata/orcl/control02.ctl NO
/u01/app/oracle/oradata/orcl/control03.ctl NO

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl

As we can see I have three Control Files however each residing on one single disk, which means that in case of hardware failure I will lose all my Control Files. To prevent this I will multiplex into two more locations, which should be placed on separate hard drives. For presentation purposes I will simulate hard drives my creating directories in /. The suggested location for storing additional CONTROL_FILES is /u[n]/app/oracle/oradata/instance_name/ , where is two digit string like 01 or 02.

[root@localhost ~]# mkdir -p /u02/app/oracle/oradata/orcl
[root@localhost ~]# mkdir -p /u03/app/oracle/oradata/orcl
[root@localhost ~]# chown -R oracle:oinstall /u02/app/
[root@localhost ~]# chown -R oracle:oinstall /u03/app
[root@localhost ~]# chmod -R 775 /u02/app
[root@localhost ~]# chmod -R 775 /u03/app
[root@localhost ~]# ls -l /u02/app/
total 4
drwxrwxr-x 3 oracle oinstall 4096 Jul 17 12:45 oracle
[root@localhost ~]# ls -l /u03/app/
total 4
drwxrwxr-x 3 oracle oinstall 4096 Jul 17 12:45 oracle

After the locations for multiplexed control files were created we need to modify CONTROL_FILES instance parameter, by using ALTER SYSTEM command. We use scope=spfile to indicate that the change will be applied after database restart.

SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl',
2 '/u02/app/oracle/oradata/orcl/control02.ctl', '/u03/app/oracle/oradata/orcl/control03.ctl'
3 scope=spfile
4 /

System altered.

Then we shut down the database using shutdown immediate and move Control Files to new locations.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@localhost ~]$ mv /u01/app/oracle/oradata/orcl/control02.ctl /u02/app/oracle/oradata/orcl/
[oracle@localhost ~]$ mv /u01/app/oracle/oradata/orcl/control03.ctl /u03/app/oracle/oradata/orcl/
[oracle@localhost ~]$ ls -l /u02/app/oracle/oradata/orcl/
total 9536
-rw-r----- 1 oracle oinstall 9748480 Jul 17 13:02 control02.ctl
[oracle@localhost ~]$ ls -l /u03/app/oracle/oradata/orcl/
total 9536
-rw-r----- 1 oracle oinstall 9748480 Jul 17 13:02 control03.ctl

The last thing to do is to start the database and check the location of Control Files.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jul 17 13:10:29 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 477073408 bytes
Fixed Size 1300716 bytes
Variable Size 201328404 bytes
Database Buffers 268435456 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.

SQL> set linesize 160
SQL> column name format a60
SQL> column is_recovery_dest_file format a25
SQL> select status, name, is_recovery_dest_file
2 from v$controlfile
3 /

STATUS NAME IS_RECOVERY_DEST_FILE
------- ------------------------------------------------------------ -------------------------
/u01/app/oracle/oradata/orcl/control01.ctl NO
/u02/app/oracle/oradata/orcl/control02.ctl NO
/u03/app/oracle/oradata/orcl/control03.ctl NO

Categories: