If your Oracle datafiles host on filesystem architecture than you may need to move your datafiles to one other path because of not enough disk size etc.

In this post, I am talking about a move or rename datafiles to a new location. We will use cp command to achieve move data file from one to another.

I made a similar post before. For check it please click here

You may also use below methods for rename&move data files in Oracle Database which is Oracle Database versions such as
– Online data file move (12c)
– RMAN (12c&11g)

In this post, don’t forget I won’t change My control file path.

My env is:
– Operating System is Linux
– Oracle database version is 11gR2.

My datafiles exist on 9 paths. Those paths are:

/oracle/test/system.dbf
/oracle1/test/system02.dbf
/oracle1/test/system03.dbf
/oracle1/test/temp01.dbf
.
/oracle10/test/xxx.dbf

I am going to move all data files from /oracle/test to /oracle10/test path.

For this I am going to follow below steps for my purpose:

– Shutdown the database.
– Move all datafiles to a new location the physical file on the OS.
– Start the database in mount mode.
– Use ALTER DATABASE RENAME FILE command
– Open the database.

Before shutdown database, I need to run below SQLs to identified related data files.

We need to define where those datafiles are represented.
– Temp datafile
– Redo datafile
– Related Tablespace datafile

select ‘cp ‘ ||member || ‘ ‘ || ‘/oradata_new/test/’ from v$logfile;
select ‘cp ‘ ||file_name|| ‘ ‘ || ‘/oradata_new/test/’ from dba_temp_files;
select ‘cp ‘ ||name|| ‘ ‘ || ‘/oradata_new/test/’ from v$datafile;

Step One:
Shutdown database with immediate option:

SQL> SHUTDOWN IMMEDIATE

Step Two:
Copy all related files to a new location. Use cp comment instead of mv. DO NOT USE MV Command!

Step Three:
SQL> STARTUP MOUNT
ORACLE instance started.

Step Four:
For all related data file, use below syntax:

ALTER DATABASE RENAME FILE '/oracle/test/xxx.dbf' TO '/oradata_new/test/xxx.dbf';

PS: While you move your data file to destination path, Somehow you may have some datafile which has the same naming.

xxx1.dbf can be stored in /oracle/test and xxx1.dbf can also  be store /oracle1/test/.

In this case, you need to rename those file such as xxx1.dbf to xx2.dbf

Step Five:
Open Database:
SQL> ALTER DATABASE OPEN;

Database altered.