Online Move Datafile in Oracle Database 12c Release 1 (12.1)

jiawang

Prior to Oracle 12c, moving datafiles has always been an offline task. There were certain techniques you could employ to minimize that downtime, but you couldn't remove it completely. Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online.


Basic Syntax
Examples
Pluggable Database (PDB)
Tempfiles
Data Guard
Related articles.


Multitenant : Online Move of Datafiles in CDBs and PDBs
Renaming or Moving Oracle Files
Basic Syntax
The text description of the syntax is shown below, but the syntax diagrams and a full description of the ALTER DATABASE command is available in the documentation here.


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.


When the source file is an OMF file the KEEP option can not be used. If the destination file is an OMF file, the TO clause can be omitted and the file will be created with an OMF name in the DB_CREATE_FILE_DEST location.


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>


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


SQL>
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>




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


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


SQL>




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


SQL>
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>




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>




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


SQL>
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>




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


SQL>
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>




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>




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


SQL>
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


SQL>
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"


SQL>
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>




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


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


SQL>




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


Database altered.


SQL>




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.


SQL>
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>




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"


SQL>
That is not major problem as temporary files can be created and removed quite simply.


Data Guard
We connect to the primary database (cdb1), check the datafile location, move it and check again.


sqlplus sys/${SYS_PASSWORD}@cdb1 as sysdba


SQL> SELECT name FROM v$datafile where file# = 1;


NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1/system01.dbf


SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/CDB1/system01.dbf' TO '/tmp/system01.dbf';


Database altered.


SQL> SELECT name FROM v$datafile where file# = 1;


NAME
--------------------------------------------------------------------------------
/tmp/system01.dbf


SQL>
We connect to the standby database (cdb1_stby) and check the location of file 1. We can see it hasn't moved.


sqlplus sys/${SYS_PASSWORD}@cdb1_stby as sysdba


SQL> SELECT name FROM v$datafile where file# = 1;


NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1_STBY/datafile/o1_mf_system_h4p21n94_.dbf


SQL>
We can move it on the standby database, but we have to turn off the apply process.


dgmgrl sys/${SYS_PASSWORD}@cdb1 <<EOF
EDIT DATABASE 'cdb1_stby' SET STATE='APPLY-OFF';
EXIT;
EOF
We can now move the file on the standby.


sqlplus sys/${SYS_PASSWORD}@cdb1_stby as sysdba


SQL> SELECT name FROM v$datafile where file# = 1;


NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1_STBY/datafile/o1_mf_system_h4p21n94_.dbf


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


Database altered.


SQL> SELECT name FROM v$datafile where file# = 1;


NAME
--------------------------------------------------------------------------------
/tmp/system01.dbf


SQL>
Once the move is complete we need to turn on the apply process.


dgmgrl sys/${SYS_PASSWORD}@cdb1 <<EOF
EDIT DATABASE 'cdb1_stby' SET STATE='APPLY-ON';
EXIT;
EOF