最近给小型机加了内存，调整了SGA参数，以应付明年到来的重压，昨天在alert日志发现了600错误：

Mon Feb  8 10:00:54 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_m001_598774.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Mon Feb  8 10:00:58 2010
Trace dumping is performing id=[cdmp_20100208100058]

检查trace文件，发现了执行的SQL语句如下：

*** 2010-02-08 10:00:54.825
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Current SQL statement for this session:

SELECT count(*) over() as total_count,
sd_xe_ash_nm.event_name,
sd_xe_ash_nm.event_id,
sd_xe_ash_nm.parameter1 as p1text,
(CASE
WHEN (sd_xe_ash_nm.parameter1 is NULL OR
sd_xe_ash_nm.parameter1 = '0') THEN
0
ELSE
1
END) as p1valid,
sd_xe_ash_nm.parameter2 as p2text,
(CASE
WHEN (sd_xe_ash_nm.parameter2 is NULL OR
sd_xe_ash_nm.parameter2 = '0') THEN
0
ELSE
1
END) as p2valid,
sd_xe_ash_nm.parameter3 as p3text,
(CASE
WHEN (sd_xe_ash_nm.parameter3 is NULL OR
sd_xe_ash_nm.parameter3 = '0') THEN
0
ELSE
1
END) as p3valid,
sd_xe_ash_nm.keh_evt_id,
nvl(xc.class#, 0) as class_num,
sd_xe_ash_nm.wait_class_id,
nvl(xc.keh_id, 0) as keh_ecl_id,
sd_xe_ash_nm.ash_cnt,
sd_xe_ash_nm.tot_wts_diff,
sd_xe_ash_nm.tot_tmo_diff,
sd_xe_ash_nm.tim_wait_diff
FROM (SELECT sd_xe_ash.*,
evtname.event_name,
evtname.wait_class_id,
evtname.parameter1,
evtname.parameter2,
evtname.parameter3
FROM (SELECT sd_xe.*, nvl(ash.cnt, 0) as ash_cnt
FROM (SELECT nvl(xe.keh_id, 0) as keh_evt_id,
nvl(sd.event_id, xe.event_hash) as event_id,
nvl(sd.tot_wts_diff, 0) as tot_wts_diff,
nvl(sd.tot_tmo_diff, 0) as tot_tmo_diff,
nvl(sd.tim_wait_diff, 0) as tim_wait_diff
FROM (SELECT endsn.event_id as event_id,
(endsn.total_waits -
nvl(begsn.total_waits, 0)) as tot_wts_diff,
(endsn.total_timeouts -
nvl(begsn.total_timeouts, 0)) as tot_tmo_diff,
(endsn.time_waited_micro -
nvl(begsn.time_waited_micro, 0)) as tim_wait_diff
FROM (SELECT end_snap.*
FROM (SELECT t1.*
FROM WRH$_SYSTEM_EVENT t1,
WRM$_SNAPSHOT     s1
WHERE t1.dbid = s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id = s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_SYSTEM_EVENT_BL t2,
WRM$_SNAPSHOT        s2
WHERE t2.dbid = s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id = s2.snap_id
AND s2.bl_moved <> 0) end_snap
WHERE end_snap.dbid = :dbid
and end_snap.instance_number =
:instance_number
and end_snap.snap_id = :end_snap) endsn
LEFT OUTER JOIN (SELECT beg_snap.*
FROM (SELECT t1.*
FROM WRH$_SYSTEM_EVENT t1,
WRM$_SNAPSHOT     s1
WHERE t1.dbid =
s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id =
s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_SYSTEM_EVENT_BL t2,
WRM$_SNAPSHOT        s2
WHERE t2.dbid =
s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id =
s2.snap_id
AND s2.bl_moved <> 0) beg_snap
WHERE beg_snap.dbid = :dbid
and beg_snap.instance_number =
:instance_number
and beg_snap.snap_id =
:beg_snap) begsn ON endsn.event_id =
begsn.event_id) sd
FULL OUTER JOIN X$KEHEVTMAP xe ON sd.event_id =
xe.event_hash) sd_xe
LEFT OUTER JOIN (SELECT a.event_id, count(*) as cnt
FROM (SELECT t1.*
FROM WRH$_ACTIVE_SESSION_HISTORY t1,
WRM$_SNAPSHOT               s1
WHERE t1.dbid = s1.dbid
AND t1.instance_number =
s1.instance_number
AND t1.snap_id = s1.snap_id
AND s1.bl_moved = 0
UNION ALL
SELECT t2.*
FROM WRH$_ACTIVE_SESSION_HISTORY_BL t2,
WRM$_SNAPSHOT                  s2
WHERE t2.dbid = s2.dbid
AND t2.instance_number =
s2.instance_number
AND t2.snap_id = s2.snap_id
AND s2.bl_moved <> 0) a
WHERE a.dbid = :dbid
and a.instance_number = :instance_number
and a.snap_id > :beg_snap
and a.snap_id <= :end_snap
and a.wait_time = 0
GROUP BY a.event_id) ash ON sd_xe.event_id =
ash.event_id) sd_xe_ash,
WRH$_EVENT_NAME evtname
WHERE evtname.event_id = sd_xe_ash.event_id
and evtname.event_id > 0
and evtname.dbid = :dbid) sd_xe_ash_nm,
X$KEHECLMAP xc
WHERE sd_xe_ash_nm.wait_class_id = xc.class_hash
ORDER BY sd_xe_ash_nm.wait_class_id,
sd_xe_ash_nm.tim_wait_diff DESC,
sd_xe_ash_nm.event_id

到了晚上，收集统计信息的时候日志再次出现提示：

Mon Feb  8 22:05:29 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_j000_270930.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [2], [0], [7], [], [], [], []
Mon Feb  8 22:05:32 2010
Trace dumping is performing id=[cdmp_20100208220532]
Mon Feb  8 22:05:36 2010
Errors in file /u01/admin/erpdb/bdump/erpdb2_j000_270930.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [2], [0], [7], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 19089
], [], [], [], [], [], [], []

而从trace文件查看这次是执行了以下SQL语句：

insert /*+ append */
into sys.ora_temp_2_ds_1073343
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
"PLSQL_ENTRY_OBJECT_ID",
"PLSQL_ENTRY_SUBPROGRAM_ID",
"PLSQL_OBJECT_ID",
"PLSQL_SUBPROGRAM_ID",
"SNAP_ID",
"DBID",
"INSTANCE_NUMBER",
"SAMPLE_ID",
"SAMPLE_TIME",
"SESSION_ID",
"SESSION_SERIAL#",
"USER_ID",
"SQL_ID",
"SQL_CHILD_NUMBER",
"SQL_PLAN_HASH_VALUE",
"SERVICE_HASH",
"SESSION_TYPE",
"SQL_OPCODE",
"QC_SESSION_ID",
"QC_INSTANCE_ID",
"CURRENT_OBJ#",
"CURRENT_FILE#",
"CURRENT_BLOCK#",
"SEQ#",
"EVENT_ID",
"P1",
"P2",
"P3",
"WAIT_TIME",
"TIME_WAITED",
"PROGRAM",
"MODULE",
"ACTION",
"CLIENT_ID",
"FORCE_MATCHING_SIGNATURE",
"BLOCKING_SESSION",
"BLOCKING_SESSION_SERIAL#",
"XID"
from "SYS"."WRH$_ACTIVE_SESSION_HISTORY" sample(6.1241078288) t

从metalink查看这个错误可能是以下原因：

一、并行查询导致的，解决办法就是改用串行查询

Bug# 6405339   See Note:6405339.8
Corruption / OERI[kcbz_check_objd_typ_3] / OERI:5400 / OERI:5357 from PQ
Fixed: 10.2.0.5, 11.1.0.7, 11.2

Bug 6405339  Corruption / OERI[kcbz_check_objd_typ_3] / OERI:5400 / OERI:5357 from PQ
A parallel query which uses TEMP segments can suffer corruption
within temporary segments between the slaves leading to various ORA-600
errors like:

ORA-600 [kcbz_check_objd_typ_3]
ORA-600 [5400]
ORA-600 [5357]

Workaround
Run the query serially

二、基于SCN的恢复之后

Bug# 5689412   See Note:5689412.8
ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery
Fixed: 10.2.0.5

Bug 5689412  ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery

ORA-600 [kcbz_check_objd_typ_3] after SCN based recovery to SCN just before
a TRUNCATE operation.

eg:
On primary
create table y (d date, v varchar2(30));
insert into y values (SYSDATE, 'test1');
insert into y values (SYSDATE, 'test2');
truncate table y;

Recover the standby to a point just prior to the
truncate to recover this specific table. (Detemine
the SCN by mining the redo)

recover standby database until change <SCN>;
-> media recovery completes successfully

alter database open read only;
select * from y;
^
ORA-600 [kcbz_check_objd_typ_3]

三、using a multi-table insert SQL with direct load operations

Bug# 4592596   See Note:4592596.8
Corruption (ORA-1410 / ORA-8103) from multi-table insert with direct load
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.2.P01, 10.2.0.2.P08, 10.2.0.3.P03
Bug 4592596  Corruption (ORA-1410 / ORA-8103) from multi-table insert with direct load

Corruption can occur using a multi-table insert SQL with
direct load operations. Eg: If the SQL goes parallel.

This can result in subsequent ORA-1410 / ORA-8103 type errors on selects
from the target table/s.

In the case of an internal error like ORA-600 [kcbz_check_objd_typ_3] the
trace file may contain a redo dump with OP:19.1 for the affected object:

OP:19.1 Direct Loader block redo entry

It may affect AWR tables as some of them are populated with Direct Load.

四、Segment advisor带来的buffer坏块，这应该也是本案例的原因所在，可以禁用AUTO_SPACE_ADVISOR_JOB并清空buffer来解决。

Bug# 4430244 +  See Note:4430244.8
Segment advisor can load blocks of dropped objects into buffer cache (KCB OERI errors)
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.2.P11, 10.2.0.3.P11
Bug 4430244  Segment advisor can load blocks of dropped objects into buffer cache (KCB OERI errors)

Segment advisor code (eg: DBMS_SPACE.OBJECT_GROWTH_TREND)
can load blocks into the cache for DROPped objects as
CURRENT leading to subsequent operations seeing an incorrect
(old) version of a block.
This can lead to various internal buffer cache related
errors such as ORA-600 [kcbnew_3] / ORA-600 [kcbz_check_objd_typ_3].
The exact error depends on which code sees the block.

eg:
If the segment advisor decides to scan table USER1.TAB1
then this table is concurrently dropped then the advisor
may read blocks for the dropped object into the cache as
"CURRENT" block images.
A seperate session may do a direct load of a block from
the dropped object so the block now belongs to USER2.TAB2.
A subsequent select from USER2.TAB2 can see the old
USER1.TAB1 block in the cache so fails.

Workaround
Do not use segment advisor to prevent the problem occurring.
eg:  connect / as sysdba;
exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

Use "alter system flush buffer_cache" to clear bad blocks
from the cache (In RAC the block may have sourced from a
different instance so you need to flush all buffer caches).

另外一个Bug 和这个类似，解决办法相同：5218905  OERI[kcbnew_3] when segment advisor has been used。

2010/02/09 10:02 - Oracle