Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/oracle/TDB/controlfile/TDB01.ctl
/oracle/TDB/controlfile/TDB02.ctl
/oracle/TDB/controlfile/TDB03.ctl

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps

•  Alter the control_files parameter

ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;

• Shutdown the database and copy or move the files to new location

SQL> SHUTDOWN IMMEDIATE
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl

• Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/dbf1/TDB/controlfile/TDB01.ctl
/dbf2/TDB/controlfile/TDB02.ctl'
/dbf3/TDB/controlfile/TDB03.ctl'

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

Follow the below steps to move or rename a Redo log file

• Shutdown the Db and rename the file at operating system

SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo

• Start the database in mount mode and ALTER DATABASE RENAME FILE

SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;

• Open the database and verify

SQL> Alter database open;
SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.

• You can check the redo status from the V$log view

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE

• Drop and recreate the redo log group with new location

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;

• Switch the log file to change the current redo and recreate the other redo groups

SQL> Alter system switch log file;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards.

http://oracleracexpert.com, Oracle ACE