How to Relocate Oracle datafiles
By glennf on Jun 08, 2004
From time to time it is necessary to move the datafile location for Oracle.
SQL> startup mount; SQL> alter database backup controlfile to trace; SQL> shutdown immediate;
This will dump the control file to a trace file located in the UDUMP destination directory. The tracefile will contain commands to recreate the control file with or without reset logs. Edit this file to include the new location of the datafiles then move the datafiles to that location. The SQL will look something like:
STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "IGEN10G" NORESETLOGS NOARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 4 MAXLOGHISTORY 453 LOGFILE GROUP 1 '/log/igen10g/log01.dbf' SIZE 2000M, GROUP 2 '/log/igen10g/log02.dbf' SIZE 2000M -- STANDBY LOGFILE DATAFILE '/data1/igen10g/sys01.dbf', '/data1/igen10g/undo01.dbf', '/data1/igen10g/sysaux.dbf', '/data1/igen/data01.dbf', '/data2/igen/data02.dbf', '/data2/igen/index01.dbf' CHARACTER SET US7ASCII ;
Now you should be able to start the database as always.