告警日志中报错ORA-07445 kkqstcrf

jiawang

问题描述：数据库例行巡检时发现告警日志中报错ORA-07445 kkqstcrf，如下所示：

数据库：oracle 11.2.0.1

告警日志：

Mon Aug 05 22:00:22 2024

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x71] [PC:0x1CBAD19, kkqstcrf()+1355] [flags: 0x0, count: 1]

Errors in file /u01/app/oracle/diag/rdbms/jhemrdb/jhemr/trace/jhemr_j004_16801.trc  (incident=73575):

ORA-07445: exception encountered: core dump [kkqstcrf()+1355] [SIGSEGV] [ADDR:0x71] [PC:0x1CBAD19] [Address not mapped to object] []

Incident details in: /u01/app/oracle/diag/rdbms/jhemrdb/jhemr/incident/incdir_73575/jhemr_j004_16801_i73575.trc

异常分析：

ora-07445告警频率：每隔4小时报警一次，每天早上6点直到22点；根据MOS [ID 977299.1]文档，此异常与BUG 9050716匹配.

从incident文件可以找到触发该告警的sql语句.

解决方案：

--以下列出两个解决方案：

a、禁用隐藏参数”_optimizer_join_elimination_enabled”，语句如下：

alter system set “_optimizer_join_elimination_enabled”=false scope=both;

如下语句查询该隐含参数值：

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description复制

  from x$ksppi a,x$ksppcv b

where a.indx=b.indx

   and a.ksppinm='_optimizer_join_elimination_enabled';

b、将数据库从11.2.0.1升级到11.2.0.4