oracle rebuild online,rebuild online被终止后的错误 ora-08104

先是在主库上对justin执行DML操作，涉及数据不到两万条，却因此导致备库延迟；

备库上对应的justin的上没有任何索引，而该表有600多万数据，生成库上的操作到此就演化为对该表全表扫描将近两万次，无怪乎备库会延迟；

当时为了避免这种状况再次发生，在备库执行了create index indx_justin_number on justin(justin_number)online

却因为基于该表的DML没有结束而排队等待，最后将此创建索引操作kill；

问题来了，明明该列没有索引，再次执行却报以下错误

SQL> create index indx_justin_number on justin(justin_number) online;

create index indx_justin_number on justin(justin_number) online

ORA-01408: such column list already indexed

SQL> drop table justin;

drop table justin

ORA-08104: this index object 405828 is being online built or rebuilt

此时要停止逻辑备库应用也被阻塞了

Tue Sep 20 16:47:25 CST 2011

alter database stop logical standby apply

Tue Sep 20 16:47:25 CST 2011

ALTER DATABASE STOP LOGICAL STANDBY APPLY

Tue Sep 20 16:47:25 CST 2011

LOGSTDBY: Shutdown acknowledged

Tue Sep 20 16:48:51 CST 2011

RFS LogMiner: Client enabled and ready for notification

Tue Sep 20 16:48:51 CST 2011

Primary database is in MAXIMUM PERFORMANCE mode

RFS[6]: Successfully opened standby log 15: '/data/oracle/oradata/justin/stdby_redo15.log'

Tue Sep 20 16:48:53 CST 2011

Primary database is in MAXIMUM PERFORMANCE mode

RFS[23]: Successfully opened standby log 26: '/data/oracle/oradata/justin/stdby_redo26.log'

Tue Sep 20 16:48:55 CST 2011

RFS LogMiner: Registered logfile [/data/oracle/oradata/justin/arch/2_6591_657561562.dbf] to LogMiner session id [1]

Tue Sep 20 16:48:55 CST 2011

RFS LogMiner: Client enabled and ready for notification

Tue Sep 20 16:48:56 CST 2011

RFS LogMiner: Registered logfile [/data/oracle/oradata/justin/arch/4_2617_657561562.dbf] to LogMiner session id [1]

Tue Sep 20 16:52:10 CST 2011

alter database stop logical standby apply

Tue Sep 20 16:52:10 CST 2011

ALTER DATABASE STOP LOGICAL STANDBY APPLY

找到目前正在更新表justin的session，将其kill后方才成功将逻辑备库停掉；

再说说ORA-08104

这是rebuild online被手工kill常见的现象，解决方案很简单。

先找到该索引的object_id

SQL> select object_id from dba_objects where object_name ='IND_justin_NUMBER';

OBJECT_ID

----------

405828

然后通过sys用户执行

SQL> declare

2     v_clean boolean;

3  begin

4    v_clean := FALSE;

5    v_clean := dbms_repair.online_index_clean(405828,dbms_repair.lock_wait);

6  end;

7  /

PL/SQL procedure successfully completed.

此时以上错误便可解决，可再度在此列上创建索引

关于ora-8104，metalink有很多文章介绍

Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]

________________________________________

修改时间 29-APR-2010     类型 PROBLEM     状态 MODERATED

In this Document

Symptoms

Cause

Solution

References

________________________________________

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 11.1.0.6 - Release: 9.2 to 11.1

Information in this document applies to any platform.

Symptoms

While running an online index rebuild your session was killed or otherwise terminated abnormally.  You are now attempting to run the index rebuild again and is throwing the error:

ORA-08104: this index object ##### is being online built or rebuilt

Cause

A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.

Solution

The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.

* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.  The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not allowed in patchsets; therefore, this is not available in a patchset but is available in 10gR2.

- Check your patch list to verify the database is patched for Bug 3805539 using the following command and patch for the bug if it is not listed:

opatch lsinventory -detail