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