How to Move, Rename and Relocate Datafiles in Oracle

Hi,

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

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

 

 

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';


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 read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply