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>
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!!