Change Control File Location
Here are the steps to change the location of control file:
- Stop Database Service
- Copy Control File to New Location
- Create PFILE from Current SPFILE
- Modify Control File Location in PFILE
- Restore SPFILE from Modified PFILE
- Startup NOMOUNT to Check New Setting
- Open Database
If you'd like to move SPFILE as well in your plan, I suggest that you should move SPFILE before moving control files.
1. Stop Database Service
Moving a control file requires the database service to be stopped, so that the control file stops going further.
SQL> shutdown immediate;
2. Copy Control File to New Location
Now we can copy the cold control file from the original location to the new one.
[oracle@test ~]$ cp -p /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl /oracle/oradata/ORCL/control02.ctl
[oracle@test ~]$ ll /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
-rw-r-----. 1 oracle oinstall 9781248 Feb 21 21:18 /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
[oracle@test ~]$ ll /oracle/oradata/ORCL/control02.ctl
-rw-r-----. 1 oracle oinstall 9781248 Feb 21 21:18 /oracle/oradata/ORCL/control02.ctl
Good! The above two files are the same.
3. Create PFILE from Current SPFILE
The parameter CONTROL_FILES that we are going to modify is a static one, which means you can only change it in PFILE.
SQL> create pfile from spfile;
PFILE is created or overwritten if file exists.
4. Modify Control File Location in PFILE
[oracle@test ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
The original value has been discarded. Instead, the new location are going to take over.
5. Restore SPFILE from Modified PFILE
SQL> create spfile from pfile;
6. Startup NOMOUNT to Check New Setting
We have to know the new location of the control file reflects to the instance, so we startup nomount in order to check the new setting.
SQL> startup nomount;
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCL/c
If everything is ready, we are going to open the database.
7. Open Database
SQL> alter database mount;
SQL> alter database open;
In addition to move control files, I think you might want to know about How to Move Data Files to Another Place.
A more complete solution to move everything in the same machine, you may read: How to Move Oracle Database