ORA-00600: [7005], [192]内部错误一例

一套AIX上的9.2.0.6系统,应用的某条查询语句执行时频繁报ORA-00600:[7005]错误,alert告警日志内容如下:

Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Trace dumping is performing id=[cdmp_20091207152027]
Mon Dec  7 15:20:28 2009
Thread 2 advanced to log sequence 909143
 Current log# 7 seq# 909143 mem# 0: /oradata2/bidw/BIDW/redo2_3
...............
Mon Dec  7 15:21:10 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:21:11 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []


相关的trace文件列出了问题SQL语句:


   Dump file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

*** SESSION ID:(436.20428) 2009-12-07 15:20:26.734
*** 2009-12-07 15:20:26.734
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Current SQL statement for this session:
select /*t.consume_month,
*/
 nvl(t.cust_count, 0),
 nvl(t.net_count, 0),
 nvl(t.item_1, 0),
 nvl(t.item_2, 0),
 nvl(t.item_3, 0),
 nvl(t.item_4, 0),
 nvl(t.item_5, 0),
 nvl(t.item_6, 0),
 nvl(t.item_7, 0),
 nvl(t.item_8, 0),
 nvl(t.item_9, 0),
 nvl(t.item_10, 0),
 nvl(t.item_11, 0),
 nvl(t.item_12, 0)
  from (with t as (select t.consume_month consume_month,
                          t.cust_count cust_count,
                          min(t.net_count) net_count,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 01 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_1,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 02 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_2,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 03 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_3,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 04 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_4,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 05 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_5,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 06 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_6,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 07 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_7,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 08 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_8,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 09 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_9,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 10 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_10,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 11 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_11,
                          round(sum(case
                                      when substr(t.statis_month, 5, 2) = 12 then
                                       t.call_duration / t.net_count
                                      else
                                       0
                                    end),
                                0) item_12
                     from hwkr.tr_sc_138_consume_analyse_m t
                    where t.op_code = '1145'
                      and t.new_join_flag = '0'
                      and t.statis_month >=
                          to_number(substr(to_char(:1, 'yyyymm'), 1, 4) || '01')
                      and t.statis_month <= to_number(to_char(:2, 'yyyymm'))
                    group by t.consume_month, t.cust_count)
         select to_number(substr(t.consume_month, 5, 2)) consume_month,
                t.cust_count,
                t.net_count,
                t.item_1,
                t.item_2,
                t.item_3,
                t.item_4,
                t.item_5,
                t.item_6,
                t.item_7,
                t.item_8,
                t.item_9,
                t.item_10,
                t.item_11,
                t.item_12
           from t
         union all
         select 99,
                sum(t.cust_count),
                sum(t.net_count),
                sum(t.item_1),
                sum(t.item_2),
                sum(t.item_3),
                sum(t.item_4),
                sum(t.item_5),
                sum(t.item_6),
                sum(t.item_7),
                sum(t.item_8),
                sum(t.item_9),
                sum(t.item_10),
                sum(t.item_11),
                sum(t.item_12)
           from t) t, hwkr.tr_sc_138_m s
          where s.months = t.consume_month(+)
          order by s.months
----- Call Stack Trace -----
ksedmp <- ksfdmp <- kgeriv <- kgesiv <- ksesic1
       <- kprcdt <- rpiacp <- kprbbnda <- kprbbnd <- qes3tExecSQL
        <- qerleStart <- rwsstd <- qersoStart <- qerjoFetch <- opifch2
         <- opifch <- opiodr <- ttcpip <- opitsk <- opiino
          <- opiodr <- opidrv <- sou2o <- main <- start

----------------------------------------
   SO: 7000003c3970640, type: 4, owner: 7000003c3708368, flag: INIT/-/-/0x00
   (session) trans: 7000003dbd0d170, creator: 7000003c3708368, flag: (41) USR/- BSY/-/-/-/-/-
             DID: 0002-012E-0001F21C, short-term DID: 0000-0000-00000000
             txn branch: 0
             oct: 3, prv: 0, sql: 700000437fc5fd8, psql: 700000437fc5fd8, user: 214/BIOP
   O/S info: user: , term: , ospid: 1234, machine: BIDM2
             program:
   client info: 10.110.16.106
   last wait for 'db file scattered read' blocking sess=0x0 seq=21 wait_time=28955
               file#=129, block#=10088, blocks=3
   temporary object counter: 0
     ----------------------------------------

Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------
| Operation                      | Name               | Rows  | Bytes | Cost  |  TQ  |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT               |                    |     0 |     0 |     0 |      |      |           |       |       |
|  MERGE JOIN OUTER              |                    |     0 |     0 |     0 |      |      |           |       |       |
|   SORT JOIN                    |                    |     0 |     0 |     0 |      |      |           |       |       |
|    TABLE ACCESS FULL           | TR_SC_138_M        |     0 |     0 |     0 |      |      |           |       |       |
|   SORT JOIN                    |                    |     0 |     0 |     0 |      |      |           |       |       |
|    VIEW                        |                    |     0 |     0 |     0 |      |      |           |       |       |
|                                |                    |     0 |     0 |     0 |      |      |           |       |       |
|      UNION-ALL                 |                    |     0 |     0 |     0 |      |      |           |       |       |
|       VIEW                     |                    |     0 |     0 |     0 |      |      |           |       |       |
|        TABLE ACCESS FULL       | SYS_TEMP_4255117445|     0 |     0 |     0 |      |      |           |       |       |
|       SORT AGGREGATE           |                    |     0 |     0 |     0 |      |      |           |       |       |
|        VIEW                    |                    |     0 |     0 |     0 |      |      |           |       |       |
|         TABLE ACCESS FULL      | SYS_TEMP_4255117445|     0 |     0 |     0 |      |      |           |       |       |
-------------------------------------------------------------------------------------------------------------------------

经过研究发现可能是9.2.0.6版本上的Bug 3390566 "OERI / dump from functional indexes on TIMESTAMP columns",当使用存在TIMESTAMP列的函数索引时可能引发该Bug。这个Case同时提交了SR,Oracle GCS建议通过避免使用该索引的Workaround方式,因为原执行计划利用到了星型变化,故可以通过设置STAR_TRANSFORAMTION_ENABLED=FALSE来避免使用索引。当然长久之计还是升级到9.2.0.7以上版本(MOS宣称此Bug在该版本修复了)。

Setting STAR_TRANSFORAMTION_ENABLED=FALSE has been successfully used.
As a permanent solution, it is recommended to start planning for an upgrade to a supported Database Server version.
Currently supported Server versions are ver. 10.2, 11.1 and 11.2 with their latest patchsets.
Bug 3390566 is solved since 9.2.0.7.

Advantages for being on the latest patchset of a supported release:

+ new bugs can only be logged against latest patchset release of supported versions
+ backports can only be requested against latest patchset release of supported versions
+ most stable release since a lot of bugs are already fixed in the successive patchsets

Comments

  1. admin says

    Bug 3390566  OERI / dump from functional indexes on TIMESTAMP columns

    This note gives a brief overview of bug 3390566.
     The content was last updated on: 08-AUG-2005
     Click here for details of each of the sections below.

    Affects:

    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions < 10.2
    Versions confirmed as being affected
    Platforms affected Generic (all / most platforms affected)

    Fixed:

    This issue is fixed in

    Symptoms:

    Related To:

    Description

    Use of functional indexes against TIMESTAMP columns may cause unexpected  results if the functional index uses a conversion operator (Eg TO_CHAR)  with a format mask.       To be seeing this bug the following must be true :  -  A SQL operation fails in some unexpected way (there are various symptoms     such as Ora-600 [kksumc-xpin-required], Ora-600 [15212], Ora-932 or core     dump in kpobav())  -  The SQL statement operates on a column that has a functional index     defined upon it  -  The functional index involves a literal format mask being applied to a      TIMESTAMP column  -  col$.default$ for the column concerned has a conversion operator (Eg     TO_CHAR) where the format mask is represented as a bind variable and not      the original literal format string.    Workaround:    Rewrite the functional index will so that it explicitly casts     the datatype to a non-timestamp one. This also means that the SQL that needs    to use the index will need to be changed to use the altered index.  



    Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

    References

    Bug:3390566 (This link will only work for PUBLISHED bugs)
    Note:245840.1 Information on the sections in this article

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号