12c New Features – Online DataFile Move

In this post we would cover another handy feature introduced with 12c i.e. Online Datafile Move. Every DBA might have come across this task to move datafiles to a different storage due to several reasons and prior to 12c this always needed Downtime as this has been an offline task. Back in 11g you could use a certain tricks to minimize that downtime but its required, and for a highly available env. this used to be a problem.

Up to Oracle, you had three ways to relocate a datafile (Please comment below if you want to know the steps involved to do so):-

  1. Put the tablespace offline
  2. Put the datafile offline
  3. Shutdown and mount the database

But with 12c you can now use the command ALTER DATABASE MOVE DATAFILE in order to rename, relocate, or copy a datafile when the datafiles or the database are online. Therefore just a single command to move, rename your datafiles with database being entirely available in read and write mode for users, without any data loss.

Renaming a datafile

ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/test1.dbf' TO '/u01/oradata/DBTEST/test01.dbf';

Relocating a datafile

ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/test1.dbf' TO '/u02/oradata/DBTEST/test01.dbf';

By default, Oracle automatically deletes old data file after moving them and prevents the user from overwriting an existing file.

When you move a data file, Oracle first makes a copy of the datafile. Then, when the file is successfully copied, pointers to the datafile are updated and the old file is removed from the file system. This is why the operation requires twice the size of the files to be copied as free space.

You can use KEEP clause to keep the old datafile used to make a copy of the file. Also REUSE clause can be used to overwrite an existing file.

Datafiles can be moved online:-

  • From file system to file system
  • From file system to ASM
  • From ASM to file system
  • From ASM to ASM

Hope this helps you understand the feature and its internals, please comment below if you need any further clarity on this feature.