How to move datafile online in oracle 12c

In this article we will discuss how to move/relocate datafile online in oracle. We know that Oracle introduced moving datafiles online in 12c to minimize the downtime. Prior to oracle 12c, required taking datafile offline and copy it at OS level and bring it back online again. Oracle 12c includes an enhancement to ALTER DATABASE MOVE DATAFILE command to moved, relocate or rename when database are online.

Check the existing datafiles:-

SQL>SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;

FILE# NAME

---------- ------------------------------------------------------------------

  1 /data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf

  3 /data01/12cdatabase/oracle/oradata/gcdb1/sysaux01.dbf

  4 /data01/12cdatabase/oracle/oradata/gcdb1/undotbs01.dbf

  6 /data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf

Moving/relocate Datafile:-

SQL> ALTER DATABASE MOVE DATAFILE '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

 FILE_ID FILE_NAME

----------------------------------------------------------------------

  1 /tmp/system01.dbf

Renaming a datafile:-

SQL> ALTER DATABASE MOVE DATAFILE '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf' TO '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf';

Database altered.

The next example uses the file number for the source file and keeps the original file.

SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/tmp/system01.dbf' KEEP;

Migrate/relocate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';

Migrate/ relocate a data file from one ASM disk group to another:

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';

Overwrite the datafile with the same name, if it exists at the new destination:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;

Copy the file to a new destination while retaining the old copy in the old destination:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;