| 導購 | 订阅 | 在线投稿
分享
 
 
 

數據字典表Truncate掉將導致數據庫不可用

來源:互聯網  2008-06-01 03:36:35  評論

在實際的工作環境下,如果你在操作時將數據庫內的幾個數據字典表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等腳本。

注意:即使以花費大量的時間爲代價,一些ORA-00600錯誤也必須解決。

在實際的工作環境下,如果你在操作時將數據庫內的幾個數據字典表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等腳本。 注意:即使以花費大量的時間爲代價,一些ORA-00600錯誤也必須解決。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有