ORA-600(4454)错误

Posted on 2012/02/13 by yangtingkun

客户数据库出席ORA-600[4454]错误。
错误信息如下：

Fri Feb 10 18:43:57 2012
Errors IN file /oracle9/app/admin/settl/udump/settl1_ora_1667162.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Fri Feb 10 18:43:58 2012
Errors IN file /oracle9/app/admin/settl/udump/settl1_ora_1667162.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []

对应的TRACE文件信息为：

*** SESSION ID:(100.63267) 2012-02-10 18:43:57.789
*** 2012-02-10 18:43:57.789
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746CC ?
ksfdmp+0018          bl       01FD4010             
kgeriv+0118          bl       _ptrgl               
kgeasi+00cc          bl       kgeriv               1103C7470 ? 1103C7430 ?
                                                   1103C7450 ? 000000000 ?
                                                   FFFFFFFFFFFA9B8 ?
ktcsptg+00c4         bl       kgeasi               1100062D8 ? 110389E88 ?
                                                   116600001166 ? 200000002 ?
                                                   000000000 ? 70000010738F0E8 ?
                                                   0FFFFFFFF ? 0FFFFFFFF ?
ksupucg+1834         bl       ktcsptg              000000000 ? 000000000 ?
opiodr+0474          bl       ksupucg              100000001 ?
ttcpip+0cc4          bl       _ptrgl               
opitsk+0d60          bl       ttcpip               11000CF60 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF48 ?
                                                   FFFFFFFFFFFF790 ? 0A000EEA8 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A0147B50 ?
                                                   FFFFFFFFFFFF790 ?
main+0138            bl       01FD3A28             
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
.
.
.
===================================================
Files currently opened BY this process:
===================================================
PROCESS STATE
-------------
Process global information:
     process: 7000001043a07d8, CALL: 70000011126cab8, xact: 70000010dd8de90, curses: 70000010738f0e8, usrses: 70000010738f0e8
  ----------------------------------------
  SO: 7000001043a07d8, TYPE: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=63, calls cur/top: 70000011126cab8/70000011126cab8, flag: (0) -
            INT error: 0, CALL error: 0, sess error: 0, txn error 0
  (post info) LAST post received: 0 0 50
              LAST post received-location: kcbzww
              LAST process TO post me: 7000001043a1288 32 0
              LAST post sent: 789830 0 50
              LAST post sent-location: kcbzww
              LAST process posted BY me: 700000104398798 1 0
    (latch info) wait_event=0 bits=0
    Process GROUP: DEFAULT, pseudo proc: 70000010537bfc8
    O/S info: USER: oracle9, term: UNKNOWN, ospid: 1667162
    OSD pid info: Unix process pid: 1667162, image: oracle@zwq_jies1 (TNS V1-V3)
    ----------------------------------------
    SO: 7000001150d3e80, TYPE: 8, owner: 7000001043a07d8, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=138c9638 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: 7000001150d3dc8, TYPE: 8, owner: 7000001043a07d8, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=138c7908 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: 7000001150d3d10, TYPE: 8, owner: 7000001043a07d8, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=138c9468 incno=0 pending i/o cnt=0
    ----------------------------------------
    SO: 7000001150d3c58, TYPE: 8, owner: 7000001043a07d8, flag: INIT/-/-/0x00
    (FOB) flags=2 fib ptr=138c6e10 incno=1 pending i/o cnt=0
    ----------------------------------------
    SO: 70000010738f0e8, TYPE: 4, owner: 7000001043a07d8, flag: INIT/-/-/0x00
    (SESSION) trans: 70000010dd8de90, creator: 7000001043a07d8, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-003F-00002EDF, short-term DID: 0000-0000-00000000
              txn branch: 7000001147dbed8
              oct: 0, prv: 0, SQL: 0, psql: 700000117b7f778, USER: 24/SETTLE
    O/S info: USER: settle, term: , ospid: 1056780, machine: zwq_jies2
              program: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T
    application name: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T, hash VALUE=0
    LAST wait FOR 'SQL*Net message from client' blocking sess=0x0 seq=19236 wait_time=220
                driver id=54435000, #bytes=1, =0
    TEMPORARY object counter: 0
      ----------------------------------------
      SO: 70000010644cf68, TYPE: 6, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      (enqueue) DX-00000007-00000000	DID: 0001-003F-40019DB5
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 
      res: 70000010545ea28, mode: X, prv: 70000010545ea38, sess: 70000010738f0e8, proc: 7000001043a07d8
      ----------------------------------------
      SO: 700000117488008, TYPE: 51, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=700000117488008 handle=70000011bfe6650 mode=N
      CALL pin=0 SESSION pin=700000129bb44e0
      htl=700000117488078[7000001122c7900,7000001122c7900] htb=7000001122c7900
      USER=70000010738f0e8 SESSION=70000010738f0e8 COUNT=1 flags=[00] SAVEPOINT=126982
      LIBRARY OBJECT HANDLE: handle=70000011bfe6650
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0001-0041 LOCK=N pin=0 latch#=16
      lwt=70000011bfe6680[70000011bfe6680,70000011bfe6680] ltm=70000011bfe6690[70000011bfe6690,70000011bfe6690]
      pwt=70000011bfe66b0[70000011bfe66b0,70000011bfe66b0] ptm=70000011bfe6740[70000011bfe6740,70000011bfe6740]
      REF=70000011bfe6660[70000011bfe6a68, 70000011bfe6a68] lnd=70000011bfe6758[70000011bfe6758,70000011bfe6758]
        LIBRARY OBJECT: object=70000011bfe6278
        TYPE=CRSR flags=EXS[0001] pflags= [00] STATUS=VALD LOAD=0
        DEPENDENCIES: COUNT=1 SIZE=16
        AUTHORIZATIONS: COUNT=1 SIZE=16 minimum entrysize=16
        ACCESSES: COUNT=1 SIZE=16
        TRANSLATIONS: COUNT=1 SIZE=16
        DATA BLOCKS:
        DATA#     heap  pointer STATUS pins CHANGE
        ----- -------- -------- ------ ---- ------
            0 70000011bfe6590 70000011bfe5f00 I/P/A     0 NONE  
            6 70000011bfe6398        0 I/P/-     0 NONE  
      ----------------------------------------
      SO: 700000113f7d738, TYPE: 51, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=700000113f7d738 handle=70000011bfe6f10 mode=N
      CALL pin=7000001113b2e20 SESSION pin=0
      htl=700000113f7d7a8[7000001122c8030,7000001122c8030] htb=7000001122c8030
      USER=70000010738f0e8 SESSION=70000010738f0e8 COUNT=1 flags=[00] SAVEPOINT=126981
      LIBRARY OBJECT HANDLE: handle=70000011bfe6f10
      name=SELECT TRIM(fee_type) ,TRIM(fee_dir) ,TRIM(fee_rate) ,TRIM(rate_unit)  FROM app_clearing_fee_rate  ORDER BY fee_type ASC             
      hash=336a54ce TIMESTAMP=01-17-2012 17:46:10
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 LOCK=N pin=0 latch#=16
      lwt=70000011bfe6f40[70000011bfe6f40,70000011bfe6f40] ltm=70000011bfe6f50[70000011bfe6f50,70000011bfe6f50]
      pwt=70000011bfe6f70[70000011bfe6f70,70000011bfe6f70] ptm=70000011bfe7000[70000011bfe7000,70000011bfe7000]
      REF=70000011bfe6f20[70000011bfe6f20, 70000011bfe6f20] lnd=70000011bfe7018[70000011bfe7018,70000011bfe7018]
        LIBRARY OBJECT: object=70000011bfe6b38
        TYPE=CRSR flags=EXS[0001] pflags= [00] STATUS=VALD LOAD=0
        CHILDREN: SIZE=16
        child#    TABLE reference   handle
        ------ -------- --------- --------
             0 70000011bfe6d98 70000011bfe6a68 70000011bfe6650
        DATA BLOCKS:
        DATA#     heap  pointer STATUS pins CHANGE
        ----- -------- -------- ------ ---- ------
            0 70000011bfe6e50 70000011bfe6c30 I/P/A     0 NONE  
      ----------------------------------------
.
.
.
      ----------------------------------------
      SO: 700000111208478, TYPE: 51, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=700000111208478 handle=700000119951970 mode=N
      CALL pin=7000001171fea08 SESSION pin=0
      htl=7000001112084e8[7000001122c76a0,7000001122c76a0] htb=7000001122c76a0
      USER=70000010738f0e8 SESSION=70000010738f0e8 COUNT=1 flags=[00] SAVEPOINT=125499
      LIBRARY OBJECT HANDLE: handle=700000119951970
      name=SELECT COUNT(*)  INTO :b1  FROM app_msc_default 
      hash=1042c435 TIMESTAMP=01-17-2012 17:46:09
      namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/[12010000]
      kkkk-dddd-llll=0000-0001-0001 LOCK=N pin=0 latch#=6
      lwt=7000001199519a0[7000001199519a0,7000001199519a0] ltm=7000001199519b0[7000001199519b0,7000001199519b0]
      pwt=7000001199519d0[7000001199519d0,7000001199519d0] ptm=700000119951a60[700000119951a60,700000119951a60]
      REF=700000119951980[700000119951980, 700000119951980] lnd=700000119951a78[700000119951a78,700000119951a78]
        LIBRARY OBJECT: object=7000001199516a8
        TYPE=CRSR flags=EXS[0001] pflags= [00] STATUS=VALD LOAD=0
        CHILDREN: SIZE=16
        child#    TABLE reference   handle
        ------ -------- --------- --------
             0 700000119936450 7000001199361b8 700000119936048
        DATA BLOCKS:
        DATA#     heap  pointer STATUS pins CHANGE
        ----- -------- -------- ------ ---- ------
            0 7000001199518b0 7000001199517a0 I/P/A     0 NONE  
      ----------------------------------------
      SO: 700000114c02960, TYPE: 51, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=700000114c02960 handle=700000118a36ab0 mode=N
      CALL pin=0 SESSION pin=7000001126e7a20
      htl=700000114c029d0[7000001122c8230,7000001122c8230] htb=7000001122c8230
      USER=70000010738f0e8 SESSION=70000010738f0e8 COUNT=1 flags=[00] SAVEPOINT=873
      LIBRARY OBJECT HANDLE: handle=700000118a36ab0
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0001-0041 LOCK=N pin=0 latch#=7
      lwt=700000118a36ae0[700000118a36ae0,700000118a36ae0] ltm=700000118a36af0[700000118a36af0,700000118a36af0]
      pwt=700000118a36b10[700000118a36b10,700000118a36b10] ptm=700000118a36ba0[700000118a36ba0,700000118a36ba0]
      REF=700000118a36ac0[700000118a36ec8, 700000118a36ec8] lnd=700000118a36bb8[700000118a36bb8,700000118a36bb8]
        LIBRARY OBJECT: object=700000118a366d8
        TYPE=CRSR flags=EXS[0001] pflags= [00] STATUS=VALD LOAD=0
        DEPENDENCIES: COUNT=1 SIZE=16
        AUTHORIZATIONS: COUNT=1 SIZE=16 minimum entrysize=16
        ACCESSES: COUNT=1 SIZE=16
        TRANSLATIONS: COUNT=1 SIZE=16
        DATA BLOCKS:
        DATA#     heap  pointer STATUS pins CHANGE
        ----- -------- -------- ------ ---- ------
            0 700000118a369f0 700000118a36360 I/P/A     0 NONE  
            6 700000118a367f8        0 I/P/-     0 NONE  
      ----------------------------------------
      SO: 700000114c025a8, TYPE: 51, owner: 70000010738f0e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: LOCK=700000114c025a8 handle=700000118a37370 mode=N
      CALL pin=70000011226a160 SESSION pin=0
      htl=700000114c02618[7000001122c76f0,7000001122c76f0] htb=7000001122c76f0
      USER=70000010738f0e8 SESSION=70000010738f0e8 COUNT=1 flags=[00] SAVEPOINT=854
      LIBRARY OBJECT HANDLE: handle=700000118a37370
      name=SELECT ltrim(rtrim(h1h2h3h4)) ,ltrim(rtrim(long_code_new)) ,ltrim(rtrim(settle_type))  FROM b_h1h2h3_code_allocate  ORDER BY LENGTH(ltrim(rtrim(h1h2h3h4)))            
      hash=2238ad3a TIMESTAMP=01-17-2012 17:45:01
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 LOCK=N pin=0 latch#=7
      lwt=700000118a373a0[700000118a373a0,700000118a373a0] ltm=700000118a373b0[700000118a373b0,700000118a373b0]
      pwt=700000118a373d0[700000118a373d0,700000118a373d0] ptm=700000118a37460[700000118a37460,700000118a37460]
      REF=700000118a37380[700000118a37380, 700000118a37380] lnd=700000118a37478[700000118a37478,700000118a37478]
        LIBRARY OBJECT: object=700000118a36f98
        TYPE=CRSR flags=EXS[0001] pflags= [00] STATUS=VALD LOAD=0
        CHILDREN: SIZE=16
        child#    TABLE reference   handle
        ------ -------- --------- --------
             0 700000118a371f8 700000118a36ec8 700000118a36ab0
        DATA BLOCKS:
        DATA#     heap  pointer STATUS pins CHANGE
        ----- -------- -------- ------ ---- ------
            0 700000118a372b0 700000118a37090 I/P/A     0 NONE  
      ----------------------------------------

查询MOS发现，这个错误和Bug 1402161 – OERI:4411/OERI:4454 on long running job [ID 1402161.8]描述的十分相近，导致问题的原因是由于SAVEPOINT信息被覆盖，导致会话异常。
虽然这个错误确认在9.2.0.1中被修正，理论上在9.2.0.8中出现的概率很低，不过参考文档Large SQL Statement or Import Fails With ORA-600 [4454] [ID 353190.1]描述的内容，Oracle在10.2中才彻底改变了内部SAVEPOINT的实现，因此这个问题在9.2和10.1中仍然可能出现。

Posted in BUG | Tagged , , | Leave a comment

STATSPACK报告REDO SIZE出现负值

Posted on 2012/02/12 by yangtingkun

客户9.2.0.8数据库的STATSPACK报告出现负的REDO SIZE。
类似信息如下：

LOAD Profile
~~~~~~~~~~~~                            Per SECOND       Per TRANSACTION
                                   ---------------       ---------------
                  Redo SIZE:           -263,339.94           -685,483.59

其实经常出现的还包括逻辑读以及物理读等。其实不查询MOS也可以确定，这是Oracle的bug，而导致bug的原因应该是底层的C代码中没有使用UNSIGNED类型，以至于变量溢出后出现了负值。
在MOS中的对应描述为：Redo Size Negative In Statspack Report [ID 308414.1]，Oracle在10g中解决了这个问题。确认影响版本从8.1.7.4到9.2.0.8。

Posted in BUG | Tagged , , | Leave a comment

以FORCE选项VOTEDISK导致OCR配置异常

Posted on 2012/02/11 by yangtingkun

在一次Oracle 10.2.0.1 RAC环境迁移到新存储的过程中，添加VOTEDISK后，OCR配置出现了异常。
由于RAC环境整体迁移到新存储上，因此需要将OCR和VOTEDISK从原有存储的裸设备上迁移到新存储的裸设备上。
在迁移OCR的时候已经碰到了不少错误，所幸都很快得以解决。
运行ocrconfig replace ocr出现PROT-16错误：https://yangtingkun.net/?p=116
运行ocrconfig replace ocrmirror出现PROT-22错误：https://yangtingkun.net/?p=146
没想到随后的votedisk迁移，虽然并未报错，但是却引发了更大的麻烦，步骤是先增加一个新存储上的votedisk，然后删除所有旧存储上的votedisk，最后添加其他四个新存储上的votedisk，命令如下：

# ./crsctl ADD css votedisk /dev/vote1 –force

使用上面的命令添加前三个votedisk没有问题，添加第四个和第五个时，可能是由于AIX上的10.2.0.1不支持，且使用了force选项，导致命令虽然成功执行，但是实际上ocr中记录的信息是混乱的。
通过命令检查votedisk的配置发现：

# ./crsctl query css votedisk
 0.     0    /dev/vote4
 1.     0    /dev/vote1
 2.     0    /dev/vote4
 3.     0    /dev/vote4
 4.     0    /dev/vote5

配置中出现了重复的VOTEDISK记录，随后通过一系列的delete和add命令使得查询的结果变得正常：

crsctl DELETE css votedisk /dev/vote4 –force
# ./crsctl DELETE css votedisk /dev/vote4 -force
# ./crsctl DELETE css votedisk /dev/vote4 -force
# ./crsctl DELETE css votedisk /dev/vote4 -force
# ./crsctl DELETE css votedisk /dev/vote5 -force
# ./crsctl ADD css votedisk /dev/vote2 -force
# ./crsctl ADD css votedisk /dev/vote3 –force
# ./crsctl query css votedisk
 0.     0    /dev/vote2
 1.     0    /dev/vote1
 2.     0    /dev/vote3

可惜问题并非如此简单，虽然随后的数据库相关操作都没有问题，但是在迁移完成后尝试重启时，发现votedisk的配置仍然存在问题，导致cluster环境无法启动。
检查时发现OCR中记录的信息已经和刚才配置好的信息不符，没有办法值得再次利用delete和add的方法解决，CLUSTER虽然成功启动，但是这个问题的本质是ocr中的配置存在错乱，仅仅利用add votedisk和delete votedisk无法彻底解决这个问题。果然不久以后查询VOTEDISK信息再次发现了问题。
为了解决OCR中错误的配置问题，专门申请了停机时间，并设计了OCR重建的方案，而最坏的打算是将整个RAC环境重建。
当然正常情况下，只需要重建OCR和VOT既可，大概的步骤如下：
备份ocr设备和votedisk设备：

# ./ocrconfig -export /home/oracle/rmanocr_date.exp
# dd IF=/dev/vote1 OF=/rmanhome/oracle/vote_disk_date.bak

删除ocr配置信息

# $ORA_CRS_HOME/bin/crsctl stop crs
# $ORA_CRS_HOME/install/rootdelete.sh
# $ORA_CRS_HOME/install/rootdeinstall.sh

crsctl stop crs和rootdelete.sh需要root在两个节点上分别运行，rootdeinstall.sh用root在节点1上运行。
确认cluster状态，这三个命令应该不返回结果

$ ps -ef | grep cssd
$ ps -ef | grep evmd
$ ps -ef | grep crsd

重建ocr和vot

# $ORA_CRS_HOME/root.sh

在节点1上使用root执行root.sh，等到节点1上的命令正常结束后，节点2上用root运行root.sh
root.sh的脚本在运行前需要检查，确保其中的配置正常。
确认votedisk正常

# ./crsctl query css votedisk

向ocr中添加rac的其他配置

$ <CRS_HOME>/bin/racgons add_config hostname1:port hostname2:port
其中端口信息可以从$ORA_CRS_HOME/log/hostname/racg/ora.instance_name.ons文件中获取。
$/u01/crs/bin/oifcfg setif -global eth1/private_ip:cluster_interconnect eth0/public_ip:public
利用netca添加监听利用srvctl添加asm、db和instance信息
$ srvctl ADD DATABASE -d dbname -o $ORACLE_HOME -p +DATA/dbname/spfiletest.ora -n global_name 
$ srvctl ADD instance -d dbname -i instance1 -n hostname
$ srvctl ADD instance -d dbname -i instance2 -n hostname

检查配置健康

$ cluvfy stage -post crsinst -n node1,node2

启动cluster和数据库

# $ORA_CRS_HOME/bin/crsctl START crs

而在实际迁移过程中，并没有执行这么复杂的操作。在备份完毕后，检查发现ocrconfig导出的备份似乎并没有包含混乱的votedisk配置信息，于是尝试利用ocrconfig –import功能导入刚刚导出的备份：

# ./ocrconfig -export /home/oracle/rmanocr_date.exp

结果发现ocr中votedisk的配置恢复了正常，而且此后也不会再发生变化。
看来Oracle在使用逻辑备份导出导入ocr配置的时候，可以自动过滤或清理掉错误的配置。

Posted in ORACLE | Tagged , , | Leave a comment

SHUTDOWN IMMEDIATE出现ORA-27144错误

Posted on 2012/02/10 by yangtingkun

客户的10.2.0.1 RAC for AIX数据库在关闭一个节点时碰到这个错误。
告警日志中信息如下：

Tue Jan 10 21:11:42 2012
Shutting down instance (immediate)
License high water mark = 721
Tue Jan 10 21:11:42 2012
Stopping Job queue slave processes
Tue Jan 10 21:11:42 2012
Job queue slave processes stopped
ALL dispatchers AND shared servers shutdown
Tue Jan 10 21:11:52 2012
PMON failed TO acquire latch, see PMON dump
PMON failed TO acquire latch, see PMON dump
PMON failed TO acquire latch, see PMON dump
Tue Jan 10 21:12:04 2012
PMON failed TO acquire latch, see PMON dump
PMON failed TO acquire latch, see PMON dump
Tue Jan 10 21:12:12 2012
Shutting down instance (abort)
License high water mark = 721
Tue Jan 10 21:12:13 2012
Errors IN file /u01/app/oracle/admin/wdyx/udump/wdyx1_ora_1434094.trc:
ORA-27300: OS system dependent operation:invalid_process failed WITH STATUS: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgpstime3
ORA-27144: attempt TO KILL process failed

显然SHUTDOWN IMMEDIATE无法正常关闭数据库，必须使用SHUTDOWN ABORT来完成数据库的关闭操作，但是SHUTDOWN ABORT在关闭时引发了ORA-27144错误。
在MOS文档The following Errors are reported during Shutdown Immediate: ORA-27300, ORA-27301, ORA-27302, ORA-27144 [ID 1186504.1]描述了这个问题，Oracle给出的建议是直接忽略这个问题。
文档中出现这个错误的是10.2.0.4版本，但是在出现ORA-27144错误后能正常关闭，而不像当前10.2.0.1版本这样，在SHUTDOWN IMMEDIATE时出现PMON failed to acquire latch信息，而SHUTDOWN ABORT后才出现ORA-27144错误。
看来升级到高版本虽然不能完全的解决这个问题，但是至少可以解决SHUTDOWN IMMEDIATE无法正常关闭的问题。

Posted in BUG | Tagged , , , , , | Leave a comment

用存储过程封装awrrpt脚本（五）

Posted on 2012/02/09 by yangtingkun

做了一个存储过程，封装了awr的report的功能。
根据11.2.0.3的TYPE的变化，修改了定义，新增一个变量控制是否生成RAC的GLOBAL报告，默认不生成全局报告。将DBNAME输入改变为DBID，解决DBNAME同名问题。
用存储过程封装awrrpt脚本（一）：http://yangtingkun.itpub.net/post/468/515180
用存储过程封装awrrpt脚本（二）：http://yangtingkun.itpub.net/post/468/515229
用存储过程封装awrrpt脚本（三）：http://yangtingkun.itpub.net/post/468/515271
用存储过程封装awrrpt脚本（四）：http://yangtingkun.itpub.net/post/468/517487
修改后的过程如下：

SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (
2 P_BEGIN IN VARCHAR2,
3 P_END IN VARCHAR2,
4 P_DIR IN VARCHAR2,
5 P_DBID IN NUMBER DEFAULT NULL,
6 P_PERINTERVAL IN BOOLEAN DEFAULT FALSE,
7 P_GLOBAL IN BOOLEAN DEFAULT FALSE) AS
8 TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER;
9 V_REPORT T_VARCHAR;
10 V_FILE UTL_FILE.FILE_TYPE;
11 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12 TYPE T_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER;
13 V_INSTANCE T_NUM;
14 V_SNAP_BEG T_NUM;
15 V_SNAP_END T_NUM;
16 V_BEGIN_DATE T_DATE;
17 V_END_DATE T_DATE;
18 V_BEGIN NUMBER;
19 V_END NUMBER;
20 V_DBID NUMBER;
21 V_DBNAME V$DATABASE.NAME%TYPE;
22 V_VERSION V$VERSION.BANNER%TYPE;
23 BEGIN
24 
25 IF P_DBID IS NOT NULL THEN
26 SELECT DISTINCT DBID, DB_NAME
27 INTO V_DBID, V_DBNAME
28 FROM DBA_HIST_DATABASE_INSTANCE
29 WHERE DBID = P_DBID;
30 ELSE
31 SELECT DBID, NAME
32 INTO V_DBID, V_DBNAME
33 FROM V$DATABASE;
34 END IF;
35 
36 SELECT DISTINCT INSTANCE_NUMBER,
37 FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS'))),
38 FIRST_VALUE(END_INTERVAL_TIME) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS')))
39 BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG, V_BEGIN_DATE
40 FROM DBA_HIST_SNAPSHOT
41 WHERE DBID = V_DBID
42 ORDER BY INSTANCE_NUMBER;
43 
44 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
45 BEGIN
46 SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS'))),
47 FIRST_VALUE(END_INTERVAL_TIME) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS')))
48 INTO V_SNAP_END(I), V_END_DATE(I)
49 FROM DBA_HIST_SNAPSHOT
50 WHERE SNAP_ID > V_SNAP_BEG(I)
51 AND DBID = V_DBID
52 AND INSTANCE_NUMBER = V_INSTANCE(I);
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 V_INSTANCE.DELETE(I);
56 END;
57 END LOOP;
58 
59 IF V_INSTANCE.COUNT = 1
60 THEN
61 V_BEGIN := V_SNAP_BEG(1);
62 IF P_PERINTERVAL = FALSE
63 THEN
64 V_END := V_SNAP_END(1);
65 ELSE
66 V_END := V_SNAP_BEG(1) + 1;
67 SELECT END_INTERVAL_TIME
68 INTO V_END_DATE(1)
69 FROM DBA_HIST_SNAPSHOT
70 WHERE DBID = V_DBID
71 AND INSTANCE_NUMBER = 1
72 AND SNAP_ID = V_END;
73 END IF;
74 WHILE(V_END <= V_SNAP_END(1)) LOOP
75 V_FILE := UTL_FILE.FOPEN(
76 P_DIR,
77 'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_'
78 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_'
79 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html',
80 'w',
81 32767);
82 
83 SELECT OUTPUT
84 BULK COLLECT INTO V_REPORT
85 FROM TABLE(
86 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
87 V_DBID,
88 V_INSTANCE(1),
89 V_BEGIN,
90 V_END,
91 0));
92 FOR I IN 1..V_REPORT.COUNT LOOP
93 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
94 END LOOP;
95 UTL_FILE.FCLOSE(V_FILE);
96 V_BEGIN := V_BEGIN + 1;
97 V_END := V_END + 1;
98 V_BEGIN_DATE(1) := V_END_DATE(1);
99 SELECT MAX(END_INTERVAL_TIME)
100 INTO V_END_DATE(1)
101 FROM DBA_HIST_SNAPSHOT
102 WHERE DBID = V_DBID
103 AND INSTANCE_NUMBER = 1
104 AND SNAP_ID = V_END;
105 END LOOP;
106 ELSE
107 SELECT BANNER
108 INTO V_VERSION
109 FROM V$VERSION
110 WHERE BANNER LIKE 'CORE%';
111 IF (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) > 11
112 OR (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) = 11
113 AND TO_NUMBER(SUBSTR(V_VERSION, INSTR(V_VERSION, '.', 1) + 1, INSTR(V_VERSION, '.', 1, 2) - INSTR(V_VERSION, '.', 1))) = 2))
114 AND P_GLOBAL
115 THEN
116 V_BEGIN := V_SNAP_BEG(1);
117 IF P_PERINTERVAL = FALSE
118 THEN
119 V_END := V_SNAP_END(1);
120 ELSE
121 V_END := V_SNAP_BEG(1) + 1;
122 SELECT END_INTERVAL_TIME
123 INTO V_END_DATE(1)
124 FROM DBA_HIST_SNAPSHOT
125 WHERE DBID = V_DBID
126 AND INSTANCE_NUMBER = 1
127 AND SNAP_ID = V_END;
128 END IF;
129 WHILE(V_END <= V_SNAP_END(1)) LOOP
130 V_FILE := UTL_FILE.FOPEN(
131 P_DIR,
132 'awr_' || V_DBNAME || '_' || 'RAC' || '_'
133 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_'
134 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html',
135 'w',
136 32767);
137 
138 EXECUTE IMMEDIATE
139 'SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DBID, '''', :BEGIN, :END, 0))'
140 BULK COLLECT INTO V_REPORT
141 USING V_DBID, V_BEGIN, V_END;
142 
143 FOR I IN 1..V_REPORT.COUNT LOOP
144 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
145 END LOOP;
146 UTL_FILE.FCLOSE(V_FILE);
147 V_BEGIN := V_BEGIN + 1;
148 V_END := V_END + 1;
149 V_BEGIN_DATE(1) := V_END_DATE(1);
150 SELECT MAX(END_INTERVAL_TIME)
151 INTO V_END_DATE(1)
152 FROM DBA_HIST_SNAPSHOT
153 WHERE DBID = V_DBID
154 AND INSTANCE_NUMBER = 1
155 AND SNAP_ID = V_END;
156 END LOOP;
157 ELSE
158 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
159 V_BEGIN := V_SNAP_BEG(I);
160 IF P_PERINTERVAL = FALSE
161 THEN
162 V_END := V_SNAP_END(I);
163 ELSE
164 V_END := V_SNAP_BEG(I) + 1;
165 SELECT END_INTERVAL_TIME
166 INTO V_END_DATE(I)
167 FROM DBA_HIST_SNAPSHOT
168 WHERE DBID = V_DBID
169 AND INSTANCE_NUMBER = V_INSTANCE(I)
170 AND SNAP_ID = V_END;
171 END IF;
172 WHILE(V_END <= V_SNAP_END(I)) LOOP
173 V_FILE := UTL_FILE.FOPEN(
174 P_DIR,
175 'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_'
176 || TO_CHAR(V_BEGIN_DATE(I), 'YYYYMMDD_HH24MISS') || '_'
177 || TO_CHAR(V_END_DATE(I), 'YYYYMMDD_HH24MISS') || '.html',
178 'w',
179 32767);
180 
181 SELECT OUTPUT
182 BULK COLLECT INTO V_REPORT
183 FROM TABLE(
184 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
185 V_DBID,
186 V_INSTANCE(I),
187 V_BEGIN,
188 V_END,
189 0));
190 FOR I IN 1..V_REPORT.COUNT LOOP
191 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
192 END LOOP;
193 UTL_FILE.FCLOSE(V_FILE);
194 V_BEGIN := V_BEGIN + 1;
195 V_END := V_END + 1;
196 V_BEGIN_DATE(I) := V_END_DATE(I);
197 SELECT MAX(END_INTERVAL_TIME)
198 INTO V_END_DATE(I)
199 FROM DBA_HIST_SNAPSHOT
200 WHERE DBID = V_DBID
201 AND INSTANCE_NUMBER = V_INSTANCE(I)
202 AND SNAP_ID = V_END;
203 END LOOP;
204 END LOOP;
205 END IF;
206 END IF;
207 END;
208 /
PROCEDURE created.

运行过程：

SQL> EXEC p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558, FALSE, TRUE)
PL/SQL PROCEDURE successfully completed.
SQL> host ls -l *20120205*.html
-rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html
SQL> EXEC p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558)
PL/SQL PROCEDURE successfully completed.
SQL> host ls -l *20120205*.html
-rw-r--r-- 1 ora11g oinstall 642609 Feb 10 16:20 awr_ECARD_1_20120205_100035_20120205_120002.html
-rw-r--r-- 1 ora11g oinstall 619464 Feb 10 16:20 awr_ECARD_2_20120205_100035_20120205_120002.html
-rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html
Posted in ORACLE | Tagged , , | Leave a comment

11.2.0.3更改AWR报告底层TYPE类型

Posted on 2012/02/08 by yangtingkun

在11.2.0.2中读取AWR报告的过程在11.2.0.3中报错。
由于过程太长，将关键部分简化，分别在10.2和11.2.0.3中运行：

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS FOR Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 – Production
SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT 
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN, 
 20                     V_END,
 21                     0));
 22  END;
 23  /
PL/SQL PROCEDURE successfully completed.

10204上运行没有任何问题，但是在11.2.0.3中：

SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS FOR Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 – Production
SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT 
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN, 
 20                     V_END,
 21                     0));
 22  END;
 23  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: NUMERIC OR VALUE error: Bulk Bind: Truncated Bind
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: at line 1
ORA-06512: at line 13

同样的代码在不同的版本中表现不同，很显然是Oracle的实现发生的变化。
从错误信息看，报错出现在BULK COLLECT INTO上，那么导致问题的应该是函数的返回值。

SQL> DESC DBMS_WORKLOAD_REPOSITORY
FUNCTION ASH_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE
 Argument Name                  TYPE                    IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BTIME                        DATE                    IN
 L_ETIME                        DATE                    IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT
 L_SLOT_WIDTH                   NUMBER                  IN     DEFAULT
 L_SID                          NUMBER                  IN     DEFAULT
 L_SQL_ID                       VARCHAR2                IN     DEFAULT
 L_WAIT_CLASS                   VARCHAR2                IN     DEFAULT
 L_SERVICE_HASH                 NUMBER                  IN     DEFAULT
 L_MODULE                       VARCHAR2                IN     DEFAULT
 L_ACTION                       VARCHAR2                IN     DEFAULT
 L_CLIENT_ID                    VARCHAR2                IN     DEFAULT
 L_PLSQL_ENTRY                  VARCHAR2                IN     DEFAULT
.
.
.
FUNCTION AWR_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE
 Argument Name                  TYPE                    IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BID                          NUMBER                  IN
 L_EID                          NUMBER                  IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT
.
.
.
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
 Argument Name                  TYPE                    IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 RETENTION                      NUMBER                  IN     DEFAULT
 INTERVAL                       NUMBER                  IN     DEFAULT
 TOPNSQL                        VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT

返回结果为AWRRPT_HTML_TYPE_TABLE类型，查询TYPE类型获取详细信息：

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE_TABLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE_TABLE')
--------------------------------------------------------------------------------
  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE_TABLE"
  AS TABLE OF AWRRPT_HTML_TYPE
SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL;
DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE')
--------------------------------------------------------------------------------
  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE"
  AS object (output varchar2(1500 CHAR))

可以看到10.2.0.4中，或者说在11.2.0.3以前的版本，TYPE的定义长度是1500 CHAR，而在11.2.0.3中定义变成：

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL;
DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE')
--------------------------------------------------------------------------------
  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE"
  AS object (output varchar2(8000 CHAR))

显然RETURN类型的长度变化导致了这个问题，根据Oracle定义的变化简单修改代码，可以避免11.2.0.3上错误的产生：

SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT 
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN, 
 20                     V_END,
 21                     0));
 22  END;
 23  /
PL/SQL PROCEDURE successfully completed.
Posted in ORACLE | Tagged , , , | Leave a comment

数据泵导出出现ORA-31623错误

Posted on 2012/02/07 by yangtingkun

客户数据库在调整SGA后，导出出现ORA-31623错误。
Oracle对于这个错误的描述为：

ORA-31623: a job IS NOT attached TO this SESSION via the specified handle
Cause: An attempt TO reference a job USING a handle which IS invalid OR no longer valid FOR the CURRENT SESSION.
Action: SELECT a handle corresponding TO a valid active job OR START a NEW job.

从这个错误看不出导致问题的真正原因，不过从告警日志中可以发现进一步的信息：

Tue Jan 17 19:30:08 2012
Errors IN file /opt/oracle/diag/rdbms/mobiledb_n/mobiledb/trace/mobiledb_ora_17167.trc (incident=84810):
ORA-04031: unable TO allocate 56 bytes OF shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Incident details IN: /opt/oracle/diag/rdbms/mobiledb_n/mobiledb/incident/incdir_84810/mobiledb_ora_17167_i84810.trc
USE ADRCI OR Support Workbench TO package the incident.
See Note 411.1 at My Oracle Support FOR error AND packaging details.
Tue Jan 17 19:30:11 2012
Dumping diagnostic DATA IN directory=[cdmp_20120117193011], requested BY (instance=1, osid=17167), summary=[incident=84810].
Tue Jan 17 19:30:12 2012
Sweep [inc][84810]: completed
Sweep [inc2][84810]: completed

数据库在尝试分配STREAMS POOL的时候出现了ORA-4031错误。此时查询V$MEMORY_RESIZE_OPS视图，发现同一时间大量的STREAMS POOL扩展失败的记录，Oracle尝试分配STREAMS POOL但是目前数据库的内存采用了在SGA_TARGET下手工分配的方式，为了避免Oracle内存的动态调整，所有的内存组件已经将内存全部使用，因此STREAMS POOL无法获取到空间。
解决问题的方法很简单，手工收缩DB_CACHE_SIZE或SHARED_POOL_SIZE的值，分配给STREAMS POOL，就可以避免这个错误，不过数据泵的导出还要使用STREAMS POOL，还是第一次发现这个问题。

Posted in ORACLE | Tagged , , , | Leave a comment

ORA-600(1158)错误

Posted on 2012/02/06 by yangtingkun

执行DDL时取消当前的操作出现这个错误。
详细错误信息如下：

SQL> ALTER TRIGGER DBFW_CONSOLE_ACCESS.TRIGGER_DDL disable;
^Calter TRIGGER DBFW_CONSOLE_ACCESS.TRIGGER_DDL disable
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [1158], [], [], [], [], [], [], []

告警日志中信息为：

Tue Feb 7 14:56:28 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/eygle_ora_21708.trc:
ORA-00600: internal error code, arguments: [1158], [], [], [], [], [], [], []
Tue Feb 7 14:56:38 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/eygle_ora_22172.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at line 3
Tue Feb 7 15:01:59 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/eygle_ora_22401.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at line 3

详细的TRACE文件：

[ora10g@hpserver2 bdump]$ more /u01/app/oracle/admin/ORCL/udump/eygle_ora_21708.trc
/u01/app/oracle/admin/ORCL/udump/eygle_ora_21708.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      hpserver2.enmotech.com
Release:        2.6.32-100.28.5.el6.x86_64
Version:        #1 SMP Wed Feb 2 18:40:23 EST 2011
Machine:        x86_64
Instance name: eygle
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 16
Unix process pid: 21708, image: oracle@hpserver2.enmotech.com (TNS V1-V3)
 
*** ACTION NAME:() 2012-02-07 14:48:48.867
*** MODULE NAME:(sqlplus@hpserver2.enmotech.com (TNS V1-V3)) 2012-02-07 14:48:48.867
*** SERVICE NAME:(SYS$USERS) 2012-02-07 14:48:48.867
*** SESSION ID:(148.61) 2012-02-07 14:48:48.867
Error IN executing triggers ON CONNECT internal
*** 2012-02-07 14:48:48.868
ksedmp: internal OR fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation IN system triggers
ORA-06512: at line 3
(enqueue) TX-00080010-00000DB9  DID: 0001-0010-00000011
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
res: 0x1155771b8, mode: X, lock_flag: 0x0
own: 0x11540ce98, sess: 0x11540ce98, proc: 0x1152fc168, prv: 0x1155771c8
===================================================
ENQUEUES
--------
Active:    LOCK: (nil), resource: (nil)
Global:    name: ORCL
Hash:      LENGTH: 375, at: 0x115597448
           [115597448,115597448]  [115597478,115597478]  [1155974a8,1155974a8]  
           [1155974d8,1155974d8]  [115597508,115597508]  [115597538,115597538]  
           [115597568,115597568]  [115597598,115597598]  [1155975c8,1155975c8]  
           [1155975f8,1155975f8]  [115597628,115597628]  [115597658,115597658]  
.
.
.
           [11559b8e8,11559b8e8]  [11559b918,11559b918]  [11559b948,11559b948]  
           [11559b978,11559b978]  [11559b9a8,11559b9a8]  [11559b9d8,11559b9d8]  
           [11559ba08,11559ba08]  [11559ba38,11559ba38]  [11559ba68,11559ba68]  
res      identification         NUL SS  SX  S   SSX X   md link
         owners              converters          waiters
-------------------------------------------------------------------------
0x115576d60 XR-00000004-00000000     1   0   0   0   0   0  2 [11559a838,11559a838]
         [11551fe88,11551fe88] [115576d90,115576d90] [115576d80,115576d80]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x11551fe78 OWN 0x115423a80 0x115423a80 (165) NULL NLCK   1 [115576d70,115576d70]
0x115577130 CF-00000000-00000000     0   1   0   0   0   0  4 [11559b3d8,11559b3d8]
         [11551ff20,11551ff20] [115577160,115577160] [115577150,115577150]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x11551ff10 OWN 0x115423a80 0x115423a80 (165)   SS NLCK   1 [115577140,115577140]
0x1155771b8 TX-00080010-00000db9 U   0   0   0   0   0   1 40 [11559b498,11559b498]
         [112c2b260,112c2b260] [1155771e8,1155771e8] [1155771d8,1155771d8]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x112c2b250 OWN 0x11540ce98 0x11540ce98 (148)    X NLCK  61 [1155771c8,1155771c8]
0x115577460 RT-00000001-00000000     0   0   0   0   0   1 40 [115598408,115598408]
         [115520180,115520180] [115577490,115577490] [115577480,115577480]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520170 OWN 0x115424fe8 0x115424fe8 (166)    X NLCK   1 [115577470,115577470]
0x1155774e8 RS-00000019-00000001     0   1   0   0   0   0  4 [115598888,115598888]
         [115520050,115520050] [115577518,115577518] [115577508,115577508]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520040 OWN 0x115423a80 0x115423a80 (165)   SS NLCK   1 [1155774f8,1155774f8]
0x1155778b8 MR-00000007-00000000     0   0   0   1   0   0 10 [11559ad18,11559ad18]
         [115520770,115520770] [1155778e8,1155778e8] [1155778d8,1155778d8]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520760 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [1155778c8,1155778c8]
0x115577940 TX-0004001f-00000aa8 U   0   0   0   0   0   1 40 [11559ad48,11559ad48]
         [112c6bfb0,112c6bfb0] [115577970,115577970] [115577960,115577960]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x112c6bfa0 OWN 0x11540a3c8 0x11540a3c8 (146)    X NLCK  71 [115577950,115577950]
0x115577ad8 MR-00000001-00000000     0   0   0   1   0   0 10 [11559abf8,11559abf8]
         [1155203e0,1155203e0] [115577b08,115577b08] [115577af8,115577af8]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x1155203d0 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115577ae8,115577ae8]
0x115577b60 MR-00000002-00000000     0   0   0   1   0   0 10 [11559ac28,11559ac28]
         [115520478,115520478] [115577b90,115577b90] [115577b80,115577b80]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520468 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115577b70,115577b70]
0x115577e90 MR-00000003-00000000     0   0   0   1   0   0 10 [11559ac58,11559ac58]
         [115520510,115520510] [115577ec0,115577ec0] [115577eb0,115577eb0]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520500 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115577ea0,115577ea0]
0x115577f18 MR-00000004-00000000     0   0   0   1   0   0 10 [11559ac88,11559ac88]
         [1155205a8,1155205a8] [115577f48,115577f48] [115577f38,115577f38]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520598 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115577f28,115577f28]
0x115577fa0 MR-00000005-00000000     0   0   0   1   0   0 10 [11559acb8,11559acb8]
         [115520640,115520640] [115577fd0,115577fd0] [115577fc0,115577fc0]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520630 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115577fb0,115577fb0]
0x115578028 MR-00000006-00000000     0   0   0   1   0   0 10 [11559ace8,11559ace8]
         [1155206d8,1155206d8] [115578058,115578058] [115578048,115578048]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x1155206c8 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [115578038,115578038]
0x1155780c8 MR-000000c9-00000000     0   0   0   1   0   0 10 [115598b28,115598b28]
         [115520820,115520820] [1155780f8,1155780f8] [1155780e8,1155780e8]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520810 OWN 0x115426550 0x115426550 (167)    S NLCK   1 [1155780d8,1155780d8]
0x115579048 PW-00000001-00000000 U   0   0   1   0   0   0  8 [115597898,115597898]
         [115520218,115520218] [115579078,115579078] [115579068,115579068]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520208 OWN 0x115426550 0x115426550 (167)   SX NLCK   1 [115579058,115579058]
0x115579f58 TA-00000006-00000001     0   0   0   0   0   1 40 [115598d98,115598d98]
         [11551ffb8,11551ffb8] [115579f88,115579f88] [115579f78,115579f78]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x11551ffa8 OWN (NULL)    X NLCK [115579f68,115579f68]
0x11557b2a8 TS-00000003-00000001     0   0   1   0   0   0  8 [115599068,115599068]
         [115520950,115520950] [11557b2d8,11557b2d8] [11557b2c8,11557b2c8]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x115520940 OWN 0x115422518 0x115422518 (164)   SX NLCK   1 [11557b2b8,11557b2b8]
0x11557e1f8 TX-00060003-00000da2 U   0   0   0   0   0   1 40 [1155988e8,1155988e8]
         [112c46650,112c46650] [11557e228,11557e228] [11557e218,11557e218]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x112c46640 OWN 0x1154078f8 0x1154078f8 (144)    X NLCK  14 [11557e208,11557e208]
0x115580920 TM-000022f2-00000000 U   0   0   0   0   0   1 40 [115598468,115598468]
         [112bda4c8,112bda4c8] [115580950,115580950] [115580940,115580940]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x112bda4b8 OWN 0x1154078f8 0x1154078f8 (144)    X NLCK  14 [115580930,115580930]
0x115582150 TX-00060023-00000da1 U   0   0   0   0   0   1 40 [115598eb8,115598eb8]
         [112c46ef0,112c46ef0] [115582180,115582180] [115582170,115582170]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x112c46ee0 OWN 0x1154078f8 0x1154078f8 (144)    X NLCK  14 [115582160,115582160]
0x11558a7a0 TO-0000cb4e-00000001     0   0   1   0   0   0  8 [115598048,115598048]
         [1155200e8,1155200e8] [11558a7d0,11558a7d0] [11558a7c0,11558a7c0]
   LOCK     que owner    SESSION        hold wait ser link
   ----------------------------------------------------------------------
   0x1155200d8 OWN 0x11540a3c8 0x11540a3c8 (146)   SX NLCK  71 [11558a7b0,11558a7b0]
*** 2012-02-07 14:56:28.522
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [1158], [], [], [], [], [], [], []
CURRENT SQL statement FOR this SESSION:
ALTER TRIGGER DBFW_CONSOLE_ACCESS.TRIGGER_DDL disable
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+31          CALL     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFFC3C2CC0 ? 7FFFFC3C2D20 ?
                                                   7FFFFC3C2C60 ? 000000000 ?
ksedmp()+610         CALL     ksedst()             000000000 ? 000000001 ?
                                                   7FFFFC3C2CC0 ? 7FFFFC3C2D20 ?
                                                   7FFFFC3C2C60 ? 000000000 ?
ksfdmp()+21          CALL     ksedmp()             000000003 ? 000000001 ?
                                                   7FFFFC3C2CC0 ? 7FFFFC3C2D20 ?
                                                   7FFFFC3C2C60 ? 000000000 ?
kgeriv()+176         CALL     ksfdmp()             000000003 ? 000000001 ?
                                                   7FFFFC3C2CC0 ? 7FFFFC3C2D20 ?
                                                   7FFFFC3C2C60 ? 000000000 ?
kgesiv()+119         CALL     kgeriv()             0068C8BA0 ? 008686B30 ?
                                                   000000000 ? 000000005 ?
                                                   7FFFFC3C2C60 ? 000000000 ?
ksesic0()+209        CALL     kgesiv()             0068C8BA0 ? 008686B30 ?
                                                   000000486 ? 000000000 ?
                                                   7FFFFC3C3A40 ? 000000000 ?
ksqgtlctx()+5189     CALL     ksesic0()            000000486 ? 000000040 ?
                                                   115597400 ? 000000000 ?
                                                   000000013 ? 000000001 ?
ktubnd()+3130        CALL     ksqgtlctx()          112C2B250 ? 000000006 ?
                                                   000000000 ? 000000000 ?
                                                   00000003B ? 000080010 ?
ktubgn()+222         CALL     ktubnd()             7FFFFC3C40D0 ? 112C2B2B0 ?
                                                   000000000 ? 000000000 ?
                                                   00000003B ? 000080010 ?
krvgfci_GatherFixed  CALL     ktubgn()             000000000 ? 000000000 ?
CommonInfo()+98                                    000000000 ? 000000000 ?
                                                   00000003B ? 000080010 ?
krvgrgd()+323        CALL     krvgfci_GatherFixed  7FFFFC3C4538 ? 000000000 ?
                              CommonInfo()         000000000 ? 000000000 ?
                                                   00000003B ? 000080010 ?
knldcddl()+603       CALL     krvgrgd()            7FFFFC3C4538 ? 000000000 ?
                                                   7FAF93439778 ? 000000000 ?
                                                   00000003B ? 000080010 ?
opiexe()+11908       CALL     knldcddl()           00000003C ? 10A659E98 ?
                                                   000000000 ? 000000000 ?
                                                   00000003B ? 000080010 ?
opiosq0()+3316       CALL     opiexe()             000000004 ? 000000000 ?
                                                   7FFFFC3C57F8 ? 000000001 ?
                                                   00000003B ? 000080010 ?
kpooprx()+315        CALL     opiosq0()            000000003 ? 00000000E ?
                                                   7FFFFC3C5968 ? 0000000A4 ?
                                                   00000003B ? 000080010 ?
kpoal8()+799         CALL     kpooprx()            7FFFFC3C8B14 ? 7FFFFC3C6AD8 ?
                                                   000000035 ? 000000001 ?
                                                   000000000 ? 000080010 ?
opiodr()+984         CALL     kpoal8()             00000005E ? 000000017 ?
                                                   7FFFFC3C8B10 ? 000000001 ?
                                                   000000001 ? 000080010 ?
ttcpip()+1226        CALL     opiodr()             00000005E ? 000000017 ?
                                                   7FFFFC3C8B10 ? 000000000 ?
                                                   0059F0470 ? 000080010 ?
opitsk()+1322        CALL     ttcpip()             0068D0870 ? 0052E52C0 ?
                                                   7FFFFC3C8B10 ? 000000000 ?
                                                   7FFFFC3C8608 ? 7FFFFC3C8C78 ?
opiino()+1026        CALL     opitsk()             000000003 ? 000000000 ?
                                                   7FFFFC3C8B10 ? 000000001 ?
                                                   000000000 ? 866002D00000001 ?
opiodr()+984         CALL     opiino()             00000003C ? 000000004 ?
                                                   7FFFFC3C9CD8 ? 000000001 ?
                                                   000000000 ? 866002D00000001 ?
opidrv()+547         CALL     opiodr()             00000003C ? 000000004 ?
                                                   7FFFFC3C9CD8 ? 000000000 ?
                                                   0059EFF20 ? 866002D00000001 ?
sou2o()+114          CALL     opidrv()             00000003C ? 000000004 ?
                                                   7FFFFC3C9CD8 ? 000000000 ?
                                                   0059EFF20 ? 866002D00000001 ?
opimai_real()+163    CALL     sou2o()              7FFFFC3C9CB0 ? 00000003C ?
                                                   000000004 ? 7FFFFC3C9CD8 ?
                                                   0059EFF20 ? 866002D00000001 ?
main()+116           CALL     opimai_real()        000000002 ? 7FFFFC3C9D40 ?
                                                   000000004 ? 7FFFFC3C9CD8 ?
                                                   0059EFF20 ? 866002D00000001 ?
__libc_start_main()  CALL     main()               000000002 ? 7FFFFC3C9D40 ?
+253                                               000000004 ? 7FFFFC3C9CD8 ?
                                                   0059EFF20 ? 866002D00000001 ?
_start()+41          CALL     __libc_start_main()  000723734 ? 000000002 ?
                                                   7FFFFC3C9E98 ? 000000000 ?
                                                   0059EFF20 ? 866002D00000001 ?
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (ksedst()+31 -> ksedst1()) ==========

这个1158的ORA-600错误和其他有所不同，在文件开始并非是SQL信息，而是锁的信息，而且从屏幕显式可以看到，对于这个禁止触发器的DDL执行了CTRL+C中止命令的操作。虽然观察数据库的日志发现，数据库由于归档路径写满，导致系统进入HANG的状态，因此当前执行的DDL所依赖的对象被锁，而当取消当前的操作时，由于系统状态的不正常引发了这个错误。
查询了一下MOS，发现问题与Bug 2828026 – Interrupted sessions may fail with ORA-600[1158] / DB can hang [ID 2828026.8]描述的非常类似，但是这个bug已经在9.2.0.4和10.1.0.2中已经解决，理论上在10.2.0.4中重新的可能性不大。因此，问题的产生肯定还是与数据库状态异常有关。

Posted in BUG | Tagged , , | Leave a comment

ORA-600(ktspNextL1:4)错误

Posted on 2012/02/05 by yangtingkun

告警日志出现ORA-600[ktspNextL1:4]错误。
错误信息如下：

Tue Jan 15 22:00:45 2012
Errors IN file /oracle/admin/orcl/bdump/orcl_j000_13722891.trc:
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Tue Jan 15 22:01:10 2012
Trace dumping IS performing id=[cdmp_20120131220118]
Tue Jan 15 22:01:10 2012
Errors IN file /oracle/admin/orcl/bdump/ orcl_j000_13722891.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1500
ORA-06512: at "SYS.DBMS_SPACE", line 1566
], [], [], [], [], [], [], []

详细TRACE文件如下：

*** 2012-01-15 22:00:45 2012
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
CURRENT SQL statement FOR this SESSION:
INSERT INTO wri$_adv_objspace_trend_data SELECT timepoint,  space_usage, space_alloc, quality FROM  TABLE(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,  NULL, 'FALSE', :5, 'FALSE'))
----- PL/SQL Call Stack -----
  object      line  object
  handle    NUMBER  name
70000010d9a65e0      1834  package body SYS.DBMS_SPACE
70000010d9a65e0      3922  package body SYS.DBMS_SPACE
70000010d9a65e0      4233  package body SYS.DBMS_SPACE
70000011a7d9c88         1  anonymous block
700000180678048       344  SYS.WRI$_ADV_OBJSPACE_TREND_T
700000180678048      1485  SYS.WRI$_ADV_OBJSPACE_TREND_T
7000001334867d0      1535  package body SYS.PRVT_ADVISOR
7000001334867d0      1618  package body SYS.PRVT_ADVISOR
70000011f9f64a8       186  package body SYS.DBMS_ADVISOR
70000010d9a65e0      1500  package body SYS.DBMS_SPACE
70000010d9a65e0      1566  package body SYS.DBMS_SPACE
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              70000017E9975D0 ? 100000001 ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C             
kgerinv+00dc         bl       _ptrgl               
kgeasnmierr+004c     bl       kgerinv              000000000 ? 000000001 ?
                                                   000000005 ? 7000000E19760FC ?
                                                   7000000E1976014 ?
ktspGetNextL1ForSca  bl       01F94828             n+0104

显然这又是一个ORACLE内部的bug，仍然是定时任务导致的，Oracle在进行空间趋势分析的时候出现了这个错误。
Oracle在文档ORA-600 [ktspNextL1:4] from SYS.DBMS_SPACE / SYS.DBMS_ADVISOR/WRI$_ADV_OBJSPACE_TREND_DATA [ID 841158.1]描述了这个问题，给出的解决方案简单粗暴。方案一、忽略错误；方案二、升级到11.1.0.7；方案三、重新执行JOB。这个问题影响不大，而且并非每次都能重新，因此解决方案还算靠谱。

Posted in BUG | Tagged , , , | Leave a comment

收集统计信息出现ORA-1476错误

Posted on 2012/02/04 by yangtingkun

告警日志中出现JOB运行错误，而发生错误的任务是在收集统计信息。
错误信息如下：

Fri Feb  2 22:00:05 2012
GATHER_STATS_JOB encountered errors. CHECK the trace file.
Fri Feb  2 22:00:05 2012
Errors IN file /oracle/admin/orcl/bdump/orcl_j000_23148.trc:
ORA-01476: divisor IS equal TO zero

显然这是数据库在晚上10点启动JOB来自动收集统计信息时出现了这个错误，那么问题多半是bug导致的，因为错误和用户的操作无关。
查询MOS发现在文档Bug 6319761 – ORA-1476 from DBMS_STATS.GATHER_SCHEMA_STATS [ID 6319761.8]中描述了这个错误。这个bug影响的版本包括10.2.0.3和10.2.0.4，Oracle在10.2.0.5和11.1.0.7中解决了这个问题，除了升级之外，通过alter session set events ‘38041 trace name context forever, level 24’也有可能解决这个错误。
而且这个错误的产生并非每次都能重现，如果错误并非每次重现，也可以简单忽略之。

Posted in BUG | Tagged , , | Leave a comment