Move datafile to different location

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards.

Choose one of the following procedures based on the log mode of your database (select log_mode from sys.v_$database):

Database is in ARCHIVELOG mode

• Take the datafile offline with the "ALTER DATABASE DATAFILE '/old/location' OFFLINE;" command.
• Copy or move the datafile to its new location. On Unix this can be done with the "dd" command.

Example:

dd if=/old/location of=/new/location bs=4096

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;

Database is in NOARCHIVELOG mode

• Shutdown the database
• Copy or move the datafile to its new location. On Unix this can be done with the "dd" command. Example:

dd if=/old/location of=/new/location bs=4096

• Start SQL*Plus, do a "STARTUP MOUNT" and rename the file:

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> ALTER DATABASE OPEN;

Note: '/old/location' and '/new/location' means the file name of the old and new location. Generic selection with an asterisk is unfortunately not possible.

Note: If you use new 3GB HDD and the physical block size is 4K - use fsutil to check - it is NOT possible to use REDO logs on this device. Rename fails with error ORA-01512 and a message, that the header couldn't read with ReadFile(). If this occurs, use another device which have a correct block size.

Check current location of datafiles

If the database is offline you can still check the current location of the datafiles and archive log mode.

SQL> select log_mode from v$database;
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;

Note that the location of data files (and other fixed tables/views) is stored in the control files, and the location of the control files is stored in the init file (pfile or spfile).