Easy steps for beginners to manually move Oracle database files, works from 8i upward.
Filesystem full ? Damaged on your hard drive disk ?
For a lot of reasons you could have to move your database from one filesystem to another. If you want to move only some files, this works for you too.
Here, we are going to move database files from drive C:\ to drive D:\, on directory D:\EASYDB.
You will need to cleanly shutdown your database to do that.
/!\ Just in case don’t forget to backup your database including controlfiles and spfile /!\
1 – Moving controlfiles

• Find controlfiles location:

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\oracle\EASYDB\CONTROL.DBF

• Create a pfile

SQL>create pfile='C:\initEASY.ora' from spfile;
File created

• Shutdown cleanly your database and move your controlfiles

SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
C:\Users\BSO> move C:\oracle\EASYDB\CONTROL.DBF D:\EASYDB\

• Edit pfile C:\initEASY.ora, and change controlfiles location

*.control_files='D:\EASYDB\CONTROL.DBF'

• Mount database using pfile

SQL> startup mount pfile='C:\initEASY.ora';

• Don’t forget to create spfile from pfile :

SQL> create spfile from pfile='C:\initEASY.ora';
File created.

2 – Moving logfiles

• Find logfiles location:

SQL> select member from v$logfile;
MEMBER
--------------------------------------------
C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG
C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG

• Keep your database MOUNTED and move files to D:\EASYDB

C:\Users\BSO> move C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG D:\EASYDB\
C:\Users\BSO> move C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG D:\EASYDB\

• Rename files on Oracle, using the ‘RENAME FILE’ command :

ALTER DATABASE RENAME FILE 'C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG' TO 'D:\EASYDB\O1_MF_1_8YLTNYXK_.LOG';
ALTER DATABASE RENAME FILE 'C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG' TO 'D:\EASYDB\O1_MF_2_8YLTNZQ5_.LOG';

• Check the v$logfile view to be sure it’s ok :

SQL> select member from v$logfile;
MEMBER
----------------------------------
D:\EASYDB\O1_MF_2_8YLTNZQ5_.LOG
D:\EASYDB\O1_MF_1_8YLTNYXK_.LOG

3 – Moving datafiles
Same as previous operations

• Find datafiles and tempfiles location:

SQL> SELECT name FROM v$datafile;
NAME
------------------------------------------------
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF
SQL> select FILE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF

• Shutdown your database and move datafiles

SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> exit
C:> move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF D:\EASYDB\SYSTEM.DBF;
C:> move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF D:\EASYDB\UNDOTBS1.DBF;
C:> move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF D:\EASYDB\SYSAUX.DBF;
C:> move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF D:\EASYDB\USERS.DBF;
C:> move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF D:\EASYDB\TEMP.DBF;

• Mount database and use the « RENAME FILE » command :

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1388352 bytes
Variable Size 666894528 bytes
Database Buffers 398458880 bytes
Redo Buffers 4591616 bytes
Database mounted.
SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF' to 'D:\EASYDB\SYSTEM.DBF';
Database altered.
SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF' to 'D:\EASYDB\UNDOTBS1.DBF';
Database altered.
SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF' to 'D:\EASYDB\SYSAUX.DBF';
Database altered.
SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF'to 'D:\EASYDB\USERS.DBF';
Database altered.
SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF'to 'D:\EASYDB\TEMP.DBF';
Database altered.

• Open your database and check if everything is ok :

SQL> alter database open;
Database altered.
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------
D:\EASYDB\SYSTEM.DBF
D:\EASYDB\UNDOTBS1.DBF
D:\EASYDB\SYSAUX.DBF
D:\EASYDB\USERS.DBF
SQL> select FILE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------
D:\EASYDB\TEMP.DBF