Changing location control files in Oracle

Hello everyone, we are going to comment on one of the actions that as DBAs we will have to perform sometime if we work in Oracle database environments, the change of location of the control files of the Database. To do this in production environments you have to be careful, because it requires restarting 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” statement, 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 to 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>

Changing the location of control files

Once we know the location of the files, to change the location of the same, we must change the parameter control_files, but only in spfile so that the changes begin to work after the restart. To do this operation it is necessary to 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 database 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 Oracle gpsos control files

Starting the instance

When the files have been copied, it must be checked that the user who owns the installation has permissions on the drive, since the next step is to lift the instance, and will already 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>

To finish, it is necessary to 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 you found it useful.

Greetings.

Database computer

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 only one email per month.

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

Tags: No tags

Comments are closed.