Move or rename oracle control files

posted Sep 8, 2010, 8:43 AM by Sachchida Ojha
1. Get the current location of the control file.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/MJBDB/oradata/mjb_ctl01.ctl
/u03/MJBDB/oradata/mjb_ctl02.ctl
/u04/MJBDB/oradata/mjb_ctl03.ctl
In order to rename or move these files we must alter the value of the control_files  instance parameter.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/MJBDB/oradata/mjb_ctl
                                                 01.ctl, /u03/MJBDB/oradata/
                                                 mjb_ctl02.ctl, /u04/MJBDB/
                                                 oradata/mjb_ctl03.ctl
To move or rename a controlfile do the following:
* Alter the control_files parameter using the ALTER SYSTEM comamnd.
* Shutdown the database.
* Rename the physical file on the OS.
* Start the database.
 
                                               
SQL> ALTER SYSTEM SET control_files='/u02/MJBDB/oradata/rename_mjb_ctl01.ctl', '/u03/MJBDB/oradata/mjb_ctl02.ctl','/u04/MJBDB/oradata/mjb_ctl03.ctl' SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST mv /u02/MJBDB/oradata/mjb_ctl01.ctl /u02/MJBDB/oradata/rename_mjb_ctl01.ctl
SQL> STARTUP
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/MJBDB/oradata/rename_mjb_ctl01.ctl
/u03/MJBDB/oradata/mjb_ctl02.ctl
/u04/MJBDB/oradata/mjb_ctl03.ctl
Recreating the Controlfile
SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>
The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.
This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.
Comments