RAC環境並行insert，當觸發內部清理回收站時導致ORA-00600 [ktssdrp1]

liiinuuux發表於2015-01-06
並行
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章