RAC环境并行insert，当触发内部清理回收站时导致ORA-00600 [ktssdrp1]

Errors in file /u01/app/oracle/diag/rdbms/btas/btas1/trace/btas1_p001_130280.trc  (incident=176570):

ORA-00600: ????????????????????????, ????????: [ktssdrp1], [0], [0], [0], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/btas/btas1/incident/incdir_176570/btas1_p001_130280_i176570.trc

Wed Oct 08 05:32:28 2014

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Oct 08 05:32:28 2014

Errors in file /u01/app/oracle/diag/rdbms/btas/btas1/trace/btas1_ora_122776.trc  (incident=176650):

ORA-00600: ????????????????????????, ????????: [ktssdrp1], [0], [0], [0], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/btas/btas1/incident/incdir_176650/btas1_ora_122776_i176650.trc

Wed Oct 08 05:32:28 2014

Dumping diagnostic data in directory=[cdmp_20141008053228], requested by (instance=1, osid=130280 (P001)), summary=[incident=176570].

语句如下
INSERT INTO XXXXXX
  (一些字段)
  SELECT 一些字段
    FROM t1 B, t2 A
   WHERE A.c1 = B.c1(+)
     AND A.c2 >= :B2
     AND A.c2 <= :B1
   GROUP BY 一些字段

第一个trace中充斥着大量的kdpurtab: err= 604 goc1=604 goc2=54 goc3=0

ora604的说明如下

00604, 00000, "  error occurred at recursive SQL level %s "

// *Cause:  An error occurred while processing a recursive SQL statement

//         (a statement applying to internal dictionary tables).

// *Action: If the situation described in the next error on the stack

//         can be corrected, do so; otherwise contact Oracle Support.

第二个trace里有这条递归sql

*** 2014-10-08 05:31:55.654
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=2y9dn9t14fkns) -----
drop table "BTAS"."BIN$81woU0P6icPgQwNSAQrABg==$0" purge

metalink上有相关Bug

Ora-00600 [Ktssdrp1] during DROP TABLE (Doc ID 1329358.1)

=== ODM Issue Clarification ===

ORA-00600 with arguments: [ktssdrp1].

The Failing query looks something similar to :

drop table "ODI_UPD"."BIN$pDWO7q1Bc+/gQBQKChJoqQ==$0" purge;

CALL STACK

===========

ktssdrp_segment dtbdrp dtbdrv opiexe opiosq0 opiall0 opikpr opiodr  PGOSF175_rpidrus  skgmstack rpiswu2 kprball kdpurtab kdpurts kdpursp ktsx_purge_bin  ktsscrseg ktssctr_segment1 ktssctr_segment ktrsexec ktspcfs ktsscf_segment kkpoctds_crt_tmp_data_seg qespcNpSegmentCreat qesmaGetTblSeg_INT qesmaInitMetadataCt x_Int opiexe

下面是  trace 中的 coll stack ，和 bug  文档里很像

[00]: dbgexProcessError [diag_dde]

[01]: dbgeExecuteForError [diag_dde]

[02]: dbgePostErrorKGE [diag_dde]

[03]: dbkePostKGE_kgsf [rdbms_dde]

[04]: kgeadse []

[05]: kgerinv_internal []

[06]: kgerinv []

[07]: kgeasnmierr []

[08]: ktssdrp_segment []

[09]: dtbdrp [SQL_DDL]

[10]: dtbdrv [SQL_DDL]

[11]: opiexe []

[12]: opiosq0 []

[13]: opiall0 []

[14]: opikpr []

[15]: opiodr []

[16]: rpidrus []

[17]: skgmstack []

[18]: rpiswu2 []

[19]: kprball []

[20]: kdpurtab []

[21]: kdpurts []

[22]: kdpursp []

[23]: ktsx_purge_bin []

[24]: ktsxssr_sadd []

[25]: ktrsexec []

[26]: ktelwbl2 []

[27]: kdblba [DIRPATH_LOAD]

[28]: kdblGetBlockDba [DIRPATH_LOAD]

[29]: kdblgb [DIRPATH_LOAD]

[30]: kdblailb [DIRPATH_LOAD]

[31]: kdblai [DIRPATH_LOAD]

[32]: klclil1r [DIRPATH_LOAD]

[33]: qerltRop [SQL_Execution]

[34]: qerghFetch [SQL_Execution]

[35]: rwsfcd []

[36]: qerltFetch [SQL_Execution]

[37]: qertqoFetch [Parallel_Execution]

[38]: qerpxSlaveFetch [Parallel_Execution]

[39]: qerpxFetch [Parallel_Execution]

[40]: insdlexe [DML]

[41]: insExecStmtExecIniEngine [DML]

另一份  bug 报告里，关于 merge 语句的，call stack和我们的insert语句非常像

Bug 18192858 : INTERNAL ERROR MAY OCCUR WHEN RUNNING MERGE STATEMENT

DIAGNOSTIC ANALYSIS:

--------------------

Error is detected by following SQL.

  drop table "HXJFDB01"."BIN$8Q0V5v4Q66/gQ1FSPZYwFw==$0" purge

"recyclebin" parameter is enabled, and above SQL is try to delete the

table in recyclebin.

问题原因也是  drop 回收站里的表， call stack 也类似

TACK TRACE:

------------

kgeasnmierr()+143        ktssdrp_segment()+730     dtbdrp()+523

dtbdrv()+1800            opiexe()+21676            opiosq0()+3865

opiall0()+5478           opikpr()+536              opiodr()+916

rpidrus()+206            skgmstack()+148           rpiswu2()+633

kprball()+1236           kdpurtab()+900            kdpurts()+2694

kdpursp()+153            ktsx_purge_bin()+288       ktsscrseg()+2497

ktssctr_segment1()+1073  ktssctr_segment()+230     ktrsexec()+529

ktspcfs()+148            ktsscf_segment()+151      qerlt_lsa()+1413

klclil1r()+427           qerltRop()+438            qerusRop()+631

qersoFetch()+491         qerusFetch()+723          qertqoFetch()+406

qerpxSlaveFetch()+1781   qerpxFetch()+12329        upsexe()+3147

opiexe()+10221           kpoal8()+2190             opiodr()+916

kpoodr()+648             upirtrc()+2497            kpurcsc()+93

kpuexec()+10802          OCIStmtExecute()+34       kxfxsStmtExecute()+222

kxfxsExecute()+389       kxfxsp()+617              kxfxmai()+671

kxfprdp()+1470           opirip()+908              opidrv()+598

sou2o()+98               opimai_real()+261         ssthrdmain()+252

main()+196               __libc_start_main()+244   _start()+36

另又有一个bug报告说这个bug 只存在于开启并行写的RAC环境。

Bug 18192858 - ORA-600 [ktssdrp1] on a MERGE statement in a RAC environment (Doc ID 18192858.8)

This bug is only relevant when using Real Application Clusters (RAC) and Parallel Query (PQO)
ORA-600 [ktssdrp1] can occur when running a MERGE statement using PDML in a RAC environment.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-1391950/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-1391950/