11G RAC ORA-32701
节点1:
Wed Feb 13 16:08:06 2019
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_dia0_9267.trc (incident=1248083):
ORA-32701: Possible hangs up to hang ID=4 detected
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb1/incident/incdir_1248083/testdb1_dia0_9267_i1248083.trc
DIA0 requesting termination of session sid:5190 with serial # 42237 (ospid:180727) on instance 2
due to a GLOBAL, HIGH confidence hang with ID=4.
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
DIA0: Examine the alert log on instance 2 for session termination status of hang with ID=4.
Wed Feb 13 16:08:08 2019
Sweep [inc][1248083]: completed
Sweep [inc2][1248083]: completed
Wed Feb 13 16:09:41 2019
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_dia0_9267.trc (incident=1248084):
ORA-32701: Possible hangs up to hang ID=4 detected
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb1/incident/incdir_1248084/testdb1_dia0_9267_i1248084.trc
DIA0 requesting termination of process sid:5190 with serial # 42237 (ospid:180727) on instance 2
due to a GLOBAL, HIGH confidence hang with ID=4.
Previous SESSION termination failed.
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
DIA0: Examine the alert log on instance 2 for process termination status of hang with ID=4.
[oracle@testdb1 trace]$ more /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_dia0_9267.trc
Trace file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_dia0_9267.trc
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Incident 1248083 created, dump file: /u01/app/oracle/diag/rdbms/testdb/testdb1/incident/incdir_1248083/testdb1_dia0_9267_i1248083.trc
ORA-32701: Possible hangs up to hang ID=4 detected
Incident 1248084 created, dump file: /u01/app/oracle/diag/rdbms/testdb/testdb1/incident/incdir_1248084/testdb1_dia0_9267_i1248084.trc
ORA-32701: Possible hangs up to hang ID=4 detected
节点2:
Wed Feb 13 16:09:41 2019
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_dia0_7404.trc (incident=1008107):
ORA-32701: Possible hangs up to hang ID=4 detected
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb2/incident/incdir_1008107/testdb2_dia0_7404_i1008107.trc
DIA0 terminating blocker (ospid: 180727 sid: 5190 ser#: 42237) of hang with ID = 4
[oracle@testdb2 trace]$ more /u01/app/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_dia0_7404.trc
Trace file /u01/app/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_dia0_7404.trc
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Incident 1008106 created, dump file: /u01/app/oracle/diag/rdbms/testdb/testdb2/incident/incdir_1008106/testdb2_dia0_7404_i1008106.trc
ORA-32701: Possible hangs up to hang ID=4 detected
Incident 1008107 created, dump file: /u01/app/oracle/diag/rdbms/testdb/testdb2/incident/incdir_1008107/testdb2_dia0_7404_i1008107.trc
ORA-32701: Possible hangs up to hang ID=4 detected
等待事件
inst# SessId Ser# OSPID PrcNm Event
----- ------ ----- --------- ----- -----
1 6746 23425 37352 M000 enq: WF - contention
2 5190 42237 180727 M000 not in wait
inst# SessId Ser# OSPID PrcNm Event
----- ------ ----- --------- ----- -----
1 6746 23425 37352 M000 enq: WF - contention
2 5190 42237 180727 M000 not in wait
---解决办法
-----MOS 上文章 2226216.1
1. Collect statistics on following fixed table:
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
PL/SQL procedure successfully completed.
Or
2. Restarting the database will release of X$KQLFBC table data
Or
3. Flush shared_pool on a regular basis
*** 2019-02-14 06:25:08.352
current sql: insert into wrh$_sql_bind_metadata (snap_id, dbid, sql_id, name, position, dup_position, datatype, datatype_string, characte
r_sid, precision, scale, max_length) SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */ :lah_snap_id, :dbid, bnd.sql_id, name, position,
dup_position, datatype, dataty
---处理方法
exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
--立马生效
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRATTRNEW',no_invalidate => FALSE);
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRSQLIDTAB',no_invalidate => FALSE);
---定时任务
# flush shared_pool 每个月执行一次
33 02 15 * * /bin/sh /home/oracle/flush_shared_pool/flush_shared_pool.sh &> /dev/null
[oracle@testdb2 ~]$ cat /home/oracle/flush_shared_pool/flush_shared_pool.sh
#!/bin/bash
source /home/oracle/.bash_profile
sqlplus / as sysdba >> /home/oracle/flush_shared_pool/exec_shared_pool.log <<EOF
set timing on;
@/home/oracle/flush_shared_pool/flush_shared_pool.sql
EOF
[oracle@testdb2 ~]$ cat /home/oracle/flush_shared_pool/flush_shared_pool.sql
alter system flush shared_pool;
---还有可能是死锁引起的故障
1.业务查询程序死锁问题
2.执行刷新shared_pool--两个节点都需要执行
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
alter system flush shared_pool;
--查询2个节点基表信息
select count(*) from x$ksmsp;
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRATTRNEW',no_invalidate => FALSE);
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KEWRSQLIDTAB',no_invalidate => FALSE);
--绝招
alter system set "_awr_disabled_flush_tables" = 'wrh$_sql_bind_metadata';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤