ORA-7445(xsStkPurge)错误

Posted on 2012/08/11 by yangtingkun

客户数据库出现ORA-7445(xsStkPurge)错误。
数据库告警日志出现如下的错误信息：

Fri Aug 3 17:10:03 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_23101.trc:
ORA-07445: exception encountered: core dump [xsStkPurge()+73] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
ORA-04030: OUT OF process memory WHEN trying TO allocate 8184 bytes (OLAP stack hea,OLAP Stack Seg)
Fri Aug 3 17:10:19 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_23101.trc:
ORA-04030: OUT OF process memory WHEN trying TO allocate 8156 bytes (callheap,kdbmal allocation)
ORA-07445: exception encountered: core dump [xsStkPurge()+73] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
ORA-04030: OUT OF process memory WHEN trying TO allocate 8184 bytes (OLAP stack hea,OLAP Stack Seg)

对应的TRACE文件详细信息为：

*** 2012-08-03 17:10:03.098
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [xsStkPurge()+73] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
ORA-04030: OUT OF process memory WHEN trying TO allocate 8184 bytes (OLAP stack hea,OLAP Stack Seg)
CURRENT SQL statement FOR this SESSION:
BEGIN :1 := dbms_aw.interp(:2); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    NUMBER  name
0x9b6ad21c        93  package body SYS.DBMS_AW
0x9b6ad21c       180  package body SYS.DBMS_AW
0x9b58b904         1  anonymous block
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+27          CALL     ksedst1()            1 ? 1 ?
ksedmp()+557         CALL     ksedst()             1 ? 8168FB8 ? B748BF3C ?
                                                   A9A650 ? 10 ? B7491D38 ?
ssexhd()+882         CALL     ksedmp()             3 ? B3F831F ? 74537378 ?
                                                   7275506B ? 29286567 ?
                                                   33372B ?
xsStkPurge()+73      signal   00000000             B ? B748DC90 ? B748DD10 ?
xsStkPop()+48        CALL     xsStkPurge()         BDAFBA7C ? B63CC640 ?
                                                   B63CC644 ? 1 ?
xsPMTSETUP()+2213    CALL     xsStkPop()           BDAFBA7C ? 12EB2 ? 0 ?
                                                   B61D1528 ? B471A440 ? 0 ?
xsPMTRST()+223       CALL     xsPMTSETUP()         B63B70DC ? B60FE628 ?
                                                   A933EDF0 ? 0 ?
xsINTERP()+1135      CALL     00000000             B63B70DC ? B63CA66C ?
                                                   BFFF9018 ?
xsILPXEQ()+4347      CALL     xsINTERP()           B63B70DC ? BFFF9018 ?
                                                   BFFF8A84 ? 1C6 ? 0 ?
                                                   B621ED28 ?
xsILPCALL()+2621     CALL     xsILPXEQ()           CCF04F0 ? B2CFAD5 ?
                                                   B63B70DC ? B60ED380 ?
                                                   B63DA8C4 ?
.
.
.
opiodr()+985         CALL     00000000             3C ? 4 ? BFFFF800 ?
opidrv()+466         CALL     opiodr()             3C ? 4 ? BFFFF800 ? 0 ?
sou2o()+91           CALL     opidrv()             3C ? 4 ? BFFFF800 ?
opimai_real()+117    CALL     sou2o()              BFFFF7E4 ? 3C ? 4 ?
                                                   BFFFF800 ?
main()+111           CALL     opimai_real()        2 ? BFFFF830 ?
__libc_start_main()  CALL     00000000             2 ? BFFFF8F4 ? BFFFF900 ?
+211                                               A8F1D6 ? BC7FF4 ? 0 ?
--------------------- Binary Stack Dump ---------------------

根据MOS的查询，只有文章Bug 6755052 : ORA-7445 [XSSTKPURGE()+73] [SIGSEGV] IN EPB WHEN DISTRIBUTING DOCUMENTS与之最为接近。应该的版本同为10.2.0.3，可以确认的是导致ORA-7445错误的主要原因还是ORA-4030错误。而且文档和当前问题的一个重要相同点在于，二者都是32位系统。
由于32位数据库的限制，SGA的大小只有1.7G，而SHARED_POOL只有300M左右，STREAMS_POOL更是只有16M，这就导致了Oracle在处理一些复杂的组件时，很容易出现内存不足的情况。
从问题时刻的AWR分析，TOP 5中出现了明显的Streams AQ: enqueue blocked on low memory等待，这个等待只出现了1次但是经历了293秒，说明STREAMS_POOL空间不足，且数据库在尝试给STREAMS_POOL分配更多空间时报错。
客户主机拥有16G内存，那么这个问题的最有限的方法就是重建64位数据库环境，彻底避免该问题的产生。

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

ODA一键式清除

Posted on 2012/08/10 by yangtingkun

测试了一下ODA的一键式清除，果然是“破坏”比建设更容易，整个操作比本来已经非常简单的ODA安装还要简化得多。
很多DBA都有RAC的安装经验，但是真正进行过RAC环境清除的恐怕并不是很多。虽然Oracle提供了脚本来删除节点或清除RAC环境，但是真正做起来还是有些烦琐的，而且如果不小心，很容易造成部分信息没有彻底清除，从而给RAC的再次安装留下隐患。
而ODA提供的一键式清除功能极大的简化了RAC环境清除的过程，全程只需要执行一个命令，在任意一个节点上执行：

# cd /opt/oracle/oak/onecmd
# ./cleanupDeploy.pl
Please enter the root password FOR performing cleanup:
Re-enter root password:
About TO clear up OAK deployment,public network connectivity will be lost,root password will be SET TO DEFAULT AND BOTH nodes will be rebooted
Do you want TO continue(yes/no): yes
Setting up ssh FOR root
INFO	: Logging ALL actions IN /opt/oracle/oak/onecmd/tmp/odademo1-20120810042843.log AND traces IN /opt/oracle/oak/onecmd/tmp/odademo1-20120810042843.trc
INFO	: Loading configuration file /opt/oracle/oak/onecmd/onecommand.params...
INFO	: Creating nodelist files:
===============================================================================
INFO	: 2012-08-10 04:28:43
INFO	: Step 1  SetupSSHroot
INFO	: Setting up ssh FOR root...
INFO	: Setting up ssh across the private network...
......

这个命令执行之后，不但RAC环境被清除，ASM磁盘头信息被清除，连两个节点的IP地址都已经被清除了。
因此，如果要再次安装，需要登录ILON，进入图形化界面，首先配置两个节点的网络信息，然后才能进行再次安装。

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

ODA一键式安装

Posted on 2012/08/09 by yangtingkun

今天测试了一下ODA的一键式安装，果然是方便快捷。
一般而言，即使一个有一定基础的熟手，RAC的搭建过程也要一天的时间，这还是安装过程没有碰到太多问题的情况下，而ODA将整个RAC搭建过程简化到了极致，只要一个对于RAC环境IP地址分配有一定了解的DBA，就可以在2个小时之内把ODA中整个RAC环境包括ASM和数据库完全建立起来。
使用ROOT登录节点，确认图形化工具配置正确，进入/opt/oracle/oak/bin目录执行安装命令：

[root@oak1 ~]# cd /opt/oracle/oak/bin
[root@oak1 bin]# ./oakcli deploy

执行这个简单的命令后，会弹出一个窗口，用来输入设置RAC的IP等信息。整个安装过程如果选择复杂的客户定制方式，也只有12个页面，其中第一个是Welcome，最后一个是Complete，也就是说是个步骤就可以完成所有的输入。
第一个有意义的页面是Configuration Type，这里如果输入Typical的话，设置过程会更加简单，但是可定制化太差，建议还是选择Custom方式。这里还可以载入或保存整个安装配置文件，默认的安装配置文件名称是onecommand.params；
随后是System Information，这里输入系统名称、时区、配置数据库的类型以及ROOT用户的密码。其中数据库的类型包括RAC、RAC One Node和单实例企业版；
随后是比较关键的网络配置部分，首先是Generic Network部分，以前ODA必须要求配置DNS服务器，在最新版中ODA增加了一个选项，可以不使用DNS，如果环境中没有DNS服务器，那么这里记得选择No ODA Server available；
接着配置Public Network：主要是输入两个节点的PUBLIC IP、VIP和SCAN IP的名称以及地址，此外还需要输入NETMASK、GATEWAY和INTERFACE。这里还配置ILOM的地址信息，ILOM是ODA设置在两台服务器之外的系统，用来在节点环境清除后，重新安装服务器的操作系统重新设置IP地址等。
在Other Network部分可以直接跳过。
在Database Information部分输入数据库名称、数据库类型、语言、字符集、BLOCKSIZE等设置，其中数据库类型只是选择数据量是Small、Normal、Large和Very Large等几个选项。
ASR Information页，如果数据库可以连接外网，或通过代理服务连接外网，可以在这里设置自动Service Request，ODA可以在数据库出现严重错误时，自动将TRACE等信息打包发送到MOS上并建立SR。
CloudFS Information：如果需要配置CLUSTER文件系统，可以在这个页面进行设置，只需要输入MOUNT点和大小既可，显然这个功能是通过ASM的ACFS功能实现的。
随后是汇总和安装进度界面，在我们的测试中，选择了Very Large数据库类型，在完成了所有页面的输入并点击开始后，大约过了55分钟左右，整个RAC环境包括ASM和数据库都全部建立完成。

Posted in ORACLE | Tagged , , | Leave a comment

包含OLAP组件SCHEMA使用imp导入碰到严重性能问题

Posted on 2012/08/08 by yangtingkun

客户一个十几个G的用户尝试使用IMP导入到数据库中，执行时间超过了2天。
数据库版本为10.2.0.5 FOR LINUX X86-64，而导入的dmp文件是在10.2.0.3 FOR LINUX X86平台上导出的，这个用户包含了OLAP组件。
整个导入过程异常缓慢，一共不到20G的数据量，导入的时间超过了两天。虽然imp导入效率不高，但是也没有理由慢到如此地步，通过10046跟踪imp进程：

*** 2012-08-07 17:25:03.099
*** ACTION NAME:() 2012-08-07 17:25:03.051
*** MODULE NAME:(imp@db (TNS V1-V3)) 2012-08-07 17:25:03.051
*** SERVICE NAME:(SYS$USERS) 2012-08-07 17:25:03.051
*** SESSION ID:(880.53796) 2012-08-07 17:25:03.051
=====================
PARSING IN CURSOR #61 len=6 dep=1 uid=100 oct=44 lid=100 tim=5574818059202 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #61:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=5574818059197
XCTEND rlbk=0, rd_only=1
EXEC #61:c=0,e=4723,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=5574818115096
=====================
PARSING IN CURSOR #3 len=29323 dep=0 uid=100 oct=47 lid=100 tim=5574818115145 hv=1172437913 ad='fe303758'
BEGIN 
dbms_aw_exp.import_chunk100(1492, 'AADavwAAKAAAAAYAAADavwAAKQAAAAYAAADavwAAKgAAAAYAAADavwAAKwAAAAYA
AADavwAALAAAAAYAAADavwAALQAAAAYAAADavwAALgAAAAYAAADavwAALwAAAAYA
AADavwAAJAAAAAYAAABy0wAAJQAAAAYAAABy0wAAJgAAAAYAAABy0wAAJwAAAAYA
AABy0wAAKAAAAAYAAABy0wAAKQAAAAYAAABy0wAAKgAAAAYAAABy0wAAKwAAAAYA
AABy0wAALAAAAAYAAABy0wAALQAAAAYAAABy0wAALgAAAAYAAABy0wAALwAAAAYA
AABy0wAAJAAAAAYAAAC90wAAJQAAAAYAAAC90wAAJgAAAAYAAAC90wAAJwAAAAYA
AAC90wAAKAAAAAYAAAC90wAAKQAAAAYAAAC90wAAKgAAAAYAAAC90wAAKwAAAAYA
AAC90wAALAAAAAYAAAC90wAALQAAAAYAAAC90wAALgAAAAYAAAC90wAALwAAAAYA
AAC90wAAJAAAAAYAAACJrQAAJQAAAAYAAACJrQAAJgAAAAYAAACJrQAAJwAAAAYA
AACJrQAAKAAAAAYAAACJrQAAKQAAAAYAAACJrQAAKgAAAAYAAACJrQAAKwAAAAYA
AACJrQAALAAAAAYAAACJrQAALQAAAAYAAACJrQAALgAAAAYAAACJrQAALwAAAAYA
AACJrQAAJAAAAAYAAAAKvAAAJQAAAAYAAAAKvAAAJgAAAAYAAAAKvAAAJwAAAAYA
AAAKvAAAKAAAAAYAAAAKvAAAKQAAAAYAAAAKvAAAKgAAAAYAAAAKvAAAKwAAAAYA
AAAKvAAALAAAAAYAAAAKvAAALQAAAAYAAAAKvAAALgAAAAYAAAAKvAAALwAAAAYA
AAAKvAAAJAAAAAYAAABXvwAAJQAAAAYAAABXvwAAJgAAAAYAAABXvwAAJwAAAAYA
AABXvwAAKAAAAAYAAABXvwAAKQAAAAYAAABXvwAAKgAAAAYAAABXvwAAKwAAAAYA
AABXvwAALAAAAAYAAABXvwAALQAAAAYAAABXvwAALgAAAAYAAABXvwAALwAAAAYA
AABXvwAAJAAAAAYAAABJswAAJQAAAAYAAABJswAAJgAAAAYAAABJswAAJwAAAAYA
AABJswAAKAAAAAYAAABJswAAKQAAAAYAAABJswAAKgAAAAYAAABJswAAKwAAAAYA
AABJswAALAAAAAYAAABJswAALQAAAAYAAABJswAALgAAAAYAAABJswAALwAAAAYA
AABJswAAJAAAAAYAAAAJuAAAJQAAAAYAAAAJuAAAJgAAAAYAAAAJuAAAJwAAAAYA
AAAJuAAAKAAAAAYAAAAJuAAAKQAAAAYAAAAJuAAAKgAAAAYAAAAJuAAAKwAAAAYA
AAAJuAAALAAAAAYAAAAJuAAALQAAAAYAAAAJuAAALgAAAAYAAAAJuAAALwAAAAYA
AAAJuAAAJAAAAAYAAAAOxwAAJQAAAAYAAAAOxwAAJgAAAAYAAAAOxwAAJwAAAAYA
AAAOxwAAKAAAAAYAAAAOxwAAKQAAAAYAAAAOxwAAKgAAAAYAAAAOxwAAKwAAAAYA
AAAOxwAALAAAAAYAAAAOxwAALQAAAAYAAAAOxwAALgAAAAYAAAAOxwAALwAAAAYA
AAAOxwAAJAAAAAYAAAAMpAAAJQAAAAYAAAAMpAAAJgAAAAYAAAAMpAAAJwAAAAYA
AAAMpAAAKAAAAAYAAAAMpAAAKQAAAAYAAAAMpAAAKgAAAAYAAAAMpAAAKwAAAAYA
AAAMpAAALAAAAAYAAAAMpAAALQAAAAYAAAAMpAAALgAAAAYAAAAMpAAALwAAAAYA
AAAMpAAAJAAAAAYAAABXwwAAJQAAAAYAAABXwwAAJgAAAAYAAABXwwAAJwAAAAYA
AABXwwAAKAAAAAYAAABXwwAAKQAAAAYAAABXwwAAKgAAAAYAAABXwwAAKwAAAAYA
AABXww==');
dbms_aw_exp.import_chunk100(1492, 'AAAsAAAABgAAAFfDAAAtAAAABgAAAFfDAAAuAAAABgAAAFfDAAAvAAAABgAAAFfD
AAAkAAAABgAAAC66AAAlAAAABgAAAC66AAAmAAAABgAAAC66AAAnAAAABgAAAC66
AAAoAAAABgAAAC66AAApAAAABgAAAC66AAAqAAAABgAAAC66AAArAAAABgAAAC66
AAAsAAAABgAAAC66AAAtAAAABgAAAC66AAAuAAAABgAAAC66AAAvAAAABgAAAC66
AAAkAAAABgAAALLAAAAlAAAABgAAALLAAAAmAAAABgAAALLAAAAnAAAABgAAALLA
AAAoAAAABgAAALLAAAApAAAABgAAALLAAAAqAAAABgAAALLAAAArAAAABgAAALLA
AAAsAAAABgAAALLAAAAtAAAABgAAALLAAAAuAAAABgAAALLAAAAvAAAABgAAALLA
AAAkAAAABgAAAAC8AAAlAAAABgAAAAC8AAAmAAAABgAAAAC8AAAnAAAABgAAAAC8
AAAoAAAABgAAAAC8AAApAAAABgAAAAC8AAAqAAAABgAAAAC8AAArAAAABgAAAAC8
AAAsAAAABgAAAAC8AAAtAAAABgAAAAC8AAAuAAAABgAAAAC8AAAvAAAABgAAAAC8
AAAkAAAABgAAABOyAAAlAAAABgAAABOyAAAmAAAABgAAABOyAAAnAAAABgAAABOy
AAAoAAAABgAAABOyAAApAAAABgAAABOyAAAqAAAABgAAABOyAAArAAAABgAAABOy
AAAsAAAABgAAABOyAAAtAAAABgAAABOyAAAuAAAABgAAABOyAAAvAAAABgAAABOy
AAAkAAAABgAAAA2zAAAlAAAABgAAAA2zAAAmAAAABgAAAA2zAAAnAAAABgAAAA2z
AAAoAAAABgAAAA2zAAApAAAABgAAAA2zAAAqAAAABgAAAA2zAAArAAAABgAAAA2z
AAAsAAAABgAAAA2zAAAtAAAABgAAAA2zAAAuAAAABgAAAA2zAAAvAAAABgAAAA2z
AAAkAAAABgAAAPy0AAAlAAAABgAAAPy0AAAmAAAABgAAAPy0AAAnAAAABgAAAPy0
AAAoAAAABgAAAPy0AAApAAAABgAAAPy0AAAqAAAABgAAAPy0AAArAAAABgAAAPy0
AAAsAAAABgAAAPy0AAAtAAAABgAAAPy0AAAuAAAABgAAAPy0AAAvAAAABgAAAPy0
AAAkAAAABgAAAFEdAAAlAAAABgAAAFEdAAAmAAAABgAAAFEdAAAnAAAABgAAAFEd
AAAoAAAABgAAAFEdAAApAAAABgAAAFEdAAAqAAAABgAAAFEdAAArAAAABgAAAFEd
AAAsAAAABgAAAFEdAAAtAAAABgAAAFEdAAAuAAAABgAAAFEdAAAvAAAABgAAAFEd
AAAkAAAAIwAAAPUKAAAlAAAAIwAAAPUKAAAnAAAAIwAAAPUKAAAoAAAAIwAAAPUK
AAApAAAAIwAAAPUKAAAtAAAAIwAAAPUKAAAoAAAAIwAAAOQMAAAtAAAAIwAAAOQM
AAAoAAAAIwAAADgPAAApAAAAIwAAADgPAAAtAAAAIwAAADgPAAAmAAAAIwAAAEQQ
AAAtAAAAIwAAAEQQAAAkAAAAIwAAAKMYAAAlAAAAIwAAAKMYAAAoAAAAIwAAAKMY
AAAtAAAAIwAAAKMYAAAkAAAAIwAAAFUhAAAlAAAAIwAAAFUhAAAnAAAAIwAAAFUh
AAAoAAAAIwAAAFUhAAApAAAAIwAAAFUhAAAtAAAAIwAAAFUhAAAkAAAAIwAAAB8k
AAAlAAAAIwAAAB8kAAAoAAAAIwAAAB8kAAAtAAAAIwAAAB8kAAAkAAAAIwAAALk3
AAAlAAAAIwAAALk3AAAtAAAAIwAAALk3AAAkAAAAIwAAANE3AAAlAAAAIwAAANE3
AAAnAAAAIwAAANE3AAAoAAAAIwAAANE3AAAtAAAAIwAAANE3AAAkAAAAIwAAAOQ6
AAAlAA==');
dbms_aw_exp.import_chunk100(1492, 'AAAjAAAA5DoAACcAAAAjAAAA5DoAACkAAAAjAAAA5DoAAC0AAAAjAAAA5DoAACQA
AAAjAAAAGUAAACUAAAAjAAAAGUAAACgAAAAjAAAAGUAAACkAAAAjAAAAGUAAAC0A
AAAjAAAAGUAAAC0AAAAjAAAAIksAACQAAAAjAAAAO0sAACUAAAAjAAAAO0sAACcA
AAAjAAAAO0sAAC0AAAAjAAAAO0sAACcAAAAjAAAAvkwAAC0AAAAjAAAAvkwAACkA
AAAjAAAAfawAAC0AAAAjAAAAfawAAC0AAAAjAAAAGT4AAC4AAAAjAAAAGT4AAC0A
AAAjAAAAfRMAACcAAAAjAAAAFAwAACgAAAAjAAAAFAwAACkAAAAjAAAAFAwAAC0A
AAAjAAAAFAwAAC0AAAAjAAAAVwwAACQAAAAjAAAAWwwAACUAAAAjAAAAWwwAACcA
AAAjAAAAWwwAAC0AAAAjAAAAWwwAACQAAAAjAAAAgw0AACUAAAAjAAAAgw0AACgA
AAAjAAAAgw0AAC0AAAAjAAAAgw0AAC0AAAAjAAAApQ4AACQAAAAjAAAAEhAAACUA
AAAjAAAAEhAAACcAAAAjAAAAEhAAAC0AAAAjAAAAEhAAAC0AAAAjAAAAsRMAACQA
AAAjAAAA5xYAACUAAAAjAAAA5xYAAC0AAAAjAAAA5xYAAC0AAAAjAAAAJBcAACQA
AAAjAAAAbhcAACUAAAAjAAAAbhcAACcAAAAjAAAAbhcAACgAAAAjAAAAbhcAACQA
AAAjAAAApBgAACUAAAAjAAAApBgAACcAAAAjAAAApBgAACgAAAAjAAAApBgAACkA
AAAjAAAApBgAAC0AAAAjAAAApBgAACQAAAAjAAAAAR4AACUAAAAjAAAAAR4AAC0A
AAAjAAAAAR4AAC0AAAAjAAAAliAAACQAAAAjAAAA+yAAACUAAAAjAAAA+yAAACgA
AAAjAAAA+yAAACkAAAAjAAAA+yAAAC0AAAAjAAAA+yAAACQAAAAjAAAAZCEAACUA
AAAjAAAAZCEAACYAAAAjAAAAZCEAACgAAAAjAAAAZCEAAC0AAAAjAAAAZCEAACgA
AAAjAAAACSIAAC0AAAAjAAAACSIAAC0AAAAjAAAAQCIAACQAAAAjAAAALiQAACUA
AAAjAAAALiQAACcAAAAjAAAALiQAAC0AAAAjAAAALiQAACQAAAAjAAAALScAACUA
AAAjAAAALScAACwAAAAjAAAALScAACQAAAAjAAAAYi4AACUAAAAjAAAAYi4AAC0A
AAAjAAAAYi4AACQAAAAjAAAAOjEAACUAAAAjAAAAOjEAAC0AAAAjAAAAOjEAAC0A
AAAjAAAAkzIAACQAAAAjAAAAXDQAACUAAAAjAAAAXDQAAC0AAAAjAAAAXDQAAC0A
AAAjAAAA6DYAACQAAAAjAAAA/D0AACUAAAAjAAAA/D0AAC0AAAAjAAAA/D0AAC0A
AAAjAAAACkEAACgAAAAjAAAAX0EAAC0AAAAjAAAAX0EAAC0AAAAjAAAAckEAACQA
AAAjAAAAOUQAACUAAAAjAAAAOUQAACcAAAAjAAAAOUQAAC0AAAAjAAAAOUQAACgA
AAAjAAAARUgAAC0AAAAjAAAARUgAACQAAAAjAAAAX0gAACUAAAAjAAAAX0gAACgA
AAAjAAAAX0gAAC0AAAAjAAAAX0gAACQAAAAjAAAAKUoAACUAAAAjAAAAKUoAACcA
AAAjAAAAKUoAACgAAAAjAAAAKUoAAC0AAAAjAAAAKUoAACQAAAAjAAAABUwAACUA
AAAjAAAABUwAACYAAAAjAAAABUwAACcAAAAjAAAABUwAACgAAAAjAAAABUwAAC0A
AAAjAAAABUwAACkAAAAjAAAAn2kAAC4AAAAjAAAAn2kAAC0AAAAjAAAAYHAAACQA
AAAjAAAA43AAACUAAAAjAAAA43AAACgAAAAjAAAA43AAACkAAAAjAAAA43AAAC0A
AAAjAA==');
dbms_aw_exp.import_chunk100(1492, 'AADjcAAAJAAAACMAAABNcwAAJQAAACMAAABNcwAAJwAAACMAAABNcwAAKAAAACMA
AABNcwAAKQAAACMAAABNcwAALQAAACMAAABNcwAALQAAACMAAAB9dAAAJAAAACMA
AABBpwAAJQAAACMAAABBpwAALQAAACMAAABBpwAAJAAAACMAAADiwwAAJQAAACMA
AADiwwAAJwAAACMAAADiwwAAKAAAACMAAADiwwAALQAAACMAAACIEwAALQAAACMA
AADBcAAAJAAAACMAAAC7EgAAJQAAACMAAAC7EgAAKQAAACMAAAC7EgAAKQAAACMA
AAD6EgAALQAAACMAAAD6EgAAJAAAACMAAACNGgAAJQAAACMAAACNGgAAJwAAACMA
AACNGgAALQAAACMAAACNGgAALQAAACMAAABLKQAALQAAACMAAADvMAAALQAAACMA
AAAeQAAALQAAACMAAAD0QAAAJAAAACMAAAD1RgAAJQAAACMAAAD1RgAAJwAAACMA
AAD1RgAAKQAAACMAAAD1RgAALQAAACMAAAD1RgAAJAAAACMAAAB7RwAAJQAAACMA
AAB7RwAAKAAAACMAAAD2TQAALQAAACMAAAD2TQAAJAAAACMAAADnTgAAJQAAACMA
AADnTgAAJwAAACMAAADnTgAAKAAAACMAAADnTgAALQAAACMAAADnTgAALQAAACMA
AABTcAAAJwAAACMAAAD3qwAAKQAAACMAAAD3qwAAJwAAACMAAAA3FAAALQAAACMA
AACkSgAAJwAAACMAAAC8owAAKAAAACMAAAApKgAAKQAAACMAAAApKgAALQAAACMA
AAA5cQAAJAAAACMAAABMowAAJQAAACMAAABMowAAJAAAACMAAABeowAAJQAAACMA
AABeowAALQAAACMAAABeowAALQAAACMAAAAaEAAAJAAAACMAAADORAAAJQAAACMA
AADORAAALQAAACMAAADORAAAJAAAACMAAACQaQAAJQAAACMAAACQaQAALQAAACMA
AACQaQAALQAAACMAAACNaQAAJAAAACMAAABtowAAJQAAACMAAABtowAALQAAACMA
AABtTwAALQAAACMAAACYnAAALQAAACMAAADrogAAJAAAACMAAABPowAAJQAAACMA
AABPowAALQAAACMAAABhowAAJAAAACMAAABSpAAAJQAAACMAAABSpAAALQAAACMA
AABSpAAALQAAACMAAACPKgAAJAAAACMAAABRCgAAJQAAACMAAABRCgAAJwAAACMA
AABRCgAAKAAAACMAAABRCgAAKQAAACMAAABRCgAALQAAACMAAABRCgAALQAAACMA
AADuFgAAJAAAACMAAAB4MQAAJQAAACMAAAB4MQAAJwAAACMAAAB4MQAAKQAAACMA
AAB4MQAALQAAACMAAAB4MQAALgAAACMAAABiTgAAJAAAACMAAAALTwAAJQAAACMA
AAALTwAALQAAACMAAAALTwAAKAAAACMAAABwRgAALQAAACMAAAB8SQAALQAAACMA
AADGGAAAJwAAACMAAABmQwAAKAAAACMAAABmQwAALQAAACMAAABmQwAAJAAAACMA
AABvUAAAJQAAACMAAABvUAAAKAAAACMAAABvUAAAKQAAACMAAABvUAAALQAAACMA
AABvUAAALQAAACMAAABlSQAAJAAAACMAAADWDQAAJQAAACMAAADWDQAAJwAAACMA
AADWDQAAKAAAACMAAADWDQAALQAAACMAAADWDQAAJAAAACMAAABaEQAAJQAAACMA
AABaEQAAJwAAACMAAABaEQAAKAAAACMAAABaEQAAKQAAACMAAABaEQAALQAAACMA
AABaEQAAJAAAACMAAACVFQAAJQAAACMAAACVFQAALQAAACMAAACVFQAAJAAAACMA
AAAzGAAAJQAAACMAAAAzGAAALQAAACMAAAAzGAAAJAAAACMAAADOHQAAJQAAACMA
AADOHQ==');
.
.
.

整个10046的TRACE文件都充斥着dbms_aw_exp.import_chunk100过程的调用。这个过程的目的是为了exp/imp过程可以跨平台，跨32/64位环境而对于OLAP数据进行的特殊处理。这个过程的效率非常低，也就造成了IMP导入的效率低下。
其实Oracle对于包含OLAP数据的用户迁移提供了专门的工具OLAP DML IMPORT/EXPORT，使用这个工具处理OLAP组件的数据是十分高效的。而传统的EXP/IMP在处理用户数据时没有问题，但是当需要导入OLAP元数据时，就会导致异常低效的问题，而如果IMP导入已经开始，那么也没有什么太好的办法，只好等它自己结束了。

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

ORA-7445(kwqbcsoa)和ORA-7445(lstclo)错误

Posted on 2012/08/07 by yangtingkun

客户环境出现多次ORA-7445错误以及Oracle executable binary mismatch detected的告警信息。
数据库版本为10.2.0.3 for Linux，错误信息如下：

Mon Aug 6 11:13:11 2012
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:15:09 2012
The VALUE (30) OF MAXTRANS parameter ignored.
Mon Aug 6 11:15:09 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19423.trc:
ORA-07445: exception encountered: core dump [kwqbcsoa()+64] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
Mon Aug 6 11:15:09 2012
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:15:09 2012
Errors IN file /ora/app/oracle/admin/ORCL/bdump/orcl_ora_19425.trc:
ORA-07445: exception encountered: core dump [kwqbcsoa()+64] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:15:18 2012
Errors IN file /ora/app/oracle/admin/ORCL/bdump/orcl_ora_19427.trc:
ORA-07445: exception encountered: core dump [kwqbcsoa()+64] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:15:26 2012
The VALUE (30) OF MAXTRANS parameter ignored.
Mon Aug 6 11:15:26 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19438.trc:
ORA-07445: exception encountered: core dump [kwqbcsoa()+64] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
Mon Aug 6 11:16:44 2012
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:22:47 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19601.trc:
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:22:50 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19606.trc:
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:23:01 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19632.trc:
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:23:05 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19636.trc:
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
Mon Aug 6 11:23:17 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_19655.trc:
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
WARNING: Oracle executable BINARY mismatch detected.
BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages

其中ORA-7445[kwqbcsoa]的TRACE信息为：

WARNING: Oracle executable BINARY mismatch detected.
 BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
startup image information
iid info sz=95109435 inode=63324683 ts=0x49dac59d
CURRENT process image information
iid info sz=95110492 inode=58490881 ts=0x501f357e
SET _disable_image_check = TRUE TO disable this CHECK
*** SERVICE NAME:(SYS$USERS) 2012-08-06 11:15:09.683
*** SESSION ID:(304.43283) 2012-08-06 11:15:09.683
Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x0, PC: [0x878fb84, kwqbcsoa()+64]
Registers:
%eax: 0x00000000 %ebx: 0x0ccf07b0 %ecx: 0xbfff71c8
%edx: 0x9bf70754 %edi: 0x00000000 %esi: 0x00000000
%esp: 0xbfff710c %ebp: 0xbfff7140 %eip: 0x0878fb84
%efl: 0x00210246
  kwqbcsoa()+48 (0x878fb74) push 0x2001514c
  kwqbcsoa()+54 (0x878fb7a) CALL 0x8414300
  kwqbcsoa()+59 (0x878fb7f) mov %eax,%edi
  kwqbcsoa()+61 (0x878fb81) mov $0x0,0x1c(%edi)
  kwqbcsoa()+68 (0x878fb88) mov $0xc247ce0,0xcd5064c
  kwqbcsoa()+78 (0x878fb92) push $0x20014ce0
  kwqbcsoa()+83 (0x878fb97) CALL 0x8319754
  kwqbcsoa()+88 (0x878fb9c) ADD $40,%esp
  kwqbcsoa()+91 (0x878fb9f) test %edi,%edi
*** 2012-08-06 11:15:09.685
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [kwqbcsoa()+64] [SIGSEGV] [Address NOT mapped TO object] [0x0] [] []
CURRENT SQL statement FOR this SESSION:
BEGIN sys.kupc$que_int.create_queues(:1, :2, :3, :4); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    NUMBER  name
0x9ba48960       700  package body SYS.DBMS_AQADM_SYSCALLS
0x9b7a4a24      4427  package body SYS.DBMS_AQADM_SYS
0x8cf9a1c0      1531  package body SYS.KUPC$QUE_INT
0x9b7200c4         1  anonymous block
0x9239ef24       148  package body SYS.KUPC$QUEUE_INT
0x9b781604       539  package body SYS.KUPV$FT_INT
0x926a05a8      1131  package body SYS.KUPV$FT
0x926a05a8       812  package body SYS.KUPV$FT
0x9b789220      4310  package body SYS.DBMS_DATAPUMP
0x8cfc0474         1  anonymous block
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+27          CALL     ksedst1()            1 ? 1 ?
ksedmp()+557         CALL     ksedst()             1 ? 8168FA0 ? B748BF3C ?
                                                   A9A650 ? 10 ? B7491D38 ?
ssexhd()+882         CALL     ksedmp()             3 ? 878FB84 ? 6271776B ?
                                                   616F7363 ? 362B2928 ? 34 ?
kwqbcsoa()+64        signal   00000000             B ? B748DC90 ? B748DD10 ?
kgqbt_search_subtre  CALL     kgqbt_search_block(  CD406A0 ? 9BF70754 ?
e()+42                        )                    8CF1E458 ? 1 ? BFFF71C8 ? 0 ?
kgqbtqry()+351       CALL     kgqbt_search_subtre  CD406A0 ? 9BF70754 ?
                              e()                  8CF1E458 ? 1 ? BFFF71C8 ?
                                                   90413274 ?
kwqbcfndqc()+55      CALL     kgqbtqry()           CD406A0 ? 9BF70754 ? 1 ?
                                                   BFFF71C8 ?
kwqbcbfq()+1623      CALL     kwqbcfndqc()         97D62 ? 0 ? 97D62 ?
                                                   904132A8 ? 3 ? 904132C7 ?
ktcccdel()+200       CALL     00000000             92541C10 ? 9AC00C8C ?
                                                   FFFFFFFF ? FFFFFFFF ? 1 ? 1 ?
ktcrcm()+901         CALL     ktcccdel()           9AC00C8C ? 9AC82E2C ? 1 ? 0 ?
                                                   0 ? 3 ? 1 ?
.
.
.

而ORA-7445[lstclo]的TRACE文件为：

WARNING: Oracle executable BINARY mismatch detected.
 BINARY OF NEW process does NOT MATCH BINARY which started instance
issue ALTER system SET "_disable_image_check" = TRUE TO disable these messages
startup image information
iid info sz=95109435 inode=63324683 ts=0x49dac59d
CURRENT process image information
iid info sz=95110492 inode=58490881 ts=0x501f357e
SET _disable_image_check = TRUE TO disable this CHECK
*** SERVICE NAME:(SRHUTF) 2012-08-06 11:22:47.552
*** SESSION ID:(316.18046) 2012-08-06 11:22:47.552
Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x66666f, PC: [0xa8dd2fd, lstclo()+45]
Registers:
%eax: 0xbfff9db0 %ebx: 0x0ccf07b0 %ecx: 0x0066666f
%edx: 0xbfff9db0 %edi: 0x9c65d0b0 %esi: 0x00000024
%esp: 0xbfff9aa4 %ebp: 0xbfff9abc %eip: 0x0a8dd2fd
%efl: 0x00210202
  lstclo()+32 (0xa8dd2f0) pop %ebx
  lstclo()+33 (0xa8dd2f1) ADD $0x24134c0,%ebx
  lstclo()+39 (0xa8dd2f7) mov 0xfffffffc(%ebp),%ecx
  lstclo()+42 (0xa8dd2fa) mov 0xfffffff8(%ebp),%eax
> lstclo()+45 (0xa8dd2fd) movzb (%ecx),%esi
  lstclo()+48 (0xa8dd300) movzb (%eax),%edi
  lstclo()+51 (0xa8dd303) ADD $1,%ecx
  lstclo()+54 (0xa8dd306) ADD $1,%eax
  lstclo()+57 (0xa8dd309) mov %ecx,0xfffffffc(%ebp)
*** 2012-08-06 11:22:47.554
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [lstclo()+45] [SIGSEGV] [Address NOT mapped TO object] [0x66666F] [] []
CURRENT SQL statement FOR this SESSION:
ALTER SESSION SET plsql_code_type = interpreted
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+27          CALL     ksedst1()            1 ? 1 ?
ksedmp()+557         CALL     ksedst()             1 ? 8168FA0 ? B748BF3C ?
                                                   A9A650 ? 10 ? B7491D38 ?
ssexhd()+882         CALL     ksedmp()             3 ? A8DD2FD ? 6374736C ?
                                                   29286F6C ? 35342B ? 0 ?
lstclo()+45          signal   00000000             B ? B748DC90 ? B748DD10 ?
lrmcvv()+234         CALL     lstclo()             66666F ? BFFF9DB0 ?
                                                   BFFF9AE0 ? 0 ? 0 ? BFFF9DB0 ?
kspsetv0()+538       CALL     lrmcvv()             9C6844A8 ? 9C65D0B0 ?
                                                   BFFF9DB0 ? C0ED320 ?
kspsetv()+33         CALL     kspsetv0()           20004D08 ? C8DC7DC ? 34E ?
                                                   BFFFA124 ? BFFFA11C ? 2 ?
                                                   100 ? 0 ? C8DC800 ?
kkxctycb()+159       CALL     kspsetv()            20004D08 ? C8DC7DC ?
                                                   BFFFA124 ? BFFFA11C ? 2 ?
                                                   100 ? C8DC800 ?
kspptval()+2207      CALL     00000000             100 ? BFFFA47C ? B72EF7EC ?
kspset0()+1552       CALL     kspptval()           352 ? BFFFAAD8 ? 101 ?
                                                   20004D08 ? 0 ? 0 ? BFFFA828 ?
                                                   102 ?
kkyase()+4725        CALL     kspset0()            20004D08 ? C168C44 ? 352 ?
                                                   9069C3F4 ? B ? 101 ?
                                                   9069C3D0 ? 0 ?
kksExecuteCommand()  CALL     kkyase()             9069C3BC ? 0 ? 5DBC ? 0 ?
+1016                                              1F87013 ? 4A9E9 ?
opiexe()+23116       CALL     kksExecuteCommand()  B72EF804 ? 2 ? BFFFC32C ?
kpoal8()+2089        CALL     opiexe()             49 ? 3 ? BFFFC32C ?
opiodr()+985         CALL     00000000             5E ? 17 ? BFFFEC30 ?
ttcpip()+1093        CALL     00000000             5E ? 17 ? BFFFEC30 ? 0 ?
opitsk()+1031        CALL     ttcpip()             CD47EA0 ? 5E ? BFFFEC30 ? 0 ?
                                                   BFFFE90C ? BFFFED40 ?
opiino()+821         CALL     opitsk()             0 ? 0 ?
opiodr()+985         CALL     00000000             3C ? 4 ? BFFFF800 ?
opidrv()+466         CALL     opiodr()             3C ? 4 ? BFFFF800 ? 0 ?
sou2o()+91           CALL     opidrv()             3C ? 4 ? BFFFF800 ?
opimai_real()+117    CALL     sou2o()              BFFFF7E4 ? 3C ? 4 ?
                                                   BFFFF800 ?
main()+111           CALL     opimai_real()        2 ? BFFFF830 ?
__libc_start_main()  CALL     00000000             2 ? BFFFF8F4 ? BFFFF900 ?
+211                                               A8F1D6 ? BC7FF4 ? 0 ?
--------------------- Binary Stack Dump ---------------------

根据TRACE信息可以确认，在发生ORA-7445时，数据库在进行数据泵的导入操作。第一个ORA-7445在数据泵初始化队列是报错，而第二个ORA-7445与OLAP处理有关。显然这和OLAP的数据泵导入有关。
而不停出现的Oracle executable binary mismatch detected告警信息，说明Oracle检查到当前运行的数据库与ORACLE_HOME中可执行文件不符，怀疑应该是之前打PATCH或重新MAKE没有安装正确的流程，导致这个告警的产生。
一般来说这个告警并不一定会导致错误的产生，但是当前出现了这两个ORA-7445错误很可能与这个告警有直接的关系，因为这个告警自始至终伴随着这两个错误，而一旦这个告警消失了，这两个错误也没有再次出现。因此这两个错误多半与数据库状态的不正常有直接关系。

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

ORA-600(kghstack_underflow_internal_2)错误

Posted on 2012/08/06 by yangtingkun

数据库10.2.0.3 for Linux 32环境出现ORA-600(kghstack_underflow_internal_2)错误。
详细错误信息为：

Tue Aug 7 08:36:43 2012
Errors IN file /ora/app/oracle/admin/ORC/udump/orcl_ora_29455.trc:
ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0xB72101E8], [], [], [], [], [], []
ORA-01013: USER requested cancel OF CURRENT operation
ORA-01013: USER requested cancel OF CURRENT operation

显然导致错误的原因和ORA-1013用户取消操作有直接的关系，不过查询MOS并没有看到类似的记载，而大部分和kghstack_underflow_internal_2有关的错误都与RMAN有关。
进一步检查详细TRACE文件：

*** SERVICE NAME:(SYS$USERS) 2012-08-07 08:36:43.439
*** SESSION ID:(324.19) 2012-08-07 08:36:43.439
*** 2012-08-07 08:36:43.439
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0xB72101E8], [], [], [], [], [], []
ORA-01013: USER requested cancel OF CURRENT operation
ORA-01013: USER requested cancel OF CURRENT operation
CURRENT SQL statement FOR this SESSION:
BEGIN  
dbms_aw_exp.import_chunk100(1492, 'QAECAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0AC
AAAAAACAW0ACAAAAAABAXUACAAAAAABAXUACAAAAAABAXUACAAAAAABAXUACAAAA
AABAXUABAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAA
gFtAAgAAAAAAgFtAAgAAAAAAYF1AAgAAAAAAYF1AAgAAAAAAYF1AAgAAAAAAYF1A
AgAAAAAAYF1AAQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIA
AAAAAIBbQAIAAAAAAIBbQAIAAAAAACBdQAYRAAAAAgAAAAAAgFtAAgAAAAAAgFtA
AgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAYF1AAgAA
AAAAYF1AAgAAAAAAYF1AAgAAAAAAYF1AAgAAAAAAYF1AAQIAAAAAAEBaQAIAAAAA
AEBaQAYKAAAAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAA
AAAAgFtAAgAAAAAAgFtAAgAAAAAAgF1AAgAAAAAAgF1AAgAAAAAAgF1AAgAAAAAA
gF1AAgAAAAAAgF1ABg0AAAACAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAA
AACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAABAXUACAAAAAABAXUACAAAAAABA
XUACAAAAAABAXUACAAAAAABAXUABAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtA
AgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAA4FxAAgAAAAAA4FxAAgAA
AAAA4FxAAgAAAAAA4FxAAgAAAAAA4FxAAQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAA
AIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAEBdQAIAAAAAAEBd
QAIAAAAAAEBdQAIAAAAAAEBdQAIAAAAAAEBdQAECAAAAAACAW0ACAAAAAACAW0AC
AAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAAAAXUACAAAA
AAAAXUABAQEBAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAA
AAAAgFtAAgAAAAAAgFtAAgAAAAAAIF5AAgAAAAAAIF5AAgAAAAAAIF5AAgAAAAAA
IF5AAgAAAAAAIF5AAQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBb
QAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBdQAIAAAAAAIBdQAIAAAAAAIBdQAIA
AAAAAIBdQAIAAAAAAIBdQAECAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAA
AACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAADAXEACAAAAAADAXEACAAAAAADA
XEACAAAAAADAXEACAAAAAADAXEABAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtA
AgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAQF1AAgAAAAAAQF1AAgAA
AAAAQF1AAQEBAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAAAAAAgFtAAgAA
AAAAgFtAAgAAAAAAgFtAAgAAAAAAgF1AAgAAAAAAgF1AAgAAAAAAgF1AAgAAAAAA
gF1AAgAAAAAAgF1AAQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBb
QAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBdQAIAAAAAAIBdQAIAAAAAAIBdQAIA
AAAAAIBdQAIAAAAAAIBdQAECAAAAAAAAVkACAAAAAAAAVkACAAAAAAAAVkACAAAA
AAAAVg==');
.
.
.
dbms_aw_exp.import_chunk100(1492, 'QAIAAAAAAOBlQAIAAAAAAOBlQAIAAAAAAOBlQAIAAAAAAOBlQAIAAAAAAOBlQAEC
AAAAAADAckACAAAAAADAckACAAAAAADAckACAAAAAADAckACAAAAAADAckACAAAA
AADAckACAAAAAAAAeEACAAAAAAAAeEACAAAAAAAAeEACAAAAAAAAeEACAAAAAAAA
eEABAgAAAAAAAF5AAgAAAAAAAF5AAgAAAAAAAF5AAgAAAAAAAF5AAgAAAAAAwGJA
AgAAAAAAwGJAAgAAAAAAwGJAAgAAAAAAwGJAAgAAAAAAwGJAAgAAAAAAwGJAAgAA
AAAAwGJAAQIAAAAAAEBaQAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAA
AEBaQAIAAAAAAEBaQAIAAAAAAABgQAIAAAAAAABgQAIAAAAAAABgQAIAAAAAAABg
QAEBAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZA
AgAAAAAAgGZAAgAAAAAAYGhAAgAAAAAAYGhAAgAAAAAAYGhAAgAAAAAAYGhAAgAA
AAAAQHFAAQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAA
AIBbQAIAAAAAAIBbQAIAAAAAAMBhQAIAAAAAAMBhQAIAAAAAAMBhQAIAAAAAAMBh
QAIAAAAAAMBhQAYNAAAAAgAAAAAAAGlAAgAAAAAAAGlAAgAAAAAAAGlAAgAAAAAA
AGlAAgAAAAAAAGlAAgAAAAAAAGlAAgAAAAAAAGlAAgAAAAAAAGlAAgAAAAAAAGlA
AgAAAAAAAGlAAgAAAAAAAGlAAQIAAAAAAABXQAIAAAAAAABXQAIAAAAAAABXQAIA
AAAAAABXQAIAAAAAAABXQAIAAAAAAABXQAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAA
AEBaQAIAAAAAAEBaQAEBAgAAAAAAAFlAAgAAAAAAAFlAAgAAAAAAAFlAAgAAAAAA
AFlAAgAAAAAAAFlAAgAAAAAAAFlAAgAAAAAAQFxAAgAAAAAAQFxAAgAAAAAAQFxA
AgAAAAAAQFxAAgAAAAAAQFxAAQIAAAAAAIBMQAIAAAAAAIBMQAIAAAAAAIBMQAIA
AAAAAIBMQAIAAAAAAEBUQAIAAAAAAEBUQAIAAAAAAEBUQAEBAQEBAgAAAAAAQGpA
AgAAAAAAQGpAAgAAAAAAQGpAAgAAAAAAQGpAAgAAAAAAQGpAAgAAAAAAQGpABgYA
AAACAAAAAABAZEACAAAAAABAZEAGFgAAAAIAAAAAAABZQAIAAAAAAABZQAIAAAAA
AABZQAIAAAAAAABZQAIAAAAAAABZQAIAAAAAAABZQAIAAAAAAIBcQAYRAAAAAgAA
AAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAAgGZAAgAAAAAA
gGZAAgAAAAAA4GtAAgAAAAAA4GtAAgAAAAAA4GtAAgAAAAAA4GtAAgAAAAAA4GtA
AQIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIAAAAAAIBbQAIA
AAAAAIBbQAIAAAAAAIBcQAIAAAAAAIBcQAIAAAAAAIBcQAIAAAAAAIBcQAIAAAAA
AIBcQAECAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAACAW0ACAAAAAACA
W0ACAAAAAACAW0ACAAAAAADAW0ACAAAAAADAW0ACAAAAAADAW0ACAAAAAADAW0AG
DgAAAAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAAAEBaQAIAAAAAAEBa
QAIAAAAAAEBaQAIAAAAAAMBdQAIAAAAAAMBdQAIAAAAAAMBdQAIAAAAAAMBdQAIA
AAAAAMBdQAECAAAAAABAWkACAAAAAABAWkACAAAAAABAWkACAAAAAABAWkACAAAA
AABAWg==');
COMMIT; END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    NUMBER  name
0x92ee5d08       815  package body SYS.DBMS_AW_EXP
0x92ee5d08       828  package body SYS.DBMS_AW_EXP
0x92ee5d08       192  package body SYS.DBMS_AW_EXP
0x92ee5d08       219  package body SYS.DBMS_AW_EXP
0x672eefe0       354  anonymous block
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+27          CALL     ksedst1()            0 ? 1 ?
ksedmp()+557         CALL     ksedst()             0 ? 0 ? CD406A0 ? B5000000 ?
                                                   0 ? BFFFA070 ?
ksfdmp()+19          CALL     ksedmp()             3 ? BFFF9FE8 ? ACF7C35 ?
                                                   CD406A0 ? 3 ? CCF07B0 ?
kgerinv()+177        CALL     00000000             CD406A0 ? 3 ?
kgeasnmierr()+40     CALL     kgerinv()            CD406A0 ? B72B0020 ?
                                                   C89DFA0 ? 1 ? BFFFA020 ?
kghstack_underflow_  CALL     kgeasnmierr()        CD406A0 ? B72B0020 ?
internal()+254                                     C89DFA0 ? 1 ? 2 ? B72101E8 ?
kghstack_free()+619  CALL     kghstack_underflow_  CD406A0 ? B72101E8 ?
                              internal()           
ksmfrs()+17          CALL     kghstack_free()      CD406A0 ? B72101E8 ?
xsLobVWrite()+1394   CALL     ksmfrs()             B72101E8 ? B72380D0 ?
                                                   B72101E8 ? CD4079C ? 3F5 ?
                                                   1 ?
spefcifa()+81        CALL     00000000             BFFFAFBC ? B72B1EC8 ? 0 ?
                                                   B5C20E80 ?
spefmccallstd()+265  CALL     spefcifa()           BFFFAC98 ? 4 ? B72B1F98 ?
                                                   BFFFA544 ? BFFFA558 ? 0 ?
pextproc()+90        CALL     spefmccallstd()      BFFFAFEC ? BFFFAE40 ?
                                                   BFFFAD08 ? BFFFAC98 ? 0 ?
__PGOSF284_peftrust  CALL     pextproc()           BFFFAFEC ? BFFFAE40 ?
ed()+115                                           BFFFAD08 ? BFFFAC98 ?
                                                   CD4079C ? B ?
psdexsp()+188        CALL     00000000             BFFFAFEC ? CD406A0 ?
                                                   BFFFAAD4 ? 200 ? BFFFA670 ?
                                                   ACE2237 ?
rpiswu2()+334        CALL     00000000             BFFFAC18 ? BFFFA780 ?
                                                   ACD9867 ? CD406A0 ? 0 ?
                                                   B723A154 ?
psdextp()+454        CALL     rpiswu2()            9C5F4650 ? 3F ? 9C5F4678 ?
                                                   2 ? BFFFAC30 ? 3F ?
                                                   9C5F46F8 ? 0 ? BB65C90 ? 0 ?
                                                   BFFFAC18 ? 1 ?
pefccal()+457        CALL     00000000             BFFFB6E4 ? BFFFAE40 ?
                                                   BFFFAD08 ? 0 ? 20003 ?
                                                   BFFFAFEC ?
pefcal()+169         CALL     pefccal()            BFFFAFEC ? 9E3821CC ?
                                                   CCF07B0 ? BFFFAEC4 ?
                                                   B5F42E5A ? B6C002FC ?
pevm_FCAL()+133      CALL     pefcal()             BFFFAFEC ? BFFFAFEC ?
                                                   B71216CC ? BFFFAE40 ? 0 ? 1 ?
pfrinstr_FCAL()+56   CALL     pevm_FCAL()          B6470628 ? 91EBD108 ?
pfrrun_no_tool()+45  CALL     00000000             B6470628 ? 91EB6DD0 ?
                                                   B6470664 ?
pfrrun()+765         CALL     pfrrun_no_tool()     B6470628 ? 91EC3B7E ?
                                                   B6470664 ?
plsql_run()+736      CALL     pfrrun()             B6470628 ? 0 ? 0 ? 0 ?
                                                   B72CEAF0 ? B72CEB08 ?
peicnt()+552         CALL     plsql_run()          B6470628 ? 1 ? 0 ?
kkxexe()+389         CALL     peicnt()             BFFFB6E4 ? B6470628 ?
                                                   CD406A0 ? 0 ? BFFFB7FC ? 0 ?
opiexe()+4125        CALL     kkxexe()             B71B010C ? 672EE124 ? 224 ?
                                                   A8 ? 995ECE74 ? CCF07B0 ?
opiosq0()+2518       CALL     opiexe()             4 ? 0 ? BFFFC3F8 ?
opiosq()+19          CALL     opiosq0()            27 ? 3 ? BFFFECC0 ? 0 ?
opiodr()+985         CALL     00000000             27 ? 3 ? BFFFECC0 ?
ttcpip()+1093        CALL     00000000             27 ? 3 ? BFFFECC0 ? 3 ?
opitsk()+1031        CALL     ttcpip()             CD47EA0 ? 27 ? BFFFECC0 ? 0 ?
                                                   BFFFE99C ? BFFFEDD0 ?
opiino()+821         CALL     opitsk()             0 ? 0 ?
opiodr()+985         CALL     00000000             3C ? 4 ? BFFFF890 ?
opidrv()+466         CALL     opiodr()             3C ? 4 ? BFFFF890 ? 0 ?
sou2o()+91           CALL     opidrv()             3C ? 4 ? BFFFF890 ?
opimai_real()+117    CALL     sou2o()              BFFFF874 ? 3C ? 4 ?
                                                   BFFFF890 ?
main()+111           CALL     opimai_real()        2 ? BFFFF8C0 ?
__libc_start_main()  CALL     00000000             2 ? BFFFF984 ? BFFFF990 ?
+211                                               A8F1D6 ? BC7FF4 ? 0 ?
 
--------------------- Binary Stack Dump ---------------------

很明显，在出现错误时用户执行的操作是OLAP用户的导入操作，而之所以出现了ORA-600错误是由于imp工具调用dbms_aw_exp.import_chunk100过程时异常退出机制处理的存在问题，不应该顺利结束当前的执行，但是Oracle这里引发了一个内部的错误。
根据错误信息，这个错误对于数据库环境没有影响，可以简单的将其忽略掉。

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

ORA-600(kssadd_stage: null parent)错误

Posted on 2012/08/05 by yangtingkun

客户数据库出现频繁的ORA-600(kssadd_stage: null parent)错误。
数据库版本为10.2.0.5 RAC for AIX，报错时详细错误信息为：

Tue Jul 17 14:57:39 2012
Errors IN file /oracle/10g/admin/orcl/udump/orcl2_ora_307720.trc:
ORA-00600: internal error code, arguments: [kssadd_stage: NULL parent], [], [], [], [], [], [], []
Tue Jul 17 14:57:45 2012
Errors IN file /oracle/10g/admin/orcl/udump/orcl2_ora_357344.trc:
ORA-00600: internal error code, arguments: [kssadd_stage: NULL parent], [], [], [], [], [], [], []
Tue Jul 17 14:57:56 2012
Trace dumping IS performing id=[cdmp_20120717145753]
Tue Jul 17 14:58:12 2012
Trace dumping IS performing id=[cdmp_20120717145823]
Tue Jul 17 15:05:53 2012
Trace dumping IS performing id=[cdmp_20120717150633]
Tue Jul 17 15:06:42 2012
Errors IN file /oracle/10g/admin/orcl/udump/orcl2_ora_94700.trc:
ORA-00600: internal error code, arguments: [kssadd_stage: NULL parent], [], [], [], [], [], [], []
Tue Jul 17 15:06:43 2012
Trace dumping IS performing id=[cdmp_20120717150643]

相关的TRACE文件信息如下：

/oracle/10g/admin/orcl/udump/orcl2_ora_111068.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, OLAP, DATA Mining
AND REAL Application Testing options
ORACLE_HOME = /oracle/10g/db
System name:    AIX
Node name:      ora2
Release:        1
Version:        6
Machine:        00CCFD354C00
Instance name: orcl2
Redo thread mounted BY this instance: 2
Oracle process NUMBER: 89
Unix process pid: 111068, image: oracle@ora2
*** ACTION NAME:() 2012-07-17 15:08:42.043
*** MODULE NAME:(gsr.exe) 2012-07-17 15:08:42.043
*** SERVICE NAME:(orcl) 2012-07-17 15:08:42.043
*** SESSION ID:(991.44140) 2012-07-17 15:08:42.043
*** 2012-07-17 15:08:42.043
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [kssadd_stage: NULL parent], [], [], [], [], [], [], []
No CURRENT SQL statement being executed.
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex
location             TYPE     point                (? means dubious VALUE)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              40D1A9663F9E7ABB ?
                                                   6ED89E14D59386B5 ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              11041D938 ? 700000220FFBFC8 ?
                                                   110000770 ? 7000004FDF0C700 ?
                                                   FFFFFFFFFFF89C0 ?
kssadd_stage+0080    bl       kgeasnmierr          110195490 ? 110450040 ?
                                                   104AC46B8 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 7000004F0FA9208 ?
kqreqa+0058          bl       kssadd_stage         105670038 ? 104CF7BA0 ?
                                                   000000000 ? 000000000 ?
kqrpre1+0850         bl       kqreqa               100203514 ? 1101A2B20 ?
kqrpre+001c          bl       kqrpre1              710000770 ? 000000009 ?
                                                   FFFFFFFFFFF9088 ?
                                                   28A4202200000000 ?
                                                   10012AEE4 ? FFFFFFFFFFF9080 ?
                                                   000000000 ? 11022A3E0 ?
opiosq0+009c         bl       kqrpre               000000000 ? 000000000 ?
                                                   000000000 ? 1101A2B20 ?
                                                   FFFFFFFFFFF9198 ? 1104B7C60 ?
                                                   FFFFFFFFFFF9458 ?
kpooprx+0168         bl       opiosq0              4A00000001 ? 000000001 ?
                                                   000000000 ? A40000000000FF ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB964 ?
                                                   FFFFFFFFFFFB680 ?
                                                   5000000050 ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 1103A1AD8 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F971E8
opiino+0990          bl       opitsk               000000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F96034
sou2o+0090           bl       opidrv               3C02D9A29C ? 4A006E298 ?
                                                   FFFFFFFFFFFF8A0 ?
opimai_real+01bc     bl       01F939B4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0070         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

根据MOS文档Bug 4937225 ORA-22 from OCIStmtExecute after OCISessionBegin的描述，这是个客户端OCI出现的bug，问题发生在10.2.0.3以下版本的客户端连接到高版本数据库的情况，确认影响的版本为10.2.0.2，Oracle在10.2.0.3和11.1.0.6中FIXED了该bug。
当前数据库版本为10.2.0.5但是这个问题是发生在客户端，而发生问题的客户端版本为10.2.0.1，因此导致这个错误的产生就不奇怪了，通过升级客户端数据库版本到10.2.0.3以上可以解决该错误。

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

ORA-7445(xsSSEXECUT)错误

Posted on 2012/08/04 by yangtingkun

又一个OLAP组件相关的bug。
数据库版本为10.2.0.3 32bit for Linux，在告警日志出现下面的错误：

Mon Jul 30 23:29:19 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_24707.trc:
ORA-07445: exception encountered: core dump [xsSSEXECUT()+13022] [SIGSEGV] [Address NOT mapped TO object] [0x14] [] []

查询MOS发现是OLAP上的bug，不过整个MOS中只有Bug 13622968 : ORA-7445 [XSSSEXECUT+135]这一篇文档描述了相关的bug，看来OLAP问题虽然很多，但是用的人并不太多。导致这个错误的原因是Oracle的OLAP组件在处理到xsSSEXECUT函数的过程中碰到了空指针的问题。
Oracle计划在11.2.0.4和12.1中fixed这个问题，如果对于11.2来说短时间内都没有解决方案，看来对于10.2环境来说这个问题很可能会无解。
如果可能通过改变应用程序的方法来尝试绕过该错误，好在这个问题发生的频率并不高，对于系统的影响也是可接受的。

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

ORA-600(kghfremptyds)和ORA-600(kghasp1)错误

Posted on 2012/08/03 by yangtingkun

客户在进行OLAP的导入时出现了这个错误。
数据库版本为10.2.0.3，除了这两个错误外，同时还出现了其他的错误：

Mon Jul 30 23:49:08 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:09 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xAF50AF34], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:21 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:23 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xAF50AF34], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:35 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:36 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xAF50AF34], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0xAF509008], [], [], [], [], [], []
Mon Jul 30 23:49:48 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], []
Mon Jul 30 23:49:49 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [17125], [0x0], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], []
Mon Jul 30 23:50:02 2012
Errors IN file /ora/app/oracle/admin/ORCL/udump/orcl_ora_31232.trc:
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], []

导致问题的就是OLAP的imp操作，查询MOS发现Bug 7526250 : EXEC DBMS_AW.EXECUTE FAILS WITH ORA-600 [KGHFREMPTY:DS] AND ORA-600 [KGHASP1]描述的就是当前的问题，数据库版本同样也是10.2.0.3，这个bug的基本BUG指向：Bug 7395130 : ORA-4030 ERROR DURING EIF IMPORT USING THE ‘UPDATE’ OPTION。
Oracle在执行OLAP的EIF导入时，可能出现内存泄漏和ORA-4030等错误，这和当前数据库的现象完全相同。当前数据库中不但出现了ORA-4030的错误，而且还有明显的ORA-600[729]错误的出现。
Oracle在11.2中才FIXED了这个bug，而且目前没有看到这个bug的补丁，看来Oracle对于OLAP组件的支持还是赶不上DB。

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

小议ROWNUM

Posted on 2012/08/02 by yangtingkun

如何使用ROWNUM是个老生常谈的问题了，本来没有打算专门强调这个问题，但是最近在看Oracle的官方PL/SQL文档时发现了一个严重的错误，借这个机会还是简单说一下。
首先来看Oracle文档的描述，在10.2的PL/SQL文档中，Oracle关于PL/SQL中直接使用SELECT的查询描述为：

Selecting At Most One ROW: SELECT INTO Statement
IF you expect a query TO ONLY RETURN one ROW, you can WRITE a regular SQL SELECT statement WITH an additional INTO clause specifying the PL/SQL variable TO hold the RESULT.
IF the query might RETURN more than one ROW, but you do NOT care about VALUES after the FIRST, you can RESTRICT any RESULT SET TO a single ROW BY comparing the ROWNUM VALUE. IF the query might RETURN no ROWS at ALL, USE an exception handler TO specify any actions TO take WHEN no DATA IS found.

这个描述是没有问题的，但是到了11.2中，文档的描述变成了：

Single-ROW RESULT Sets
IF you expect the query TO RETURN ONLY one ROW, THEN USE the SELECT INTO statement TO store VALUES FROM that ROW IN either one OR more scalar VARIABLES (see "Assigning Values to Variables with the SELECT INTO Statement") OR one record variable (see "SELECT INTO Statement for Assigning Row to Record Variable").
IF the query might RETURN multiple ROWS, but you care about ONLY the nth ROW, THEN RESTRICT the RESULT SET TO that ROW WITH the clause WHERE ROWNUM=n. FOR more information about the ROWNUM pseudocolumn, see Oracle DATABASE SQL LANGUAGE Reference.

第一个反应是不是我看错了，居然可以通过WHERE ROWNUM = N来限制只返回第N条记录 ，再仔细看了一遍，并和10g的文档对比，发现11.2和10.2中的不同。于是第二个反应是Oracle在11.2中提供了新特性，使得PL/SQL语句中直接SELECT可以通过WHERE ROWNUM来直接控制游标，于是特意在11.2上进行了测试，发现结果和10.2上没有区别，ROWNUM = N是行不通的，除非N等于1。

SQL> SELECT * FROM v$version;
BANNER
------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS FOR 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SELECT * FROM tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COMPANIES                      TABLE
MLOG$_T_F                      TABLE
MLOG$_T_P                      TABLE
MV_T_ORACLE                    TABLE
SERVICES                       TABLE
SERVICE_RATES                  TABLE
SERVICE_USAGE                  TABLE
SERVICE_USERS                  TABLE
T                              TABLE
T_DEFER                        TABLE
T_F                            TABLE
T_LOAD_LOB                     TABLE
T_P                            TABLE
T_PART                         TABLE
已选择14行。
SQL> SET serverout ON
SQL> DECLARE
  2  v_name varchar2(30);
  3  BEGIN
  4  SELECT tname INTO v_name FROM tab WHERE rownum = 5;
  5  dbms_output.put_line(v_name);
  6  exception 
  7  WHEN no_data_found THEN
  8  dbms_output.put_line('rownum equal the num bigger than 1 is incorrect!');
  9  END;
 10  /
rownum equal the num bigger than 1 IS incorrect!
PL/SQL 过程已成功完成。

显然Oracle文档这里出现了严重的错误，如果要使用ROWNUM来控制返回第几行结果，那么至少需要2层嵌套查询才可以。
最后简单总结一下ROWNUM，很多人都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。但是却并不了解造成这种限制条件的机制是什么。
其实ROWNUM的返回很简单，ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。
理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，仍然是1，因此所有的记录都不满足条件。
了解了原理，就可以很容易的写出ROWNUM大于某值的例子：

SQL> SELECT * FROM tab WHERE rownum = 1 OR rownum > 1;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COMPANIES                      TABLE
MLOG$_T_F                      TABLE
MLOG$_T_P                      TABLE
MV_T_ORACLE                    TABLE
SERVICES                       TABLE
SERVICE_RATES                  TABLE
SERVICE_USAGE                  TABLE
SERVICE_USERS                  TABLE
T                              TABLE
T_DEFER                        TABLE
T_F                            TABLE
T_LOAD_LOB                     TABLE
T_P                            TABLE
T_PART                         TABLE
已选择14行。
Posted in ORACLE | Tagged , | Leave a comment