Oracle 12c Online Table Redefinition and Datafile Movement

Moving objects and datafiles around is required when you need to either change the physical location of the data files or when you want to migrate to ASM or some other Storage system. Up to now these operations caused the database to remain unavailable during the move activity. However in Oracle 12c this can be minimized to almost no downtime. Let’s look at how online table redefinition and datafile movement can help us in achieving zero downtime.

Online Table/Partition Redefinition

In 11g you have two options to move an object like table from one tablespace to the other. The first method is to use the “alter table .. move tablespace” option and the second one is to utilize the DBMS_REDEFINITION package. The later one is desirable in cases where availability of data cannot be compromised because it allows table to remain accessible during redefinition. Although the use of DBMS_REDEFINITION package allowed you to keep a table online while it is being moved, but it did required a lot of manual work, preparation and monitoring. For example you need to prepare the interim table required to hold data, you need to keep track of grants, indexes etc. which are required to be recreated after the redefinition is completed. You also had to copy the dependents of that table, keep track of changes that were being made to table during the move and then apply those changes once move is complete.

Oracle did provided some very useful procedures inside DBMS_REDEFINITION to accomplish these tasks but it was you who had to decide what to do and when.

But starting from 12c your life as a DBA is about to get a little easier. One less thing to worry about. You no longer have to do anything before or after the redefinition of table. Everything will be done by Oracle itself. You don’t have to create interim table either. Just execute one procedure to start the process and forget about it. The procedure will do everything. Best of all the table will be online during the operation and DML will be allowed on it. The grants and triggers will stay intact too!

For example we have a user Foo who owns a table named T1 and T1 is placed in SYSAUX tablespace by default. See below.

SQL> select tablespace_name from user_tables
2 where table_name='T1';

TABLESPACE_NAME
------------------------------
SYSAUX

Its definitely not a good idea to store a user table in SYSAUX tablespace. So we want to move this table to USERS tablespace. The following call to procedure will do everything.

SQL> begin
2 dbms_redefinition.redef_table (
3 uname=>'FOO',
4 tname=>'T1',
5 table_part_tablespace=>'USERS');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select tablespace_name from user_tables
2 where table_name='T1';

TABLESPACE_NAME
------------------------------
USERS

That’s it. Its really that simple.

If you encounter any errors while running the above procedure then make sure that your user Foo has space quota on both tablespaces involved and also have the following privileges as well.

• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP ANY TABLE
• LOCK ANY TABLE
• SELECT ANY TABLE
• CREATE ANY TRIGGER
• CREATE ANY INDEX

Table redefinition is one way to move tables and partitions from one tablespace to other. The other way is to use the Alter table … Move command. The dwonside of using this command is that you will have to take table offline first. However starting from 12C you can now use this command on partitions and sub-partitions without taking table offline first.

If you have a requirement to move certain partition or sub-partition from one tablespace to other then you can use below mentioned command.

• ALTER TABLE … MOVE PARTITION … ONLINE
• ALTER TABLE … MOVE SUBPARTITION … ONLINE

Restrictions

There are a few restrictions on the use of table redefinition and movements methods described above. The one procedure redefinition will only work if only following changes during redefinition are desired.

• Tablespace changes, including a tablespace change for a table, partition, index, or LOB columns
• Compression type changes, including a compression type change for a table, partition, index key, or LOB columns
• For LOB columns, a change to SECUREFILE or BASICFILE storage

If your requirements are beyond these changes then you will have to use the normal redefinition method which involves calling several procedures like CAN_REDEF_TABLE, START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE.

Also the Alter table … move partition .. online command will only work for partitions and subpartitions. It won’t work for entire tables.

Moving DataFiles Online

Oracle did not stopped here in the context of object movement. For the first time, the online movement of the data files from one physical location to the other has been introduced. The current process of moving data file from one location to the other goes something like this.

• You take tablespace offline
• Move files using copy paste method at OS level
• Rename the data files in control file
• Make the tablespace online again

Depending on the sizes of data files, this can result in a considerable amount of downtime for the tablespaces involved. You can minimize this downtime by using RMAN copies and incremental backups but there is always going to be some downtime. Also the process involves taking the tablespaces offline eliminating the possibility of using this method for SYSTEM and UNDO tablespaces with database opened.

Here too, starting from 12c you don’t have to worry about this. The online movement of data files can be done using the a single command. The best part about this is that the command will work for SYSTEM and UNDO tablespaces as well. For example take a look at following demonstration.

SQL> select tablespace_name,status
2 from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE

SQL> alter database move datafile '/u02/app/oracle/oradata/cdb12c/users01.dbf' to '/u01/app/oracle/oradata/cdb12c/pdb1/users01.dbf';
Database altered.

SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb12c/pdb1/system01.dbf
/u02/app/oracle/oradata/cdb12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/pdb1/users01.dbf

SQL> alter database move datafile '/u02/app/oracle/oradata/cdb12c/pdb1/system01.dbf' to '/u01/app/oracle/oradata/cdb12c/pdb1/system01.dbf';
Database altered.

SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb12c/pdb1/users01.dbf

We first moved the USERS datafile while the corresponding tablespace was online. And in second step we even manages to move SYSTEM tablespace’s datafile. Mind you this was not possible before while database is open. But it is now and that too with such simplicity.

This is incredible and huge improvement. Consider your data movement from File System to ASM or from SAN to NFS or any other storage system out there. You don’t need any downtime now.