Renaming_moving Data Files, Control Files, And Online Redo Logs

Renaming / Moving Data Files, Control Files, and Online Redo Logs by Jeff Hunter, Sr. Database Administrator

Contents 1. 2. 3. 4. 5.

Overview Moving Datafiles while the Instance is Mounted Moving Datafiles while the Instance is Open Moving Online Redo Log Files Moving Control Files

Overview Once a data file has been created in the database, it may be necessary to move it in order to better manage its size or I/O requirements. This article will provide several methods used by DBAs for moving datafiles, online redo log files and control files. In all of these methods, operating system commands are used to move the files while the Oracle commands serve primarily to reset the pointers to those files. There are two methods for moving / renaming physical database files within Oracle. The first is to shut the database down, move (or rename) the file(s) using O/S commands, and finally, use the ALTER DATABASE command to reset the pointers to those files within Oracle. The second method can be done while the database is running and uses the ALTER TABLESPACE command. The tablespace will need to be taken offline during the time the file(s) are being moved or renamed. Once the files are moved (or renamed), use the ALTER TABLESPACE command to reset the pointers within Oracle and finally, bring the tablespace back online. This method only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments. Following is an example of how to manipulate datafiles in a tablespace using both the alter database method and the alter tablespace method. All examples will use an Oracle9i databse (9.2.0.5.0) running on Sun Solaris 2.9.

Moving Datafiles while the Instance is Mounted Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down. A summary of the steps involved follows: 1. Shutdown the instance 2. Use operating system commands to move or rename the files(s). 3. Mount the database and use the ALTER DATABASE to rename the file within the database. 4. Opening the Database % sqlplus "/ as sysdba" SQL> shutdown immediate SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf SQL> startup mount SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf'; Do not disconnect after this step. Stay logged in and proceed to open the database! SQL> alter database open; SQL> exit

Moving Datafiles while the Instance is Open Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running. A summary of the steps involved follows: 1. Take the tablespace OFFLINE. 2. Use operating system commands to move or rename the file(s). 3. Use the ALTER TABLESPACE command to rename the file within the database. 4. Bring the tablespace back ONLINE. NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments. % sqlplus "/ as sysdba" SQL> alter tablespace INDX offline;

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf SQL> alter tablespace INDX 2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf'; Do not disconnect after this step. Stay logged in and proceed to bring the tablespace back online! SQL> alter tablespace INDX online; SQL> exit

Moving Online Redo Log Files Online redo log files may be moved while the database is shutdown. Once renamed (or moved) the DBA should use the ALTER DATABASE command to update the data dictionary. A summary of the steps involved follows: 1. Shutdown the instance 2. Use operating system commands to move the datafile. 3. Mount the database and use ALTER DATABASE to rename the log file within the database. 4. Opening the Database % sqlplus "/ as sysdba" SQL> shutdown immediate SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log SQL> startup mount SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03a.log' to '/u03/app/oradata/ORA920/redo_g03a.log'; SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to '/u04/app/oradata/ORA920/redo_g03b.log'; SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to '/u05/app/oradata/ORA920/redo_g03c.log'; Do not disconnect after this step. Stay logged in and proceed to open the database! SQL> alter database open; SQL> exit

Moving Control Files The following method can be used to move or rename a control file(s). A summary of the steps involved follows: 1. 2. 3. 4.

Shutdown the Instance Move the Control File Edit the init.ora Startup the Instance

% sqlplus "/ as sysdba" SQL> shutdown immediate SQL> !mv /u06/app/oradata/ORA920/control01.ctl /u03/app/oradata/ORA920/control01.ctl SQL> !mv /u06/app/oradata/ORA920/control02.ctl /u04/app/oradata/ORA920/control02.ctl SQL> !mv /u06/app/oradata/ORA920/control03.ctl /u05/app/oradata/ORA920/control03.ctl Within the init.ora file, there will be an entry for the "control_files" parameter. Edit this entry to reflect the change(s) made to the physical control file(s) moved in the previous example. ... control_files ...

= (/u03/app/oradata/ORA920/control01.ctl, /u04/app/oradata/ORA920/control02.ctl, /u05/app/oradata/ORA920/control03.ctl)

SQL> startup open SQL> exit