Relocate Database Files

Three files is called database files
1. Datafiles & Tempfiles
2. Control files
3. Redo log files

Sometime we need to move database files from old to new location.

How to move DATAFILES from old to new location
Except SYSTEM,UNDO & TEMP tablespace

Step:
1. Take tablespace offline
alter tablespace tbsname OFFLINE;

2. through OS command MOVE datafiles from old to new location.
mv 'old location' to 'new location'

3. rename datafiles in database.
alter database rename file 'old location' to 'new location';

4. Take tablespace ONLINE
alter tablespace tbsname ONLINE;


For SYSTEM or UNDO tablespace

Step:
1. shutdown database
shutdown immediate;

2. move datafile old to new location through OS command.
mv old location new location

3. startup database with mount stage.
startup mount;

4. rename datafile in database
alter database
rename file 'old location' to 'new location';

5. open database for normal use
alter database open;


We can't relocate tempfile, if we need to relocate tempfile then best option is recreate temp tablespace with new location.

How to move REDO LOG MEMBER from old to new location

Step:
1. shutdown database
shutdown immediate;

2. mv redo log member from old to new location
mv oldlocation newlocation

3. startup database with mount stage
startup mount;

4. rename redo log member in database
alter database rename file 'old location' to 'new location';

5. open database for normal use
alter database open;

How to move control file old to new location

Step:
1. shutdown database
shutdown immediate;

2. mv controlfile to old to new location
mv old location new location

3. edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'

4. recreate SPFILE from PFILE
create spfile from pfile;

5. startup database
startup;