Move Oracle data files

The procedure below will allow you to move Oracle data and redo files.

1. It is a good idea to make a backup of the directory you are moving just in case something goes horribly wrong. 🙂
# cp -rp {data_dir} {backup_dir}

2. log in as the oracle user
# su -l oracle

3. move the database files
# mv {data_dir} {new_data_dir}

4. Set SID, log into database.
# sqlplus /nolog
SQL> connect /as sysdba;

5. Create pfile from spfile
SQL> create pfile from spfile;

6. In another terminal, modify the newly created pfile. It is usually called init{SID}.ora and can be in different directories. Look in udump, dbs, and other directories until you find the newly created pfile. Check the date to make sure it is the one you are looking for. Now, fix the directories for the control_files variable.

7. Go back to the original terminal and create spfile from pfile;
SQL> create spfile from pfile;

8. Mount database.
SQL> startup mount;

9. Alter the database to look in the new location for the data files and redo logs. Below is an example. (Do this for all data and redo logs.)
SQL> alter database rename file ‘/home/oracle/oradata/{SID}/sysaux01.dbf’ to ‘/usr/local/app/oracle/oradata/{SID}/sysaux01.dbf’
SQL> alter database rename file ‘/home/oracle/oradata/{SID}/redo01.log’ to ‘/usr/local/app/oracle/oradata/{SID}/redo01.log’

10. Open database.
SQL> alter database open;

Now it should hopefully come up properly. You man want to shut it down and bring it up once more to make sure everything is working properly.