Move datafiles 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 etc. In this example we are using non-asm and windows environment. ASM would only use +DATA1 +DATA2 tag etc and for any unix os it will be “/” instead of windows tag 🙂

1. Select the current location of the datafile’s from

SQL>  SELECT NAME FROM V$DATAFILE;

2. Bring the tablespace offline

SQL> ALTER TABLESPACE USERS OFFLINE;

3.  Copy the files using OS command. In windows you can simply do copy paste to the new folder

4. Rename  datafile to new location to put  new information in the control file (Control file holds the structure of the database )

SQL> ALTER DATABASE RENAME DATAFILE ‘E:\APP\PSECC\ORADATA\SWX\SWX01.DBF’  TO  ‘F:\APP\PSECC\ORADATA\SWX\SWX01.DBF’

5. Bring the tablespace online

SQL> ALTER TABLESPACE USERS ONLINE;

Optionally if you want to delete the old files from the OS. Shutdown the database and delete the old files and bring up the database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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