暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

ORA-08104 when online index rebuild

原创 Anbob 2015-01-24
1840
Last night ,The Developers ask me they have creating an index do not use parallel,Unacceptably slow. then kill the session to cancel, but now to face ORA-08104 during trying to online index rebuild . that is a busy product database(7x24). DB version 10205 2nodes rac on hpux.
SQL> ALTER INDEX ANBOB.BB_BUS_INFO_IR REBUILD online parallel 32;
ALTER INDEX CRM_OWNER_USER.BB_BUS_INFO_IR REBUILD online parallel 32
*
ORA-08104: this index object 3878859 is being online built or rebuilt
SQL> @oid 3878858
owner object_name object_type
------------------------- ------------------------------ ------------------
ANBOB BB_BUS_INFO_IR INDEX
# 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> select STATUS from dba_indexes where index_name='BB_BUS_INFO_IR';
STATUS
--------
UNUSABLE

SQL> select obj#,flags from ind$ where obj#=3878858;
OBJ# FLAGS
---------- ----------
3878858 517
SQL> select to_char(517,'xxxxxxx') from dual;
TO_CHAR(
--------
205

ind$.flags         number not null,
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */

Tip:
Online index rebuilds which fail (for any reason) leave the dictionary marked that the rebuild was in progress and SMON should
clean up the dictionary (kdicclean). This cleanup function is only executed every hour by SMON so you have to wait for SMON to clean IND$.
Of course, we can also manually clean up, To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:
SQL> DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(3878859, DBMS_REPAIR.LOCK_WAIT);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
PL/SQL 过程已成功完成。

SQL> drop index ANBOB.BB_BUS_INFO_IR;
-- do some modify to speed up create index
SQL> create index ANBOB.BB_BUS_INFO_IR ... [online] [parallel] [nologging];
OR
-- do some modify to speed up create index
SQL> ALTER INDEX ANBOB.BB_BUS_INFO_IR REBUILD online parallel ;
Note:
If 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
So if you do lots of DML on the same table,while rebuilding index online,it should take longer time.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论