12.2.0.1 Oracle2节点RAC环境,在节点2上执行Insert卡住,SQL语句:
insert into ab07 select * from ab07@zjk where yae519='201723100034302533';
对该会话进行oradebug 10046
SQL> oradebug setorapid 1808
Oracle pid: 1808, Unix process pid: 51259, image: oracle@dc2
SQL> oradebug tracefile_name
/oracle/diag/rdbms/cdbdc/cdbdc2/trace/cdbdc2_ora_51259.trc
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL>
Trc文件存在DFS lock handle和enq: TX - row lock contention等待,详见附件:cdbdc2_ora_51259.trc
WAIT #140262258004704: nam='DFS lock handle' ela= 217 type|mode=1413545989 id1=3 id2=13 obj#=0 tim=46212871222
WAIT #140262258004704: nam='transaction' ela= 3 undo seg#|slot#=851998 wrap#=501273 count=45573 obj#=0 tim=46212871258
说明:
(1)、数据库隐含参数_clusterwide_global_transactions已设置为false的。
(2)、AB07表2500W数据,单独执行后面select * from ab07@zjk where yae519='201723100034302533';很快
麻烦分析下为什么在2节点执行insert语句卡住,在1节点执行该INSERT语句正常。
后面再次出现该insert语句卡住情况,情况应该和上面一样,Alter日志出现ORA-07445错误:
2019-09-23T12:30:23.147709+08:00
GXSIPDB2(5):Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x34] [PC:0x2F04681, kauXFinish()+1505] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/cdbdc/cdbdc1/trace/cdbdc1_j004_155744.trc (incident=3287519) (PDBNAME=GXSIPDB2):
ORA-07445: 出现异常错误: 核心转储 [kauXFinish()+1505] [SIGSEGV] [ADDR:0x34] [PC:0x2F04681] [Address not mapped to object] []
GXSIPDB2(5):Incident details in: /oracle/diag/rdbms/cdbdc/cdbdc1/incident/incdir_3287519/cdbdc1_j004_155744_i3287519.trc
GXSIPDB2(5):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2019-09-23T12:30:25.033796+08:00
cdbdc1_j004_155744_i3287519.trc文件详见附件