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


A Quick look at Delphix Cross Platform Provisioning

Why Cross Platform Provisioning (XPP)

As a consultant I am fortunate to get the opportunity to work and speak with many customers across the whole of EMEA, a topic which is often discussed is the use of the Linux or the ‘Cloud’ for Development and Test teams. I will cover how you can migrate data to the Cloud in a future Blog post, but today I want to get you ready for the Cloud.

The migration of databases to Linux on commodity hardware on-premise or in the Cloud continues to grown year-on-year as IT departments look to reduce their infrastructure and operational costs and also provide greater Agility to the Business. However Unix is far from dead and not everyone is looking to migrate their Production systems to Linux quite yet. This a great example of where Delphix XPP can be used to provide Virtual Development and Test databases on Linux whilst retaining Production on Unix.

Validate Source

If you have a Unix dSource or VDB you may have noticed the ‘Transform to Linux’ option on your dashboard, this Service-lead feature provides an automated approach to the traditional labour intensive challenge of performing Endianness conversion, e.g. Unix (Big Endian) to Linux (Little Endian).

During the validation Delphix will spin-up a temporary VDB, therefore before you start you need to ensure that your Unix staging server has had the ‘Use as Staging’ enabled from within the Server Environment.

Validate dSource

To get started Open up the dSource or VDB card, and click on the ‘Linux’ tab, from here click on the Green Tick to validate the ‘Linux Transformation’. Delphix will then perform a series of checks against known Oracle restrictions including:

  • No encryption – Columns / Tablespaces
  • Locally managed tablespaces (not dictionary managed)
  • Tablespaces are self-contained
  • Restrictions surrounding XML Types, Spatial Indexes and Advanced Queries

Transform to Linux

If the Validation process fails, use the download the ‘Transformation’ text file to assist in identification and correction of the errors reports. Perform required updates and recommended changes to the Transformation script and Upload and repeat Validation step until successful validation.

Successful Validation

Now that you have Validated dSource / VDB the previously greyed out ‘Transform to Linux’ will be available allowing the Provision of Linux VDB’s

Validated dSource

Click on ‘Transform to Linux’ and follow the Delphix Provisioning wizard providing Database name and Mount Point as normal.

Provision Linux VDB