ALTER SYSTEM SET CONTROL_FILES
In this post, I will show you how to change control file location in a single-instance and a cluster database.
- Change Control File Location of a Single-instance Database
- Change Control File Location of a RAC Database
Change Control File Location of a Single-instance Database
There're several ways to copy control files and change its location.
Recovery Manager (RMAN)
Here are the steps to change the location of control file for a single-instance database by RMAN:
- Check Current Control Files
- Backup SPFILE
- Change Location of Control Files
- Restart to NOMOUNT
- Restore Control Files
- Open Database
- Check Current Control Files Again
Check Current Control Files
Let's see the old location of control files.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCLCD
B/control01.ctl, /u01/app/orac
le/fast_recovery_area/ORCLCDB/
control02.ctl
There're two control files currently. At times, you may have no clue about it, you need to know where to find the location of control files in oracle.
Backup SPFILE
In case that we can't mount control files correctly, we should backup SPFILE by creating a new PFILE.
SQL> create pfile='?/dbs/[email protected]' from spfile;
File created.
RMAN> backup spfile;
Alter System Set Control_Files
We set the new location of control files with SCOPE=SPFILE.
RMAN> alter system set control_files='/oradata/ORCLCDB/control01.ctl','/oradata/ORCLCDB/control02.ctl' scope=spfile;
Restart to NOMOUNT
To make the above change take effect, we restart the instance to NOMOUNT.
RMAN> shutdown immediate;
RMAN> startup nomount;
Restore Control Files
Now the instance knows where the new location of control files are. We can restore them from one of old control files.
RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCLCDB/control01.ctl';
Starting restore at 31-DEC-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oradata/ORCLCDB/control01.ctl,/oradata/ORCLCDB/control02.ctl
Finished restore at 31-DEC-19
As we can see, RMAN restored control files to the new location. The key in this step is to use one of the original control file as a backup piece to restore the control files, which can guarantee that new control files are exactly the same as the old ones, not copied or backup ones. This is important.
Open Database
We mount the control files, then open the database.
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> alter database open;
Statement processed
The database is open without problem, which means, the new control files are all in good conditions.
Check Current Control Files Again
Let's make sure the new location of control files.
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCLCDB READ WRITE
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/ORCLCDB/control01.ctl
,/oradata/ORCLCDB/control02.ct
l
What the trick that RMAN did is to restore control files from an old control file.
Parameter File (PFILE)
Here are the steps to change the location of control file for a single-instance database by a parameter 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.
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;
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
You can also move the file to the new destination.
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;
File created.
PFILE is created or overwritten if file exists.
Modify Control File Location in PFILE
[oracle@test ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
...
#*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/oracle/oradata/ORCL/control02.ctl'
The original value has been discarded. Instead, the new location are going to take over.
Restore SPFILE from Modified PFILE
SQL> create spfile from pfile;
File created.
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
ontrol01.ctl, /oracle/oradata/
ORCL/control02.ctl
If everything is ready, we are going to open the database.
Open Database
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
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
Symbolic Links
Here are the steps to change the location of control file for a single-instance database by symbolic links:
- Check Current Control Files
- Backup SPFILE
- Change Location of Control Files
- Make Links
- Restart Database
- Check Current Control Files Again
Check Current Control Files
Let's see the old location of control files.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCLCD
B/control01.ctl, /u01/app/orac
le/fast_recovery_area/ORCLCDB/
control02.ctl
There're two control files currently.
Backup SPFILE
In case that we can't mount control files correctly, we should backup SPFILE by creating a new PFILE.
SQL> create pfile='?/dbs/[email protected]' from spfile;
File created.
And by RMAN.
RMAN> backup spfile;
Alter System Set Control_Files
We set the new location of control files with SCOPE=SPFILE.
RMAN> alter system set control_files='/oradata/ORCLCDB/control01.ctl','/oradata/ORCLCDB/control02.ctl' scope=spfile;
Make Links
Here we don't actually move control files, we make links for them.
[oracle@test ~]$ ln -s /u01/app/oracle/oradata/ORA19C1/control01.ctl /oradata/ORCLCDB/control01.ctl
[oracle@test ~]$ ln -s /u01/app/oracle/fast_recovery_area/ORA19C1/control02.ctl /oradata/ORCLCDB/control02.ctl
Restart Database
RMAN> shutdown immediate;
Please note that, you can't remove the original files since they are used by the links.
Check Current Control Files Again
RMAN> startup;
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/ORCLCDB/ORCLCDB/contr
ol01.ctl, /oradata/ORCLCDB/ORC
LCDB/control02.ctl
As long as you set the correct paths of control files, you can just restart the database to make it work.
Change Control File Location of a RAC Database
There're several ways to copy control files and change its location.
Recovery Manager (RMAN)
Here are the steps to change the location of control file for a RAC database by RMAN:
- Check Current Control Files
- Backup SPFILE
- Alter System Set Control_Files
- Restart to NOMOUNT
- Restore Control Files
- Open Database
- Check Current Control Files Again
If necessary, please make directory for the new destination. In this case, it's +DATA/TESTCDB.
[grid@primary01 ~]$ asmcmd mkdir +DATA/TESTCDB
Check Current Control Files
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/ORCLCDB/control01.ctl, +
DATA/ORCLCDB/control02.ctl
Currently, the control files are in +DATA/ORCLCDB directory, we'd like to put them in +DATA/TESTCDB.
Backup SPFILE
In case that we can't mount control files correctly, we should backup SPFILE in advance.
Alter System Set Control_Files
We set the new location of control files with SCOPE=SPFILE.
SQL> alter system set control_files='+DATA/TESTCDB/control01.ctl','+DATA/TESTCDB/control02.ctl' scope=spfile sid='*';
System altered.
Multiple control files are allowed to set in SPFILE, just be aware of the positions of single quotes and comma.
Restart to NOMOUNT
To make the above change take effect, we restart both nodes to NOMOUNT.
[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb -o nomount
Restore Control Files
Now the instance knows where the new location of control files. We can restore them from one of old control files.
[oracle@primary01 ~]$ rman target /
...
RMAN> restore controlfile from '+DATA/ORCLCDB/control01.ctl';
Starting restore at 31-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=TESTCDB1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/TESTCDB/control01.ctl
output file name=+DATA/TESTCDB/control02.ctl
Finished restore at 31-DEC-19
As you can see, we have copies of control files in the new location.
Restart Database
Let's see whether we can start it up normally or not.
[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb
Seems good.
Check Current Control Files Again
Let's make sure the new location of control files.
SQL> select inst_id, name, open_mode from gv$database;
INST_ID NAME OPEN_MODE
---------- --------- --------------------
2 ORCLCDB READ WRITE
1 ORCLCDB READ WRITE
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/TESTCDB/control01.ctl, +
DATA/TESTCDB/control02.ctl
Done!
Recovery Manager (RMAN) for Oracle Managed Files (OMF)
Since OMF follows system-generated naming rule, you have no way to know what files will be created in advance. Thereafter, you need another way to do it.
- Check Current Control Files
- Restart to NOMOUNT
- Restore Control Files
- Check Filenames
- Backup SPFILE
- Alter System Set Control_Files
- Open Database
- Check Current Control Files Again
Check Current Control Files
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/TESTCDB/CONTROLFILE/curr
ent.264.1693915171, +DATA/TEST
CDB/CONTROLFILE/current.263.16
93915171
Restart to NOMOUNT
To make the above change take effect, we restart both nodes to NOMOUNT.
[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb -o nomount
Restore Control Files
In this case, we'd like to move control files from +DATA to +DATA2. Since we have multiplexed control files, so we issue the same restore command twice.
[oracle@primary01 ~]$ rman target /
...
RMAN> restore controlfile to '+DATA2' from '+DATA/TESTCDB/CONTROLFILE/current.264.1693915171';
Starting restore at 07-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=252 instance=testcdb1 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 07-NOV-23
RMAN> restore controlfile to '+DATA2' from '+DATA/TESTCDB/CONTROLFILE/current.264.1693915171';
Starting restore at 07-NOV-23
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 07-NOV-23
Check Filenames
To know what files have been created, we check their names like this:
[grid@primary01 ~]$ asmcmd
ASMCMD> cd +DATA2
ASMCMD> find --type controlfile . *
+DATA2/TESTCDB/CONTROLFILE/current.363.1134103591
+DATA2/TESTCDB/CONTROLFILE/current.364.1134104389
ASMCMD> ls -l +DATA2/TESTCDB/CONTROLFILE/current.*
Type Redund Striped Time Sys Name
CONTROLFILE HIGH FINE NOV 07 09:00:00 Y current.363.1134103591
CONTROLFILE HIGH FINE NOV 07 09:00:00 Y current.364.1134104389
Backup SPFILE
In case that we can't mount control files correctly, we should backup SPFILE in advance.
Alter System Set Control_Files
We set the new location of control files with SCOPE=SPFILE.
SQL> alter system set control_files='+DATA2/TESTCDB/CONTROLFILE/current.363.1152263591','+DATA2/TESTCDB/CONTROLFILE/current.364.1152264389' sid='*' scope=spfile;
System altered.
Restart Database
Let's see whether we can start it up normally or not.
[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb
Seems good.
Check Current Control Files Again
Let's make sure the new location of control files.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA2/TESTCDB/CONTROLFILE/cur
rent.363.1134103591, +DATA2/TE
STCDB/CONTROLFILE/current.364.
1134104389
We made it.
In my opinion, OMF is convenient, but less controllable.
ASMCMD Copy
There's a drawback in this method, I'll talk about it later.
- Check Current Control Files
- Backup SPFILE
- Change Location of Control Files
- Shutdown Database
- Copy Control Files
- Startup Database
- Check Current Control Files Again
Check Current Control Files
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/ORCLCDB/control01.ctl, +
DATA/ORCLCDB/control02.ctl
Currently, the control files are in +DATA/ORCLCDB directory, we'd like to put them in +DATA/TESTCDB.
Backup SPFILE
In case that we can't mount control files correctly, we should backup SPFILE by creating a new PFILE.
SQL> create pfile='?/dbs/[email protected]' from spfile;
File created.
Just don't create a PFILE from memory in RAC systems.
And by RMAN.
RMAN> backup spfile;
Alter System Set Control_Files
We set the new location of control files with SCOPE=SPFILE.
SQL> alter system set control_files='+DATA/TESTCDB/control01.ctl','+DATA/TESTCDB/control02.ctl' scope=spfile sid='*';
System altered.
Multiple control files are allowed to set in SPFILE, just be aware of the positions of single quotes and comma.
Shutdown Database
[oracle@primary01 ~]$ srvctl stop database -d testcdb
Taking control files offline is for copying them in the next step.
Copy Control Files
We use the cp command in ASMCMD to copy them by grid.
[grid@primary01 ~]$ asmcmd cp +DATA/ORCLCDB/control01.ctl +DATA/TESTCDB/control01.ctl
copying +DATA/ORCLCDB/control01.ctl -> +DATA/TESTCDB/control01.ctl
[grid@primary01 ~]$ asmcmd cp +DATA/ORCLCDB/control02.ctl +DATA/TESTCDB/control02.ctl
copying +DATA/ORCLCDB/control02.ctl -> +DATA/TESTCDB/control02.ctl
Startup Database
We start the database normally.
[oracle@primary01 ~]$ srvctl start database -d testcdb
Check Current Control Files Again
Let's make sure the new location of control files.
SQL> select inst_id, name, open_mode from gv$database;
INST_ID NAME OPEN_MODE
---------- --------- --------------------
2 ORCLCDB READ WRITE
1 ORCLCDB READ WRITE
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/TESTCDB/control01.ctl, +
DATA/TESTCDB/control02.ctl
Let's check the actual files of them in disk group.
[grid@primary01 ~]$ asmcmd ls -l +DATA/TESTCDB/control*.ctl
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JAN 05 15:00:00 N control01.ctl => +DATA/ASM/CONTROLFILE/control01.ctl.311.1061047157
CONTROLFILE UNPROT FINE JAN 05 15:00:00 N control02.ctl => +DATA/ASM/CONTROLFILE/control02.ctl.312.1061047165
As you have noticed, the two control files fall into ASM's directory. It's a minor side effect of copying files in ASM, although the database starts up normally without problem.
Next, you may start to consider to change the location of redo logs online or offline.