[20130814] 12C Online rename and relocation of an active data file.txt

[20130814] 12C Online rename and relocation of an active data file.txt

12c下更改数据文件可以在线修改，不像以前那样需要offline，改名后再online。

自己做一个简单的测试：

SQL> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

1.建立测试环境:
CREATE TABLESPACE lfree DATAFILE
  'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table t tablespace lfree as select rownum id,'test' name from dual connect by levelTable created.

2.建立一个存储过程：
--以sys用户执行
SQL> grant execute on dbms_lock to scott;
Grant succeeded.

--再建立存储过程：
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
   FOR J IN 1..26 LOOP
      FOR i IN 1..100 LOOP
          update t set name=chr(64+j)||i where id=i;
          COMMIT;
          dbms_lock.sleep(0.1);
      END LOOP;
   END LOOP;
END;
/

Procedure created.

--会话1执行如下：
exec test_proc

--会话2执行如下：
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf';
Database altered.

--会话2已经执行完成，而会话1依旧在执行。会话1大约在100*0.1*26=260秒上下完成。

--加入keep参数，保持文件存在。
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep ;
Database altered.

--会话1结束后：

SQL> select * from t where rownum

        ID NAME

---------- --------------------

         1 Z1

         2 Z2

D:\app\oracle\oradata\test\test01p>ls -l lfree*
ls -l lfree*
-rw-rw-rw-   1 user     group    104865792 Aug 14 20:46 LFREE01.DBF
-rw-rw-rw-   1 user     group    104865792 Aug 14 20:44 LFREE01X.DBF

参看alert.log文件，也存在如下记录：
Wed Aug 14 20:41:48 2013
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
Wed Aug 14 20:41:48 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep
Wed Aug 14 20:44:40 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep