Oracle 12c: The Online Datafile Move

One of my favorite new features in Oracle 12c is probably the online datafile move. Previously, a tablespace had to be offline to move a datafile – which can be difficult to do with a live, busy production database. Getting downtime on a 24/7 system for a maintenance task can take quite a bit of time. Not to mention the numerous manual changes that must be performed to accomplish this, leaving more room for error and probability of a situation that requires recovery.

Fortunately – starting in Oracle 12.1 we have the online datafile move! No downtime required, and the move process could not be more simple. I have laid out a couple methods of moving a datafile below.

I setup an Oracle VirtualBox 2-node RAC cluster (Solaris 11.3 x86, GRID / DB version 12.1.0.2). My diskgroups are +OCRVOTE, +DATA1, +DATA2. I created my test DB on +DATA1 for this demonstration.

So first – let’s create a tablespace to test our datafile move. We’ll put this initially on diskgroup +DATA1:

 

SQL> create tablespace online_move datafile ‘+DATA1’ size 200m;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = ‘ONLINE_MOVE’;

FILE_NAME

——————————————————————————–

+DATA1/TESTDB/DATAFILE/online_move.273.944011779

 

We now have the datafile name that was created, so let’s now do an online move to the +DATA2 diskgroup:

 

SQL> alter database move datafile ‘+DATA1/TESTDB/DATAFILE/online_move.273.944011779’ to ‘+DATA2’;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name = ‘ONLINE_MOVE’;

FILE_NAME

——————————————————————————–

+DATA2/TESTDB/DATAFILE/online_move.256.944011871

 

Voila! No downtime required! Movement of datafiles is now tremendously simplified. Let’s look at another method of moving files via the DB parameter

“db_file_create_dest”.

 

I have moved the datafile back to “+DATA1”. I will modify the DB parameter to change the default db_file_create_dest, and then re-execute the command, sans the target location. When your db_file_create_dest points to the correct target location of new datafiles / moved datafiles – you do not need to specify the location on the actual move command:

                                                                               

SQL> select file_name from dba_data_files where tablespace_name = ‘ONLINE_MOVE’;

 

FILE_NAME

——————————————————————————–

+DATA1/TESTDB/DATAFILE/online_move.273.944011975

SQL> show parameter db_create_file_dest

NAME                                 TYPE       VALUE

———————————— ———– ——————————

db_create_file_dest                  string     +DATA1

SQL> alter system set db_create_file_dest = ‘+DATA2’;

System altered.

SQL> alter database move datafile ‘+DATA1/TESTDB/DATAFILE/online_move.273.944011975’;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name = ‘ONLINE_MOVE’;

FILE_NAME

——————————————————————————–

+DATA2/TESTDB/DATAFILE/online_move.256.944012071

 

Done! Now, so far our demonstrations have been purely for ASM at this point, but I’d like to give one example of a local filesystem move for you Non-RAC, Non-ASM stragglers ☺

 

SQL> create tablespace fs_move datafile ‘/ora01/datafiles/fs_move_01.dbf’ size 100m;

Tablespace created.

SQL> alter database move datafile ‘/ora01/datafiles/fs_move_01.dbf’ to ‘/ora01/datafiles2/fs_move_01.dbf’;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name = ‘FS_MOVE’;

FILE_NAME

——————————————————————————–

/ora01/datafiles2/fs_move_01.dbf

 

And there you have it – one of my favorite 12c features. Let us know what you think in the comments below!

 

NOTE: you cannot move tempfiles via the online datafile move feature, but that is unnecessary as well. Management of tempfiles is a different beast than datafiles, and you can generally be reckless with those as you wish ☺