在线重建索引报错ORA-08104

mahan

参考文档：
Customer RecommendedHow to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (Doc ID 272735.1)
Drop Partition Failed With ORA-08104: This Index Object Is Being Online Built Or Rebuilt (Doc ID 2358693.1)

ORA-08104介绍：

[19c@test bin]$ oerr ora 08104
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


报错原理：
在进行online rebuild | create 时，Oracle 会修改如下信息：
修改ind$中索引的flags，将该flags+512. 关于flags的含义，在下面进行说明。
在该用户下创建一个临时日志表 （表名为sys_journal_<object_id>）来保存在创建或者重建索引期间产生的日志信息。
如果操作异常结束，而Oracle的SMON进程还没来得及清理journal table和ind$的flags标志位，系统会认为online (re)build操作还在执行。
因此在drop索引时会报错 this index object %s is being online built or rebuilt

关于ind$中的flags字段

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

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

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

解决方法：

1. 等待SMON进程清理
根据上面的原理，如果不着急的话，可以等待SMON进程自己去清理

2. 手动清理
首先查询问题索引的object_id

select object_id from dba_objects where object_name='问题索引名字';

执行下述存储过程进行优化
declare
isClean boolean;
begin
isClean := FALSE;
while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(object_id,dbms_repair.lock_wait);
dbms_lock.sleep(2);
end loop;
exception
when others then
RAISE;
end;
/

declare
done boolean;
begin
done:=dbms_repair.online_index_clean(275314);
end;
/

或者
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;
/

dbms_repair.lock_wait 表示不断寻找资源锁，直到抢到为止（如果一直占不到锁，可能会运行非常长时间）也可以指定 dbms_repair.all_index_id 清理所有问题索引


存储过程 官方介绍：
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;
Parameters

ONLINE_INDEX_CLEAN Function Parameters

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.