Move and Rename Datafile on Oracle Database

As the Database Administrator, you might want to move datafile from one location (path/directory/file system) to other rename the datafile in order to reorganize the datafiles/files structure on your database. Basically, moving database as same as renaming the datafile itself. It’s analogically same with “mv” command in Unix OS that can be use for both moving file and renaming file.

Here’s the step if we want to move/rename datafile from ‘/mnt1/oradata/ins1/data01.dbf’ to ‘/mnt2/oradata/ins1/data01.dbf’

In the NOARCHIVELOG Database

1. Shutdown the database

   SQL> shutdown immediate

2. Move the datafile. You may use this command in Unix

   mv /mnt1/oradata/ins1/data01.dbf /mnt2/oradata/ins1/data01.dbf

3. Startup mount the database

    SQL> startup mount

4. Rename datafile in the database level

   SQL> alter database rename file '/mnt1/oradata/ins1/data01.dbf' to '/mnt2/oradata/ins1/data01.dbf';

5. Last, open the database

   SQL> alter database open;

In the ARCHIVELOG database:

1. Make the datafile offline

   SQL> alter database datafile '/mnt1/oradata/ins1/data01.dbf' offline;

2. Move the datafile. You may use this command in Unix

   mv /mnt1/oradata/ins1/data01.dbf /mnt2/oradata/ins1/data01.dbf

3. Rename datafile in the database level

   SQL> alter database rename file '/mnt1/oradata/ins1/data01.dbf' to '/mnt2/oradata/ins1/data01.dbf';

4. Recover datafile that has been renamed

   SQL> recover datafile '/mnt2/oradata/ins1/data01.dbf';

5. Last, make the renamed datafile online

   alter database datafile '/mnt2/oradata/ins1/data01.dbf' online;

For your notes :
In the NONARCHIVELOG database, you may not be able to access the database because the database has been shutdown (there’s a downtime)
In the ARCHIVELOG database, the database itself still can be accessed & queried expect for data that is stored in those particular datafile. For example if tablespace DATA have three datafiles (data01.dbf, data02.dbf & data03.dbf) then we can’t access the data that is stored in the data01.dbf, but we can still access/query data on datafile data02.dbf or data03.dbf.