Oracle: How to move data files from c:\ to d:\

Some times you may want to move your oracle data files from c:\ to another drive due to lack of space. Here is how you do it. In my example, my database is SUPPDB and it was created on default c:\



Connect as sys

Alter database backup controlfile to trace;

Go to udump folder (under admin\database_name\udump),open the latest *.trc files
and you will find 2 sets of sql's to recreate the control files.

Use option 1, if you have all your online redo log files

use option 2, if you don't have all your online redo log files.

Stop all services (like dbconsole ) accessing oracle database

Shutdown;
--wait for shutdown to complete.This way you ensure your online log files are up to date.Do not issue shutdown immediate or shutdown abort)

Now, move all your database files (like SYSTEM01.DBF,UNDOTBS01.DBF,SYSAUX01.DBF,USERS01.DBF,REDO01.LOG tablspace files )to the destination directory. In my example, I simply copied the files from c:\ to d:\...

Change the path for the above files in the create controlfile statement as shown
below.

Connect as sys

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "SUPPDB" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 'D:\Oracle\SUPPDB\Oradata\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\Oracle\SUPPDB\Oradata\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\Oracle\SUPPDB\Oradata\REDO03.LOG' SIZE 50M
DATAFILE
'D:\Oracle\SUPPDB\Oradata\SYSTEM01.DBF',
'D:\Oracle\SUPPDB\Oradata\UNDOTBS01.DBF',
'D:\Oracle\SUPPDB\Oradata\SYSAUX01.DBF',
'D:\Oracle\SUPPDB\Oradata\USERS01.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_INDX.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_INDX.DBF'
CHARACTER SET WE8MSWIN1252;

RECOVER DATABASE;
--It's ok to get an ora message saying no recovery needed, its not an error message.
ALTER DATABASE OPEN;
--Create a new TEMPFILE
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oracle\SUPPDB\Oradata\TEMP01.DBF'
SIZE 271581184 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

If you choose option 2

Connect as sys

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "SUPPDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 'D:\Oracle\SUPPDB\Oradata\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\Oracle\SUPPDB\Oradata\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\Oracle\SUPPDB\Oradata\REDO03.LOG' SIZE 50M
DATAFILE
'D:\Oracle\SUPPDB\Oradata\SYSTEM01.DBF',
'D:\Oracle\SUPPDB\Oradata\UNDOTBS01.DBF',
'D:\Oracle\SUPPDB\Oradata\SYSAUX01.DBF',
'D:\Oracle\SUPPDB\Oradata\USERS01.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_INDX.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_INDX.DBF'
CHARACTER SET WE8MSWIN1252;

RECOVER DATABASE USING BACKUP CONTROLFILE;
--At this point, I was prompted to enter my archive log files which doesn't exist.
--I pointed to my redo02.log file and it solved the problem. You may have to try all --the 3 Redo log files one by one

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\SUPPDB\TEMP01.DBF'
SIZE 271581184 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;