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 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 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-1283016/，如需轉載，請註明出處，否則將追究法律責任。