Moving Datafiles to new location

1. Make datafile backups as copy

run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%FU';
backup as copy datafile 1 format '/oracle/oradata/test/system01.dbf';
backup as copy datafile 2 format '/oracle/oradata/test/undotbs01.dbf';
backup as copy datafile 3 format '/oracle/oradata/test/sysaux01.dbf';
backup as copy datafile 4 format '/oracle/oradata/test/users01.dbf';
backup as copy datafile 5 format '/oracle/oradata/test/gravelsox.dbf';
}

2. shutdown immediate

3. startup mount

4. switch data file to copy

switch datafile 1 to copy;
switch datafile 2 to copy;
switch datafile 3 to copy;
switch datafile 4 to copy;
switch datafile 5 to copy;

5. recover the datafiles

recover datafiles 1,2,3,4,5;

6. open the database
7. Add new tmep file to the new datafile location and drop the old tempfile

alter tablespace temp add tempfile.....
alter tablespace temp drop tempfile

8. delete old datafile copies

delete datafilecopy all;

Following sqls will be helpful in generating the above commands

select 'backup as copy datafile '||file#||' format '''||replace(name,'old_location','new_location')||''';' from v$datafile;

select 'switch datafile '||file#||' to copy;' from v$datafile;

Above method is useful even when datafiles reside in ASM. There's another easier way if operating system level file movement is possible.

1. Shutdown immediate;

2. copy the datafiles to new location using OS utilities (such as cp in linux)

3. startup mount;

4. switch the datafile locations

alter database rename file '/oradata/livestg/system01.dbf' to '/u1/oradata/livestg/system01.dbf';
alter database rename file '/oradata/livestg/undotbs01.dbf' to '/u1/oradata/livestg/undotbs01.dbf';
alter database rename file '/oradata/livestg/sysaux01.dbf' to '/u1/oradata/livestg/sysaux01.dbf';
alter database rename file '/oradata/livestg/users01.dbf' to '/u1/oradata/livestg/users01.dbf';

5. alter database open;

Following sqls will be useful in generating the above commands

select 'alter database rename file '''||name||''' to '''||replace(name,'oracle','u1')||''';' as sql from v$datafile;

select 'alter database rename file '''||name||''' to '''||replace(name,'oracle','u1')||''';' as sql from v$tempfile;