oradump to mysql_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