oracle alter logging,Oracle alter index rebuild 说明

后续操作：

session 1：commit

SYS@anqing2(rac2)> commit;

Commit complete.

查看lock信息：

SYS@anqing2(rac2)> select sid,type,id1,id2,lmode,request,block from v$lock where type in('DL','TM','TX');

SID TYID1ID2LMODEREQUESTBLOCK

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

147 DL533660302

147 DL533660302

147 TM533660242

147 TM533860402

143 TM533660301

143 TX1048602287602

147 TX983080291602

这里还有两个锁，147是我们的rebuild online。143是我们之前session 3的update DML。

这里要注意的是，如果我们的session 3不commit，那么我们的rebuild online还是不会结束。 因为rebuild online在开始和结束的时候需要申请去拿TM 4的lock。 现在rebuild online的被我们的session 3阻止了。 在开始是被session 1阻止了。

我们在session 3提交一下，在查看lock：

SYS@anqing2(rac2)> commit;

Commit complete.

SYS@anqing2(rac2)> select sid,type,id1,id2,lmode,request,block from v$lock where type in('DL','TM','TX');

no rows selected

rebuild结束

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

Index altered.

Elapsed: 00:16:44.86

由此可见rebuild online是个需要谨慎使用的命令。

3.3.2场景2

在前面说过， kill掉正在rebuild online的session。 可能会导致在下次rebuild index或者drop，analyze的时候报ORA-08104的错误。 因为在异常终止online rebuild操作的时候，没来得及清理相应的临时段和标志位，系统认为online rebuild操作还在进行造成的。

在这里我们就模拟一下这个操作。

在rebuild online的时候，按下ctrl + c结束：

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

alter index idx_rbt_id rebuild online

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

Elapsed: 00:00:01.78

SYS@anqing2(rac2)> exec dbms_stats.gather_table_stats('SYS','RB_TEST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.02

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

Index altered.

Elapsed: 00:00:09.38

没有报错。把数据量弄大一点。在试试

SYS@anqing2(rac2)> insert into rb_test select * from rb_test;

1000000 rows created.

Elapsed: 00:00:23.68

SYS@anqing2(rac2)> commit;

Commit complete.

Elapsed: 00:00:00.08

SYS@anqing2(rac2)> insert into rb_test select * from rb_test;

2000000 rows created.

Elapsed: 00:01:54.36

SYS@anqing2(rac2)> commit;

Commit complete.

Elapsed: 00:00:00.00

用kill session的方法试试：

SYS@anqing2(rac2)> alter system kill session '147,31436';

System altered.

Elapsed: 00:00:01.01

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

alter index idx_rbt_id rebuild online

*

ERROR at line 1:

ORA-00028: your session has been killed

Elapsed: 00:00:10.89

还是没有报错：

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

Index altered.

Elapsed: 00:00:51.65

比较顽强啊。有点小崩溃。再来：

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

启动rebuild online之后，直接把ssh强行关闭。

在次ssh过去，尝试rebuild online：

SYS@anqing2(rac2)> alter index idx_rbt_id rebuild online;

alter index idx_rbt_id rebuild online

*

ERROR at line 1:

ORA-08104: this index object53367is being online built or rebuilt

终于报错了。 不容易啊。 不过这个测试也说明了，在rebuild online期间不能强行关闭ssh。如果在期间断网的话，估计效果相当。

查看Flag：

SYS@anqing2(rac2)> select obj#,flags from ind$ where obj#=53367;

OBJ#FLAGS

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

53367514

根据NiGoo blog上的说明，可以通过ind$的flags查看是什么类型的标志。

sql.bsq是个总的说明，在dcore.bsq里找到了ind$的创建SQL：

/* mutable flags: anything permanent should go into property */

/* unusable (dls) : 0×01 */

/* analyzed : 0×02 */

/* no logging : 0×04 */

/* index is currently being built : 0×08 */

/* index creation was incomplete : 0×10 */

/* key compression enabled : 0×20 */

/* user-specified stats : 0×40 */

/* secondary index on IOT : 0×80 */

/* index is being online built : 0×100 */

/* index is being online rebuilt : 0×200 */

/* index is disabled : 0×400 */

/* global stats : 0×800 */

/* fake index(internal) : 0×1000 */

/* index on UROWID column(s) : 0×2000 */

/* index with large key : 0×4000 */

/* move partitioned rows in base table : 0×8000 */

/* index usage monitoring enabled : 0×10000 */

这里的0×200等是十六进制来表示的。 Flags是514， 其16进制是是202，514=0×202，表示该索引状态为index is being online rebuilt : 0×200 + analyzed : 0×02

在上面，我们说减去512. 512的16进制是200.对应的是：/* index is being online rebuilt : 0×200 */。 所以，我们在rebuild的时候，会对flags加上512.

MOS 803008.1上的说明：

SMON should cleanup the failed online index rebuild operation and so correct this. However, if the table is highly active with transactions, SMON may not be able to get the required lock and so the index will not get cleaned up. In such situations, you can manually cleanup the failed index rebuild using the DBMS_REPAIR.ONLINE_INDEX_CLEAN procedure.

To do this, if activity on the problem table can be stopped, then simply execute:

connect / as sysdba

select dbms_repar.online_index_clean() from dual;

exit

不过这个命令执行没有成功：

SYS@anqing2(rac2)> select dbms_repair.online_index_clean(53367) from dual;

select dbms_repair.online_index_clean(53367) from dual

*

ERROR at line 1:

ORA-06552: PL/SQL: Statement ignored

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

纠结中...

If activity on the table cannot be stopped,then it may be possible to resolve the problem using the following PL/SQL block:

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(2);

end loop;

exception

when others then

RAISE;

end;

/

或者：

DECLARE

RetVal BOOLEAN;

OBJECT_ID BINARY_INTEGER;

WAIT_FOR_LOCK BINARY_INTEGER;

BEGIN

OBJECT_ID := 53367;

WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();

COMMIT;

END;

/

SYS@anqing2(rac2)>select obj#,flags from ind$ where obj#=53367;

OBJ#FLAGS

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

533672

在这个测试上也是相当的纠结。 之前产生了ORA-08104的错误，但是如果之后没有其他的DML来操作这张表，那么online rebuild产生的lock一段时间之后就会释放掉，然后ind$的flag也会变成2.即正常状态。

如果事务A阻止online rebuild申请TM 4锁，那么之前所有的事务都会挂住，当事务A commit之后，相关的锁会释放，索引也会变成正常状态。

在执行clean命令的时候，可能会遇到：

ORA-00054: resource busy and acquire with NOWAIT specified

多执行几次就ok了。 应该也是和这个锁有关。

可能还是环境模拟的有问题。 这个测试总感觉怪怪的。 纠结啊。