[20120730]11g下Oracle Index rebuild online之2.txt
oracle rebuild online时要建立一张IOT表，为了保证事务依旧能操作，需要记录索引rebuild期间的DML操作。
前面已经介绍了10g与11g下rebuild的机制有一些不同。再补充一些.
index online rebuild 前先建立一张IOT表跟踪后续DML操作，然后merge全部的改变到索引中。
1.开始测试：
会话1插入1行：
会话2插入1行：
回到回话1执行:
--可以发现因为前面的事务没有rollback/commit,online rebuild的过程挂起.
2.打开新回话3:
--journal table 中记录还存在! rebuild online还在进行中(因为回话1的事务没有提交).
3.现在在回话3中修改记录:
SQL> update t set id=102,name='b' where id=103;
1 row updated.
--再看看SYS_JOURNAL_101585中记录如何呢?
--可以看到仅仅记录2条.索引的修改相当于delete+insert.
--原来c0=103的 OPCODE 从"I"=>"D".插入的id=102.
再修改一条记录:
--这样在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的操作看看:
挂起!
回到回话1
可以发现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操作.
--可以发现journal table SYS_JOURNAL_101585依旧存在.
--再次执行rebuild online.看看如何?(注意这是回话1没有提交).
google发现许多链接:
要使用dbms_repair包,但是奇怪这样调用有问题:(恢复SYS_JOURNAL_101585 IOT表.)
这个函数返回类型是boolean类型的.
看文档的例子如下:
修改如下:
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
commit ;
1.开始测试：
会话1插入1行：
SQL> select * from v$mystat where rownum
SID STATISTIC# VALUE
---------- ---------- ----------
133 0 0
insert into t values (101,'a');
会话2插入1行：
SQL> select * from v$mystat where rownum
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
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;
/
