资源耗尽导致ORA-494错误

Posted on 2011/12/15 by yangtingkun

一个客户的STANDBY数据库出现了ORA-494的错误。
数据库服务器建立在IBM的POWER 7利用LPAR划分的逻辑分区上，目前上面只有一个备库在跑日志应用，前一天利用LPAR向这个分区又添加了CPU和内存资源，使得当前的CPU数从32增加到96，内存从48G增加到98G。
如此强劲的硬件配置，却出现了系统HANG的问题。而当天除了物理STANDBY数据库的正常日志应用外，只不过进行了一次操作系统级的备份。
对应的数据库的高级日志信息为：

Thu Oct 13 19:03:55 2011
Media Recovery Log /archlog/1_133940_665275242.dbf
Thu Oct 13 19:04:17 2011
Media Recovery Log /archlog/1_133941_665275242.dbf
Media Recovery Waiting FOR thread 1 SEQUENCE 133942 (IN transit)
Thu Oct 13 19:21:33 2011
Errors IN file /oracle/admin/db1/bdump/db1_arc0_3408616.trc:
ORA-00494: enqueue [CF] held FOR too long (more than 900 seconds) BY 'inst 1, osid 25886816'
Thu Oct 13 22:48:41 2011
Errors IN file /oracle/admin/db1/udump/db1_rfs_21496166.trc:
ORA-00494: enqueue [CF] held FOR too long (more than 900 seconds) BY 'inst 1, osid 8454874'

刚开始听到这个错误时以为是一个普通的BUG，随着对问题的深入了解，我的观点也发生了改变。最重要的一个理由是操作系统也一同HANG住了，以致于最终不得不通过重启系统来解决这个问题。
事实上，到现场之前我还特意留意了一下METALINK中有哪些ORA-494错误和DATA GUARD配置有关，而在现场了解了情况之后，我再没有查询过METALINK。很显然问题已经不是Oracle数据库层面上的了，即使Oracle性能再差，把所有CPU都跑满，也不可能使得操作系统完全不可登录，更不要说这只是一个应用日志的备库而已。而且就算是Oracle的bug，对于单实例配置的数据库，能把服务器一起挂掉的情况还真不多见（Windows服务器除外）。
下面就是检查操作系统的信息，确认这个问题和Oracle没有关系，而Oracle所报的ORA-494错误，仅仅是由于缺少资源或者操作系统不正常所致。
可惜的是errpt中看不到任何有价值的错误信息。不过好在系统中部署了nmon，可以看到出现问题这一天系统的运行状况。nmon正常采样数据截至到19:05，从19:05到20:25之间数据丢失，20:25是最后一条采样数据。
除了nmon信息不完整外，还找到两个疑点：一个是CPU的sys利用率很高，在HANG住之前，有两个小时左右sys占用了整个cpu的40%，而几乎看不到任何的user和wait；二是从上午数据库重启之后，可用内存阶梯状下降，空闲内存从70G一直下降到问题前1、2个小时的100M左右。其中占用内存空间主要是FSCache，它的贡献大约在整个内存的70%左右。
操作系统工程师通过vmo –F –a检查参数，排除了内存方面的问题。可用内存降到接近0是与AIX上内存使用规则和参数设置有关，这种配置下AIX上内存会优先使用空闲内存，只有空闲内存剩余小于参数设置的阈值后，AIX才会尝试在FSCache中获取内存。
而CPU中的sys一直无法解释，因此也被认为是导致问题的原因。而且在检查各个CPU的使用情况时，发现另一个比较奇怪的现象，CPU的使用基本上分为两个极端，在问题发生时刻，一部分CPU完全空闲，一部分CPU的SYS跑到100%。
问题最后由一个IBM原厂的高级工程师定位，在P7系列服务器中如果在分区之间动态划分CPU和内存资源，而没有重启主机的话，是可能导致部分CPU工作不正常的，这个问题他们曾经在其他系统中碰到过，且目前已经提交到IBM实验室进行解决。而这个系统确实在前一天进行过动态划分CPU和内存的操作，且没有进行重启操作系统。现场尝试重现问题时发现，动态划分一部分CPU再动态收回后，CPU的序号确实出现错乱，因此证明了bug的存在。
这个问题基本确定，由于大量的资源划分到当前分区中并没有完全正常的发挥作用，而操作系统和Oracle都是安装资源划分后的CPU和内存来管理和使用资源，最终导致系统出现问题，部分可用cpu sys上升到100%，而更多不可用的cpu负载为0，整个系统HANG住。
短期内解决这个问题的方法就是在LPAR划分资源后重新启动系统，这时系统就可以正常的使用划分后的资源了。

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

同一个SQL引发多个ORA-7445错误

Posted on 2011/12/14 by yangtingkun

在客户的Oracle 9204 RAC for Linux x86环境中，发现同一SQL引发了多个ORA-7445错误。
节点1上错误信息为：

Wed Nov 30 14:19:38 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8519.trc:
ORA-07445: exception encountered: core dump [kghalf()+743] [SIGSEGV] [Address NOT mapped TO object] [0x1FE0] [] []
.
.
.
Thu DEC 1 16:09:03 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:03 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:04 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:04 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:05 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:05 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:06 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:08 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
Thu DEC 1 16:09:09 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_8493.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0x4FA21E38], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x31303200] [] []
.
.
.
Wed DEC 7 17:36:33 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_30089.trc:
ORA-07445: exception encountered: core dump [kghalf()+743] [SIGSEGV] [Address NOT mapped TO object] [0x1FE0] [] []
.
.
.
Thu DEC 8 14:16:01 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_9172.trc:
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x34303133] [] []
Thu DEC 8 14:16:02 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_9172.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xEDC0DCC4] [] []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x34303133] [] []
Thu DEC 8 14:16:02 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_9172.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xEDC0DCC4] [] []
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xEDC0DCC4] [] []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0x34303133] [] []
.
.
.
Fri DEC 9 08:29:22 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_9320.trc:
ORA-07445: exception encountered: core dump [00000001] [SIGSEGV] [Address NOT mapped TO object] [0x1] [] []
.
.
.
Fri DEC 9 14:09:34 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_3437.trc:
ORA-07445: exception encountered: core dump [qerhjGetNextRowPiece()+15] [SIGSEGV] [Address NOT mapped TO object] [0x4] [] []
Fri DEC 9 14:10:13 2011
Trace dumping IS performing id=[cdmp_20111209141013]
Fri DEC 9 14:54:05 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_3425.trc:
ORA-07445: exception encountered: core dump [ksfd_gosdrq()+158] [SIGSEGV] [Address NOT mapped TO object] [0x23F9] [] []
.
.
.
Mon DEC 12 10:06:32 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_27737.trc:
ORA-07445: exception encountered: core dump [qerhjGetNextRowPiece()+15] [SIGSEGV] [Address NOT mapped TO object] [0x4] [] []
.
.
.
Tue DEC 13 09:00:45 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_28549.trc:
ORA-07445: exception encountered: core dump [ksmarfg()+296] [SIGSEGV] [Address NOT mapped TO object] [0xB7758000] [] []
Tue DEC 13 09:00:46 2011
Trace dumping IS performing id=[cdmp_20111213090046]
Tue DEC 13 09:50:30 2011
Errors IN file /u01/oracle/admin/oric/udump/oric1_ora_28539.trc:
ORA-07445: exception encountered: core dump [ksfd_gosdrq()+236] [SIGSEGV] [Address NOT mapped TO object] [0xFFCE0831] [] []

节点2上的错误包括：

Wed Nov 23 09:23:55 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_500.trc:
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0xBECB0008] [] []
Wed Nov 23 09:23:55 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_500.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xE1BB2100] [] []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0xBECB0008] [] []
Wed Nov 23 09:23:55 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_500.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xE1BB2100] [] []
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xE1BB2100] [] []
ORA-07445: exception encountered: core dump [smbput()+10979] [SIGSEGV] [Address NOT mapped TO object] [0xBECB0008] [] []
Wed Nov 23 10:16:36 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_486.trc:
ORA-07445: exception encountered: core dump [kghtshrt()+76] [SIGSEGV] [Address NOT mapped TO object] [0xEE9F0305] [] []
Wed Nov 23 10:16:36 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_486.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xC7D8B9D8] [] []
ORA-07445: exception encountered: core dump [kghtshrt()+76] [SIGSEGV] [Address NOT mapped TO object] [0xEE9F0305] [] []
Wed Nov 23 10:16:37 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_486.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xC7D8B9D8] [] []
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xC7D8B9D8] [] []
ORA-07445: exception encountered: core dump [kghtshrt()+76] [SIGSEGV] [Address NOT mapped TO object] [0xEE9F0305] [] []
Wed Nov 23 10:16:41 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:41 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:42 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:42 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:43 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:43 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:44 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:45 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
Wed Nov 23 10:16:46 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_16415.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xB77B545C], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghpmalo()+76] [SIGSEGV] [Address NOT mapped TO object] [0xB86BFFD4] [] []
.
.
.
Wed Nov 30 15:09:15 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_8526.trc:
ORA-07445: exception encountered: core dump [ksmarfg()+296] [SIGSEGV] [Address NOT mapped TO object] [0xB7758000] [] []
Wed Nov 30 15:09:16 2011
Trace dumping IS performing id=[cdmp_20111130150916]
Wed Nov 30 15:09:27 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_26073.trc:
ORA-07445: exception encountered: core dump [ksmarfg()+296] [SIGSEGV] [Address NOT mapped TO object] [0xB7758000] [] []
.
.
.
Fri DEC 9 11:01:00 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7421.trc:
ORA-07445: exception encountered: core dump [kghalf()+743] [SIGSEGV] [Address NOT mapped TO object] [0x1FE0] [] []
.
.
.
Tue DEC 13 10:24:20 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7413.trc:
ORA-07445: exception encountered: core dump [kcbhfho()+50] [SIGSEGV] [Address NOT mapped TO object] [0xCDC4B5AF] [] []
Tue DEC 13 10:24:20 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7413.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xB9455B58] [] []
ORA-07445: exception encountered: core dump [kcbhfho()+50] [SIGSEGV] [Address NOT mapped TO object] [0xCDC4B5AF] [] []
Tue DEC 13 10:24:21 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7413.trc:
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xB9455B58] [] []
ORA-07445: exception encountered: core dump [kghbigasp()+289] [SIGSEGV] [Address NOT mapped TO object] [0xB9455B58] [] []
ORA-07445: exception encountered: core dump [kcbhfho()+50] [SIGSEGV] [Address NOT mapped TO object] [0xCDC4B5AF] [] []
Tue DEC 13 10:33:46 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7407.trc:
ORA-07445: exception encountered: core dump [ksmarfg()+296] [SIGSEGV] [Address NOT mapped TO object] [0xB74D7000] [] []
Tue DEC 13 10:33:48 2011
Trace dumping IS performing id=[cdmp_20111213103348]
Tue DEC 13 10:41:50 2011
Errors IN file /u01/oracle/admin/oric/udump/oric2_ora_7371.trc:
ORA-07445: exception encountered: core dump [qerhjGetNextRowPiece()+15] [SIGSEGV] [Address NOT mapped TO object] [0x4] [] []

上面出现了多个ORA-7445错误，包括ORA-7445 [kghalf]、ORA-7445[smbput]、ORA-7445[00000001]、ORA-7445[qerhjGetNextRowPiece]、ORA-7445[ksfd_gosdrq]、ORA-7445[ksmarfg]、ORA-7445[kghtshrt]、ORA-7445[kghpmalo]和ORA-[kcbhfho]。有些ORA-7445还会伴随ORA-600错误。而且这些错误有一少半，在MOS中根本找不到对应的记录。
不过经检查发现，所有的ORA-600和ORA-7445错误都与同一个SQL语句有关：
SELECT T1.*
FROM
(
SELECT T.*,ROWNUM R
FROM
(
SELECT TAB1.*
FROM U1.TAB1 TAB1
WHERE TAB1.ID IN
(
SELECT TAB2.ID
FROM U1.TAB1 TAB1 ,U1.TAB2 TAB2
WHERE TAB1.ID=TAB2.ID
AND (ACID=’172037402731702′ OR ACRID IN (‘U172037402731702′,’D254798800365602′,’O152577300452002′,’O152577600462102’,
‘O249489802110302′,’P251776610703402′,’P249489810670402′,’P249489810680502′,’T107800000310′,’T249490300520702′,’T411499000263′))
AND ACNAME!=’1a’)
AND TAB1.PID IN (‘152566403159202′,’152610903160202′,’152622903172402′,’152703503200202′,’153158303221502’,
‘168004703241802’,’173153203272602′,’184980303328402′,’184980903332802′,’185142803340602′,’185848503350402′,
‘185851603365702’,’185851803376802′,’188153303449102′,’188153803450702′,’188154903462302′,’190177403490202′,
‘241273703509800’,’246885303517000′,’246900503523002′,’247310803539602′,’251787803620602′,’251813603659702′,
‘252391203700302’,’255115403877602′,’255370103881002′,’255373403897402′,’255376003905102′,’255834303911102′,
‘57555103980202’,’257555503992102′,’257803904023202′,’258417704030202′,’258448804040302′,’258449104051702′,
‘268091104094802’,’270679304112602′,’271251704122702′,’271800404138802′,’271815004144902′,’271816904159502′,
‘271965804164202’,’273704604172702′,’274590404190202′,’280720104192902′,’284353504233502′,’284354004247602′,
‘284354104251302’,’288819804327802′,’288829704355602′,’289099304367102′,’289786004378302′,’290681304390802′,
‘292272404437202’,’292380904440302′,’292384804458702′,’293129404463602′,’293134104487902′,’293274604507902′,
‘293993404523902’,’294289504549802′,’294693104550102′,’294718104561402′,’294718104575002′,’295007604589402′,
‘295013404607502’,’295722404611702′,’296125204620202′,’296300404648202′,’296716204652302′,’297710904667402′,
‘297856504677102’,’298902304689602′,’300050604709102′,’300051804710602′,’300777204721702′,’308108504766302′,
‘309372804770202’,’309829004782402′,’309831704795702′,’310273804811202′,’310651033655′,’310670034172′,
‘310845304823402’,’311420304851302′,’313402604910202′,’3161604972102′,’316457404983402′,’316887404991902′,
‘316892505007902’,’317174405010702′,’324060005027702′,’324490405030202′,’341348705173702′,’360825605207102′,
‘409755039057’,’477703004325′)
AND STATUS=’01’
ORDER BY TAB1.LMT DESC
) T
) T1
WHERE R>=1 AND R<21; 显然这些错误的本源和当前SQL有关系，而不同的错误信息只是不同的表相而已。根据MOS中记载的信息判断，问题应该是文档ORA-7445 KSMARFG / KGHALF Using PGA_AGGREGATE_TARGET [ID 743951.1]描述的bug。 导致这个问题的原因是9.2.0.3和9.2.0.4版在使用PGA_AGGREGATE_TARGET参数时，如果PGA的大小发生了改变，可能导致PGA内存corruption。解决的办法除了升级到9.2.0.5及以上版本外，采用手工方式设置工作区大小，以及修改问题SQL语句都是相对快速的解决问题的方法。

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

启动数据库出现ORA-27123错误

Posted on 2011/12/13 by yangtingkun

客户的数据库异常关闭后，发现无法启动，报错信息为ORA-27123。
详细的错误信息为：

$ sqlplus USER/password@dbname
SQL * Plus: Release 10.2.0.3.0 – Production ON Fri DEC 9 10:56:32 2011
Copyright (c) 1982, 2006, Oracle. ALL Rights Reserved.
Error:
ORA-01034: ORACLE NOT available
ORA-27123: unable TO attach TO shared memory segment
Linux Error: 22: Invalid argument
Additional information: 1
Additional information: 321169
Enter user-name:

而如果使用oracle用户以/ as sysdba登录，则没有任何的错误。
查询后发现在文档ORA-27123 When Connecting As Non Oracle User [ID 167250.1]描述的正是当前的问题。导致这个问题的原因很简单，由于oracle可执行文件的权限设置存在问题，导致其他用户无法连接oracle数据库创建的共享内存段。
解决方法很简单，对于oracle程序重新授权即可：

$chmod 6755 $ORACLE_HOME/bin/oracle
Posted in ORACLE | Tagged , | Leave a comment

由于472错误实例异常关闭

Posted on 2011/12/12 by yangtingkun

客户数据库实例异常关闭，告警日志出现472错误。
错误信息如下：

Fri DEC 9 10:04:45 2011
USER: terminating instance due TO error 472
Instance TERMINATED BY USER, pid = 4993

Oracle对应的ORA-472错误信息为：

ORA-00472: PMON process TERMINATED WITH error 
Cause: The process cleanup process died
Action: Warm START instance

显然导致数据库实例崩溃的原因是PMON进程异常中止。
查询了一下MOS，发现类似的bug描述：Database Fails With Message User Terminating Instance Due To Error 472 [ID 605280.1]。导致这个问题的原因就是PMON或其他后台进程被第三方工具或用户手工kill掉。
询问客户发现，在问题发生之前，客户确实部署了一个第三方工具，看来问题就是这个工具所致。
对于安装产品数据库的服务器，再安装其他软件或工具时，还是应该小心谨慎一点。

Posted in ORACLE | Tagged , | Leave a comment

限制访问表的FOR UPDATE操作

Posted on 2011/12/11 by yangtingkun

几年以前有朋友问过类似的问题，当时考虑了一下，没有想到好的解决方法，前些天有客户询问同样的问题，没有办法周末仔细琢磨了一下，总算是找到一个解决的方法。
其实现在Oracle有专门的工具可以解决这个问题，就是Oracle的FireWall，通过直连的配置方式可以阻塞预配置好的FOR UPDATE操作，不过那需要单独的软件。这里主要方案是要通过数据库现有的功能实现这个目标。
限制FOR UPDATE是有实际意义的，有时候只希望给用户分配查询权限，但是一旦分配了SELECT权限后，用户就自动拥有了FOR UPDATE能力，虽然用户并没有真正UPDATE的权限，但是仍然可以将表的记录锁定，而这有时候并不是所期望的。

SQL> conn test/test
Connected.
SQL> CREATE TABLE t_update (id NUMBER, name varchar2(30));
TABLE created.
SQL> INSERT INTO t_update VALUES (1, 'a');
1 ROW created.
SQL> commit;
Commit complete.
SQL> CREATE USER u1 IDENTIFIED BY u1; 
USER created.
SQL> GRANT CREATE SESSION TO u1;
GRANT succeeded.
SQL> GRANT SELECT ON t_update TO u1;
GRANT succeeded.

切换到U1用户，现在可以对T_UPDATE进行SELECT FOR UPDATE操作：

SQL> conn u1/u1
Connected.
SQL> SET sqlp 'SQL2> '
SQL2> SELECT * FROM test.t_update WHERE id = 1 FOR UPDATE;
        ID NAME
---------- ------------------------------
         1 a

为了避免FOR UPDATE操作，可以封装一层视图。

SQL> CREATE VIEW v_update AS SELECT * FROM t_update;
VIEW created.
SQL> GRANT SELECT ON v_update TO u1;
GRANT succeeded.

但是如果仅是视图，那么没有任何作用，FOR UPDATE操作同样可以对单表查询的视图执行：

SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE;
        ID NAME
---------- ------------------------------
         1 a

如果添加ROWNUM等伪列，可以避免直接FOR UPDATE：

SQL> CREATE OR REPLACE VIEW v_update AS SELECT rownum rn, a.* FROM t_update a;
VIEW created.

但是如果FOR UPDATE指定ROWNUM伪列外的真实列，还是可以绕开：

SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE; 
SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE
                   *
ERROR at line 1:
ORA-02014: cannot SELECT FOR UPDATE FROM VIEW WITH DISTINCT, GROUP BY, etc.
SQL2> SELECT id, name FROM test.v_update WHERE id = 1 FOR UPDATE;
        ID NAME
---------- ------------------------------
         1 a

通过报错信息可以看出，如果包含了GROUP BY或DISTINCT就可以阻止FOR UPDATE操作：

SQL> CREATE OR REPLACE VIEW v_update AS SELECT DISTINCT * FROM t_update;
VIEW created.

但是这种方式无疑会带来性能问题，更重要的是，如果表中存在重复记录，那么DISTINCT操作会使得重复记录丢失。
而最好的解决方法是采用UNION ALL方式创建视图：

SQL> CREATE OR REPLACE VIEW v_update AS 
2 SELECT * FROM t_update
3 UNION ALL
4 SELECT * FROM t_update WHERE 1 = 2;
VIEW created.

现在就达到了阻止FOR UPDATE的操作，且对于查询基表的性能影响最小：

SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE;
SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE
*
ERROR at line 1:
ORA-02014: cannot SELECT FOR UPDATE FROM VIEW WITH DISTINCT, GROUP BY, etc.

对于不希望用户执行FOR UPDATE操作的表，可以创建成UNION ALL视图，并将视图的查询权限授权给用户。

Posted in ORACLE | Tagged , , | Leave a comment

STATSPACK数据清除（三）

Posted on 2011/12/10 by yangtingkun

Oracle文档推荐的STATSPACK过期数据产生的方法是直接删除STATS$SNAPSHOT表中的记录。
这篇文章说明第一篇文章中没有描述清楚的数据清除问题。
STATSPACK数据清除（一）：http://yangtingkun.itpub.net/post/468/466248
STATSPACK数据清除（二）：http://yangtingkun.itpub.net/post/468/466514
之所以隔了3年才有这篇文章，完全是野花的缘故。野花前一段在折腾STATSPACK是发现了我的文章，并且对于我之前没有得出结论的数据清除问题做出了解答，之所以删除STATS$SNAPSHOT就会导致其他表的数据自动删除，并非是Oracle的什么内部机制，其实道理很简单，就是Oracle建立的是CASCADE ON DELETE约束条件。
看来自己在开发方面还是外行，一直没有想到这个问题，于是特意找到了一个9i上部署STATSPACK的数据库，验证一下：

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, DELETE_RULE
  2  FROM DBA_CONSTRAINTS 
  3  WHERE TABLE_NAME = 'STATS$SNAPSHOT'
  4  AND OWNER = 'PERFSTAT'   
  5  AND CONSTRAINT_TYPE = 'P';
CONSTRAINT_NAME                C STATUS   DELETE_RU
------------------------------ - -------- ---------
STATS$SNAPSHOT_PK              P ENABLED
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, DELETE_RULE
  2  FROM DBA_CONSTRAINTS 
  3  WHERE OWNER = 'PERFSTAT'   
  4  AND CONSTRAINT_TYPE = 'R'
  5  AND R_CONSTRAINT_NAME = 'STATS$SNAPSHOT_PK';
CONSTRAINT_NAME                C STATUS   DELETE_RU
------------------------------ - -------- ---------
STATS$BG_EVENT_SUMMARY_FK      R ENABLED  CASCADE
STATS$BUFFER_POOL_STATS_FK     R ENABLED  CASCADE
STATS$DB_CACHE_ADVICE_FK       R ENABLED  CASCADE
STATS$DLM_MISC_FK              R ENABLED  CASCADE
STATS$ENQUEUE_STAT_FK          R ENABLED  CASCADE
STATS$FILESTATXS_FK            R ENABLED  CASCADE
STATS$INSTANCE_RECOVERY_FK     R ENABLED  CASCADE
STATS$LATCH_CHILDREN_FK        R ENABLED  CASCADE
STATS$LATCH_FK                 R ENABLED  CASCADE
STATS$LATCH_MISSES_SUMMARY_FK  R ENABLED  CASCADE
STATS$LATCH_PARENT_FK          R ENABLED  CASCADE
STATS$LIBRARYCACHE_FK          R ENABLED  CASCADE
STATS$PARAMETER_FK             R ENABLED  CASCADE
STATS$PGA_TARGET_ADVICE_FK     R ENABLED  CASCADE
STATS$RESOURCE_LIMIT_FK        R ENABLED  CASCADE
STATS$ROLLSTAT_FK              R ENABLED  CASCADE
STATS$ROWCACHE_SUMMARY_FK      R ENABLED  CASCADE
STATS$SEG_STAT_FK              R ENABLED  CASCADE
STATS$SESSION_EVENT_FK         R ENABLED  CASCADE
STATS$SESSTAT_FK               R ENABLED  CASCADE
STATS$SGASTAT_FK               R ENABLED  CASCADE
STATS$SGA_FK                   R ENABLED  CASCADE
STATS$SHARED_POOL_ADVICE_FK    R ENABLED  CASCADE
STATS$SQL_PGASTAT_FK           R ENABLED  CASCADE
STATS$SQL_PLAN_USAGE_FK        R ENABLED  CASCADE
STATS$SQL_STATISTICS_FK        R ENABLED  CASCADE
STATS$SQL_SUMMARY_FK           R ENABLED  CASCADE
STATS$SQL_WORKAREA_HIST_FK     R ENABLED  CASCADE
STATS$SYSSTAT_FK               R ENABLED  CASCADE
STATS$SYSTEM_EVENT_FK          R ENABLED  CASCADE
STATS$TEMPSTATXS_FK            R ENABLED  CASCADE
STATS$WAITSTAT_FK              R ENABLED  CASCADE
32 ROWS selected.

显然之所以删除STATS$SNAPSHOT表会导致STATSPACK整体数据被清除，是因为所有的外键都设置了CASCADE ON DELETE。这也是第二篇文章中描述的那些不含SNAP_ID的表没有自动清除的原因。

Posted in ORACLE | Tagged , | Leave a comment

ORA-7445(kglLockIterator)错误

Posted on 2011/12/09 by yangtingkun

在客户的10.2.0.3环境中发现这个错误。
告警日志中错误为：

Mon Sep 20 10:21:04 2010
Errors IN file /home/oracle/admin/ARIC/udump/aric_ora_3776.trc:
ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [4294967377] [] [] []

对应的trace信息中的详细错误为：

*** 2010-09-20 10:21:04.190
*** ACTION NAME:() 2010-09-20 10:21:04.160
*** MODULE NAME:(TOAD 8.6.1.0) 2010-09-20 10:21:04.160
*** SERVICE NAME:(ARIC) 2010-09-20 10:21:04.160
*** SESSION ID:(97.27478) 2010-09-20 10:21:04.160
Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x100000051
*** 2010-09-20 10:21:04.190
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [4294967377] [] [] []
CURRENT SQL statement FOR this SESSION:
SELECT  sid, owner, TYPE, object FROM v$access WHERE  sid = '769' OR  sid = '204' OR  sid = '655' OR  sid = '431'
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+23          ?        0000000000000001     0017B341C 000000000 0062E5A60
                                                   000000000
ksedmp()+636         ?        0000000000000001     0017B1EB1 000000000 00000000B
                                                   000000000
ssexhd()+729         ?        0000000000000001     000E90E7E 000000000 0062E5B90
                                                   000000000
sigsetjmp()+25       ?        0000000000000001     0FDDC00B6 0FFFFFD7F 0062E5B50
                                                   000000000
call_user_handler()  ?        0000000000000001     0FDDB53A2 0FFFFFD7F 0062E5EF0
+589                                               000000000
sigacthandler()+163  ?        0000000000000001     0FDDB5588 0FFFFFD7F 0FF3FB2F0
                                                   0FFFFFD7F
kglLockIterator()+3  ?        0000000000000001     0FFFFFFFF 0FFFFFFFF 00000000B
80                                                 000000000
kqlftl()+194         ?        0000000000000001     001F2A4B7 000000000 000000000
                                                   000000000
qerfxFetch()+4999    ?        0000000000000001     00340E894 000000000 000000000
                                                   000000000
qerjotFetch()+214    ?        0000000000000001     0033BEBA3 000000000 0060B9478
                                                   000000000
qerjotFetch()+280    ?        0000000000000001     0033BEBE5 000000000 0000001F4
                                                   000000000
qerghFetch()+293     ?        0000000000000001     0034C19B2 000000000 0000001F4
                                                   000000000
qervwFetch()+158     ?        0000000000000001     0033BCFBB 000000000 0000001F4
                                                   000000000
opifch2()+2608       ?        0000000000000001     002932E8D 000000000 000000000
                                                   000000000
kpoal8()+3638        ?        0000000000000001     0028CF6BB 000000000 000000000
                                                   000000000
opiodr()+1087        ?        0000000000000001     000E97C5C 000000000 000000000
                                                   000000000
ttcpip()+1165        ?        0000000000000001     003D9F6CA 000000000 005F663F8
                                                   000000000
opitsk()+1278        ?        0000000000000001     000E939C3 000000000 000E97840
                                                   000000000
opiino()+931         ?        0000000000000001     000E96F08 000000000 005F5D840
                                                   000000000
opiodr()+1087        ?        0000000000000001     000E97C5C 000000000 000000000
                                                   000000000
opidrv()+748         ?        0000000000000001     000E924C1 000000000 0FFDFF8C8
                                                   0FFFFFD7F
sou2o()+86           ?        0000000000000001     000E8F8FB 000000000 000000000
                                                   000000000
opimai_real()+127    ?        0000000000000001     000E552D4 000000000 000000000
                                                   000000000
main()+95            ?        0000000000000001     000E551A4 000000000 000000000
                                                   000000000
0000000000E54FE7     ?        0000000000000001     000E54FEC 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

这是一个TOAD客户端在访问动态视图时出现的问题。
这个错误对应的bug信息为：ORA-07445 KGLLOCKITERATOR, _INTEL_FAST_MEMCPY, KGLNAO and Other Errors Selecting From V$ Views [ID 403163.1]，影响版本从9.2.0.1到10.2.0.3。
导致问题的原因是访问了某些参考了X$KGLLK对象的V$动态视图。
当前的SQL访问了V$ACCESS，检查V$FIXED_VIEW_DEFINITION视图：

SQL> SELECT VIEW_DEFINITION
2 FROM V$FIXED_VIEW_DEFINITION
3 WHERE VIEW_NAME = 'V$ACCESS';
VIEW_DEFINITION
-------------------------------------------------------------------------------------------
SELECT SID , OWNER , OBJECT , TYPE FROM GV$ACCESS WHERE inst_id = USERENV('Instance')
SQL> SELECT VIEW_DEFINITION
2 FROM V$FIXED_VIEW_DEFINITION
3 WHERE VIEW_NAME = 'GV$ACCESS';
VIEW_DEFINITION
-------------------------------------------------------------------------------------------
SELECT DISTINCT s.inst_id,s.ksusenum,o.kglnaown,o.kglnaobj,o.kglobtyd FROM x$ksuse s,x$kglob o,x$kgldp d,x$kgllk l WHERE
l.kgllkuse=s.addr AND l.kgllkhdl=d.kglhdadr AND l.kglnahsh=d.kglnahsh AND o.kglnahsh=d.kglrfhsh AND o.kglhdadr=d.kglrfhdl

可以看到，V$ACCESS视图会访问x$kgllk内部视图。
这个bug在11.1和10.2.0.4中被fixed。

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

AWR中出现严重kksfbc child completion等待

Posted on 2011/12/08 by yangtingkun

一个客户压力测试的AWR报告中出现了严重的kksfbc child completion等待。

数据库版本为10.2.0.5，其TOP 5等待事件为：

Event

Waits

Time(s)

Avg
Wait(ms)

%
Total Call Time

Wait
Class

kksfbc
child completion

174,808

8,678

50

27.2

Other

db
file scattered read

106,665

6,807

64

21.3

User
I/O

log
file sync

130,582

6,592

50

20.6

Commit

cursor:
pin S wait on X

552,414

5,982

11

18.7

Concurrency

CPU
time

1,801

5.6

对于kksfbc
child completion事件，还是第一次看到，不过系统中出现这个事件肯定不正常，而且这个事件排在第一位就更说明问题了。

检查MOS后发现，发现这个事件相关的bug远比想象中的多，排除了多个bug后，感觉和文档Processes Hang Waiting on ‘cursor: pin S wait on X'[ID 580273.1]描述的比较类似。

这个问题可能和操作系统上的底层调用有关。而可能影响的Oracle版本从9.2.0.1一直到11.1.0.6。

 

 

Posted in BUG | Tagged , | Leave a comment

ORA-600(17271)错误

Posted on 2011/12/07 by yangtingkun

告警日志中发现了ORA-600(17271)错误。
详细错误信息为：

Fri Mar 6 17:15:37 2009
Errors IN file /home/oracle/admin/ARIC/udump/aric_ora_28118.trc:
ORA-00600: internal error code, arguments: [17271], [instantiation SPACE leak], [], [], [], [], [], []
ORA-03135: connection lost contact

从错误信息上看，问题应该和ORA-3135的连接丢失有关，初步怀疑是由于会话连接丢失导致Oracle在清理会话时出现了异常。
查询了MOS后，果然发现了对应的bug：ORA-600 [17271], [instantiation space leak] During the Session Exit [ID 821603.1]。
导致的原因是在正在退出的会话执行了一个表查询操作，而对于丢失连接的会话可能碰到同样的问题，这个问题影响10.2.0.1到10.2.0.4版本，Oracle在10.2.0.5中修正了这个问题，不过对于Oracle的建议，这个错误并不会造成任何实际的损害，也可以简单的忽略掉。

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

ORA-7445(qerixGetKey)错误

Posted on 2011/12/06 by yangtingkun

执行并行查询时碰到的问题。
错误信息如下：

Wed Sep 28 18:03:47 2011
Errors IN file /home/oracle/admin/ARIC/bdump/aric_p001_8093.trc:
ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] []
Wed Sep 28 18:03:47 2011
Errors IN file /home/oracle/admin/ARIC/bdump/aric_p000_8091.trc:
ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] []

对应的TRACE文件信息如下：

*** 2011-09-28 18:03:47.537
*** SERVICE NAME:(SYS$USERS) 2011-09-28 18:03:47.530
*** SESSION ID:(82.818) 2011-09-28 18:03:47.530
Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x15
*** 2011-09-28 18:03:47.537
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] []
CURRENT SQL statement FOR this SESSION:
SELECT /*+parallel (D_ITEMS_SCD,2)*/
  ( D_STR.ID_STR )||' / '||( D_STR.DS_STR_CN ),
.
.
.
  D_STR_ITEMS_CUR.LAST_RECPT_DATE
FROM
  D_STR,
  D_DIV_SCD,
  D_SEC_SCD,
  D_GRP_SCD,
  D_SGR_SCD,
  D_ITEMS_SCD,
  D_STR_ITEMS_CUR
WHERE
  ( D_DIV_SCD.SID_DIV_H=D_SEC_SCD.SID_DIV_H  )
.
.
.
  )
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+23          ?        0000000000000001     0017B341C 000000000 0062E5A60
                                                   000000000
ksedmp()+636         ?        0000000000000001     0017B1EB1 000000000 00000000B
                                                   000000000
ssexhd()+729         ?        0000000000000001     000E90E7E 000000000 0062E5B90
                                                   000000000
sigsetjmp()+25       ?        0000000000000001     0FC7C00B6 0FFFFFD7F 0062E5B50
                                                   000000000
call_user_handler()  ?        0000000000000001     0FC7B53A2 0FFFFFD7F 0062E5EF0
+589                                               000000000
sigacthandler()+163  ?        0000000000000001     0FC7B5588 0FFFFFD7F 0FDDFB2F0
                                                   0FFFFFD7F
qerixGetKey()+922    ?        0000000000000001     0FFFFFFFF 0FFFFFFFF 00000000B
                                                   000000000
qerbxStart()+1858    ?        0000000000000001     0033E49D7 000000000 0FC5A2930
                                                   0FFFFFD7F
qerbtStart()+800     ?        0000000000000001     0033DE2BD 000000000 005414760
                                                   000000000
qergiStart()+833     ?        0000000000000001     00347718E 000000000 0060B9478
                                                   000000000
qerjoFetch()+668     ?        0000000000000001     0033BE169 000000000 000007FFF
                                                   000000000
qerpfFetch()+484     ?        0000000000000001     00347F491 000000000 088439128
                                                   000000003
qertbFetchByRowID()  ?        0000000000000001     0033C4451 000000000 088439128
+660                                               000000003
.
.
.
============
Plan TABLE
============
---------------------------------------------------------------------------+----------------
| Id  | Operation                                | Name                     | ROWS  | Cost  |
---------------------------------------------------------------------------+----------------
| 0   | SELECT STATEMENT                         |                          |       |   68K |
| 1   |  PX COORDINATOR                          |                          |       |       |
| 2   |   PX SEND QC (RANDOM)                    | :TQ10003                 |  2497 |   68K |
| 3   |    HASH JOIN                             |                          |  2497 |   68K |
| 4   |     BUFFER SORT                          |                          |       |       |
| 5   |      PX RECEIVE                          |                          |     1 |     4 |
| 6   |       PX SEND BROADCAST                  | :TQ10000                 |     1 |     4 |
| 7   |        TABLE ACCESS FULL                 | D_STR                    |     1 |     4 |
| 8   |     HASH JOIN                            |                          |  236K |   68K |
| 9   |      TABLE ACCESS BY INDEX ROWID         | D_ITEMS_SCD              |  1377 |   10K |
| 10  |       NESTED LOOPS                       |                          |   42K |   10K |
| 11  |        BUFFER SORT                       |                          |       |       |
| 12  |         PX RECEIVE                       |                          |       |       |
| 13  |          PX SEND BROADCAST               | :TQ10001                 |       |       |
| 14  |           HASH JOIN                      |                          |    32 |    20 |
| 15  |            HASH JOIN                     |                          |     4 |    10 |
| 16  |             MERGE JOIN                   |                          |     1 |     6 |
| 17  |              TABLE ACCESS BY INDEX ROWID | D_DIV_SCD                |     1 |     2 |
| 18  |               INDEX FULL SCAN            | PK_D_DIV_SCD             |     8 |     1 |
| 19  |              SORT JOIN                   |                          |    52 |     4 |
| 20  |               TABLE ACCESS FULL          | D_SEC_SCD                |    52 |     3 |
| 21  |             TABLE ACCESS FULL            | D_GRP_SCD                |   489 |     3 |
| 22  |            TABLE ACCESS FULL             | D_SGR_SCD                |  3752 |    10 |
| 23  |        PX BLOCK ITERATOR                 |                          |       |       |
| 24  |         BITMAP CONVERSION TO ROWIDS      |                          |       |       |
| 25  |          BITMAP INDEX SINGLE VALUE       | BIX_D_ITEMS_SCD_SID_SGR_H|       |       |
| 26  |      BUFFER SORT                         |                          |       |       |
| 27  |       PX RECEIVE                         |                          | 9548K |   58K |
| 28  |        PX SEND BROADCAST                 | :TQ10002                 | 9548K |   58K |
| 29  |         TABLE ACCESS FULL                | D_STR_ITEMS_CUR          | 9548K |   58K |
---------------------------------------------------------------------------+----------------

这里去掉了一些不必要的内容。从SQL语句中的HINT以及执行计划都可以看到，这是一个平行SQL，当然报错进程p000和p001也说明了这一点。
查询MOS发现是Bug 5113934 – PQ slave dump in qerixGetKey() when using bitmap index access [ID 5113934.8]，而当前的执行计划中确实出现了BITMAP索引。
这个bug确认影响10.2.0.3，而这正是当前的版本。这个bug在10.2.0.4和11.1.0.6中被fixed。除了打补丁外，禁止并行或星形查询同样可以避免这个错误，不过无论禁止并行还是星形查询，都会牺牲一定的性能。

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