11.2.0.1bug引发的报错：ORA-07445: exception encountered

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;