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.

What is the Tablespace and How to Create Permanent, Undo and Temp 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 )

About 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 [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *