Move datafile Online on 12c oracle database

Basic Syntax

OMF do not use KEEP option , It will skip.

Tempfile can not move online.

The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.
SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH  SMRPATCH7        SMRPATCH7                      READ WRITE           PRIMARY          NO                 0

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRPATCH7/system01.dbf
/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF
/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf
/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf
/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF
/u01/app/oracle/oradata/SMRPATCH7/users01.dbf
/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF

7 rows selected.

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH  SMRPATCH7        SMRPATCH7                      READ WRITE           PRIMARY          NO                 0

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf';

Database altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME

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

/u04/MasterDB/oradata/SMRPATCH7/system01.dbf

/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

/u04/MasterDB/oradata/SMRPATCH7/users01.dbf

/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

7 rows selected.

Alert Log Details : 

Tue Jun 11 10:16:46 2019

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF'

Tue Jun 11 10:16:46 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/indx01.DBF (7) to /u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/indx01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/indx01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf'

Tue Jun 11 10:24:41 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/users01.dbf (6) to /u04/MasterDB/oradata/SMRPATCH7/users01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/users01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/users01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/users01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF'

Tue Jun 11 10:25:43 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/idata01.DBF (5) to /u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/idata01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/idata01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf'

Tue Jun 11 10:25:53 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf (4) to /u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/undotbs01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF'

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF (2) to /u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

Tue Jun 11 10:26:04 2019

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF' TO '/u04/MasterDB/oradata/SMRPATCH7/ilog01.DBF'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf'

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf (3) to /u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/sysaux01.dbf'

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf'

Tue Jun 11 10:26:24 2019

Moving datafile /u01/app/oracle/oradata/SMRPATCH7/system01.dbf (1) to /u04/MasterDB/oradata/SMRPATCH7/system01.dbf

Move operation committed for file /u04/MasterDB/oradata/SMRPATCH7/system01.dbf

Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/system01.dbf' TO '/u04/MasterDB/oradata/SMRPATCH7/system01.dbf'

Tue Jun 11 11:05:03 2019

• We can not move tempfile online it will through error below .
• ORA-01516: nonexistent log file, data file, or temporary file .

SQL> select name from v$tempfile;

NAME

/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf' TO '/u04/db_backup/Flash_recovery_area/oradata/SMRPATCH7/temp01.dbf';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf' TO '/u04/db_backup/Flash_recovery_area/oradata/SMRPATCH7/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf"