How to Rename or Move Data Files in Oracle

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:


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.
– 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:


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

Step Three:
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:

Database altered.




Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın: Logosu hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s

%d blogcu bunu beğendi: