ORA 600 [ktspgsb-1]ORA 600 [ktecgsc:objdORA-600[ktspgsb3objdchk_kcbgcur_3]故障

1 数据库 alert 日志频繁报 ORA-600 [ktspgsb3objdchk_kcbgcur_3] 错误 Sun May 30 12:14:09 2021 Errors in file /u01/app/oracle/diag/rdbms/testhost/testhost3/trace/testhost3_ora_704478.trc  (incident=541383): ORA-00600: 内部错误代码 , 参数 : [ktspgsb3:objdchk_kcbgcur_3], [7353225], [9], [4], [0], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.使用ADRCI查看报错信息 2 查看数据库的等待事件 , 发现表锁及行锁严重 EVENT                                                COUNT(*) -------------------------------------------------- ---------- db file scattered read                                         1 ................................ enq: HW - contention                                        13 class slave wait                                             16 rdbms ipc message                                          23 enq: TX - contention                                         27 enq: TM - contention                                        36 enq: TX - row lock contention                                 48 SQL*Net message from client                                 821   31 rows selected.   3 查看数据库锁情况，发现大量表锁及行锁   col "kill session" for a60 select b.*,a.serial#,'alter system kill session '''|| b.sid || ','||a.serial# ||''' immediate;' "kill session" from gv$session a,(SELECT DECODE(request,0,'Holder: ','Waiter: ') ||  sid sess,inst_id,sid,id1, id2, lmode, request, type FROM GV$LOCK WHERE  (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request) b where b.inst_id=a.inst_id and b.sid=a.sid; 4   查看数据库目前执行的 SQL ，发现大量的锁都是在 C_CONTEST 表上，如下： 5 经过思考，此问题以前从没有发生，此次报错发生在检修后第二天，故认为有可能是应用更改业务程序引起的ORA-600错误，联系应用，将C_CONTEST表反馈给业务开发，业务人员停止c_contest表相关的JOB，等待2分钟后，数据库的ORA-600报错不再持续报错，且数据库的TM、TX锁消失，故认为是业务程序触发Oracle数据库的BUG，导致的频繁报ORA-600错误。 6 查看数据库生成的trace文件 根据 objn: 6973689 查询出来的对象，为： SQL> set linesize 300 SQL> col owner for a20 SQL> col object_name for a20 SQL> col SUBOBJECT_NAME for a20 SQL> select owner,object_name,subobject_name,object_id,object_type,created from dba_objects where OBJECT_ID='&obj_id'; Enter value for obj_id: 6973689 old   1: select owner,object_name,subobject_name,object_id,object_type,created from dba_objects where OBJECT_ID='&obj_id' new   1: select owner,object_name,subobject_name,object_id,object_type,created from dba_objects where OBJECT_ID='6973689'   OWNER                OBJECT_NAME          SUBOBJECT_NAME        OBJECT_ID OBJECT_TYPE         CREATED -------------------- -------------------- -------------------- ---------- ------------------- ------------ TEST_SN               MLOG$_C_CONTEST                            6973689 TABLE               29-AUG-20 根据 file#: 532 rdba: 0x8529b571 (532/2733425) 查询出来的对象为： SQL> select tablespace_name,owner,segment_name,segment_type from dba_extents where file_id=532 and 2733425between block_id and block_id + blocks -1;   TABLESPACE_NAME      OWNER         SEGMENT_NAME         SEGMENT_TYPE -------------------- -------------------- -------------------- -------------------- TESTDAT1             TEST_SN               MLOG$_C_CONTEST      TABLE 根据如下TRACE文件，可以确定对相同对象做TRUNCATE操作   7   查询 oracle 官网文档，可以确定是由于进行 truncate 操作，在频繁执行插入触发的 BUG ，文档内容如下： ORA-00600: [ktecgsc:objdchk_kcbgcur_3], [4042739], [4] and ORA-00600: [ktspgsb-1] Caused Database Instance Crashed (Doc ID 2298015.1)   In this Document

      APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.4 and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Information in this document applies to any platform. NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. SYMPTOMS In EBS environment with high concurrency transactions:

·  The following error(s) occurred in the alert log.   ORA-01555 caused by SQL statement below (SQL ID: 9ny2b8syfjfyw, Query Duration=35613 sec, SCN: 0x0c21.153254d4):   ORA-00600:   [ktspgsb-1], [], [], [], [], [], [], [], [], [], [], []   ORA-00600:  [ktecgsc:objdchk_kcbgcur_3], [4042739], [4], [0], [0], [], [], [], [], [], [], []

·  ORA-00600: [KTSPGSB-1] AND [KTECGSC:OBJDCHK_KCBGCUR_3] are seen when performing DELETE and INSERT actions on table MTL_SUPPLY. The issue is hit after doing truncate on the mview MTL_SUPPLY_SN.

·  ASSM is used on this database.

·  The table MTL_SUPPLY has been analyzed - no errors

·  The error(s) cannot be reproduced at will. From incident trace file --------------------------- ORA-00600: [ktecgsc:objdchk_kcbgcur_3], [4042739], [4], [0] Dump of buffer cache at level 10 for tsn=10 rdba=528780082 BH (0x700012729e13558) file#: <FILE_ID> rdba: 0x1f848b32 (<FILE_ID>/<Block ID>) class: 4 ba: 0x700012728fa6000 set: 524 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 11 obj: 4040883 objn: 1214442 tsn: 10 afn: <FILE_ID> hint: f hash: [0x700010dcfe92bd0,0x700012ffd97d180] lru: [0x700010cd5e735e8,0x700011c57e9ff48] ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL tch: 1 le: 0x0 .......... BH (0x700010dcfe92b18) file#: <FILE_ID> rdba: 0x1f848b32 (<FILE_ID>/<Block ID>) class: 4 ba: 0x700010dcf972000 set: 544 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 31 obj: 4040883 objn: 1214442 tsn: 10 afn: <FILE_ID> hint: f hash: [0x70001250bec22b0,0x700012729e13610] lru: [0x7000111bdeaefc8,0x700011f47e5e3e8] ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL tch: 1 le: 0x0 cr: [scn: 0xc21.3995a679],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0xc21.3995a679],[sfl: 0x0],[lc: 0xc21.39940184] flags: only_sequential_access GLOBAL CACHE ELEMENT DUMP (address: 0x7000122e1ff3308): id1: 0x48b32 id2: 0x7e pkey: OBJ#4040883 block: (<FILE_ID>/<Block ID>) lock: X rls: 0x5 acq: 0x0 latch: 78 flags: 0x20 fair: 0 recovery: 0 fpin: 'ktsphwh39: ktspisc' bscn: 0xc21.39940184 bctx: 0x0 write: 0 scan: 0x0 lcp: 0x0 lnk: [NULL] lch: [0x70001204fde7070,0x70001204fde7070] seq: 55406 hist: 481 74:2 145:0 118 67 143:0 208 352 197 51 41 239 LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE ELEMENT: flg: 0x02000201 state: XCURRENT tsn: 10 tsh: 1 foq: 4 addr: 0x70001204fde6f38 obj: 4040883 cls: SEG HEAD bscn: 0xc21.3995a6ba buffer tsn: 10 rdba: 0x1f848b32 (<FILE_ID>/<Block ID>) scn: 0x0c21.3995a67a seq: 0x00 flg: 0x00 tail: 0xa67a2300 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER .   Stack: ...ktecgsc ktecgetsh ktecgshx ktspisc ktspgsp_main2 kdtgsp kdtgsph kdtgrs kdtInsRow kddiriDoInsert kddiriBufferRow kntxStmtExecute kntxslt kntxit kxtrexe qesltcExecuteAfterRowTriggers qesltcAfterRowProcessing qerltcNoKdtBufferedInsRowCBK qerltcLoadStateMachine qerltcInsertSelectRop qerjoFetch rwsfcd qerltcFetch qermvlgFetch...   CHANGES  EBS ENABLE autotruncate feature CAUSE  This is due to wrong using mview log, EBS  truncate MV log periodically  during heavy transactions on base table without lock base table. SOLUTION Before truncate MV view log, should lock base table first to make sure no other transaction on base table, then truncate MV view log. Steps to truncate MV log can be found in : Fix A Huge Materialized View Log With TRUNCATE In Case Regular DBMS_MVIEW.PURGE Fails ( Doc ID 1594739.1 ) How to Purge a Large MView Log And Avoid Full Refresh ( Doc ID 1539298.1 ) 8 经过业务确定，是由于他们在检修时修改业务代码，将以前的delete操作变更为truncate操作导致触发的oracle的BUG