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