ORA-00600 kcbnew_3错误案例一则

原文出处： http://qqmengxue.itpub.net/post/42175/509361

这则案例给出了一个常规的思路，跟踪文件也说明了内部错误的信息，摘录于此：

环境：

oracle 9.2.0.5
System name: HP-UX


今天客户报账说访问核心任务表报ORA-01410: invalid ROWID错误：

SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM CC09MAIN04.CRM_CAMPAIGN_TASK_1122 A;

SELECT /*+FULL(A)*/ COUNT(*) FROM CC09MAIN04.CRM_CAMPAIGN_TASK_1122 A

ORA-01410: invalid ROWID

通过设置10200事件后发现访问23/1630块的时候停止并报错：

SQL> alter session set max_dump_file_size=unlimited;

SQL> alter session set db_file_multiblock_read_count=1;

SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';

SQL> alter session set events '10200 trace name context forever, level 1';
SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM CC09MAIN04.CRM_CAMPAIGN_TASK_1122 A;

    ERROR at line 1:
    ORA-01410: invalid ROWID

SQL> alter session set events 'immediate trace name trace_buffer_off';

导出这个块查看发现很有诡异：

1、这个块存储的 seg/obj: 0x1d834 （120884）

但是我们这个表的objid是121021：

SQL> select object_id,data_object_id from dba_objects t where t.owner='CC09MAIN04' and t.object_name='CRM_CAMPAIGN_TASK_1122';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
121021 121021

根据objid为121021的查看是哪个表：

SQL> select object_id,object_name,object_type from dba_objects t where t.object_id=120884;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------------------------------------------------------------------- ------------------
120884 CRM_CUS_YWY_DATACLEAN_HC TABLE

很显然，这个数据块里存储的数据已经发生了窜位，原本属于'CRM_CAMPAIGN_TASK_1122'的块居然存储了别人的信息。

然后我们在返回去剖析ORA-01410错误的原因，

因为'CRM_CAMPAIGN_TASK_1122'表的某一个ROWID对应的数据块已经窜位，里面存储的数据已经变成了其他他表的数据，当进行全表扫描到此块的时候，根据rowid取数据发现居然是一个空的数据，然后oracle就报：0ra-01410错误了。

数据库居然有这么严重的错误，通过查看ALTER文件发现在昨晚凌晨有人做了一些操作引起了ORA-600错误：

ARC0: Completed archiving log 4 thread 1 sequence 25804
Mon Nov 22 01:18:16 2010
Errors in file /home/oracle/app/oracle/admin/aic/udump/aic1_ora_15119.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [8], [], [], [], [], [], []
Mon Nov 22 01:18:47 2010
Trace dumping is performing id=[cdmp_20101122011847]
Mon Nov 22 01:26:03 2010

/home/oracle/app/oracle/admin/aic/udump/aic1_ora_15119.trc
*** 2010-11-22 01:18:16.819
*** SESSION ID:(218.5127) 2010-11-22 01:18:16.803
BH (0xc0000000e2f62140) file#: 23 rdba: 0x05c00679 (23/1657) class 1 ba: 0xc0000000e2c74000
set: 12 dbwrid: 0 obj: 120884 objn: 120884
hash: [c000000105f612a0,c000000134e29548] lru: [c0000000c6f4cba8,c0000000f7f3e2c8]
ckptq: [NULL] fileq: [NULL]
st: XCURRENT md: NULL rsop: 0x0000000000000000 tch: 1
flags: gotten_in_current_mode block_written_once redo_since_read
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [1] RRBA: [0x0.0.0]
GLOBAL CACHE ELEMENT DUMP (address: 0xc0000000edfb6500):
id1: 0x5c00679 id2: 0x0 lock: XL rls: 0x000 acq: 0x00
open: 1 flags: 0x1 fair: 0 recovery: 0 latch: 26
bscn: 0.0 bctx: 0x0000000000000000 write: 0 cscn: 0:0
xflg: 0 xid: 0x0000.000.00000000
lcp: 0x0000000000000000 lnk: [NULL] lch: [c0000000e2f62248,c0000000e2f62248]
seq: 30193 113:5:1 143:0:0 325:5 450:1 329:0 48:3:1 1:3:1 260:3 144:0:0 192:3:0 450:1 329:0 48:3:1 1:3:1 144:0:0 98:3:0
LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE LOCK:
flg: 0x02202000 state: XCURRENT mode: NULL waiters: 0 foq: 0 addr: 0xc0000000e2f62140
obj: 120884 cls: DATA bscn: a52.ce6857fc
GCS SHADOW handle 0xc0000000edfb6598,4024 sq[0xc000000136987de0,0xc000000136987de0] resp[0xc000000136987db8,0x5c00679.0]
grant 5 cvt 0 mdrole 0x6 st 0x40 GRANTQ rl LOCAL
master 0 owner 0 remote[0x0000000000000000,0] hist 0x118c106
history 0x6.0x4.0xc.0x6.0x1.0x0. cflag 0x0 flags 0x0
disk: 0x0000.00000000 write request: 0x0000.00000000
pi scn: 0x0000.00000000
msgseq 0x0 updseq 0x0 reqids[4102,0,0] infop 0x0
GCS RESOURCE 0xc000000136987db8 hashq [0xc00000013678c338,0xc000000136ecbe50] name[0x5c00679,0x0]
grant 0xc0000000edfb6598 cvt 0x0000000000000000 send 0x0000000000000000,0 write 0x0000000000000000,0
flag 0x0 mdrole 0x2 mode 5 scan 0 role LOCAL
disk: 0x0000.00000000 write: 0x0000.00000000 cnt 0x0 hist 0x0
xid 0x0000.000.00000000
HV bucket info: idx 600 stat 0 last rem inc 30
master 0, tobe_master 0, prev master 32767
GCS SHADOW handle 0xc0000000edfb6598,4024 sq[0xc000000136987de0,0xc000000136987de0] resp[0xc000000136987db8,0x5c00679.0]
grant 5 cvt 0 mdrole 0x6 st 0x40 GRANTQ rl LOCAL
master 0 owner 0 remote[0x0000000000000000,0] hist 0x118c106
history 0x6.0x4.0xc.0x6.0x1.0x0. cflag 0x0 flags 0x0
disk: 0x0000.00000000 write request: 0x0000.00000000
pi scn: 0x0000.00000000
msgseq 0x0 updseq 0x0 reqids[4102,0,0] infop 0x0
kjbmbassert [0x5c00679.0]
kjbmsassert(0x5c00679.0)(1)
*** 2010-11-22 01:18:16.826
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcbnew_3], [8], [], [], [], [], [], []
Current SQL statement for this session:
-- Create/Recreate indexes
create index temp_mobile on TMP_CUSTOMER (yjdh)
----- Call Stack Trace -----

oracle官方解释：

PURPOSE:
This article discusses the internal error "ORA-600 [kcbnew_3]", what
it means and possible actions. The information here is only applicable
to the versions listed and is provided only for guidance.

ERROR:
ORA-600 [kcbnew_3] [a] [b] [c]

VERSIONS:
versions 9.2 and later

DESCRIPTION:

A cache buffer holding a database block is in the process of
being reused.

The buffer is in state "current" and may be reused only if the object
is of type temp or undo.

The consistency check comparing the block class in the buffer header
with the block class passed to the cache by the caller is failing.

ARGUMENTS:

The number of arguments and their meaning vary depending on the Oracle
Server release.

Oracle 10.1 and later:

Arg [a] Internal loop counter (number of blocks to new)
Arg [b] Buffer class
Arg [c] Object Id passed to the cache by the layer accessing the cache

Oracle Release 9.2 and earlier:

Arg [a] Buffer class

FUNCTIONALITY:
Kernel Cache Buffer management Implementation

IMPACT:
PROCESS FAILURE
MEMORY CORRUPTION
NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS:

If the Known Issues section below does not help in terms of identifying
a solution, please submit the trace files and alert.log to Oracle
Support Services for further analysis.

Known Issues:

最后解决： 只有将数据进行异机恢复，打补丁！