CONTROL FILES MULTIPLEXING

 

Control files multiplexing

 

SQL> select name from v$controlfile;

 

NAME

----------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/TESTDB/control01.ctl

/u01/app/oracle/oradata/TESTDB/control02.ctl

 

SQL> desc v$controlfile;

 Name                                                                     Null?         Type

 ----------------------------------------------------- -------- ------------------------------------

 STATUS                                                                                 VARCHAR2(7)

 NAME                                                                                    VARCHAR2(513)

 IS_RECOVERY_DEST_FILE                                               VARCHAR2(3)

 BLOCK_SIZE                                                                         NUMBER

 FILE_SIZE_BLKS                                                                  NUMBER

 CON_ID                                                                                  NUMBER

 

SQL> show parameter control_files

 

NAME                                          TYPE          VALUE

------------------------------------ ----------- ------------------------------

control_files                                 string           /u01/app/oracle/oradata/TESTDB

                                                                         /control01.ctl, /u01/app/oracl

                                                                         e/oradata/TESTDB/control02.ctl

 

Multiplexing Control Files using spfile

Steps are similar as the above using init.ora file. The major difference is how CONTROL_FILES parameter is changed.
step -1
SQL> ALTER SYSTEM SET CONTROL_FILES='/u01/app/oracle/oradata/TESTDB/control01.ctl',

  2  '/u01/app/oracle/oradata/TESTDB/control02.ctl',

  3  '/u01/app/oracle/control03.ctl' SCOPE=spfile;

System altered.

SQL> show parameter control_files

 

NAME                                          TYPE          VALUE

------------------------------------ ----------- ------------------------------

control_files                                 string           /u01/app/oracle/oradata/TESTDB

                                                                         /control01.ctl, /u01/app/oracl

                                                                         e/oradata/TESTDB/control02.ctl

2. Shut down the instance

                                                           

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

3. Copy the control file to more locations using operating system command.

[oracle@testdb ~]$ pwd

/home/oracle

[oracle@testdb ~]$ cp /u01/app/oracle/oradata/TESTDB/control02.ctl /u01/app/oracle/control03.ctl

[oracle@testdb ~]$ . .testdb.env;

[oracle@testdb ~]$ sqlplus / as sysdba

4. startup;

SQL> startup

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size                        8897072 bytes

Variable Size                 385875968 bytes

Database Buffers         1207959552 bytes

Redo Buffers                   7876608 bytes

Database mounted.

Database opened.

SQL> show parameter controlfiles;

SQL> show parameter control_files

 

NAME                                          TYPE          VALUE

------------------------------------ ----------- ------------------------------

control_files                                 string           /u01/app/oracle/oradata/TESTDB

                                                                         /control01.ctl, /u01/app/oracl

                                                                         e/oradata/TESTDB/control02.ctl

                                                                         , /u01/app/oracle/control03.ct

                                                                         l

 

 

 

Multiplexing Control Files using the init.ora file

Multiplexing is the process of mintaining a copy of same control files on different disk drivers (and idealy on different controllers). To multiplex your control files, we copy the control file to multiple locations and change the CONTROL_FILES parameter in the text based initialization file init.ora to include all control files names.

1. Shut down the database
SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

2. Copy the control file to more locations using operating system command.

[oracle@testdb ~]$ cp /u01/app/oracle/oradata/TESTDB/control02.ctl u03/oracle/control04.ctl;

cp u01/app/oradata/ord/control01.ctl  u05/app/oradata/ord/control02.ctl

3. Change the initialization parameter to include new file in the parameter

.control_files='/u01/app/oracle/oradata/TESTDB/control01.ctl','/u01/app/oracle/oradata/TESTDB/control02.ctl','u03/oracle/control04.ctl';

 

*.db_block_size=8192

*.db_domain=''

*.db_name='TESTDB'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'

*.open_cursors=300

*.pga_aggregate_target=536870912

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1610612736

*.undo_tablespace='UNDOTBS1'

4. Start the instance

SQL> startup;

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size                        8897072 bytes

Variable Size                 385875968 bytes

Database Buffers         1207959552 bytes

Redo Buffers                   7876608 bytes

Database mounted.

Database opened.

SQL> show parameter control_files

 

NAME                                          TYPE          VALUE

------------------------------------ ----------- ------------------------------

control_files                                 string           /u01/app/oracle/oradata/TESTDB

                                                                         /control01.ctl, /u01/app/oracl

                                                                         e/oradata/TESTDB/control02.ctl

                                                                         , /u03/oracle/control04.ctl

                                                                         

SQL>