| 订阅 | 在线投稿
分享
 
 
 

数据字典表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- 王朝网络 版权所有