Change location of control files in Oracle

Hello everyone, we are going to comment on one of the actions that as DBAs we are going to have to perform sometime if we work in database environments Oracle , the change of location of the control files of the Database. To do this in production environments, care must be taken, because it requires a restart of the instance, and the affected applications that connect to the database must be stopped, so there may be a service outage.

Current location of control files

The first step in the process is to know the current location of the control files. This can be done in two ways:

Executing the “show parameter” instruction, since the control files are configured as a parameter in the spfile.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\oracle\oradata\BBDD\
					         control01.ctl, C:\oracle\
						 oradata\BBDD\control02.ctl
SQL>

You can also know the location by running a query on the v $ spparameter view

SQL> select name, value from v$spparameter where name = 'control_files';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
control_files
C:\oracle\oradata\BBDD\control01.ctl

control_files
C:\oracle\oradata\BBDD\control02.ctl

SQL>

Change location of control files

Once we know the location of the files, to change the location of the files, you have to change the control_files parameter, but only in spfile so that the changes start working after the reboot. To do this, you must have administrator privileges.

SQL> alter system set control_files='E:\ORADATA\BBDD\Controlfile\control01.ctl','E:\ORADATA\BBDD\Controlfile\control02.ctl' scope=spfile;

Sistema modificado.

SQL>

Once the parameter is configured, you have to stop the DB and move the files to the new location.

SQL> shutdown immediate
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
Change location of control files Oracle gpsos

Instance start

When the files have been copied, check that the user who owns the installation has permissions on the unit, since the next step is to raise the instance, and it will use the files in the new location.

SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 5167382528 bytes
Fixed Size                  8757568 bytes
Variable Size            1073745600 bytes
Database Buffers         4076863488 bytes
Redo Buffers                8015872 bytes
Base de datos montada.
Base de datos abierta.
SQL>

Finally, check that the control files of the new location are being used.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      E:\ORADATA\BBDD\Controlfile\cont
		                                 rol01.ctl, E:\ORADATA\BBDD\Contr
                                                 olfile\control02.ctl
												 
SQL>

We hope it has been useful to you.

Greetings.

Database Team

If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter . We will keep you informed with a single email per month.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

If you have questions about its administration or related to Oracle or SQL Server, do not hesitate to contact us that we will be happy to help !!