Oracle資料字典表Truncate掉導致資料庫不可用及對策

在實際的工作環境下，如果你在操作時將資料庫內的幾個資料字典表Truncate掉，將會直接導致資料庫不能再繼續使用，本文將針對一個相關案例進行詳細的講解。

案例如下：

資料庫環境：Oracle資料庫9.2.0.7 RAC。（註：由於資料庫的事務量體別大，所以資料庫沒有進行備份）。

檢查後發現的被截斷表：

SQL> select object_name,object_type from dba_objects where object_name like 'IDL%';

OBJECT_NAME            OBJECT_TYPE
------------------- ------------------
IDL_CHAR$                 TABLE
IDL_SB4$                  TABLE
IDL_UB1$                  TABLE
IDL_UB2$                  TABLE

IDL_UB1$表是特別重要的字典表，只要出現故障，資料庫就會出現大量的ORA-00600錯誤，所有事務將不能進行。

ORA-00600: internal error code, arguments: [17069],

[0xC0000000DDDFA690], [], [], [], [], [], []

ORA-600 17069錯誤是一個特別難解決的問題，問題出現後資料庫的某個追蹤記錄檔很快就會出現暴漲的情況，因為IDL系列字典表是記錄資料庫物件編譯資訊的，丟失了其中的資料，所有過程、Package等都將無法執行。

字典表作用的說明：

IDL_UB1$ is one of four tables that hold compiled PL/SQL code:

IDL_UB1$
IDL_CHAR$
IDL_UB2$
IDL_SB4$

"PL/SQL is based on the programming language Ada. As a result, PL/SQL uses a
variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), which
is a tree-structured intermediate language. It is defined using a meta-notation
called Interface Definition Language (IDL). DIANA provides for communication
internal to compilers and other tools.

"At compile time, PL/SQL source code is translated into machine-readable
m-code.Both the DIANA and m-code for a procedure or package are stored in the
database.At run time, they are loaded into the shared (memory) pool. The DIANA is
used to compile dependent procedures; the m-code is simply executed."

These four tables hold the DIANA and the so-code m-code. I think "m-code" is
short for machine-dependent byte code but there is a sizable machine-indenpendent part

as well. If you have a look at sql.bsq, you can see
that Oracle documents the "type" column of these tables as follows:
part number not null,
/* part: 0 = diana, 1 = portable pcode,
2 = machine-dependentpcode */

如果出現更為嚴重的情況，它將導致大量系統DBMS包失效，其重新編譯也將更為複雜。

恢複資料庫，消除所有ORA-600錯誤的方法：

恢複的方法是通過運行相關的指令碼，重建和重新編譯所有Procedure/Trigger/Package等對象，重建這些對象的DIANA和so-code m-code，主要包括catlog.sql,catproc.sql等指令碼。