ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130814] 12C Online rename and relocation of an active data file.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-08-16 09:34:42 0 删除 编辑
[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 level<=100;
Table 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<=2;

        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 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-768635/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2600
  • 访问量
    6374905