标签归档：insert aud$

昨天晚上有朋友咨询我,他数据库(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