ORA-08104: CREATE/REBUILD online index的失效处理

今天在online rebuild索引时由于应用的特殊性使得索引长期无法获取TM锁，最终只能cancel掉索引，对于create或者rebuild online index异常终止或者cancel掉之后需要清除一些信息（ys_journal_xxx(xxx是object_id)以及其它的不一致的东西），否则无法rebuild或者recreate，也因为被锁无法进行drop操作，在9206,9207,10.2.0.1中smon不会很快地清除index rebuild online失败所带来的对象，在9208,10.2.0.2,11g中这个bug才被fixed.所以在9206,9207,10.2.0.1这些版本中，当我们对大的索引进行index rebuild online时， 不要随意地中断rebuild index online操作，否则smon可能需要很久才能清除临时对象。

Metalink对于如下

Note:375856.1

Note:351585.1

Bug 4364202

Bug 3805539

Bug 2702410

异常终止的情况下，可以发现ind$关于该索引的状态还是online rebuild的：

SQL> select obj#,flags from ind$ where obj#=126516;

OBJ# FLAGS

———- ———-

126516 514

Flag字段的说明可以在ind$的sql.bsq脚本中找到：

/* 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 */

514=0×202，表示该索引状态为index is being online rebuilt : 0×200 + analyzed : 0×02

在SMON完成清理动作后，再次查询索引状态已经恢复正常：

SQL> select obj#,flags from ind$ where obj#=67420;

OBJ#     FLAGS

———- ———-

126516 2

清除方法为使用DBMS REPAIR.ONLINE INDEX CLEAN这个包

This function performs a manual cleanup of failed or interrupted online index builds or rebuilds. This action is also performed periodically by SMON, regardless of user-initiated cleanup.

This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up.

Syntax

DBMS_REPAIR.ONLINE_INDEX_CLEAN (

   object_id      IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,

   wait_for_lock  IN BINARY_INTEGER DEFAULT LOCK_WAIT)

 RETURN BOOLEAN;

Parameter

Description

object_id

Object id of index to be cleaned up. The default cleans up all object ids that qualify.

wait_for_lock

This parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object. The default retries up to an internal retry limit, after which the lock get will give up. If LOCK_NOWAIT is specified, then the lock get does not retry.

 

对于rebuild和rebuild online的区别metalink Note:272762.1 

========

- Online Index rebuild takes a long time.

- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:

=========

Performance issues while rebuilding very large indexes.

- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.

- Issuing index rebuild ONLINE statement => finishes in about an hour.

- This behavior. of ONLINE index rebuilds makes it a non-option for large tables

as it just takes too long to scan the table to rebuild the index. The offline may not be feasible due to due to the 24/7 nature of the database.

- This may be a loss of functionality for such situations.

- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter hanging behavior. indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:

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

We can trace the sessions rebuilding the indexes with 10046 level 12.

Comparing the IO reads for the index-rebuild and the index-rebuild-online reveals the following:

-ONLINE index rebuilds

It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds

It scans the index for the build operation.

- This behaviour is across all versions.

Cause

Cause/Explanation

=============

When you rebuild index online,

- it will do a full tablescan on the base table.

- At the same time it will maintain a journal table for DML data, which has

changed during this index rebuilding operation.

So it should take longer time, specially if you do lots of DML on the same table,while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab the index in X-mode and rebuild a new index segment by selecting the data from the old index. So here we are

- not allowing any DML on the table hence there is no journal table involved

- and it is doing an index scan

Hence it will be pretty fast.

Fix

Solution/Conclusion:

===========

- The ONLINE index rebuild reads the base table, and this is by design.

- Rebuilding index ONLINE is pretty slow.

- Rebuilding index offline is very fast, but it prevents any DML on the base table.

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

转载于:http://blog.itpub.net/22503721/viewspace-714107/