OracleDB12c New Feature: More to Online Datafile Move

As we already know, now with Oracle Database 12c, it is easier to move a data file. There is more to it… Let us see few operations in 11g, and how they all got simplified with Oracle 12c online file move feature…

1. Rename a file or move a file from one file system to another

  • Take tablespace offline, thus users/application cannot access the content.
  • Copy the file to new destination using OS commands
  • Use ALTER DATABASE RENAME FILE ‘x’ TO ‘y’;
  • Make the tablespace online

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

  • ALTER DATABASE MOVE DATAFILE ‘x’ TO ‘y’;

2. Move a file from one ASM diskgroup to another, Move a file from file system to ASM, Move a file from ASM to file system

  • Take the data file to be moved offline using ALTER DATABASE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ OFFLINE
  • Connect to RMAN and perform copy using COPY DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2’  [Note, if you are copying a non-OMF file, make sure to include full path and file name for the destination].
  • Find the name of the new file if using OMF by querying DBA_DATA_FILES.
  • Rename the file using ALTER DATABASE RENAME FILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2/mydb/datafile/x1.dbf’
  • Connect to RMAN to switch to the new file using SWITCH DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO COPY
  • Recover the data file to make it consistent using RECOVER DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ TO
  • Finally, make the file online using ALTER DATABASE DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ ONLINE;

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

ASM to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2’;

File System to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘/u01/db/x1.dbf’ TO  ‘+DG2’;

ASM to File System

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘/u01/db1/x2.dbf”;

The TO clause can be omitted for OMF, where unique data file name will be created on the location specified by DB_CREATE_FILE_DEST.

There are couple of keywords you may use with the MOVE operation.

  • KEEP does not delete the file from the source location.
  • REUSE will overwrite the file, if file with same name exists in the destination location.