How to Change Control File Location in Oracle

  • by

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.

  1. Change Control File Location of a Single-instance Database
  2. Change Control File Location of a RAC Database

A. Change Control File Location of a Single-instance Database

There're several ways to copy control files and change its location.

  1. Recovery Manager (RMAN)
  2. Parameter File (PFILE)
  3. Symbolic Links

1. Use 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/init@.ora-new' 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;

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.

2. Use 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

3. Use 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/init@.ora-new' 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.

B. Change Control File Location of a RAC Database

There're several ways to copy control files and change its location.

  1. Recovery Manager (RMAN)
  2. ASMCMD Copy

1. Use 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
  • Change Location of 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 by creating a new PFILE.

SQL> create pfile='?/dbs/init@.ora-new' 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.

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!

2. 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/init@.ora-new' 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.

Leave a Reply

Your email address will not be published. Required fields are marked *