Database unavailability caused by Truncate in the Oracle Data dictionary table and Countermeasures

In the actual working environment, if you Truncate several data dictionary tables in the database during operation, the database will no longer be available, this article will give a detailed explanation of a related case.

The example is as follows:

Database environment: Oracle Database 9.2.0.7 RAC. (Note: the database is not backed up because the transaction volume of the database is large ).

Truncated tables found after check:

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 $ table is a particularly important dictionary table, as long as there is a fault, the database will have a large number of ORA-00600 errors, all transactions will not be able to proceed.

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

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

ORA-600 17069 error is a particularly difficult to solve the problem, after the emergence of a database tracking log will soon surge, because the IDL series dictionary table is to record the database object compilation information, if the data is lost, all processes and packages cannot be executed.

Description of the functions of the dictionary table:

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
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
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 statements the "type" column of these tables as follows:
Part number not null,
/* Part: 0 = diana, 1 = portable pcode,
2 = machine-dependentpcode */

In more serious cases, it will cause a large number of System DBMS packages to become invalid, and its re-compilation will be more complicated.

Restore the database by eliminating all ORA-600 errors:

The restoration method is to re-generate the DIANA and so-code m-codes of all objects, such as Procedure, Trigger, and Package, by running related scripts, re-build and re-compile all objects such as Procedure, Trigger, and Package, it mainly includes catlog. SQL, catproc. SQL and other scripts.