Renaming / Moving Data Files, Control Files, and Online Redo Logs

Renaming / Moving Data Files, Control Files, and Online Redo Logs

*** Be sure that your database is in archivelog mode.
Overview


Once data files, online redo log files and control files has been created in the
database, it may be necessary to move them in order to better manage, size or I/O requirements. It also necessary when the file system (drive) has reached 85%-95% used. There are 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
2 '/u03/app/oradata/ORA920/redo_g03a.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to
2 '/u04/app/oradata/ORA920/redo_g03b.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to
2 '/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. Shutdown the Instance
2. Move the Control File
3. Edit the init.ora
4. 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