Wednesday, June 26, 2013

Oracle 12c New Features - Alter Database Move Datafile

In previous versions of Oracle when DBAs needed to move datafiles to new storage, or migrate between ASM and filesystem they needed to first take the datafile offline, move the physical file, rename the file, recover it, and bring it online.  This usually required application outages, and to be safe DBAs often set the full tablespace read-only during this activity.  In Oracle 12c it is not necessary to take the datafile offline for the move.

In the following example I create tablespace TEST.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M;

Tablespace created.

Then create a table and insert a record.

SQL> create table test (id integer) tablespace test;

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

In this case I am moving it within the same filesystem, but we could also move to another filesystem or ASM.  I did not need to set tablespace to read-only, or take the datafile offline.

SQL> alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf';

Database altered.

SQL> select * from test;



In the alert log we see :-

Wed Jun 26 15:40:01 2013
create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M
Completed: create tablespace test datafile '/u01/app/oracle/oradata/test_1.dbf' size 100M
alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf'
Wed Jun 26 15:41:50 2013
Moving datafile /u01/app/oracle/oradata/test_1.dbf (14) to /u01/app/oracle/oradata/test_2.dbf
Move operation committed for file /u01/app/oracle/oradata/test_2.dbf
Completed: alter database move datafile '/u01/app/oracle/oradata/test_1.dbf' to '/u01/app/oracle/oradata/test_2.dbf'

And on disk there is only the destination datafile.

[oracle@rac1 admin]$ cd /u01/app/oracle/oradata/
[oracle@rac1 oradata]$ ls -l test*
-rw-r----- 1 oracle oinstall 104865792 Jun 26 15:41 test_2.dbf