偶遇ORA-07445内部错误小记

     今天生产库的alert日志报了如下错误：
ORA-07445: exception encountered: core dump [kkqjfAdjSelOrd()+283] [SIGSEGV] [ADDR:0x8] [PC:0x2457FE3] [Address not mapped to object] []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


查询ORA-07445原因
 oerr ora 07445
07445, 00000, "exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
// *Cause: An operating system exception occurred which should result in the 
//         creation of a core file.  This is an internal error.
// *Action: Visit My Oracle Support to access the ORA-07445 Lookup tool
//          (reference Note 7445.1) for more information regarding the specific
//          ORA-07445 error encountered.
//
//          An Incident has been created for this error in the Automatic 
//          Diagnostic Repository (ADR). When logging a service request, use 
//          the Incident Packaging Service (IPS) from the Support Workbench
//          or the ADR Command Interpreter (ADRCI) to automatically package the
//          relevant trace information (reference My Oracle Support Note 411.1).
//
//          The following information should also be gathered to help determine 
//          the root cause:
//             - changes leading up to the error
//             - events or unusual circumstances leading up to the error
//             - operations attempted prior to the error
//             - conditions of the operating system and databases at the time 
//               of the error
//
//          Note: The cause of this message may manifest itself as different 
//          errors at different times. Be aware of the history of errors that 
//          occurred before this internal error.

在alter中提示的trace文件中找到如下类似的一条sql：
select a.Ucode
  from hr.arg a, hr.argstd b
 where a.unitid = b.unitid
   and b.label like '00000007%'
   AND b.isunit <> 2
   and a.UNITGE = '80_10'
union all
select a.Ucode
  from hr.arg a, hr.orgstd b
 where a.unitid = b.unitid
   and b.label like '00000007%'
   AND b.isunit <> 2
   and a.UNITGE = '80_15'
   and a.BCODE is null
   and a.CODE is not null

这条sql我直接放到测试库中想查看执行计划，直接看不了报ORA-07445,分开执行两个子查询是没有问题的，带着试试看的心屏蔽了第二个查询的AND b.isunit <> 2条件，再来执行就OK了，猜测可能跟这个isunit字段有关系，但是查询该字段对应的表怎么查都没有问题，于是改成AND b.isunit > 2 and AND b.isunit < 2再来执行上面的查询就好了。此事很是怪异，MOS及网上都说ORA-07445为bug所致，还有说是内存摔坏导致的，此次问题虽然可以通过修改sql绕过错误，但是为什么改了sql就好了这点我还是没有想明白，以后再想了。。

总结：就算ORA-07445是Bug导致，但是通过修改sql如果可以避免问题也不失为一个比较好的方法，bug升级带来的不确定因素也是蛮多的得小心谨慎，谨以此记录

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28473562/viewspace-1817166/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28473562/viewspace-1817166/