New Oracle 12C features online renaming, migrating active data files

migrating or renaming data files in the 12c version of Oracle database no longer requires too many tedious steps to rename and move data files online using an SQL statement such as ALTER database move datafile. When this data file is being transmitted, the end user can perform queries, DML, and DDL tasks. In addition, data files can be migrated between storage devices, such as migrating from non-ASM to ASM, and vice versa.

First, rename the data file:

--Note that I'm demonstrating here in the PDB container named PDB01.
Sql> Show Con_name
Con_name
------------------------------
PDB01
--View the original data file name
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/pdb01/system01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/sysaux01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/undotbs01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf
6 rows selected.
--Renaming
sql> ALTER DATABASE MOVE datafile '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb.dbf ' to
'/HOME/ORACLE/APP/ORACLE/ORADATA/ANDYCDB/PDB01/BBB01.DBF ';
Database altered.
--Check the result of renaming
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/pdb01/system01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/sysaux01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/undotbs01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf
6 rows selected.

Ii. Migrating data files to other paths

--Note that I'm demonstrating here in the PDB container named PDB01.
Sql> Show Con_name
Con_name
------------------------------
PDB01
--View the original path of the data file
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/andycdb/pdb01/system01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/sysaux01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/undotbs01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/users01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb01.dbf
/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf
6 rows selected.
--Check that the data file does exist under the original path
[Email protected] ~]$ cd/home/oracle/app/oracle/oradata/andycdb/pdb01/
[email protected] pdb01]$ ll bbb*
Total 769412
-rw-r-----. 1 Oracle Oinstall 2105344 may 04:22 bbb01.dbf
-rw-r-----. 1 Oracle Oinstall 2105344 may 04:22 bbb02.dbf
--Migrating data files to a new path
Sql>alter DATABASE MOVE datafile '/home/oracle/app/oracle/oradata/andycdb/pdb01/bbb02.dbf ' to '/home/oracle/app/o RACLE/ORADATA/ANDYCDB/PDB01/NEW/BBB02.DBF ';
Database altered.
--Verify that the data file exists under migration path
[email protected] pdb01]$ LL
Total 769412
-rw-r-----. 1 Oracle Oinstall 2105344 may 04:22 bbb01.dbf
Drwxr-x---. 2 Oracle Oinstall 4096 may 04:31 new
-rw-r-----. 1 Oracle Oinstall 387981312 may 04:15 sysaux01.dbf
-rw-r-----. 1 Oracle Oinstall 272637952 may 04:30 system01.dbf
-rw-r-----. 1 Oracle Oinstall 67117056 may 07:02 temp01.dbf
-rw-r-----. 1 Oracle Oinstall 104865792 may 04:30 undotbs01.dbf
-rw-r-----. 1 Oracle Oinstall 14426112 may 04:10 users01.dbf
-rw-r-----. 1 Oracle Oinstall 5251072 may 06:42 Users01.dbf.bak
[Email protected] pdb01]$ cd/home/oracle/app/oracle/oradata/andycdb/pdb01/new
[email protected] new]$ LL
Total 2056
-rw-r-----. 1 Oracle Oinstall 2105344 may 04:31 bbb02.dbf
Description: Migrates the data files to the new path, automating the movement of the operating system-level data files.

Supplement (several other migration types):
1. Migrating data files from non-ASM to ASM:
Sql>alter DATABASE MOVE datafile '/u00/data/users_01.dbf ' to ' +dg_data ';
2. Migrating data files from one ASM disk group to another ASM disk group:
Sql>alter DATABASE MOVE datafile ' +dg_data/dbname/datafile/users_01.dbf ' to ' +dg_data_02 ';
3. If the data file already exists in the new path, overwrite it with the same name:
Sql>alter DATABASE MOVE datafile '/u00/data/users_01.dbf ' to '/u00/data_new/users_01.dbf ' reuse;
4. Copy the file to a new path while preserving its copy under the original path:
Sql>alter DATABASE MOVE datafile '/u00/data/users_01.dbf ' to '/u00/data_new/users_01.dbf ' KEEP;

You can monitor this process when you move a file by querying the v$session_longops dynamic view. Other than that
You can also refer to alert.log,oracle in which the specific behavior is recorded.

New Oracle 12C features online renaming, migrating active data files