Oracle Database 12c – MOVE de DATAFILEs ONLINE

Oracle 12c: MOVE DATAFILE ONLINE

Uma das novas funcionalidades do Oracle Database 12c mais incríveis que vi (e pouco comentada), é o MOVE de DATAFILEs ONLINE.

Como está no New Features Guide:

“In this release, a data file can now be moved online while it is open and being accessed.“

E o link para esta funcionalidade no Administrator’s Guide.

[oracle@nerv00 ~]$ sqlplus / AS SYSDBA 
 
 SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 00:54:03 2013
 
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 SQL> exit
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 [oracle@nerv00 ~]$ sqlplus / AS SYSDBA
 
 SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 00:54:12 2013
 
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
 SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
 
 FILE_NAME
 --------------------------------------------------------------------------------
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8wyfkf35_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_8wyfhov3_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8wyfm5n1_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_8wyfm4bv_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/soe.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_8x4mj4yh_.dbf
 
 6 rows selected.
 
 SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/soe.dbf' TO '/home/oracle/soe.dbf';
 
 Database altered.
 
 SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
 
 FILE_NAME
 --------------------------------------------------------------------------------
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8wyfkf35_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_8wyfhov3_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8wyfm5n1_.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_8wyfm4bv_.dbf
 /home/oracle/soe.dbf
 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_teste_8x4mj4yh_.dbf
 
 6 rows selected.
 
 SQL>

Abaixo, o Alert Log durante o MOVE.

Tue Jul 02 00:55:02 2013
 ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/soe.dbf' TO '/home/oracle/soe.dbf'
 Tue Jul 02 00:55:02 2013
 Moving datafile /u01/app/oracle/oradata/ORCL/datafile/soe.dbf (14) to /home/oracle/soe.dbf
 Tue Jul 02 00:59:27 2013
 Move operation committed for file /home/oracle/soe.dbf
 Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/soe.dbf' TO '/home/oracle/soe.dbf'