ORA-08104: 該索引物件68100正在被聯機建立或重建||如何清除建立失敗的索引?
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選項)時均無法刪除。
#########################################
故障原因是:
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,在新增分割槽也無法繼續。
此時對此進行驗證:
- 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 ;
- /
-----------------
也可以指定具體的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;使用如下語句查詢:
- select status,instance_name from v$instance;
- select pid,spid from v$process p,v$bgprocess b where b.paddr=p.addr and name = 'SMON' ;
- PID SPID
- ---------- ------------------------
- 22 1741
- oradebug wakeup 22
- 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錯誤
- 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(10);
- end loop;
- 10 end ;
- 11 /
- PL/SQL 過程已成功完成。
注意事項:在執行過程中,需要在索引所在表上獲取鎖,因此應儘可能的保證索引表不被其他事務鎖定,以儘快清理臨時資料,如果長時間不能清除資料,檢視後臺日誌,我們會發現
- sql> update ind$ set flags=flags-512 where obj#=<object id>; /* 首先要確認flags>512如果不是,說明這個標誌是正常的*/
- 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-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:
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:
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群, 學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2142437/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- online 建立索引失敗處理索引
- MySQL建立表的時候建立聯合索引的方法MySql索引
- Oracle如何建立B樹索引Oracle索引
- mysql 建立和刪除聯合索引MySql索引
- ElasticSearch建立索引Elasticsearch索引
- DocumentDB 建立索引索引
- 多表關聯查詢中,關聯欄位都應該建立索引嗎?索引
- 聯合索引和多個單列索引使用中的索引命中情況及索引建立原則索引
- MySQ索引操作命令總結(建立、重建、查詢和刪除索引命令詳解)索引
- mysql 建立索引的方法--建立檢視MySql索引
- mongodb資料庫如何建立索引?MongoDB資料庫索引
- MongoDB如何在後臺建立索引MongoDB索引
- zt_如何加速索引index建立索引Index
- MySQL如何建立一個好索引?建立索引的5條建議【宇哥帶你玩轉MySQL 索引篇(三)】MySql索引
- 【Oracle】-【建立索引】-建立索引的操作原理與一些體會Oracle索引
- MySQL建立索引、修改索引、刪除索引的命令語句MySql索引
- mysql建立字首索引MySql索引
- 建立索引,這些知識應該瞭解索引
- 如何匯出MySQL索引的建立語句MySql索引
- oracle如何估算即將建立的索引大小Oracle索引
- 【oracle 】如何估算即將建立的索引大小Oracle索引
- oracle 建立或重建索引時收集統計資訊不自動收集(_optimizer_compute_index_stats)Oracle索引Index
- [20200108]線上建立索引失敗分析.txt索引
- mongodb建立索引和刪除索引和背景索引backgroundMongoDB索引
- oracle 索引的建立與管理Oracle索引
- 索引的重建命令索引
- oracle 索引分析及索引重建Oracle索引
- 索引組織表上建立BITMAP索引(三)索引
- 索引組織表上建立BITMAP索引(二)索引
- 索引組織表上建立BITMAP索引(一)索引
- postgresql怎麼建立索引SQL索引
- Lucene建立索引流程索引
- MySQL建立複合索引MySql索引
- SqlServer 建立全文索引SQLServer索引
- MySQL索引建立原則MySql索引
- MySQL 的索引型別及如何建立維護MySql索引型別
- 【Mongo】MongoDB索引管理-索引的建立、檢視、刪除MongoDB索引
- 建立函式失敗函式