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;

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$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).

 

Reference : http://www.orafaq.com/wiki/Move_datafile_to_different_location

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s