Published on May 16, 2017 by Matt Edwards
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 ☺
Share on
