[20120730]11g下Oracle Index rebuild online之2.txt

oracle rebuild online时要建立一张IOT表，为了保证事务依旧能操作，需要记录索引rebuild期间的DML操作。
前面已经介绍了10g与11g下rebuild的机制有一些不同。再补充一些.

index online rebuild 前先建立一张IOT表跟踪后续DML操作，然后merge全部的改变到索引中。

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t (id number,name varchar2(10));

create index i_t_id on t(id);

insert into t  select rownum id ,'test' name from dual connect by level <=100;

commit ;

1.开始测试：
会话1插入1行：

SQL> select * from v$mystat where rownum<=1;

       SID STATISTIC#      VALUE

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

       133          0          0

insert into t values (101,'a');

会话2插入1行：

SQL> select * from v$mystat where rownum<=1;

       SID STATISTIC#      VALUE

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

       192          0          0

alter index i_t_id rebuild online ;

回到回话1执行:

SQL> host cat viewlock.sql

SELECT lk.SID, se.username, se.osuser, se.machine,

       DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,

       DECODE (lk.lmode,

               0, 'None',

               1, 'Null',

               2, 'Row-S (SS)',

               3, 'Row-X (SX)',

               4, 'Share',

               5, 'S/Row-X (SSX)',

               6, 'Exclusive',

               TO_CHAR (lk.lmode)

              ) mode_held,

       DECODE (lk.request,

               0, 'None',

               1, 'Null',

               2, 'Row-S (SS)',

               3, 'Row-X (SX)',

               4, 'Share',

               5, 'S/Row-X (SSX)',

               6, 'Exclusive',

               TO_CHAR (lk.request)

              ) mode_requested,

       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,

       DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait

  FROM v$lock lk, dba_objects ob, v$session se

 WHERE lk.TYPE IN ('TX','TM','UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);

SQL> @viewlock ;

   SID USERNAME   OSUSER     MACHINE    LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT

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

   192 SCOTT      oracle11g  hisdg      DML          Share      None       101683     0          SCOTT  TABLE      SYS_JOURNAL_101585   No    00000000DFC524D0

   192 SCOTT      oracle11g  hisdg      DML          Row-S (SS) None       101584     0          SCOTT  TABLE      T                    No    00000000DFC524D0

   133 SCOTT      oracle11g  hisdg      DML          Row-X (SX) None       101584     0          SCOTT  TABLE      T                    No

   133 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       655364     6348                                              Yes

   192 SCOTT      oracle11g  hisdg      Transaction  None       Share      655364     6348                                              No    00000000DFC524D0

   192 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       589841     7146                                              No    00000000DFC524D0

6 rows selected.

--可以发现因为前面的事务没有rollback/commit,online rebuild的过程挂起.

2.打开新回话3:

SQL> select * from v$mystat where rownum<=1;

       SID STATISTIC#      VALUE

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

        70          0          0

SQL> select * from  SYS_JOURNAL_101585;

no rows selected

SQL> insert into t  values (103,'c');

1 row created.

--上次提到回话3并没有挂起！而是正常执行。

SQL> @viewlock

   SID USERNAME   OSUSER     MACHINE    LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT

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

   192 SCOTT      oracle11g  hisdg      DML          Share      None       101683     0          SCOTT  TABLE      SYS_JOURNAL_101585   No    00000000DFC524D0

   192 SCOTT      oracle11g  hisdg      DML          Row-S (SS) None       101584     0          SCOTT  TABLE      T                    No    00000000DFC524D0

   133 SCOTT      oracle11g  hisdg      DML          Row-X (SX) None       101584     0          SCOTT  TABLE      T                    No

    70 SCOTT      oracle11g  hisdg      DML          Row-X (SX) None       101584     0          SCOTT  TABLE      T                    No

   133 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       655364     6348                                              Yes

   192 SCOTT      oracle11g  hisdg      Transaction  None       Share      655364     6348                                              No    00000000DFC524D0

    70 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       327685     7460                                              No

   192 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       589841     7146                                              No    00000000DFC524D0

8 rows selected.

SQL> select * from  SYS_JOURNAL_101585;

        C0 O     PARTNO RID

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

       103 I          0 D/////AAEAAAACmAAB

--检查IOT表可以发现记录一条记录。

SQL> commit ;

Commit complete.

SQL> select * from  SYS_JOURNAL_101585;

        C0 O     PARTNO RID

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

       103 I          0 D/////AAEAAAACmAAB

--journal table 中记录还存在! rebuild online还在进行中(因为回话1的事务没有提交).

3.现在在回话3中修改记录:

SQL> update t set id=102,name='b' where id=103;
1 row updated.

--再看看SYS_JOURNAL_101585中记录如何呢?

SQL> select * from  SYS_JOURNAL_101585;

        C0 O     PARTNO RID

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

       102 I          0 D/////AAEAAAACmAAB

       103 D          0 D/////AAEAAAACmAAB

--可以看到仅仅记录2条.索引的修改相当于delete+insert.
--原来c0=103的 OPCODE  从"I"=>"D".插入的id=102.

再修改一条记录:

SQL> update t set id=999,name='z' where id=100;

1 row updated.

SQL> select * from  SYS_JOURNAL_101585;

        C0 O     PARTNO RID

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

       100 D          0 D/////AAEAAAAClABj

       102 I          0 D/////AAEAAAACmAAB

       103 D          0 D/////AAEAAAACmAAB

       999 I          0 D/////AAEAAAAClABj

--再次看出索引的修改相当于 delete+insert.
--这样在rebuild online 时merge journal table阶段,因为IOT表是按照C0,RID为主键的组织的,如果我们顺序扫描IOT,可以看出.
       102 I          0 D/////AAEAAAACmAAB
       103 D          0 D/////AAEAAAACmAAB
C0=103的这条103 D          0 D/////AAEAAAACmAAB,

先执行插入的是102,后执行delete103.
执行的delete操作时,应该是操作对象不存在.因为前面的插入是id=102.但是这样并没有导致索引不一致的情况.

4.现在在回话3中:
做一个奇怪delete的操作看看:

commit;

delete from SYS_JOURNAL_101585 where rid='D/////AAEAAAAClABj';

挂起!

回到回话1

SQL> @viewlock ;

   SID USERNAME   OSUSER     MACHINE    LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT

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

    70 SCOTT      oracle11g  hisdg      DML          None       Row-X (SX) 101686     0          SCOTT  TABLE      SYS_JOURNAL_101585   No    00000000DCB9C248

   192 SCOTT      oracle11g  hisdg      DML          Share      None       101686     0          SCOTT  TABLE      SYS_JOURNAL_101585   Yes   00000000DFC52670

   133 SCOTT      oracle11g  hisdg      DML          Row-X (SX) None       101584     0          SCOTT  TABLE      T                    No

   192 SCOTT      oracle11g  hisdg      DML          Row-S (SS) None       101584     0          SCOTT  TABLE      T                    No    00000000DFC52670

   133 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       393224     8268                                              Yes

   192 SCOTT      oracle11g  hisdg      Transaction  None       Share      393224     8268                                              No    00000000DFC52670

   192 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       196617     7070                                              No    00000000DFC52670

7 rows selected.

可以发现SYS_JOURNAL_101585在share模式,其他用户不能对该表直接进行DML操作.

回到回话3,按ctrl+c取消操作.
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


5.回到回话2,按ctrl+c取消rebuild online操作.

SQL> alter index i_t_id rebuild online ;

alter index i_t_id rebuild online

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> select * from  SYS_JOURNAL_101585;

        C0 O     PARTNO RID

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

       100 D          0 D/////AAEAAAAClABj

       102 I          0 D/////AAEAAAACmAAB

       103 D          0 D/////AAEAAAACmAAB

       999 I          0 D/////AAEAAAAClABj

--可以发现journal table SYS_JOURNAL_101585依旧存在.
--再次执行rebuild online.看看如何?(注意这是回话1没有提交).

SQL> alter index i_t_id rebuild online ;

alter index i_t_id rebuild online

*

ERROR at line 1:

ORA-08104: this index object 101585 is being online built or rebuilt

$ oerr  ORA 8104

08104, 00000, "this index object %s is being online built or rebuilt"

// *Cause:  the index is being created or rebuild or waited for recovering

//          from the online (re)build

// *Action: wait the online index build or recovery to complete

SQL> drop table  SYS_JOURNAL_101585;

Table dropped.

SQL> alter index i_t_id rebuild online ;

alter index i_t_id rebuild online

*

ERROR at line 1:

ORA-08104: this index object 101585 is being online built or rebuilt

--可以发现即使删除 SYS_JOURNAL_101585 IOT表,在rebuild online时一样报错!why?该如何解决呢?

google发现许多链接:
要使用dbms_repair包,但是奇怪这样调用有问题:(恢复SYS_JOURNAL_101585 IOT表.)

SQL> select sys.dbms_repair.online_index_clean(101585) from dual;

select sys.dbms_repair.online_index_clean(101585) from dual

           *

ERROR at line 1:

ORA-06552: PL/SQL: Statement ignored

ORA-06553: PLS-382: expression is of wrong type

这个函数返回类型是boolean类型的.

看文档的例子如下:

function online_index_clean(

   object_id      IN binary_integer DEFAULT ALL_INDEX_ID,

   wait_for_lock  IN binary_integer DEFAULT LOCK_WAIT)

   return boolean;

 --   Example Usage of online_index_clean:

 --   DECLARE

 --     isClean BOOLEAN;

 --   BEGIN

 --

 --     isClean := FALSE;

 --     WHILE isClean=FALSE

 --     LOOP

 --       isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,

 --                                                 DBMS_REPAIR.LOCK_WAIT);

 --       DBMS_LOCK.SLEEP(10);

 --     END LOOP;

 --

 --     EXCEPTION

 --      WHEN OTHERS THEN

 --      RAISE;

 --   END;

 --   /

修改如下:

DECLARE

  isClean BOOLEAN;

BEGIN

  isClean := FALSE;

    isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(101585,DBMS_REPAIR.LOCK_WAIT);

  EXCEPTION

   WHEN OTHERS THEN

   RAISE;

END;

/