11.2.0.1bug引發的報錯：ORA-07445: exception encountered

DBA_每日記發表於2019-10-21
Exception

11.2.0.1bug引發的報錯：ORA-07445: exception encountered

問題背景：客戶反饋DB每天產生的incident日誌很多，需要排查原因


1> 檢視alert日誌發現大量的ORA-07445、ORA-00600錯誤

Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc  (incident=111052):

ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x2278EA1, kghstack_err()+85] [flags: 0x0, count: 1]

Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc  (incident=111053):

ORA-07445: exception encountered: core dump [kghstack_err()+85] [SIGSEGV] [ADDR:0x0] [PC:0x2278EA1] [SI_KERNEL(general_protection)] []

ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []

Incident details in: /data/oracle/diag/rdbms/bydata/bydata/incident/incdir_111053/bydata_mmon_3667_i111053.trc

Mon Oct 21 09:50:30 2019


一般情況下，ORA-600被證明為oracle的內部錯誤，通常由資料檔案的壞塊或者oracle的bug引起

首先檢視是否有資料檔案壞塊,

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@OA_oracle incident]$ dbv file=/data/oracle/oradata/oradb/tablespace/oradb_DATA.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on Mon Oct 21 10:25:28 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /data/oracle/oradata/oradb/tablespace/oradb_DATA.DBF

DBVERIFY - Verification complete

Total Pages Examined         : 57600

Total Pages Processed (Data) : 20706

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 11725

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 13062

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 12107

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 296071350 (0.296071350)

檢視所有的資料檔案均未發現壞塊現象


2> 檢視 trace裡有大量的SQL引用多個left outer join

檢視mos和BUG 9050716比較匹配，當前SQL引用多個left outer join，在11.2.0.1.0版本，外關聯存在多個BUG，

建議禁用_optimizer_join_elimination_enabled引數問題解決


檢視隱含引數的語句

SELECT   ksppinm, ksppstvl, ksppdesc  FROM   x$ksppi x, x$ksppcv y   WHERE   x.indx = y.indx AND  ksppinm like '_optimizer%';


SQL> SELECT   ksppinm, ksppstvl, ksppdesc  FROM   x$ksppi x, x$ksppcv y   WHERE   x.indx = y.indx AND  ksppinm like '_optimizer_join%';


KSPPINM                             KSPPSTVL                  KSPPDESC

----------------------------------- ------------------------- ---------------------------------------------------------------------------

_optimizer_join_sel_sanity_check    TRUE                      enable/disable sanity check for multi-column join selectivity

_optimizer_join_order_control       3                         controls the optimizer join order search algorithm

_optimizer_join_elimination_enabled TRUE                      optimizer join elimination enabled

_optimizer_join_factorization       TRUE                      use join factorization transformation


修改隱含引數_optimizer_join_elimination_enabled

alter system set "_optimizer_join_elimination_enabled" =false scope=both;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69950231/viewspace-2660847/，如需轉載，請註明出處，否則將追究法律責任。

相關文章