联系：手机/微信(+86 17813235971) QQ(107644445)
标题：insert into aud$引起高版本问题导致ORA-600[17059]
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
昨天晚上有朋友咨询我,他数据库(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-错误.通过查询数据库高版本信息,在重启一会儿的库中,发现
过几分钟后
通过这里,进一步诊断,引起高版本的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
_cursor_features_enabled=34???
应该是这个参数_cursor_obsolete_threshold.
_cursor_obsolete_threshold 这个参数11.2.0.3开始才有的，以前版本没有该参数，只能通过_cursor_features_enabled+event 106001去实现限制