ORA-08104: 該索引物件68100正在被聯機建立或重建||如何清除建立失敗的索引?

lhrbest發表於2017-07-23

ORA-08104: 該索引物件68100正在被聯機建立或重建



在建立一張大表的索引時,因為沒有加並行而導致建立時間很長,這時候如果終止操作,比如直接關閉終端,然後再次登入建立時就會報錯ORA-00095提示物件名字已被用,而去刪除物件時又報錯ORA-08104索引正在被建立。

SQL> drop index xxx.BM_IX;
drop index xxx.BM_IX
                *
ERROR at line 1:
ORA-08104: this index object 1443829 is being online built or rebuilt
SQL> select object_id from dba_objects where object_name='BM_IX';
 OBJECT_ID
----------
   1443829
$ 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

從oerr工具看到的ORA-08104的解釋是索引並沒有建立失敗,而是在終端關閉之後,建立的操作還在繼續進行。建立或者重建索引時,系統會建立一個臨時日誌表,這張表被用於存放建立或者重建索引期間產生的日誌資訊,同時在基表IND$中這個索引的FLAG欄位上會被設定為BUILD或者REBUILD標識,當索引資訊變更時會把變更資訊存入日誌表。如果索引建立或者重建失敗,這個日誌表和資料字典中的狀態位都需要後臺程式smon進行清理。

因此這裡的索引不能被刪除是因為後臺程式smon還沒來得及清理相應的臨時段和標誌位,認為online rebuild操作還在進行。

那麼現在如何終止rebuild index這一操作?查詢metalink得到一篇文件:ORA-600 [12813] When Dropping A Table Partition After a Failed IndexRebuild (文件 ID 803008.1)。它給出了兩種方法:

1)使用包dbms_repair包來清理

如果在出現問題的物件的資料庫活動能停下來,則直接簡單地執行如下語句即可:

connect / as sysdba
select dbms_repair.online_index_clean(<problem index object_id>) from dual; 
exit


 

2)使用PL/SQL block呼叫dbms_repair包來清理

如果在出現問題的物件的資料庫活動不能停下來,則如下的PL/SQL block來處理

注:加上dbms_repair.lock_wait表示不是立刻清理,需要不斷的尋找資源鎖,直到搶到為止

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







測試人員報告某個sql查詢操作比較慢,希望協助查詢一下原因。

檢查發現IDX_LOG_BUSINON 碎片較為嚴重,決定重建索引。

為了不影響大家使用,決定用rebuild online的方式重建該索引。

 

 

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>  alter index IDX_LOG_BUSINON rebuild online ;

 

一會有人來叫去會議室討論影像遷移的問題,與是拔掉網線拿起筆記本去了

會議室。到了會議室發現這個會話已經斷開了。找根網線插上繼續rebuild索引。

 

SQL> alter index IDX_LOG_BUSINON rebuild online ;
alter index IDX_LOG_BUSINON rebuild online
*
第 1 行出現錯誤:
ORA-08104: 該索引物件 68100 正在被聯機建立或重建

 

檢查了一下68100物件,發現就是要rebuild的那個索引。

 

SQL> select OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE
  2    from dba_objects o
  3   where o.object_id = '68100';

OWNER    OBJECT_NAME         OBJECT_ID OBJECT_TYPE
-------- ------------------ ---------- -----------
REPORT   IDX_LOG_BUSINON         68100 INDEX

 

 

由於之前在ORACLE 10g 上遇到過這個問題,所以覺得沒啥。直接用

DBMS_REPAIR.ONLINE_INDEX_CLEAN 清理掉,在重建就好了。

 

SQL> desc dbms_repair

。。。省略部分描述
 FUNCTION  ONLINE_INDEX_CLEAN   RETURNS BOOLEAN


引數名稱                            型別                       輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN     DEFAULT
 WAIT_FOR_LOCK             BINARY_INTEGER          IN     DEFAULT

 

說明:DBMS_REPAIR.ONLINE_INDEX_CLEAN ()要求有返回值。


SQL> DECLARE
  2    RetVal BOOLEAN;
  3    OBJECT_ID BINARY_INTEGER;
  4    WAIT_FOR_LOCK BINARY_INTEGER;
  5  
  6  BEGIN
  7    OBJECT_ID := 68100;
  8    WAIT_FOR_LOCK := NULL;
  9    RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
 10    COMMIT;
 11  END;
 12  /

繼續開會。。。大約20分鐘會議結束。感覺索引應該rebuild結束。

但還沒執行完,這時候突然緊張起來了。趕緊去看alert*.log沒發現

有異常。什麼原因呢,測試環境中這張表的資料並不多,應該很快就能

搞定的。為什麼這麼長時間還沒完呢。是不是有人鎖表了呢。

 

 

SQL> SELECT /*+ rule */
  2         s.username,
  3         decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
  4         o.owner,
  5         o.object_name,
  6         o.object_type,
  7         s.sid,
  8         s.serial#,
  9    FROM gv$session s, gv$lock l, dba_objects o
 10   WHERE l.sid = s.sid
 11     AND l.id1 = o.object_id(+)
 12     AND s.username is NOT NULL ;

USERNAME    LOCK_LEVEL OWNER      OBJECT_NAME        OBJECT_TYPE    SID  SERIAL# 
----------- ---------- ---------- ------------------ ------------ ----- -------- 
REPORT                 REPORT     WFLOG              TABLE          154      159 
REPORT                 SYS        TAB$               TABLE          154      159 
REPORT                 REPORT     WFLOG              TABLE          154      159 
REPORT      TABLE LOCK REPORT     SYS_JOURNAL_68100  TABLE          154      159 
REPORT      ROW LOCK                                                154      159 
REPORT      TABLE LOCK REPORT     WFLOG              TABLE          154      159 
REPORT      ROW LOCK                                                138       10 
REPORT      TABLE LOCK REPORT     WFLOG              TABLE          138       10

 

果然有人鎖表了,找到那個哥們,發現她剛才也來開會了。她commit後,果然很快清理完了。

再次rebuild 這個索引,也很快搞定。

 

SQL> alter index IDX_LOG_BUSINON rebuild online ;

 

 

總結: 幸虧是趕在快吃飯時間用測試庫的人比較少,影響比較小。

          要是在生產庫上就是一次嚴重的事故了。不過在生產庫上

          有嚴格的審批流程,沒人敢去輕易操作。

 

結論:做事情要一心一意,不能分心。尤其是運算元據庫。



ONLINE方式線上重建索引異常中斷後遇到ORA-08104錯誤的處理思路

最近在處理ORA-08102錯誤時,使用ONLINE方式線上重建索引異常中斷後遇到ORA-08104錯誤;
ORA-08104錯誤網上有許多相關案例和解決方法,這裡我也彙總一下解決方法,記錄一下本次解決的心得。

當線上重建索引"ALTER INDEX ... REBUILD ONLINE"異常中斷後(異常的定義是沒有正常完成吧);再次重建時可能會遇到如下錯誤 :
ORA-08104: this index object 114615 is being online built or rebuilt
刪除(包括force選項)時均無法刪除。
#########################################

ORA-08104: 該索引物件68100正在被聯機建立或重建||如何清除建立失敗的索引? 故障原因是:

 create /rebuild index online時會修改資料庫字典表obj$,並在該索引使用者下建立表sys_journal_obj#(具體的物件號)和在ind$、ind_online$表裡(ind_online$字典基表記錄了索引線上建立/重建的歷史)標記256或512,(11g裡rebuild online是514??)。
如果伺服器程式在語句執行過程中意外終止的話,可能會導致相關在ind$標記位資訊及線上日誌中間表不能及時處理及清除(清除動作一般有smon程式處理,如果重建過程異常中斷,smon會清理重建痕跡,但是如果系統非常繁忙導致smon無法清除,或dml操作頻繁,導致smon無法獲取相關表上的鎖,從而造成無法清理痕跡,當再次重建索引或對錶進行dml操作會報本篇提示錯誤),這將導致對該索引的後續操作因ora-08104錯誤而無法繼續,如果是分割槽表,索引是global,在新增分割槽也無法繼續。

此時對此進行驗證:
  1. declare   
  2. isClean boolean;  
  3. begin   
  4. isClean :=  FALSE ;  
  5. while isClean= FALSE  loop  
  6. isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,  
  7. dbms_repair.lock_wait);  
  8. dbms_lock.sleep(2);  
  9. end  loop;  
  10. exception  
  11. when  others  then   
  12. RAISE;  
  13. end ;  
  14. /   


-----------------
也可以指定具體的OBJECT_ID,如:dbms_repair.online_index_clean(114615);
--------------------
關於此函式,11gR2官方文件介紹如下:
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.


如果一直嘗試清理且不成功,對應的alert日誌中會有如下提示(隔幾分鐘一次):
Mon Dec 07 19:33:51 2015
online index (re)build cleanup: objn=114615 maxretry=2000 forever=0

如果一直不成功,一個可選方法是將此索引對應表的TM鎖的程式KILL;使用如下語句查詢:

  1. select  status,instance_name  from  v$instance;  
  2. select  pid,spid  from  v$process p,v$bgprocess b  where  b.paddr=p.addr  and   name = 'SMON' ;  
  3.        PID SPID  
  4. ---------- ------------------------   
  5.         22 1741  
  6. oradebug wakeup 22  
  7.   
  8. select  status,instance_name  from  v$instance;  



此步驟可以多次嘗試。關於為什麼SMON程式未清理掉,可能是未到達SMON程式清理的閥值,或者與當時資料庫負載等多種因素有關。
網上解釋有:
SMON負責在啟動後(startup)的每小時執行一次對IND$基表中因線上建立/重建索引失敗所留下記錄的清理,這種清理工作由kdicclean函式驅動(kdicclean is run by smon every 1 hour,called from SMON to find if there is any online builder death and cleanup our ind$ and obj$ and drop the journal table, stop journaling)。 這種清理工作典型的呼叫堆疊stack call如下:
ksbrdp -> ktmSmonMain  ktmmon -> kdicclean -> kdic_cleanup -> ktssdrp_segment
注意因為SMON程式的清理工作每小時才執行一次,而且在工作負載很高的情況下可能實際很久都不會得到清理,
OBJ$基表是一張低階資料字典表,該表幾乎對庫中的每個物件(表、索引、包、檢視等)都包含有一行記錄。很多情況下,這些條目所代表的物件是不存在的物件(non-existent),引起這種現象的一種可能的原因是物件本身已經被從資料庫中刪除了,但是物件條目仍被保留下來以滿足消極依賴機制(negative dependency)。因為這些條目的存在會導致OBJ$表不斷膨脹,這時就需要由SMON程式來刪除這些不再需要的行。SMON會在例項啟動(after startup of DB is started cleanup function again)時以及啟動後的每12個小時執行一次清理任務(the cleanup is scheduled to run after startup and then every 12 hours)。


如果方法2喚醒SMON程式進行清理也不成功,建議是安排停機時間,重啟資料庫例項了。
如果實在不方便重啟資料庫例項,對此索引又可以暫時不執行DDL操作,那麼可以暫時忽略(此時原索引狀態是VALID,不影響使用),等待停機視窗對資料庫例項進行重啟。
如果實在不方便重啟資料庫例項又需要重建索引(如索引遇到ORA-08102錯誤),那麼還有一招是修改資料庫字典基表,這個方法就不介紹了,生產環境是不會用的;並且底層基表多數存在互相關聯,容易出錯,慎用!!!





線上重建索引 (alter index index_name rebuild online)雖然延長了索引重建的時間,卻也賦予了我們線上重建索引,提高資料可用性的能力。如果在聯機重建索引的過程中出現錯誤,如使用者終止,網路中斷等,那麼當我們再次重建索引時,有可能會產生ORA-08104錯誤。這是由於先前的操作痕跡沒有清除而造成的。

線上重建索引的過程中,oracle資料庫會修改資料字典表,並生成中間表(IOT)來記錄索引重建期間發生的dml操作。如果重建過程異常中斷,smon程式會清理重建痕跡,但是如果系統非常繁忙導致smon應接不暇或者dml操作過多導致smon無法獲取相關表上的鎖,從而無法清理重建痕跡,當我們再次重建索引時,就會產生ora-08104錯誤。

下面我們構造一個ora-08104錯誤


  1. declare   
  2. isclean boolean;  
  3. begin   
  4. isclean := false ;  
  5. while isclean= false   
  6. loop  
  7. isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);  
  8. dbms_lock.sleep(10);  
  9. end  loop;  
  10.  10   end ;  
  11.  11  /  
  12.   
  13. PL/SQL 過程已成功完成。  


注意事項:在執行過程中,需要在索引所在表上獲取鎖,因此應儘可能的保證索引表不被其他事務鎖定,以儘快清理臨時資料,如果長時間不能清除資料,檢視後臺日誌,我們會發現


  1. sql> update  ind$  set  flags=flags-512  where  obj#=<object id>; /* 首先要確認flags>512如果不是,說明這個標誌是正常的*/  
  2. sql> drop   table  <owner>.sys_journal_<object_id>; /*這個步驟可能會報資源忙,因為有大量的日誌正在插入,可以反覆重試一下 */  


注意順序操作順序,不到萬不得已,不要修改資料字典



  • 喚醒SMON    


我們可以嘗試使用ORADEBUG WAKEUP 來喚醒smon,可以多試幾次




ORA-600 [12813] When Dropping A Table Partition After a Failed Index Rebuild (文件 ID 803008.1)

In this Document


Symptoms

Changes

Cause

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.

SYMPTOMS

When trying to drop a partition of a table which has an index which failed an online index rebuild operation, an ORA-600 [12813] error is signaled, e.g.:

ORA-20000: this index object "<owner>"."<index name>" is being online built or rebuilt 
ORA-00600: internal error code, arguments: [12813], [1], [268453], [], [], [], [], []

CHANGES

An online index rebuild was canceled or interrupted.

CAUSE

This is due to the failed online index rebuild not having been cleaned up successfully.

SOLUTION

SMON should cleanup the failed online index rebuild operation and so correct this.  However, if the table is highly active with transactions, SMON may not be able to get the required lock and so the index will not get cleaned up.  In such situations, you can manually cleanup the failed index rebuild using the DBMS_REPAIR.ONLINE_INDEX_CLEAN procedure.

To do this, if activity on the problem table can be stopped, then simply execute:

connect / as sysdba
select dbms_repair.online_index_clean(<problem index object_id>) from dual; 
exit

If activity on the table cannot be stopped, then it may be possible to resolve the problem using the following PL/SQL block:

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

NOTE:
This may need to run for many hours however before it can finally get the required access to the table and index.

REFERENCES


NOTE:3805539.8  - Bug 3805539 - Add DBMS_REPAIR.ONLINE_INDEX_CLEAN to manually clean up failed ONLINE builds




About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub( http://blog.itpub.net/26736162 )、部落格園( http://www.cnblogs.com/lhrbest )和個人微信公眾號( xiaomaimiaolhr )上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址: http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群: 230161599      微信群:私聊

● 聯絡我請加QQ好友(646634621),註明新增緣由

● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

...............................................................................................................................

拿起手機使用 微信客戶端 掃描下邊的 左邊 圖片來關注小麥苗的微信公眾號: xiaomaimiaolhr,掃描 右邊 的二維碼加入小麥苗的QQ群, 學習最實用的資料庫技術。

ORA-08104: 該索引物件68100正在被聯機建立或重建||如何清除建立失敗的索引?
DBA筆試面試講解
歡迎與我聯絡

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2142437/,如需轉載,請註明出處,否則將追究法律責任。

相關文章