11.2.0.1bug引发的报错：ORA-07445: exception encountered
问题背景：客户反馈DB每天产生的incident日志很多，需要排查原因
1> 查看alert日志发现大量的ORA-07445、ORA-00600错误
1 Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc (incident=111052):
2
3 ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
4
5 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x2278EA1, kghstack_err()+85] [flags: 0x0, count: 1]
6
7 Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc (incident=111053):
8
9 ORA-07445: exception encountered: core dump [kghstack_err()+85] [SIGSEGV] [ADDR:0x0] [PC:0x2278EA1] [SI_KERNEL(general_protection)] []
10
11 ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
12
13 Incident details in: /data/oracle/diag/rdbms/bydata/bydata/incident/incdir_111053/bydata_mmon_3667_i111053.trc
14
15 Mon Oct 21 09:50:30 2019</pre>
一般情况下，ORA-600被证明为oracle的内部错误，通常由数据文件的坏块或者oracle的bug引起
首先查看是否有数据文件坏块,
1 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2
3 With the Partitioning, OLAP, Data Mining and Real Application Testing options
4
5 [oracle@OA_oracle incident]$ dbv file=/data/oracle/oradata/oradb/tablespace/oradb_DATA.DBF
6
7 DBVERIFY: Release 11.2.0.1.0 - Production on Mon Oct 21 10:25:28 2019
8
9 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
10
11 DBVERIFY - Verification starting : FILE = /data/oracle/oradata/oradb/tablespace/oradb_DATA.DBF
12
13 DBVERIFY - Verification complete
14
15 Total Pages Examined : 57600
16
17 Total Pages Processed (Data) : 20706
18
19 Total Pages Failing (Data) : 0
20
21 Total Pages Processed (Index): 11725
22
23 Total Pages Failing (Index): 0
24
25 Total Pages Processed (Other): 13062
26
27 Total Pages Processed (Seg) : 0
28
29 Total Pages Failing (Seg) : 0
30
31 Total Pages Empty : 12107
32
33 Total Pages Marked Corrupt : 0
34
35 Total Pages Influx : 0
36
37 Total Pages Encrypted : 0
38
39 Highest block SCN : 296071350 (0.296071350)</pre>
查看所有的数据文件均未发现坏块现象
2> 查看trace里有大量的SQL引用多个left outer join
查看mos和BUG 9050716比较匹配，当前SQL引用多个left outer join，在11.2.0.1.0版本，外关联存在多个BUG，
建议禁用_optimizer_join_elimination_enabled参数问题解决
查看隐含参数的语句
1 SELECT ksppinm, ksppstvl, ksppdesc FROM xksppcv y WHERE x.indx = y.indx AND ksppinm like '_optimizer%';
2
3 SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM xksppcv y WHERE x.indx = y.indx AND ksppinm like '_optimizer_join%';
4
5
6
7 KSPPINM KSPPSTVL KSPPDESC
8
9 ----------------------------------- ------------------------- ---------------------------------------------------------------------------
10
11 _optimizer_join_sel_sanity_check TRUE enable/disable sanity check for multi-column join selectivity
12
13 _optimizer_join_order_control 3 controls the optimizer join order search algorithm
14
15 _optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
16
17 _optimizer_join_factorization TRUE use join factorization transformation
18
19
20
21 修改隐含参数_optimizer_join_elimination_enabled
22
23 alter system set "_optimizer_join_elimination_enabled" =false scope=both;</pre>
