How to Move a Datafile to Different Location

If the database is in archivelog mode, you can move the datafile while database is up. Take the datafile offline, copy (or move) the datafile to the new location:

ALTER DATABASE DATAFILE '/old_path/name_of_datafile' OFFLINE
!mv /old_path/name_of_file /new_path/name_of_file

1 2	ALTER DATABASE DATAFILE '/old_path/name_of_datafile' OFFLINE !mv /old_path/name_of_file /new_path/name_of_file

Then issue the following commands to rename the datafile and make it online:

ALTER DATABASE RENAME FILE '/old_path/name_of_datafile' TO '/new_path/name_of_file';
RECOVER DATAFILE '/new_path/name_of_file';
ALTER DATABASE DATAFILE '/new_path/name_of_file' ONLINE;

ALTER DATABASE RENAME FILE '/old_path/name_of_datafile' TO '/new_path/name_of_file';

RECOVER DATAFILE '/new_path/name_of_file';

ALTER DATABASE DATAFILE '/new_path/name_of_file' ONLINE;

If the database is in no archivelog mode, you need to shutdown the database, open the database in mount mode and copy/move the datafile:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
!mv /old_path/name_of_file /new_path/name_of_file

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

!mv /old_path/name_of_file /new_path/name_of_file

Then rename the file and open the database:

ALTER DATABASE RENAME FILE '/old_path/name_of_datafile' 
TO '/new_path/name_of_file';
ALTER DATABASE OPEN;

ALTER DATABASE RENAME FILE '/old_path/name_of_datafile'

TO '/new_path/name_of_file';

ALTER DATABASE OPEN;

Please share

3 Comments

Dilawar

May 2, 2015 Reply

Good & Easy step

I am newbie. Is this step applicable to 11gR2 & 12c

Please reply me on my mail also

Thanks
- Gokhan Atil
  
  May 2, 2015
  
  Dilawar,
  
  In 12c, you can use “ALTER DATABASE MOVE DATAFILE ‘old_full_path’ TO ‘new_full_path'” to move datafiles online. It doesn’t need to recover the file after movement and there’s no need to move the file with OS copy commands;
Quinc

June 28, 2018 Reply

Is it possible to manually move these files to a directory with the same name and then remove the original directory? I am trying to move from a failing san to a windows share.

Thanks!