Successful recovery case of error truncate of dictionary table

Yesterday, successfully helped the customer recover a failed database.

The reason for the failure is that the technician truncate several data dictionary tables in the database, which directly results in the database being unavailable.

The database environment is an Oracle 9.2.0.7 RAC environment. The database is not backed up because of the large transaction volume of the database.

Site inspection confirmed that the main truncated table has such some:

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

As for the idl_ub1$ table, as I have discussed before, this is a very important dictionary table, in the event of a failure, the database will be a large number of ORA-00600 errors, all transactions can not be done:

Ora-00600:internal error code, arguments: [17069], [0xc0000000dddfa690], [], [], [], [], [], []

ORA-600 17069 error is the most headaches, the customer then database of a tracking log has soared to about 5G.

The IDL Series Dictionary table is the record database object compiles the information, loses the data in it, all processes, package and so on will be unable to execute.

Quote a little bit about the role of these dictionary tables:

Idl_ub1$ is one of the 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 was 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 ' M-code ' is
short for machine-dependent byte-code but there is-a sizable machine-indenpendent part as. If you have a look in sql.bsq, you can
That Oracle documents the ' type ' column of these tables as follows:
Part number is not NULL,
/* part:0 = Diana, 1 = portable pcode, 2 = machine-dependentpcode * *

A more serious situation could result in a large number of system DBMS packages being invalidated, and their recompilation would be more complex.

The recovery took 6 hours, successfully helping users recover the database, eliminating all ORA-600 errors.

The main thing to recover is to rebuild the Diana and So-code M-code of these objects by running the relevant scripts, rebuilding and recompiling all the Procedure/trigger/package objects, These main scripts include Catlog.sql,catproc.sql and so on.

Of course, the most important thing is that some ORA-00600 errors need to be resolved, which can take a lot of time.

The recovery process for this case is not important, and it is important that DBAs learn never to put our databases in such a dangerous situation.

One of the four rules of DBA: Backup is more than everything.

One of the basic qualities of DBA: Rigor.

-the end-