Moving Oracle control files in oracle 10g

Method 1:
********

If using a pfile, you need not to bring the database into the nomount state to change the control_files parameter.
Edit the pfile and the change the coltrol file location and save it


[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 18:16:26 2016

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

SQL> set line 10000 pages 10000 time on timing on
18:16:39 SQL>
18:16:39 SQL> select * from v$controlfile;

STATUS  NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------------------------
        /u01/oradata/primdb/control01.ctl NO        16384            416
        /u01/oradata/primdb/control02.ctl NO        16384            416

Elapsed: 00:00:00.29
18:16:47 SQL> !
[oracle@localhost ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg00-lvol_root
                      9.7G  3.7G  5.6G  40% /
/dev/mapper/vg00-lvol_home
                      9.7G  152M  9.1G   2% /home
/dev/mapper/vg00-lvol_usr
                       15G  2.7G   12G  20% /usr
/dev/mapper/vg00-lvol_var
                      4.9G  219M  4.4G   5% /var
/dev/hdc1              99M   12M   82M  13% /boot
tmpfs                 2.0G     0  2.0G   0% /dev/shm
/dev/mapper/vg00-u01   69G   12G   54G  18% /u01
/dev/mapper/vg00-u02  197G -3.5G  191G   -  /u02
/dev/mapper/vg00-u03  119G   11G  102G  10% /u03
[oracle@localhost ~]$ cd /u01/oradata/primdb/
[oracle@localhost primdb]$ ls -lrt
total 9562416
-rw-r----- 1 oracle oinstall   52429312 Sep 30 14:01 redo04.log
-rw-r----- 1 oracle oinstall 3221233664 Oct  1 06:00 temp.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct  3 18:01 sysaux.dbf
-rw-r----- 1 oracle oinstall 5368717312 Oct  3 18:16 undotbs.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct  3 18:16 system01.dbf
-rw-r----- 1 oracle oinstall   52429312 Oct  3 18:16 redo01.log
-rw-r----- 1 oracle oinstall    6832128 Oct  3 18:17 control02.ctl
-rw-r----- 1 oracle oinstall    6832128 Oct  3 18:17 control01.ctl
[oracle@localhost primdb]$ mv control02.ctl /u02/oradata/primdb/
[oracle@localhost primdb]$ ls -lrt
total 9555732
-rw-r----- 1 oracle oinstall   52429312 Sep 30 14:01 redo04.log
-rw-r----- 1 oracle oinstall 3221233664 Oct  1 06:00 temp.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct  3 18:01 sysaux.dbf
-rw-r----- 1 oracle oinstall 5368717312 Oct  3 18:16 undotbs.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct  3 18:16 system01.dbf
-rw-r----- 1 oracle oinstall   52429312 Oct  3 18:16 redo01.log
-rw-r----- 1 oracle oinstall    6832128 Oct  3 18:17 control01.ctl
[oracle@localhost primdb]$ exit
exit

[oracle@localhost ~]$ cd /u02/oradata/primdb/
[oracle@localhost primdb]$ ls -lrt
total 109204
-rw-r----- 1 oracle oinstall 52429312 Sep 30 13:56 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 30 14:01 redo05.log
-rw-r----- 1 oracle oinstall  6832128 Oct  3 18:17 control02.ctl
[oracle@localhost primdb]$ mv control02.ctl /u01/oradata/primdb/
[oracle@localhost primdb]$ ls -lrt
total 102520
-rw-r----- 1 oracle oinstall 52429312 Sep 30 13:56 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 30 14:01 redo05.log
[oracle@localhost primdb]$ exit
exit

18:18:34 SQL>
18:18:35 SQL> show parameter controlfile;
18:18:45 SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/oradata/primdb/control01.
                                                 ctl, /u01/oradata/primdb/contr
                                                 ol02.ctl
18:18:53 SQL> shutdown
 immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
18:19:20 SQL>
18:19:22 SQL>
18:19:22 SQL> !
[oracle@localhost ~]$ cd /u01/oradata/primdb
[oracle@localhost primdb]$ mv control02.ctl /u02/oradata/primdb/
[oracle@localhost primdb]$
[oracle@localhost primdb]$

SQL> shutdown
 immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019320 bytes
Variable Size             113246216 bytes
Database Buffers           50331648 bytes
Redo Buffers                2174976 bytes
Database mounted.
Database opened.
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/oradata/primdb/control01.
                                                 ctl, /u02/oradata/primdb/contr
                                                 ol02.ctl
SQL>


Method 2:
********

If using a pfile, you need  to bring the database into the nomount state to change the control_files parameter.
The parameter is changed using the alter system command and the instance must then be shut down and restarted for the new value to be loaded.


SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/oradata/primdb/control01.
                                                 ctl, /u01/oradata/primdb/contr
                                                 ol02.ctl
SQL> set line 10000 pages 10000 time on timing on
18:43:57 SQL>
18:43:57 SQL>
18:43:57 SQL>
18:43:57 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
18:44:24 SQL>
18:44:26 SQL>
18:44:26 SQL> !
[oracle@localhost primdb]$ mv /u01/oradata/primdb/control02.ctl /u02/oradata/primdb/control02.ctl
[oracle@localhost primdb]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 18:45:13 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019320 bytes
Variable Size             113246216 bytes
Database Buffers           50331648 bytes
Redo Buffers                2174976 bytes
SQL> alter system set control_files='/u02/oradata/primdb/control02.ctl' scope=SPFILE;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019320 bytes
Variable Size             113246216 bytes
Database Buffers           50331648 bytes
Redo Buffers                2174976 bytes
Database mounted.
Database opened.
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/oradata/primdb/control02.
                                                 ctl
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019320 bytes
Variable Size             113246216 bytes
Database Buffers           50331648 bytes
Redo Buffers                2174976 bytes
SQL> alter system set control_files='/u02/oradata/primdb/control02.ctl','/u01/oradata/primdb/control01.ctl' scope=SPFILE;

System altered.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/oradata/primdb/control02.
                                                 ctl, /u01/oradata/primdb/control02.ctl
SQL>