【问题诊断】Oracle 11g ORA-7445 [opiaba] 和 ORA-600 [17147]错误案例分享

集群环境：
srvctl status database -d ywzd2 -v
Instance ywzd21 is running on node ywzd-db03. Instance status: Open.
Instance ywzd22 is running on node ywzd-db04. Instance status: Open.


单机环境：
sqlplus  as sysdba
SQL> select startup_time,status from v$instance;
STARTUP_TIME        STATUS
----------------------- ------------
06-DEC-2023 12:29:59    OPEN

第一段日志出现ORA-07445[opiaba()+639]错误
Wed Dec 06 12:29:21 2023
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x185806F, opiaba()+639] [flags: 0x0, count: 1]
Errors in file u01/app/oracle/diag/rdbms/ywzd2/ywzd21/trace/ywzd21_ora_538783.trc  (incident=418676):
ORA-07445: exception encountered: core dump [opiaba()+639] [SIGSEGV] [ADDR:0x0] [PC:0x185806F] [SI_KERNEL(general_protection)] []
Incident details in: u01/app/oracle/diag/rdbms/ywzd2/ywzd21/incident/incdir_418676/ywzd21_ora_538783_i418676.trc
第二段日志出现ORA-00600[17147]错误
Wed Dec 06 12:29:47 2023
Errors in file u01/app/oracle/diag/rdbms/ywzd2/ywzd21/trace/ywzd21_pmon_512337.trc  (incident=411292):
ORA-00600: internal error code, arguments: [17147], [0x14DAE09578], [], [], [], [], [], [], [], [], [], []
Incident details in: u01/app/oracle/diag/rdbms/ywzd2/ywzd21/incident/incdir_411292/ywzd21_pmon_512337_i411292.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Dec 06 12:29:48 2023
Dumping diagnostic data in directory=[cdmp_20231206122948], requested by (instance=1, osid=512337 (PMON)), summary=[incident=411292].
Errors in file u01/app/oracle/diag/rdbms/ywzd2/ywzd21/trace/ywzd21_pmon_512337.trc:
ORA-00600: internal error code, arguments: [17147], [0x14DAE09578], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 512337): terminating the instance due to error 472
System state dump requested by (instance=1, osid=512337 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file u01/app/oracle/diag/rdbms/ywzd2/ywzd21/trace/ywzd21_diag_512357_20231206122952.trc
Instance terminated by PMON, pid = 512337
第三段日志集群实例自动重启
Wed Dec 06 12:29:59 2023
Starting ORACLE instance (normal)


批量检查命令：
grep -ib1E 'opiaba|\[17147|terminating the instance due to error 472' /u01/app/oracle/diag/rdbms/ywzd2/ywzd21/trace/alert_ywzd21.log

========= Dump for incident 328879 (ORA 600 [17147]) ========
----- Beginning of Customized Incident Dump(s) -----
********** Internal heap ERROR 17147 addr=0x12b0accc78 *********
...


dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=btng01wnhnvnw) -----
begin


update 业务表1
set 字段1 = :1 ,
字段2 = :2 ,
字段3 = :3 ,
字段4 = :4 ,
字段5 = :5 ,
字段6 = :6 ,
字段7 = '1'
where 字段8 = :7
and 字段9 = :8
and 字段10 = :9 ;


....（sql 太长，部分省略）...


update 业务表N
set 字段1 = :100381 ,
字段2 = :100382 ,
字段3 = :100383
where 字段4 = :100384
and 字段5 = :100385
and 字段6 = :100386 ;
end;

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
...


----- Incident Context Dump -----
Address: 0x7fff22dbad78
Incident ID: 327263
Problem Key: ORA 600 [17147]
Error: ORA-600 [17147] [0x12B0ACCC78] [] [] [] [] [] [] [] [] [] []
[00]: dbgexExplicitEndInc [diag_dde]
[01]: dbgeEndDDEInvocationImpl [diag_dde]
[02]: dbgeEndDDEInvocation [diag_dde]
[03]: kgherror [KGH]<-- Signaling
[04]: kghfrmrg [KGH]
[05]: kghfre [KGH]
[06]: kghsfx [KGH]
[07]: kghunfhp [KGH]
[08]: kghfrh_internal [KGH]
[09]: kghfrh [KGH]
[10]: kksFreeHeap [cursor]
[11]: kksLockDelete [cursor]
[12]: kksLockOperation [cursor]
[13]: kgllkdl [LIBCACHE]
[14]: kgllkds [LIBCACHE]
[15]: kssxdl [VOS]
[16]: kssdel [VOS]
[17]: kssdch [VOS]
[18]: ksuxds [ksu]
[19]: kssxdl [VOS]
[20]: kssdel [VOS]
[21]: kssdch [VOS]
[22]: ksudlp [ksu]
[23]: kssxdl [VOS]
[24]: kssdel [VOS]
[25]: ksuxdl [ksu]
[26]: ksuxda_del_proc [ksu]
[27]: ksucln_dpc_cleanup [ksu]
[28]: ksucln_dpc_dfs [ksu]
[29]: ksucln_dpc_main [ksu]
[30]: ksucln_dpc [ksu]
[31]: ksucln [ksu]
[32]: ksbrdp [background_proc]
[33]: opirip []

select sql_id, count(*) bind_count
  from v$sql_bind_capture t
 where t.CHILD_NUMBER = 0
 group by t.SQL_ID
having count(*) > 1000
 order by count(*);

-------------堆栈调用函数-------------------
kgdsdst   
skdstdst                operating system dependent kernel dump a stack trace
ksedst1                 kernel service (VOS) error debug dump the call stack stack trace 1 helper
ksedst                  kernel service (VOS) error debug dump the call stack
dbkedDefDump            debug and diagnostics RDBMS diagnostic data extractor Dumper do DDE default dump
ksedmp                  kernel service (VOS) error debug dump process state
ssexhd                  operating system dependent system dump routine
__sighandler        
opiaba
opiprs                  oracle program interface parse
kksParseChildCursor     kernel compile shared objects (cursor) parse child cursor
rpiswu2                 recursive program interface switch user in recursive sql
kksLoadChild            kernel compile shared objects (cursor) load child
kxsGetRuntimeLock       kernel execution shared cursor get runtime lock
kksfbc                  kernel compile shared objects (cursor) find bound cursor
kkspsc0                 kernel compile shared objects (cursor) parse shared cursor
kksParseCursor          kernel compile shared objects (cursor) parse cursor
opiosq0                 oracle program interface prepare to parse a sql command 0
kpooprx                 kernel programmatic interface oracle open, parse, and optionally execute
kpoal8                  kernel programmatic interface oracle V8 bundled execution
opiodr                  oracle program interface oracle code request driver, route the current request
ttcpip                  two task common pipe read/write
opitsk                  oracle program interface two task function dispatcher
opiino                  oracle program interface initialize opi
opiodr 
opidrv                  oracle program interface route current request driver, entry side into two task interface
sou2o                   main oracle executable entry point
opimai_real             oracle program interface main real oracle start point
ssthrdmain              operating system dependent system main for every thread in a threaded oracle
main                    general c starting function

1、创建测试表
create table test01 as select * from dba_tables;
2、修改共享游标参数值为SIMILAR
alter session set CURSOR_SHARING=SIMILAR; 
3、第一次执行语句1
select table_name from test01 where table_name='OBJ$';
4、查看语句1的SQL_ID和执行次数
set linesize 1000
col sql_text for a80
select sql_id,EXECUTIONS,LOADS,sql_text from v$sql 
where sql_text like 'select table_name from test01 where table_name%';
SQL_ID          EXECUTIONS      LOADS SQL_TEXT
------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
9dhppyxtr9qck           1      1 select table_name from test01 where table_name=:"SYS_B_0"
5、通过SQL_ID获取语句1的绑定变量值
col value_string for a30
select child_address,to_char(LAST_CAPTURED,'dd-mm-yyyy hh24:mi:ss'),VALUE_STRING 
from v$sql_bind_capture where sql_id='9dhppyxtr9qck';
CHILD_ADDRESS     TO_CHAR(LAST_CAPTUR VALUE_STRING
---------------- ------------------- ------------------------------
0000000068BD8220 17-03-2024 20:32:52 OBJ$
6、查看参数_cursor_bind_capture_interval的值为900秒，默认15分钟
set linesize 1000
col name for a50
col value for a30
select nam.ksppinm NAME,val.KSPPSTVL VALUE 
from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx 
and nam.ksppinm like '%_cursor_bind_capture_interval%' 
order by 1;
7、修改动态参数为10秒捕获一次
alter system set "_cursor_bind_capture_interval"=10;
8、再次执行语句1，参数值不一样，传入参数须为4个字节，避免硬解析
select table_name from test01 where table_name='TAB$';
9、再次查询绑定变值，已从COL$变为TAB$
CHILD_ADDRESS     TO_CHAR(LAST_CAPTUR VALUE_STRING
---------------- ------------------- ------------------------------
0000000068BD8220 17-03-2024 20:35:56 TAB$