Oracle Database 12c: Move datafile online (New feature)

Hello readers!!
Today I will talk about one interesting feature on Oracle 12c: Move a datafile online.
You could have many reasons to move a datafile online, for example: to another file system, or to another ASM disk group.
On previous versions if you want to move a datafile or rename, you must to put datafile offline and you need to recover the datafile and bring back online. During this operation it is not possible access the data inside the datafile.
This could have a negative impact into the business.

Now on Oracle Database 12c enables you to move or rename a datafile online, without requiring any outage.
This operation could be done with database in ARCHIVELOG and NOARCHIVELOG mode.

Lets start:

  • On the command below I will rename the datafile:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/DB12C/datafile/salesdf01.dbf’ TO ‘/u01/app/oracle/oradata/DB12C/datafile/sales01.dbf’

This command could take longer time to complete with a simple file rename, because the file itself is actually being copied to the new filename.
While a file copy is occuring, you will see waits in v$session_wait on db file single write and db file sequential read. You will also see the move in v$session_longops with an OPNAME of Online datafile move.

  • Oracle by default will clean up the source file. You can override this behavior by using the KEEP parameter.

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/DB12C/datafile/sales01.dbf’ TO ‘/u01/app/oracle/oradata/DB12C/datafile/sales01_df.dbf’ keep;

You can use the parameter REUSE. It’s possible that the datafile with the same name will already be in place and you will want to overwrite the file.

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/DB12C/datafile/sales01.dbf’ TO ‘/u01/app/oracle/oradata/DB12C/datafile/sales01DB12C.dbf’ keep;

  • You can use this command on ASM to move files between the disk groups.

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/DB12C/datafile/sales01.dbf’ TO ‘+DG_DATA’;

Of course that this operation could cause some performance impacts during the file move, but that certainly beats an outage!

I hope that you enjoy!

Cheers, Felipe.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s