ORA-00600 7999

今日巡检发现一台数据库alert日志中出现

ORA-00600：internal error code,arguments:[7999],[300]错误，查看MOS上的文档，发现该错误可能是由于BUG11814891 引起，与update lob字段有关，查看trc文件发现是在对一个表做update操作的时候引起的ORA-600报错，查看该表的表结构发现update的其中一个字段果然是blob类型，基本可以定位到BUG 11814891上。

参考以下MOS文档：

Bug 11814891 - ORA-600 [7999] [9] [1] [<lob block rdba>] / ORA-1555 double allocated LOB block [ID 11814891.8]

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.2.0.2 11.2.0.1 11.1.0.7 10.2.0.5 10.2.0.4
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1 (Future Release) 11.2.0.3 (Server Patch Set) 11.2.0.2.7 Patch Set Update 11.2.0.2 Bundle Patch 12 for Exadata Database 10.2.0.5.6 Patch Set Update 11.2.0.2 Patch 8 on Windows Platforms 10.2.0.5 Patch 10 on Windows Platforms

Symptoms:	Related To:
Corruption Corruption (Logical) Error May Occur Internal Error May Occur (ORA-600) ORA-1555 / ORA-22924 / ORA-1551 ORA-600 [7999]	Datatypes (LOBs/CLOB/BLOB/BFILE) UPDATE TO_LOB

Description

ORA-600 [7999] [9] [1] [<lob block rdba>] / ORA-1555 caused by
a double allocated LOB block after a LOB column UPDATE.
 
The second argument [1] in the ORA-600 [7999] indicates ORA-1.
 
The problem is introduced by an UPDATE producing an ORA-1551 (internal
error not visible to user) of a LOB column based on a subquery. The
statement is basically of the form:
 
   update D set c1 = (select to_lob(c1) from S where D.pk = S.pk)
 
   Where D.c1 is a CLOB column and S.c1 is a LONG column.
 
This problem may occur in both ASSM and MSSM tablespaces.
 
If the problem is detected during SMON transaction recovery, it may cause a
hang / spin which is fixed by Bug 11790175 .
 
The affected rows can be identified by running the next plsql:
 
find_lob.sql :
 set echo on
 TRUNCATE TABLE CORRUPT_LOBS;
 CREATE TABLE CORRUPT_LOBS (corrupt_rowid ROWID, error_number NUMBER);
 set echo off
 set feedback off
 alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
 set serveroutput on
 set head off
 set timing on
 set verify off
 set concat off
 declare
  error_1555 exception;
  error_22922 exception;
  pragma exception_init(error_1555,-1555);
  pragma exception_init(error_22922,-22922);
  n number;
  invalid_rows number := 0;
 begin
  for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_name) loop
  begin
   n := dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
  exception
   when error_1555 then
    insert into corrupt_lobs values (cursor_lob.r,1555);
    commit;
    invalid_rows := invalid_rows+1;
   when error_22922 then
    insert into corrupt_lobs values (cursor_lob.r,22922);
    commit;
    invalid_rows := invalid_rows+1;
  end;
  end loop;
  if invalid_rows > 0 then /* plsql found invalid rowids */
    dbms_output.put_line('==============================================');
    dbms_output.put_line('Problem found in '||invalid_rows||' rows:');
    dbms_output.put_line('==============================================');
  else
    dbms_output.put_line('No problems identified');
  end if;
 end;
 /
 set timing off
 undefine lob_column
 
 select CORRUPT_ROWID
     , dbms_rowid.rowid_relative_fno(CORRUPT_ROWID) rfile#
     , dbms_rowid.rowid_block_number(CORRUPT_ROWID) block#
     , dbms_rowid.ROWID_ROW_NUMBER(CORRUPT_ROWID) row#
     , error_number
 from corrupt_lobs;
 
Note:
  If a system encounters this bug then the corruption introduced
  can also cause a spin in SMON. See bug 11790175 for details.
 

Getting a Fix
 Use one of the "Fixed" versions listed above
 (for Patch Sets / bundles use the latest version available as
  contents are cumulative - the "Fixed" version listed above is
  the first version where the fix is included)
 or
 You can check for existing interim patches here: Patch:11814891
 or
 Click here for suggestions on how to get a fix for this issue