Oracle 12C - Online Datafile movement and Actions are possible during an Online Data file Move operation

Online Datafile movement
The text description of the syntax is shown below.

ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]

The source file can be specified using the file number or name, while the destination file must be specified by the file name. 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.

The file number can be queried from the V$DATAFILE and DBA_DATA_FILES views.

SQL> CONN / AS SYSDBA

SQL> SET LINESIZE 100
SQL> COLUMN name FORMAT A70
SQL> SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;

     FILE# NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/system01.dbf
  3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
  4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
  6 /u01/app/oracle/oradata/cdb1/users01.dbf

SQL> COLUMN file_name FORMAT A70
SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/system01.dbf
  3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
  4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
  6 /u01/app/oracle/oradata/cdb1/users01.dbf

Examples

The following example shows a basic file move, specifying both source and destination by name. Notice the original file is no longer present.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/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

SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf
ls: cannot access /u01/app/oracle/oradata/cdb1/system01.dbf: No such file or directory

SQL> HOST ls -al /tmp/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:48 /tmp/system01.dbf

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

SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP;

Database altered.

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

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:48 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL> HOST ls -al /tmp/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:49 /tmp/system01.dbf

The next example shows the use of OMF.

SQL> ALTER SYSTEM SET db_create_file_dest='/u01/app/oracle/oradata/cdb1';

System altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf';

Database altered.

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

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf

The final example attempts to use the KEEP option, where the source file in an OMF file. Notice how the KEEP option is ignored.

SQL> ALTER DATABASE MOVE DATAFILE 1 To '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP;

Database altered.

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

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL> host ls -al /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf
ls: cannot access /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf: No such file or directory

Pluggable Database (PDB)

The container database (CDB) can not move files that belong to a pluggable database. The following query displays all the datafiles for the CDB and the PDBs.

SQL> SELECT file#, name FROM v$datafile ORDER BY file#;

     FILE# NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/system01.dbf
  3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
  4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
  5 /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
  6 /u01/app/oracle/oradata/cdb1/users01.dbf
  7 /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
  8 /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
  9 /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
 10 /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
 29 /u01/app/oracle/oradata/pdb2/system01.dbf
 30 /u01/app/oracle/oradata/pdb2/sysaux01.dbf
 31 /u01/app/oracle/oradata/pdb2/pdb2_users01.dbf

If we try to move a datafile belonging to a PDB an error is returned.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "29"

If we switch to the PDB container, the datafile can be moved as normal.

SQL> ALTER SESSION SET container=pdb2;

Session altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE;

Database altered.

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

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
 29 /tmp/system01.dbf

SQL> ALTER DATABASE MOVE DATAFILE 29 TO '/u01/app/oracle/oradata/pdb2/system01.dbf' REUSE;

Database altered.

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

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
 29 /u01/app/oracle/oradata/pdb2/system01.dbf

SQL>ALTER SESSION SET container=cdb1;

SQL> ALTER SESSION SET container=CDB$ROOT;

Session altered.

Tempfiles

Not surprisingly, the ALTER DATABASE MOVE DATAFILE syntax does not work for temporary files.

SQL> SELECT file_id, file_name FROM dba_temp_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
  1 /u01/app/oracle/oradata/cdb1/temp01.dbf


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

Actions are possible during an Online Data file Move operation


1. Creating and dropping tables in the data file being moved
2. Querying tables in the data file being moved
3. Performing Block Media Recovery for a data block in the data file being moved
4. Executing DML statements on objects stored in the data file being moved