ORA-00600 [25027]问题处理

郑全

客户在数据库环境对一张表插入数据库的时候报错，报错内容为ORA-00600: 内部错误代码, 参数: [25027], [15], [2503204186], [], [], [], [], [], [], [], [], []

查看trace文件中的内容也很简单，如下：

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3
System name:    Linux
Release:        2.6.18-348.3.1.el5
Version:        #1 SMP Mon Mar 11 19:39:25 EDT 2013
Machine:        x86_64
*** 2014-09-25 11:14:22.890
*** SESSION ID:(4.20757) 2014-09-25 11:14:22.890
*** CLIENT ID:() 2014-09-25 11:14:22.890
*** SERVICE NAME:(SYS$USERS) 2014-09-25 11:14:22.890
*** MODULE NAME:() 2014-09-25 11:14:22.890
*** ACTION NAME:() 2014-09-25 11:14:22.890
DDE: Problem Key 'ORA 600 [25027]' was flood controlled (0x4) (incident: 89280)
ORA-00600: 内部错误代码, 参数: [25027], [15], [2503204186], [], [], [], [], [], [], [], [], []

问题分析：
    该问题是由于Oracle的一个bug导致（参考文档 ID 1608861.1）
    是由于含有LOB字段的表在插入数据时，一个块（Block）在ASSM metadata L1 bitmap block中被标示为格式化的，但是在LOG段中被标识为未格式化的。
    ora-600[25027][x][0] 中的x表示的是该LOB字段所在的表空间的ts#,即表空间号
    第三个参数[0]可能并不总是0(比如我们遇到的这个问题中是2503204186).
    可以使用DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY 存储过程，使用verify_option=>DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC and attrib=>DBMS_SPACE_ADMIN.BITMAPS_CHECK来验证。

解决方法：
1.将表export导出，drop表，再imp进去
2.对lob段做move到新的表空间
  Alter table <name> move lob(&lob_column) store as (tablespace &tbsp);
3.patch 18024115 & alter system set "_fix_control"='18024115:ON';

官网内容：
Symptoms
Insert into table with lob fails with ora-600[25027][x][0] where x is ts# for the tablespace that has the lob.

Tracefile shows the stack function similar to:

krtd2abh  kcbgcur  ktspgfblk3  ktsplbfmb  ktsplbrecl  ktspgsp_main  kdlgsp_init  kdl_write1  kdlf_write   koklicbf  koklcre

Cause
The cause of this error can be LOST IO which may cause other errors like ORA-600 [kdlpdba:kcbz_objdchk] during INSERT.

The problem described in bug 13869187 is because a Block is marked as Formatted in the ASSM metadata L1 bitmap block but the block is unformatted for the LOB segment. 

The 3rd argument may not be always 0 (zero) as the problem is that if the block is unformatted, Oracle still tries to locate a pdba assuming that the block is formatted and that pdba offset may be zero when the block is empty (affected block has never formatted:block flag contains 1 - KCBHFNEW and type is zero).  If the block is formatted for a former dropped object, then the argument can be different than  zero.

DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY with verify_option=>DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC and
attrib=>DBMS_SPACE_ADMIN.BITMAPS_CHECK; however it may be canceled when visiting the first problematic block; thus may not identify all affected blocks.

Syntax example of executing the above procedure:

exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('SYS','T_C2_LOB','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK)

For more details reference Bug 18607613

Solution
The error is fixed by:

recreating the table using exp-drop-import.

OR

Move the lob in a new tablespace.

Alter table <name> move lob(&lob_column) store as (tablespace &tbsp);

@OR

@If the table or the lob are too big taking too long to recreate PATCH 18024115 could be installed, if available, and enabled by

@alter system set "_fix_control"='18024115:ON';

@See Doc ID 18024115.8