Online Datafile Move or Rename

Here I am going to discuss one of my favourite new feature of Oracle 12c which has taken the down time away from the DBAs while moving or renaming a datafile online. There is no requirement of any downtime during this operation

Renaming/Moving a Datafile Prior to 12c

Prior to Oracle 12c, if we need to move or rename a datafile, we would need to take tablespace offline, or take full database offline (mount) in case of SYSTEM, SYSAUX and/or current UNDO tablespace in order to move or rename a datafile. Prior to executing the RENAME command, it was also required to manually copy/move the datafile to the destination location. As mentioned above, this downtime could be substantial if database is huge.

Renaming/Moving a Datafile in 12c and above

In Oracle 12c, a brilliant new feature is moving/renaming datafile online from one location to another location. The source location can be either ASM or file system and destination could also be either ASM or file system. While datafile move is in process all contents of datafile remains accessible and hence this is a totally online procedure with a few exceptions - A datafile can't be moved if

·                     Datafile is offline

·                     Any flashback or recovery operation is currently running on the datafile

·                     Tablespace of datafile is offline

·                     Datafile resize (shrink only) operation is currently in progress for this datafile

Please note again that there is no need to manually move the datafile to destination as Oracle will perform this this task itself.  (To learn about online partition move feature, click here)

Syntax

ALTER DATABASE MOVE DATAFILE <file_name> TO <new_location_and_name>;

Original file can be preserved at the original location while moving to the other location. This file on the source location can be used as a backup copy. In this case, the syntax would add keyword "KEEP" at the end of the statement.

ALTER DATABASE MOVE DATAFILE <file_name> TO <new_location_and_name> KEEP;

Example (Moving system datafile from ASM to Filesystem and then moving back from Filesystem to ASM)

In the following I am illustrating an example where I would be moving SYSTEM tablespace datafile to the file system, and after that I would move back this file from file system to the ASM diskgroup from where it was moved originally.

SQL> select name from v$datafile;

NAME --------------------------------------------------------------------------------
+DATA/CDB/DATAFILE/system.261.850522599
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.

SQL> alter database move datafile '+DATA/CDB/DATAFILE/system.261.850522599' to '/u01/app/oracle/system01.dbf';

Database altered.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system01.dbf
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.

SQL>  alter database move datafile '/u01/app/oracle/system01.dbf' to '+DATA';

Database altered.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB/DATAFILE/system.261.850525283
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/system.268.845569249
+DATA/CDB/DATAFILE/sysaux.256.845569259
+DATA/CDB/F79E49A638DF11C3E0430A20A8C0DEA9/DATAFILE/sysaux.279.845569277
+DATA/CDB/DATAFILE/undotbs1.276.845569283
+DATA/CDB/DATAFILE/users.267.845569333
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/system.259.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/sysaux.258.847554537
+DATA/CDB/F95865AF2AD416EAE0430A20A8C08E01/DATAFILE/salman.264.847991093
+DATA/CDB/DATAFILE/salman.278.848010629
+DATA/CDB/DATAFILE/testdbs.260.848593699

11 rows selected.