How to move datafiles to another location

Sometimes a DBA might need to move database datafiles from one location to another. There can be various reasons for this movement :-

1. Change/Rectify incorrect naming of the datafiles.
2. I/O balancing on File systems due to performance reasons.

Datafiles can be moved to another location in two ways :-

1. Database is in online mode :-

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE

SQL> alter database datafile '/disk2/oracle/oradata/amit/amit/test.dbf' offline;

Database altered.


SQL> !
$ bash
bash-3.2$ cd /disk2/oracle/oradata/amit/amit/
bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit
$
$ exit

SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> recover datafile '/disk1/oracle/oradata/amit/amit/test.dbf';
Media recovery complete.
SQL>
SQL> alter database datafile '/disk1/oracle/oradata/amit/amit/test.dbf' online;

Database altered.

Note :- IN online mode we cannot rename or move system datafiles. To do that PFB link :-
Renaming/Moving System datafiles

2. Database is in offline mode :-

SQL> select open_mode from v$database;

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !

$ bash

bash-3.2$ cd /disk2/oracle/oradata/amit/amit/

bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit

bash-3.2$

bash-3.2$

bash-3.2$ exit

exit

$

$ exit

SQL> startup mount

ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.


SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> alter database open;

Database altered.


I hope this article helped you.

Regards,
Amit Rath