DBA contents multiplexing.

Control File is a physical component of every Oracle Database.



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.