Move datafile to different location

From Oracle FAQ

Jump to: navigation, search

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):

[edit]

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;
[edit]

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;
Retrieved from "http://www.orafaq.com/wiki/Move_datafile_to_different_location"
Personal tools