Relocating Database Files

The CRD&T files are called as Database Files:

1. Control files
2. Redo log files
3. Datafiles & Tempfiles


At some moments we need to move or relocate the Database files from original(old) location to new location.

STEPS FOR RELOCATING THESE DATABASE FILES:

I.How to move DATAFILES from old to new location(not SYSTEM,UNDO & TEMP tablespaces'):

Step 1: Take tablespace offline
SQL> alter tablespace tbsname OFFLINE;

Step 2:Use OS level command to MOVE datafiles from old to new location.
$ mv 'old location' to 'new location'

Step 3: Rename datafiles in database.
SQL> alter database rename file 'old location' to 'new location';

Step 4: Take tablespace ONLINE
SQL> alter tablespace tbsname ONLINE;


II.For SYSTEM or UNDO tablespace:

Step 1: Shutdown database
SQL> shutdown immediate;

Step 2: Move datafiles from old to new location using OS command.
$ mv 'old location' 'new location'

Step 3: Startup database in mount stage.
SQL> startup mount;

Step 4: Rename the datafile
SQL> alter database rename file 'old location' to 'new location';

Step 5: Open the database for normal use
SQL> alter database open;


III.How to move control file from old to new location:

Step 1: Shutdown the Database
SQL> shutdown immediate;

Step 2: Move controlfile from old to new location
$ mv old location new location

Step 3: Edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'

Step 4: recreate SPFILE from PFILE
SQL> create spfile from pfile;

Step 5: startup database
SQL> startup;

IV.How to move REDO LOG MEMBER from old to new location

Step 1: shutdown database
SQL> shutdown immediate;

Step 2: Move redo log member from old to new location
$ mv oldlocation newlocation

Step 3: startup database with mount stage
SQL> startup mount;

Step 4: rename redo log member in database
SQL> alter database rename file 'old location' to 'new location';

Step 5: open database for normal use
SQL> alter database open;


V. We cannot relocate the tempfile, if we need to relocate it(tempfile) then the best option is to recreate the temp tablespace in the new location.