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

lfree發表於2012-07-31
OracleIndexRebuild
[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;
/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-739206/，如需轉載，請註明出處，否則將追究法律責任。

相關文章