Oracle 12c Online Database Move Datafile

After watching an Oracle R12c new features presentation at a recent conference somebody asked me what was my favourite Oracle R12c database feature, for me it was easy has to be ‘ALTER DATABASE MOVE DATAFILE’

Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, with Oracle 12c this is no longer the case.

Storage Maintenance

You can use the ALTER DATABASE MOVE DATAFILE to move a data file to different location, disk or storage system. This provides a simple Online method of migrating to a new storage platform.


The Oracle ALTER DATABASE MOVE DATAFILE does all the hard work for you including renaming or relocating the data file at operating system level and changing pointers in the Control Files.

Oracle also provide a way of monitor progress by using the V$SESSION_LONGOPS view, the reported bytes done will increment until the move is completed.

SQL> select file_name, tablespace_name from dba_data_files;

——————– ———————————————————–

TEST                /u01/app/oracle/oradata/PSTG/datafile/test.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;

SQL> select to_char(start_time,’DD-MON-YY hh24:mi:ss’),  timestamp, time_remaining, elapsed_seconds, message from V$SESSION_LONGOPS;

02-JUN-17 10:51:44   0 158
Online data file move: data file 4: 10737418240 out of 10737418240 bytes done

SQL>  select tablespace_name, file_name from dba_data_files;

——————– ———————————————————–

TEST               /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

Please note before you begin make sure you have enough space on the source and target storage platforms as Oracle will allocate the required storage space on the target system and will not free any space up on the source until the move is complete.

If you specify the ‘KEEP’ parameter Oracle will copy the data file to the new location and retain the old datafile in the original location, it will be no longer reference in the control files and can be removed when no longer required.

SQL>  select tablespace_name, file_name from dba_data_files;

——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Database altered.

SQL> select tablespace_name, file_name from dba_data_files;

——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/test.dbf

[oracle@ol72-oraclepub datafile]$ ls -lh

-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 test.dbf
-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 tester.dbf

Please consult the Oracle R12.2 Docs for further details.

Code examples:


ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;


ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u02/app/oracle/oradata/PSTG/datafile/test.dbf’;


ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Move into ASM:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;

REUSE: overwrite data file with the same name
KEEP: copies the data file to a new location and retains original data file in old location


Taking Oracle EBS R12 up into the Clouds with Delphix

Getting ‘Cloud Ready’

In my previous Oracle E-Business Suite (EBS) Virtualisation postings I have shown how Delphix can be used to Virtualise your EBS environment, and then how it can be used to to provision new virtual EBS environments.

Part 1: Virtualising Oracle EBS R12.1 using Delphix

Part 2: Creating a Virtual Oracle EBS Environment using Delphix

Now that we are ‘Cloud Ready’ I will show how we can use Delphix Replication to migrate your Oracle EBS environments into the Cloud.

Delphix supports the Amazon Elastic Cloud Compute (EC2) platform, and has also recently announced support for VMware vCloud Air.

Amazon i2 Family

Note: When using choosing your Amazon Cloud instance for Delphix select a storage optimised instance type from the I2 family. The larger instances provide greater CPU and memory, the more memory you can give your Delphix Engine the better as this will be used as a cache improving read performance.


The I2 This family includes the High Storage Instances that provide very fast SSD-backed instance storage optimized for very high random I/O performance, and provide high IOPS at a low cost. 

Delphix requires that the all the storage attached be Amazon Elastic Block Store (EBS), Delphix does not support the use of instance store volumes

Using the Cloud

EBS Replication
Oracle EBS Replication with Delphix

In the example above the On-Premise Oracle R12 environment (Database, dbTech Stack & Apps Tier) are all replicated to a Delphix E2C instance.

This provides an extremely Agile deployment model where Production, Prod Support, Development, Test, Integration, Acceptance Testing and Training can all be hosted where makes best sense for the business.

It is becoming increasling common to see Production On-Prem and non-Prod in the Cloud, this could be as part of a longer strategic journey to the Cloud, with quick wins being delivered by using ‘Compute On-Demand’ or the final architecture.

When customers look at ‘SaaS’ based solutions a requirement which is frequently overlooked and can be challenging for non-Virtualised solutions is business reporting and integration. Very few organisations rely on a single business solution and often struggle to get their data out of the hosted solution. This is example where a Cloud Delphix Engine could be used to replicate back to On-Prem Delphix Engine, providing the business access to the required data.

Configuring Delphix for the Cloud

To get started logon to your Source Delphix Engine as a Delphix Administrator



Navigate to System -> Replication






Provide Replication Target IP Address, Username and Password, configure Schedule and Network Options.



Hit OK



Click on ‘Run Now’ to start the Replication process



You can now Logon to the empty Target Server and from the pulldown select the Source Server and check the ‘Active Jobs’ to see the Replication process.



Now that you have Virtualised your Oracle EBS environment, configured Delphix Replication and Replicated the dSource and VDB’s to your Target server you are free to ‘Cloud Burst’, expanding and shrinking the number of EBS environments in-line with business demand.

For further information on how Delphix can be used to support your Data Migration projects visit the Delphix web site