Move系統表DEPENDENCY$導致索引失效的資料庫故障的另一種處理方式

westzq1984發表於2012-12-16

索引資料庫

年底不安全，盡出些稀奇古怪的問題。今天一個客戶的一個非核心繫統，9i的庫的dependency$被MOVE導致索引失效，但是又沒有去重建索引，導致資料庫關閉後無法啟動

該案例的現象為：

MOVE dependency$ 後，其上索引I_DEPENDENCY1/ I_DEPENDENCY2失效

資料庫正常關閉後，開啟報錯：

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

alert日誌報錯：

Sun Dec 16 21:04:40 2012

Errors in file /u01/app/oracle/admin/o9208/udump/o9208_ora_28069.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state

Sun Dec 16 21:04:40 2012

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 28069

ORA-1092 signalled during: ALTER DATABASE OPEN...

sqltrace可以得到報錯的語句為：

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#

dbsnake在oracle手記2中分享過處理的辦法為BBED修改。但是覺得遠端做風險太大，而且我也很久沒看過了，都忘記了，再去研究下太麻煩。

先讓客戶確認備份，如果有備份從備份恢復，如果沒備份就備個份下

同時，我想到的幾個其他方案並進行了測試：

1. 用10g軟體來開啟這個庫，期望skip_unusable_indexes引數能有效。但是可能由於compatible引數原因，失敗

2. 期望通過其他引數影響索引成本計算，失敗。可能9I資料字典根本未收集統計資料，可能是RBO模式

3. 期望用gdb可以跟蹤到執行這個語句的步驟，失敗，沒有符號表，無法定位到可能的函式，無法next執行下一步

難道只有BBED？突然想起雙全兄以前分享的一個global_names被置空後，其通過修改oracle執行檔案，跳過該步驟方法，那這個方法如何了？

用UltraEdit開啟我機器上的Oracle的執行檔案，發現果然這個語句就在該檔案中

(不知道如何調大圖，點選看大圖)

下一步是測試如何修改。最後測試的結果為：該SQL的語句不能超過當前長度，可以小於。

最後，我對語句進行了如下修改：

(不知道如何調大圖，點選看大圖)

1. 通過d_obj#+0=:1 遮蔽掉索引的使用

2. 拿一個其他庫測試，得到bootstrap過程中該SQL的繫結變數，測試了這些資料，發現存放的資料和order#的順序一致，語句決定刪除order by order#，避免SQL語句超長，來測試一把

3. 另外，雖然在oracle檔案中發現了很多涉及到該表的其他語句，包括一些DML，但是在10046TRC中沒有發現有涉及到這個表的其他SQL語句

然後用修改過的該檔案代替以前的舊檔案：

[oracle@zhangqiaoc bin]$ sql

SQL*Plus: Release 9.2.0.8.0 - Production on Sat Dec 15 23:29:38 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 840401056 bytes

Fixed Size 741536 bytes

Variable Size 520093696 bytes

Database Buffers 318767104 bytes

Redo Buffers 798720 bytes

Database mounted.

Database opened.

成功OPEN開資料庫，開後臺日誌，這次不再報告SYS.I_DEPENDENCY1不可用，而只是SMON報告了下SYS.I_DEPENDENCY2不可用。但是此時，bootstrap已經完成了，應該SMON再做一些動作，還好SMON報錯後依然堅挺，否則可能需要考慮設定EVENT或者其他辦法了。

Sat Dec 15 23:29:45 2012

Errors in file /u01/app/oracle/admin/o9208/bdump/o9208_smon_26556.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index 'SYS.I_DEPENDENCY2' or partition of such index is in unusable state

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: ALTER DATABASE OPEN

檢視啟動過程的sqltrace檔案，看到bootstrap執行的語句已經變成我修改過的語句了，執行計劃也已經是全表掃描了

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,

d_owner#, nvl(property,0),subname

from

dependency$,obj$ where d_obj#+0=:1 and p_obj#=obj#(+)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 12 0.00 0.00 0 0 0 0

Execute 12 0.00 0.00 0 0 0 0

Fetch 71 0.13 0.13 288 3608 0 59

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 95 0.13 0.13 288 3608 0 59

Misses in library cache during parse: 2

Optimizer mode: CHOOSE

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

------- ---------------------------------------------------

0 NESTED LOOPS OUTER

0 TABLE ACCESS FULL OBJ#(96)

0 TABLE ACCESS BY INDEX ROWID OBJ#(18)

0 INDEX UNIQUE SCAN OBJ#(36) (object id 36)

另外，關於SMON的報錯，我跟蹤下發現是如下語句：

select o.owner#,o.obj#,decode(o.linkname,null, decode(u.name,null,'SYS',u.name),o.remoteowner), o.name,o.linkname,o.namespace,o.subname from user$ u, obj$ o where u.user#(+)=o.owner# and o.type#=:1 and not exists (select p_obj# from dependency$ where p_obj# = o.obj#)

該語句在ORACLE也有

(不知道如何調大圖，點選看大圖)