Wednesday, December 16, 2009

How to recover from a corruption of an object in system tablespace

Table IDL_UB2$ who's owner is SYS got corrupted. This was noticed while trying to compile a package EAM_PROCESS_WO_PVT.

=================================================
SQL> alter package EAM_PROCESS_WO_PVT compile package;
Warning: Package altered with compilation errors.
SQL> show errors;
No errors.
SQL> alter package EAM_PROCESS_WO_PVT compile body;

Warning: Package Body altered with compilation errors.
SQL> show errors;
Errors for PACKAGE BODY EAM_PROCESS_WO_PVT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: internal error [phdite:node kind]
9361/5 PL/SQL: SQL Statement ignored
9362/32 PL/SQL: ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 2, block # 40099)
ORA-01110: data file 2:
'/u01/oracle/PROD/db/apps_st/data/system02.dbf'
9366/46 PL/SQL: Statement ignored

=================================================

As a first step. we found the object that was in question and its type.

=================================================
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = 2
AND 40099 BETWEEN BLOCK_ID AND (BLOCK_ID + BLOCKS -1); 2 3
OWNER
------------------------------
SEGMENT_NAME
-------------------------
SEGMENT_TYPE
------------------
SYS
IDL_UB2$
TABLE

=================================================

then performed the following steps:

=================================================
SQL> shutdown immediate
SQL> startup upgrade
SQL> truncate table idl_ub1$;
SQL> truncate table idl_char$;
SQL> truncate table idl_ub2$;
SQL> truncate table idl_sb4$;
SQL> @?/rdbms/admin/utlirp
SQL> shutdown immediate
SQL> startup upgrade
processes to connect to dbs
SQL> @?/javavm/install/rmjvm
SQL> shutdown immediate
SQL> exit <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> alter system set "_system_trig_enabled"=false scope=memory;
SQL> @?/javavm/install/initjvm.sql
SQL> shutdown immediate
SQL> exit <-- important! <-- important!<<<<<<<<<<<<<<<<SQL> startup upgrade
processes to connect to dbs
SQL> @?/rdbms/admin/utlrp
SQL> shutdown immediate
SQL> startup

=============================================

The corruption was fixed.

No comments: