I wrote about this feature in Hebrew quite some time ago but in the last week I was asked by couple of my customers about the correct way to move datafiles with minimal downtime so I thought it would be nice to show the Oracle 12c solution before talking about the solution I gave them.

For years, moving datafiles was a pain in the DBA behinds. It required some database action, then some OS actions and then some other database actions. Starting Oracle 12c we can now move datafile online, without interfering the application and without taking the tablespace offline or read only.

Even though Oracle 12c brought a lot of new features and changes, this feature must be one of my favorites. It made thing so easy and so simple that it made me wonder what took them so long…

In order to understand the greatness of this feature, let us understand what needed to be done in order to move a datafile before Oracle 12.
When we wanted to move a datafile from place to place, we had quite a few steps and some of them even required us to take the tablespace offline causing a potential downtime to our applications.

The steps were simple:

1. Change the tablespace offline state so the DBWR will not write new blocks there. This is a database command which starts a downtime to the application
2. Move the data file to the new location. This is an OS command, but not all DBAs have server access
3. Change the datafiles location in the control file. This is again a database command that must be ran after the files were completely moved

Alter database datafile '/old/datafile.dbf' rename to '/new/datafile.dbf';

4. Move the tablespace to online state which ends the downtime to the applications

Along the years, we found some workarounds like changing the tablespace to read only and copying the files instead of moving them. In any case, there was some kind of downtime to the applications, which could have been very long, depending of the size of the data transferred.

Starting Oracle 12c, we can now move the files online. In order to do that, all we need to do is use the new syntax:

Alter database move datafile '/u01/oracle/orcl/dbf/old_name.dbf' to 
                             '/u01/oracle/orcl/dbf/new_name.dbf';

This will not only change the location of the file in the control file but will also move the file in the OS. If the file already exists, it will show an error message and we can use the RESUSE clause to overwrite it.

If we’d like to copy the file instead of moving it, we can use the KEEP clause and the file will not be removed from the original location.

Alter database move datafile '/u01/oracle/orcl/dbf/old_name.dbf' to 
                             '/u01/oracle/orcl/dbf/new_name.dbf' keep;

The really cool thing about this feature is the ability to move files in and out of ASM without using RMAN or any other special commands:

Alter database move datafile '/u01/oracle/orcl/dbf/old_name.dbf' to '+DATA';

This is a great feature and I feel that it is going to make our DBA life much easier.

Quick note: when working with Pluggable databases, we cannot move PDBs datafiles while having our current container as the CDB. When doing that, we will hit ORA-01516 (nonexistent log file, data file, or temporary file “%d”).

SQL> alter database move datafile '/u01/oracle/oradata/pdb1/old_name.dbf' to '/u01/oracle/oradata/pdb1/new_name.dbf' REUSE;
alter database move datafile '/u01/oracle/oradata/pdb1/old_name.dbf' to '/u01/oracle/oradata/pdb1/new_name.dbf' REUSE
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "22"

In order to move the files, we must change our current container to the PDB and run the command from there.

SQL> alter session set container=pdb1;
Session altered.

SQL> alter database move datafile '/u01/oracle/oradata/pdb1/old_name.dbf' TO '/u01/oracle/oradata/pdb1/new_name.dbf' REUSE;
Database altered.

SQL> alter session set container=CDB$ROOT;
Session altered.