Movement of Oracle Control Files

So you installed Oracle and left the control file setting at default values. A few months later you loose a disk on your database, luckily not the one with your control files on it, but your realize it could have been. Now being a delegent DBA you see the value in separating these control files on separate physical disks. Here is how its done.
NOTE: When performing this operation no users should be login.
1. Logon to Oracle as SYSDBA.
2. Use the show parameter command to get the current value of you control_files parameter.
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ———————-
control_files string /u05/oradata/Db1
/control01.ctl,/u0
5/oradata/Db1/contro
l02.cl,/u05/oradata/
Db1/control03.ctl
SQL>
Now, we have three control files defined of our database Db1, if we loose the Disk u05, we will loose our database(not really loose, but not easy to get back either).
3. We need to get the value of our current spfile.
SQL> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ———————
spfile string /u01/opt/app/oracle/
product/10.2.0/Db_1/
dbs/spfileDb1.ora
4. Create a text readable version of the spfileDb1 to change the control_files parameter.
SQL> create pfile=’/u01/opt/app/oracle/product/10.2.0/Db_1/dbs/pfileDb1_a.ora’
2 from spfile=’/u01/opt/app/oracle/product/10.2.0/Db_1/dbs/spfileDb1.ora’;
File created.
5. Shutdown your oracle database immediately.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
6. At the OS level, logon to a text editor and change the value of control_files from
*.control_files=’/u05/oradata/Db1/control01.ctl’,'/u05/oradata/Db1/control02.ctl’,'/u05/oradata/Db1/control03.ctl’
to
*.control_files=’/u05/oradata/Db1/control01.ctl’,'/u02/oradata/Db1/control02.ctl’,'/u03/oradata/Db1/control03.ctl’
7. At the OS level move the old control files to there new locations:
mylinux# mv /u05/oradata/Db1/control02.ctl /u02/oradata/Db1/control02.ctl
mylinux# mv /u05/oradata/Db1/control03.ctl
/u03/oradata/Db1/control03.ctl
mylinux#
8. Now startup oracle using the pfile clause to point at your edited pfile.
mylinux# sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 11 12:43:45 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup pfile=/u01/opt/app/oracle/product/10.2.0/Db_1/dbs/pfileDb1_a.ora
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2028568 bytes
Variable Size 201329640 bytes
Database Buffers 104857600 bytes
Redo Buffers 6356992 bytes
Database mounted.
Database opened.
SQL>
9. Now ensure that the parameter control_files is point at your new locations. Which by the it is because oracle startup did not fail on you.
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ———————-
control_files string /u05/oradata/Db1
/control01.ctl, /u0
2/oradata/Db1/contr
ol02.ctl, /u03/oradata
/Db1/control03.ctl
10. Now we have to recreate our spfile to allow for dynamic parameter changes. First at the OS level rename your old spfile.
mylinux# mv spfileDb1.ora spfileDb1.bak
mylinux#
11. Logon to Oracle as sysdba and recreate a new spfile.
mylinux# sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 11 12:54:22 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected.
SQL> create spfile=’/u01/opt/app/oracle/product/10.2.0/Db_1/dbs/spfileDb1.ora’
2 from pfile=’/u01/opt/app/oracle/product/10.2.0/Db_1/dbs/pfileDb1_a.ora’;
File created.
SQL>
12. Shutdown your oracle database immediately.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
13. Restart your oracle database without the pfile clause.
SQL> startup open
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2028568 bytes
Variable Size 201329640 bytes
Database Buffers 104857600 bytes
Redo Buffers 6356992 bytes
Database mounted.
Database opened.
SQL>
And that is it!