How to Move, Rename and Relocate Datafiles in Oracle

I will explain How to Move Rename and Relocate Datafiles in Oracle database.

 

Move, Rename and Relocate Datafiles in Oracle

If you don’t know what is the tablespace and datafile, you can read the following article.

https://ittutorial.org/create-tablespace-and-create-permanent-undo-and-temp-tablespace-add-datafile-tablespace-in-oracle/

 

 

 

 

Move Datafile in Oracle

You can move or relocate datafiles online with Oracle 12c as follows.

 

You can move datafile from ASM to File System Online as follows.

ALTER DATABASE MOVE DATAFILE '+DATA/TEST12C/DATAFILE/users.268.876908629' to '/u01/oradata/users.dbf';

 

You can move datafile from File System to ASM Online as follows.

ALTER DATABASE MOVE DATAFILE '/u01/oradata/users.dbf' to '+DATA/TEST12C/DATAFILE/users';

 

You can move datafile from File System to File system Online as follows.

ALTER DATABASE MOVE DATAFILE '/u01/oradata/system01.dbf' TO '/u01/oradata/new_location/system01.dbf';

 

Rename Datafile

You can rename datafiles as follows.

 

ALTER TABLESPACE TABLESPACE_NAME OFFLINE NORMAL;

After Offline operation is completed, rename datafile with mv command from ‘users.dbf’ to ‘users_tbs.dbf’.

 

execute the following commands to rename datafile in Controlfile and make it online again.

 

ALTER TABLESPACE TABLESPACE_NAME name RENAME DATAFILE /u01/oradata/users.dbf' TO /u01/oradata/users_tbs.dbf';

ALTER TABLESPACE TABLESPACE_NAME ONLINE;

 

You can relocate datafiles to new location because of insufficent disk space.

 

List the datafiles.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
/u01/oradata/SYSTEM01.DBF
/u01/oradata/UNDOTBS01.DBF
/u01/oradata/SYSAUX01.DBF
/u01/oradata/USERS01.DBF
/u01/oradata/MSD01.DBF

5 rows selected.

SQL>

 

Shutdown database.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

Move physical datafile to the new disk with mv command.

[MSD]/home/oracle $ mv /u01/oradata/MSD01.DBF  /u02/oradata

 

Startup database mount mode.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.

 

Rename datafile in Controlfile as follows.

 

SQL> ALTER DATABASE RENAME FILE '/u01/oradata/MSD01.DBF' TO '/u02/oradata/MSD01.DBF';

Database altered.

 

 

 

Now Open database.

 

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

 

List the datafiles again.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
/u01/oradata/SYSTEM01.DBF
/u01/oradata/UNDOTBS01.DBF
/u01/oradata/SYSAUX01.DBF
/u01/oradata/USERS01.DBF
/u02/oradata/MSD01.DBF

5 rows selected.

SQL>

 

You can move all datafile to new location or disks, in this time you can use the following script to generate it automatically.

Sometimes you have 1000+ datafiles, so you don’t want to write it one by one. So you can use the following script to generate it automatically.

 

Modify just ‘/oradata1/MSDUPG/’ according to your environment.

select distinct 'ALTER DATABASE RENAME FILE '''||File_name ||''' to ''/oradata1/MSDUPG/'|| substr(File_name,21,length(File_name) ) ||''' ;' 
from dba_data_files;

 

Example output is as follows.

ALTER DATABASE rename FILE '/oracle/oradata/MSD/datanew008.dbf' to '/oradata1/MSDUPG/datanew008.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/datanew009.dbf' to '/oradata1/MSDUPG/datanew009.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/sysaux01.dbf' to '/oradata1/MSDUPG/sysaux01.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/sysaux02.dbf' to '/oradata1/MSDUPG/sysaux02.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/system01.dbf' to '/oradata1/MSDUPG/system01.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/tasind04.dbf' to '/oradata1/MSDUPG/tasind04.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/MSDarcind_01.dbf' to '/oradata1/MSDUPG/MSDarcind_01.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/MSDydk_02.dbf' to '/oradata1/MSDUPG/MSDydk_02.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/undotbs1_01.dbf' to '/oradata1/MSDUPG/undotbs1_01.dbf' ;
ALTER DATABASE rename FILE '/oracle/oradata/MSD/undotbs2_01.dbf' to '/oradata1/MSDUPG/undotbs2_01.dbf' ;

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )