数据库报ORA-00600: 内部错误代码, 参数: [17059]，并产生大量trace日志文件

2015-07-20 13:40 abce 阅读(3411) 评论(0) 编辑收藏举报

用户反馈数据库服务器磁盘空间使用耗尽。登录服务器后查看，发现数据库产生大量的trace日志，并在alert日志中发现ora-600错误

alert日志信息：

Fri Jul 17 06:13:40 2015
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_53832.trc  (incident=35764):
ORA-00600: 内部错误代码, 参数: [17059], [0xDD7E984D0], [0xDD7E98D68], [0xE17AD9658], [], [], [], [], [], [], [], []
Fri Jul 17 06:14:01 2015
Sweep [inc][39570]: completed
Sweep [inc][39568]: completed
Sweep [inc][39466]: completed
Sweep [inc][35764]: completed
Fri Jul 17 06:14:10 2015
DDE: Problem Key 'ORA 600 [17059]' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes

trace文件内容：

*** 2015-07-17 06:35:20.058
*** SESSION ID:(42.1215) 2015-07-17 06:35:20.058
*** CLIENT ID:(server#Thread-42) 2015-07-17 06:35:20.058
*** SERVICE NAME:(SYS$USERS) 2015-07-17 06:35:20.058
*** MODULE NAME:(JDBC Thin Client) 2015-07-17 06:35:20.058
*** ACTION NAME:() 2015-07-17 06:35:20.058
 

LibraryHandle:  Address=17ad9658 Hash=931b2cd4 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 
  ObjectName:  Name=SELECT invokedId, timeConfig, lastTime, nextTime, nextTime2, handlerClz FROM bi_schd_timer WHERE nvl ( to_char( nextTime ), :"SYS_B_0" ) = :"SYS_B_1" or ( nextTime >= :"SYS_B_2" and nextTime < :"SYS_B_3" )

    FullHashValue=ee068dcfa54c7cea1ff5ed5c931b2cd4 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=2468031700 OwnerIdn=87 
  Statistics:  InvalidationCount=0 ExecutionCount=32768 LoadCount=32769 ActiveLocks=1 TotalLockCount=43844 TotalPinCount=1 
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=32768 KeepHandle=32768 BucketInUse=43841 HandleInUse=43841 
  Concurrency:  DependencyMutex=17ad9708(0, 11073, 0, 0) Mutex=17ad9780(42, 1409403, 3395054, 6) 
  Flags=RON/PIN/TIM/PN0/DBN/[10012841] 
  WaitersLists:  
    Lock=17ad96e8[17ad96e8,17ad96e8] 
    Pin=17ad96f8[17ad96c8,17ad96c8] 
  Timestamp:  Current=07-09-2015 12:55:05 
  LibraryObject:  Address=d7e984d0 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
    DataBlocks:  
      Block:  #='0' name=PCUR^931b2cd4 pins=0 Change=NONE   
        Heap=17ad95a0 Pointer=d7e985b8 Extent=d7e98450 Flags=I/-/P/A/-/- 
        FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=17207577371 
    ChildTable:  size='32768' 
      Child:  id='0' Table=d7e99380 Reference=d7e98e20 Handle=17ad9248 
      Child:  id='1' Table=d7e99380 Reference=d7e99188 Handle=17ad8558 
      Child:  id='2' Table=d7e99380 Reference=d7e869b0 Handle=17ad7868 
      ...
      Child:  id='32767' Table=31d8e5e8 Reference=31d624c0 Handle=163589d8 
  NamespaceDump:  
    Parent Cursor:  sql_id=1zxgdbk9jqb6n parent=0000000DD7E985B8 maxchild=32769 plk=y ppn=n KGX cleanup...

*** 2015-07-17 06:35:20.885
KGX Atomic Operation Log 0000000E16076E40
 Mutex 0000000E17AD9780(42, 0) idn 931b2cd4 oper EXCL
 Library Cache uid 42 efd 5 whr 102 slp 0
 oper=0 pt1=17ad9658 pt2=0 pt3=0
 pt4=0 pt5=0 ub4=0
Trace file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_12308.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

LibraryHandle:  Address=17ad9658 Hash=931b2cd4 LockMode=N PinMode=0 LoadLockMode=0

登陆数据库查看：

发现cursor_sharing被设置为smilar，修改为exact后错误消除

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      SIMILAR

SQL> alter system set cursor_sharing=exact scope=both;

System altered.

SQL>

其它类似错误可以参考MOS文档：

Understanding and Diagnosing ORA-600 [17059] Errors (Doc ID 1683417.1)
Bug 9689310 - Excessive child cursors / high VERSION_COUNT / ORA-600 [17059] due to bind mismatch (Doc ID 9689310.8)