Adding an Extra Control File

When you create an Oracle database, a control file is set up at the same time. This records the physical structure of the database. You only need one control file and this might be enough for a test database. Here is one that I created earlier. The data files, redo log files and control file are all in the same directory:

C:\Test10>dir
 Volume in drive C has no label.
 Volume Serial Number is 18E4-B972

 Directory of C:\Test10

24/12/2010  16:34    <DIR> .
24/12/2010  16:34    <DIR> ..
24/12/2010  16:40         7,061,504 ORIG.CTL
24/12/2010  16:33        52,429,312 REDO01.LOG
24/12/2010  16:33        52,429,312 REDO02.LOG
24/12/2010  16:40        52,429,312 REDO03.LOG
24/12/2010  16:40       241,180,672 SYSAUX01.DBF
24/12/2010  16:40       492,838,912 SYSTEM01.DBF
24/12/2010  16:28        20,979,712 TEMP01.DBF
24/12/2010  16:40        26,222,592 UNDOTBS01.DBF
24/12/2010  16:40        5,251,072 USERS01.DBF
               9 File(s)   950,822,400 bytes
               2 Dir(s) 10,268,143,616 bytes free

C:\Test10>
 
You can see in v$controlfile that this is the only control file on the machine:

SQL> col name format a40
SQL> select name from v$controlfile;

NAME
----------------------------------------
C:\TEST10\ORIG.CTL

SQL>
 
Oracle recommends that you should have two or more control files on different disks and you should always follow this advice for any important database. You can add extra control files as follows. First you need to close the database:

C:\oracle\product\10.2.0\db_1\dbs>set ORACLE_SID=TEST10

C:\oracle\product\10.2.0\db_1\dbs>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 17:38:53 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

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

Then you need to edit the parameter file and change the control_files entry as shown below. The first line shows the original entry commented out by a #. The second line shows the new entry with an extra file name added on the end:

#control_files='C:\Test10\orig.ctl'
control_files='C:\Test10\orig.ctl','G:\Test10\extra.ctl'

Then, at the OS level, copy the original control file into the new location:

C:\Test10>copy ORIG.CTL G:\Test10\EXTRA.CTL
        1 file(s) copied.

C:\Test10>

Open the database again:

C:\oracle\product\10.2.0\db_1\dbs>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 18:04:08 2010

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

Connected to an idle instance.

SQL> startup pfile=initTEST10.ora
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             167775084 bytes
Database Buffers          440401920 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL>

Then if you look at v$controlfile again, the new control file will appear in the list:

SQL> l
  1* select name from v$controlfile
SQL> /

NAME
----------------------------------------
C:\TEST10\ORIG.CTL
G:\TEST10\EXTRA.CTL

SQL>