标签归档：ora-600 17059

insert into aud$引起高版本问题导致ORA-600[17059]

发表于 2016 年 3 月 20 日 由 惜分飞

昨天晚上有朋友咨询我,他数据库(win2008 11.2.0.1 单机)出现大量ORA-00600[17059],如下错误,让帮忙分析原因
alert日志报错

Sat Mar 19 21:31:02 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3336.trc  (incident=45304):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:06 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4168.trc  (incident=45166):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
Sat Mar 19 21:31:09 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2928.trc  (incident=45342):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
Sat Mar 19 21:31:12 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc  (incident=45399):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:17 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5884.trc  (incident=45255):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:21 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2976.trc  (incident=45305):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:24 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc  (incident=45256):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:28 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6044.trc  (incident=45351):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:32 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2928.trc  (incident=45343):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-02002: 写入审计线索时出错

这里虽然报了ORA-00600[17059],ORA-28001,ORA-02002但是根据经验感觉很可能是由于ORA-00600[17059]错误导致后面的其他两个错误.

trace文件信息

Dump continued from file: d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5484.trc
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []

========= Dump for incident 45253 (ORA 600 [17059]) ========

*** 2016-03-19 21:28:34.244
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=4vs91dcv7u1p6) -----
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode,
 obj$creator,obj$name,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, 
logoff$lwrite,logoff$dead,comment$text,spare1,spare2,  priv$used,clientid,sessioncpu,proxy$sid,user$guid, 
instance#,process#,xid,scn,auditid,  sqlbind,sqltext,obj$edition,dbid)  values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),   
  :4,:5,:6,:7,:8,     :9,:10,:11,:12,     :13,:14,:15,:16,:17,     :18,:19,:20,:21,:22,     :23,:24,:25,:26,:27,  
   :28,:29,:30,:31,:32,     :33,:34,:35,:36)

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009173DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexProcessError()  CALL???  dbgexPhaseII()       021160570 02116D448 00000B0C5
+2563                                              000000002
dbgeExecuteForError  CALL???  dbgexProcessError()  021160570 021167540 000000001
()+65                                              000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  025455460 00000000C 000000001
1726                          ()                   4A9A0FFD0
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()   025455460 024690040 000000258
75                                                 7F883243A05D
kgeade()+560         CALL???  dbkePostKGE_kgsf()   000000001 000000000 00A684E58
                                                   00A5553E2
kgeriv_int()+111     CALL???  kgeade()             024444C49 000000000 000000000
                                                   000000000
kgeriv()+29          CALL???  kgeriv_int()         000000000 000000001 02B474DD0
                                                   000000001
kgesiv()+105         CALL???  kgeriv()             461A8AD00 7F883243A05D
                                                   025455460 000000001
kgesic3()+60         CALL???  kgesiv()             401122000 000000000 009B4310C
                                                   4A9A0EC80
kgltba()+739         CALL???  kgesic3()            000000200 6236313231656434
                                                   4000042A3 000000002
kglhdgc()+384        CALL???  kgltba()             4A095BC50 461A31858 02B476418
                                                   000000001
kglLock()+3063       CALL???  kglhdgc()            000000000 000020C68 4ADE08E18
                                                   000000008
kglget()+403         CALL???  kglLock()            02B476008 02B475360 02B475360
                                                   0004D112F
kxsGetLookupLock()+  CALL???  kglget()             025455460 02B4756A0 000000001
327                                                000000003
kksfbc()+14464       CALL???  kxsGetLookupLock()   4AC4EDFB0 0004FD4CA 0246CC6B8
                                                   4AC4EDFB0
kkspsc0()+2117       CALL???  kksfbc()             0246CC6B8 000000003 000000008
                                                   0070D7F80
kksParseCursor()+18  CALL???  kkspsc0()            0246B19B8 0070D7F80 000000256
1                                                  000000003
opiosq0()+2538       CALL???  kksParseCursor()     000000000 025454EA0 000000000
                                                   0033917DF
opiosq()+23          CALL???  opiosq0()            000000003 00000000F 02B478A28
                                                   025450000
opiodr()+1662        CALL???  opiosq()             0746E6172 000000002 06567656C
                                                   000000000
rpidrus()+862        CALL???  opiodr()             00000004A 00000000F 02B478A28
                                                   000000001
rpidru()+154         CALL???  rpidrus()            02B478028 000000001 000000000
                                                   000400000
rpiswu2()+2757       CALL???  rpidru()             02B4788B0 000000000
                                                   1D181E32DAE2234 000000000
rpidrv()+6105        CALL???  rpiswu2()            4AC4EC300 000000000 02B478680
                                                   000000002
rpisplu()+1607       CALL???  rpidrv()             400000001 7F880000004A
                                                   02B478A28 000000008
audins()+2562        CALL???  rpisplu()            000000001 000000000 000000000
                                                   000000000
audlon()+1286        CALL???  audins()             000005028 0070D9274 0070D9280
                                                   000000036
auddft()+2140        CALL???  audlon()             006F79DE0 0092ACAF1 000000000
                                                   000000000
kpolnb()+4007        CALL???  auddft()             0246AF458 000000064 000000000
                                                   000000000
kpolon()+237         CALL???  kpolnb()             1D181E300000051 02B47AF20
                                                   000000000 000026161
opiodr()+1662        CALL???  kpolon()             000000001 000000000 000000000
                                                   00A42F224
ttcpip()+1325        CALL???  opiodr()             4800000000000051 40000001A
                                                   02B47E100 000000000
opitsk()+2040        CALL???  ttcpip()             02546F180 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             000000000 000000000 000000000
                                                   02B47F9F8
opiodr()+1662        CALL???  opiino()             00000003C 000000004 02B47FAB0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 02B47FAB0
                                                   615C3A6400000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 02B47FAB0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              1D181E32DAE2234 000000000
                                                   150013000307E0 601B80009001C
opimai()+191         CALL???  opimai_real()        000000000 02B47FC68 01D3200A0
                                                   02B47FC68
OracleThreadStart()  CALL???  opimai()             000000000 006DF0B34 0000000E0
+724                                               0000027CC
0000000076E1652D     CALL???  OracleThreadStart()  02957FF18 000000000 000000000
                                                   000000000
0000000076F4C521     CALL???  0000000076E16520     000000000 000000000 000000000
                                                   000000000
 

--------------------- Binary Stack Dump ---------------------

LibraryObject:  Address=9be74cb0 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
  ChildTable:  size='32768' 
    Child:  id='0' Table=9be75b60 Reference=9be75600 Handle=a9a0eb20 
    Child:  id='1' Table=9be75b60 Reference=9be61ed8 Handle=a9a06700 
    Child:  id='2' Table=9be75b60 Reference=9be621f0 Handle=a9a06500 
    Child:  id='3' Table=9be75b60 Reference=9be62528 Handle=a9997200 
    Child:  id='4' Table=9be75b60 Reference=9be62818 Handle=a99597a8 
    Child:  id='5' Table=9be75b60 Reference=9be62b50 Handle=a99cd210 
    Child:  id='6' Table=9be75b60 Reference=9bd0a418 Handle=a994a308 
    Child:  id='7' Table=9be75b60 Reference=9bd0a750 Handle=a9920980 
    Child:  id='8' Table=9be75b60 Reference=9bd0aa40 Handle=a99a6d48 
    Child:  id='9' Table=9be75b60 Reference=9bd0ad78 Handle=a99a6918 
    Child:  id='10' Table=9be75b60 Reference=9bd0b068 Handle=a9993388 
    …………
    Child:  id='32764' Table=becbd118 Reference=bec882d8 Handle=522c1118 
    Child:  id='32765' Table=becbd118 Reference=bec7c708 Handle=52319608 
    Child:  id='32766' Table=becbd118 Reference=bec7c9f8 Handle=522c0f18 
    Child:  id='32767' Table=becbd118 Reference=bec7cd30 Handle=522a7858

结合trace信息,我们可以发现高版本数量已经到了32768,理论最大值.因此数据库报了ORA-600[17059]错误(以前写过类似文章:因为高版本引起ORA-00600[17059]),并且引起了其他的ORA-错误.通过查询数据库高版本信息,在重启一会儿的库中,发现
insert-aud$

过几分钟后
insert-aud$-高版本

通过这里,进一步诊断,引起高版本的sql，是由于aud$表相关的插入时绑定参数导致（如果需要可以进一步分析是由于什么原因导致了高版本）.这里根据经验具体原因已经不再重要，对于11.2.0.1版本,本身bug比较多,且暂时无法升级
处理方法
1.因为本库的本地审计意义不大直接从数据库层面关闭

audit_trail='none'

2.在11g的初始版本中,acs特性导致子游标过多,建议关闭

_optimizer_adaptive_cursor_sharing=false 
_optimizer_extended_cursor_sharing=none 
_optimizer_extended_cursor_sharing_rel=none

3.限制数据库sql游标数量，当游标超过该数量直接抛弃重新硬解析

_cursor_features_enabled=34
event='106001 trace name context forever,level 100'

通过以上配置，重启数据库之后,运行一天alert日志未再出现任何错误,通过查询mos匹配Bug 10196339 : ORA600[17059] OCCURS DUE TO BIND_MISMATCH

发表在 Oracle Bug | 标签为 , , , | 2 条评论

因为高版本引起ORA-00600[17059]

发表于 2012 年 8 月 1 日 由 惜分飞

alert日志出现ORA-00600[17059]

Wed Aug 01 03:19:29 2012
Errors in file /oracle/diag/rdbms/sgerp5/sgerp5/trace/sgerp5_ora_5177388.trc  (incident=682625):
ORA-00600: 内部错误代码, 参数: [17059], [0x700000A18EE85E0], [0x70000085506A820], [0x700000A51F56E40], [], [], [], []
Incident details in: /oracle/diag/rdbms/sgerp5/sgerp5/incident/incdir_682625/sgerp5_ora_5177388_i682625.trc

查看trace文件

Dump continued from file: /oracle/diag/rdbms/sgerp5/sgerp5/trace/sgerp5_ora_5177388.trc
ORA-00600: 内部错误代码, 参数: [17059], [0x700000A3ED7B240], [0x7000007C19384F8], [0x70000078BC51048], [], [], [], []

========= Dump for incident 682624 (ORA 600 [17059]) ========

*** 2012-07-31 22:38:06.130
----- Current SQL Statement for this session (sql_id=03vurqhdanbv6) -----
begin  
 insert into "000".tbExhRes(DeptCode,ERCode,ERName,CategoryCode,GoodsCategoryCode,TermMoney,IsAllowOwned,CategoryItemCode,GoodsCategoryItemCode)
values('1521','20070115210130','连云港通灌路购物广场地堆130','200701','0201',1200,1,'0000','0000');
 insert into "000".tbExhRes(DeptCode,ERCode,ERName,CategoryCode,GoodsCategoryCode,TermMoney,IsAllowOwned,CategoryItemCode,GoodsCategoryItemCode)
values('1521','20070115210131','连云港通灌路购物广场地堆131','200701','0201',1200,1,'0000','0000');
 …………近5万条类此sql
end; 
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+002c      bl       105e27f1c            
ksedst1()+0064       bl       101fadb74            
ksedst()+0028        bl       ksedst1()            000000000 ? 000000000 ?
dbkedDefDump()+07fc  bl       101faf398            
ksedmp()+0048        bl       101fadb70            
ksfdmp()+0058        bl       ksedmp()             000000000 ?
dbgexPhaseII()+0130  bl       _ptrgl()             
dbgexProcessError()  bl       dbgexPhaseII()       110383690 ? 000000000 ?
+09bc                                              600000006 ?
dbgeExecuteForError  bl       dbgexProcessError()  110383690 ? 110388B10 ?
()+0040                                            1729C5E38 ? 000004028 ?
dbgePostErrorKGE()+  bl       dbgeExecuteForError  FFFFFFFFFFF02C0 ? 110000328 ?
03c0                          ()                   FFFFFFFFFFF0350 ?
                                                   484222407FFFFFF8 ?
                                                   100154D20 ?
dbkePostKGE_kgsf()+  bl       dbgePostErrorKGE()   000000258 ? 110000328 ?
003c                                               25810594518 ?
kgeade()+0300        bl       _ptrgl()             
kgeriv_int()+0068    bl       kgeade()             1070DA620 ? 1070DADB8 ?
                                                   1070D9E48 ? 1070DB208 ?
                                                   1070DA784 ? 1070DA788 ?
                                                   1070DA77C ? 1070DA794 ?
kgeriv()+0020        bl       kgeriv_int()         7000007C19384F8 ? 000000000 ?
                                                   000000001 ? FFFFFFFFFFF11E0 ?
                                                   700000A62228320 ?
                                                   7000007C19384F8 ?
kgesiv()+007c        bl       kgeriv()             10102D590 ? 700000A3ED7B350 ?
                                                   700000A62228320 ? 000000005 ?
                                                   000000000 ?
kgesic3()+0040       bl       kgesiv()             7000007C19384F8 ? 000000000 ?
                                                   000000001 ? FFFFFFFFFFF11E0 ?
                                                   700000A62228320 ?
kgltba()+0134        bl       kgesic3()            110000328 ? 110594518 ?
                                                   42A3000042A3 ? 000000002 ?
                                                   700000A3ED7B240 ? 000000002 ?
                                                   7000007C19384F8 ? 000000002 ?
kglrdtin()+01d4      bl       kgltba()             FFFFFFFFFFF1470 ? 000000000 ?
                                                   FFFFFFFFFFF1318 ? 000000000 ?
                                                   000000001 ? 12BFB90F8 ?
kglrtl()+0020        bl       kglrdtin()           000000001 ? 000000000 ?
                                                   000000001 ? 169F90F18 ?
                                                   169F8E7F0 ? 12BFB90F8 ?
                                                   FFFFFFFFFFF1470 ?
pdy3M06_Add_Sql_Str  bl       kglrtl()             FFFFFFFFFFF14F0 ?
ing()+00c4                                         7FFF000000007FFF ?
                                                   10262A2A8 ? 11032DD20 ?
                                                   26FDBFA00 ?
pdy8M92_Fill_SQD()+  bl       pdy3M06_Add_Sql_Str  102683768 ? 000000000 ?
006c                          ing()                10267265C ?
pdy8M74_Fill_Descri  bl       101faf1a4            
ptor()+0124                                        
pdy8F07_Materialize  bl       pdy8M74_Fill_Descri  164B00E58 ? 165BD0380 ?
_Descriptors()+038c           ptor()               7000008C99BA368 ?
pdy1F82_Write_MCode  bl       pdy8F07_Materialize  FFFFFFFFFFF3968 ? 000000000 ?
()+0084                       _Descriptors()       
pdy1F01_Driver()+01  bl       pdy1F82_Write_MCode  FFFFFFFFFFF18D0 ? 10726667C ?
58                            ()                   7000008EA0F18D8 ?
pdw0F82_Run_Code_Ge  bl       pdy1F01_Driver()     1028771D4 ? 000000000 ?
n()+0088                                           
pdw0F01_Code_Gen()+  bl       pdw0F82_Run_Code_Ge  FFFFFFFFFFF1A50 ? 12BFC3E48 ?
0250                          n()                  
phpcog()+0010        bl       pdw0F01_Code_Gen()   FFFFFFFFFFF3968 ?
                                                   70000078BC51048 ? 000000004 ?
phpcmp()+13a0        bl       phpcog()             11037D140 ? 700000000003640 ?
pcicog()+0234        bl       phpcmp()             FFFFFFFFFFF3968 ?
                                                   70000078BC51048 ? 000000000 ?
                                                   000000000 ? 400000110000BEC ?
                                                   FFFFFFFFFFF33F0 ? 000000000 ?
                                                   000000000 ?
kkxcog()+01cc        bl       pcicog()             FFFFFFFFFFF3968 ?
                                                   70000078BC51048 ?
opitca()+0a50        bl       kkxcog()             10009697C ?
kksFullTypeCheck()+  bl       opitca()             11082EDB8 ? 7000008C99BA740 ?
001c                                               
rpiswu2()+03c0       bl       _ptrgl()             
kksLoadChild()+31e4  bl       rpiswu2()            700000A717262C8 ?
                                                   7000008EA0F1970 ?
                                                   7000008C99BAA78 ? 107267650 ?
                                                   000000000 ? 55D16B348 ?
                                                   FFFFFFFFFFF72B8 ? 000000000 ?
kxsGetRuntimeLock()  bl       kksLoadChild()       110000328 ? 70000078BC51048 ?
+083c                                              700000A57852278 ?
kksfbc()+2230        bl       kxsGetRuntimeLock()  110000328 ? 11082EDB8 ?
                                                   FFFFFFFFFFF72B8 ?
                                                   12C00000000 ?
                                                   100006300000000 ?
kkspsc0()+106c       bl       kksfbc()             11082EDB8 ? 3FFFF8150 ?
                                                   1085D16C170 ? 149510630 ?
                                                   000AE3AD5 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kksParseCursor()+00  bl       kkspsc0()            1105B2748 ? 149510630 ?
cc                                                 000AE3AD5 ? 31032DD20 ?
                                                   600000000 ? A4000000000000 ?
                                                   000000000 ?
opiosq0()+0b18       bl       103ecdd38            
kpooprx()+019c       bl       101fadff0            
kpoal8()+04c0        bl       kpooprx()            FFFFFFFFFFFB454 ? 149510630 ?
                                                   AE3AD400AE3AD4 ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
opiodr()+0b48        bl       103ec82b0            
ttcpip()+114c        bl       _ptrgl()             
opitsk()+169c        bl       103eca2bc            
opiino()+09a0        bl       opitsk()             1100981E8 ? 000000000 ?
opiodr()+0b48        bl       103ec82b0            
opidrv()+0440        bl       opiodr()             3C2F736765 ? 41032DD20 ?
                                                   FFFFFFFFFFFF8C0 ? 05F353137 ?
sou2o()+0090         bl       opidrv()             3C05C052EC ? 4A0071E60 ?
                                                   FFFFFFFFFFFF8C0 ?
opimai_real()+01b0   bl       101fad8cc            
main()+0090          bl       opimai_real()        000000000 ? 000000000 ?
__start()+0070       bl       main()               000000000 ? 000000000 ?
 

--------------------- Binary Stack Dump ---------------------

ORA-00600[17059]错误大部分都是因为高版本导致,对于本库的分析:因为该库的shared pool老化比较频繁,到我介入的时候,发现相关该类此sql已经不能找到,不能非常肯定的找出来原因,猜测可能原因是:这个库(版本为11.1.0.6 FOR AIX)因为开发基本上没有绑定参数,设置cursor_sharing=force,这里5万多条的insert会自动转换为参数形式,因为每个insert参数对应的值长度区别较大,使得BIND_MISMATCH不匹配导致高版本现象严重,从而出现了该Bug 9689310.

解决该问题
1.不要通过程序拼接sql出来,使用绑定参数形式
2.升级到相应新版本解决该问题
3.一次减少执行的sql数量,定时刷新shared pool[临时办法]

补充说明相关bug
Bug 5177766 OERI[17059] with SESSION_CACHED_CURSORS
Bug 8946311 Increase max children before reporting ORA-600 [17059]
BUG 9094984 ORA-600 [17059] WITH VERY HIGH CHILD CURSOR COUNTS BEING GENERATED
Bug 8922013 OERI [17059] / excess child cursors for SQL referencing REMOTE objects
Bug 9689310 Excessive child cursors / high VERSION_COUNT / OERI:17059 due to bind mismatch
BUG 8981059 HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH

发表在 ORA-xxxxx | 标签为 | 评论关闭