How to change Control File Location with Different Name in 12c

In this blog we will be discussing about how to change the control file location, while changing the location we can also change their names.

This example has been tested in 12cR1 (12.1.0.2), however it will also work in previous versions.

Find out the current control file location, make sure the current container is root, however you can also find this location from pluggable container:

SQL> select name from v$controlfile;

NAME
———————————————————————–
/u01/app/oracle/oradata/CDB2/controlfile/o1_mf_cgf7hgn8_.ctl
/u01/app/oracle/fast_recovery_area/CDB2/controlfile/o1_mf_cgf7hgq2_.ctl

SQL>

Check out the current parameter being used:

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ————————–
spfile string /u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilecdb3.ora

SQL>

Create directories for new location where we would like to keep those new control file, we are choosing name as an cdb3, because in our last post we renamed our database from cdb2 to cdb3:

[oracle@oracle12c CDB2]$ mkdir -p /u01/app/oracle/oradata/cdb3/controlfile
[oracle@oracle12c CDB2]$ mkdir -p /u01/app/oracle/fast_recovery_area/cdb3/controlfile

Now try to  change the location while your database is up, you see below we cant change the location in spfile while our database is in the pluggable container (ORA-65040: operation not allowed from within a pluggable database), we have to be in the root container to change the controlfile location:

SQL> alter system set control_files=’/u01/app/oracle/oradata/cdb3/controlfile/control01.ctl’,’/u01/app/oracle/fast_recovery_area/cdb3/controlfile/control02.ctl’ scope=spfile;
alter system set control_files=’/u01/app/oracle/oradata/cdb3/controlfile/control01.ctl’,’/u01/app/oracle/fast_recovery_area/cdb3/controlfile/control02.ctl’ scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL>
SQL> show con_name

CON_NAME
——————————
PDB1

Change the container to root to have the above operation successful:

SQL> alter session set container=cdb$root;

Session altered.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

SQL>

Change the location finally now:

SQL> alter system set control_files=’/u01/app/oracle/oradata/cdb3/controlfile/control01.ctl’,’/u01/app/oracle/fast_recovery_area/cdb3/controlfile/control02.ctl’ scope=spfile;

System altered.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

After you change the location in spfile, we must need to keep the files on the new location, so that once you start the instance, Database is able to find the files on the specific location.

Copy or Move the files from the existing control file to the new location, just see we are also changing the name of controlfile:

[oracle@oracle12c CDB2]$ cp /u01/app/oracle/oradata/CDB2/controlfile/o1_mf_cgf7hgn8_.ctl /u01/app/oracle/oradata/cdb3/controlfile/control01.ctl
[oracle@oracle12c CDB2]$ cp /u01/app/oracle/fast_recovery_area/CDB2/controlfile/o1_mf_cgf7hgq2_.ctl /u01/app/oracle/fast_recovery_area/cdb3/controlfile/control02.ctl

Now startup the instance and you will be able to find the new control file location at a new place:

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 2927528 bytes
Variable Size 671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL>
SQL> select name from v$controlfile;

NAME
————————————————————————–
/u01/app/oracle/oradata/cdb3/controlfile/control01.ctl
/u01/app/oracle/fast_recovery_area/cdb3/controlfile/control02.ctl

SQL>

We are good. Yo.

Thanks for watching.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s