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. You may also use below methods:
Online move (12c)
RMAN


In this post, My control file path won’t be changed. My OS is Linux, my db version is 11gR2.

My datafiles exist on 9 paths. Those paths are:
/oracle/test/system.dbf
/oracle1/test/system02.dbf
/oracle1/test/system03.dbf
.
.
/oracle10/test/xxx.dbf

I am going to move all datafiles which is represent from /oracle/test to /oracle10/test, to a new location /oradata_new/test/… 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 datafiles.

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.

Step Three:
SQL> STARTUP MOUNT
ORACLE instance started.

Step Four:
For all related datafile, use below syntax:

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

In this part you may create some datafile with same name. xxx1.dbf can be store in /oracle/test and xxx1.dbf can be also 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.

 

 

Advertisements