环境
2节点的oracle一体机
数据库版本
14:28:06 sys@WMS>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
环境是一套11.2.0.4 2nodes RAC on hpux-ia31, alert中出现ora-32701 hangmgr错误, 从trace文件中发现是m000进程是mmon的辅助进程,用于flush AWR相关数据,有一个wait event: enq: WF – contention, 这也是flush AWR数据时相关的enqueue等待,但是blocker进程是not in wait, (另mmon hang是可以直接kill 该进程spid, 通常会在不重启实例的前掉下重启该进程)。 这里简单的记录该问题
1,alter报错
LNS: Standby redo logfile selected for thread 2 sequence 53492 for destination LOG_ARCHIVE_DEST_2
Wed Sep 06 12:51:03 2017
Archived Log entry 97033 added for thread 2 sequence 53491 ID 0x172b5599 dest 1:
Wed Sep 06 12:53:01 2017
Errors in file /u01/app/oracle/diag/rdbms/wmsdb/wmsdb2/trace/wmsdb2_dia0_1035.trc (incident=96089):
ORA-32701: Possible hangs up to hang ID=69 detected
Incident details in: /u01/app/oracle/diag/rdbms/wmsdb/wmsdb2/incident/incdir_96089/wmsdb2_dia0_1035_i96089.trc
DIA0 terminating blocker (ospid: 276865 sid: 1610 ser#: 15433) of hang with ID = 69
requested by master DIA0 process on instance 1
Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.
by terminating session sid:1610 with serial # 15433 (ospid:276865)
Wed Sep 06 12:53:03 2017
Sweep [inc][96089]: completed
Sweep [inc2][96089]: completed
DIA0 successfully terminated session sid:1610 with serial # 15433 (ospid:276865) with status 31.
Wed Sep 06 12:54:34 2017
LGWR: Standby redo logfile selected for thread 2 sequence 53493 for destination LOG_ARCHIVE_DEST_3
Thread 2 advanced to log sequence 53493 (LGWR switch)
2,trace报错
inst# SessId Ser# OSPID PrcNm Event
----- ------ ----- --------- ----- -----
1 400 11049 102437 M000 enq: WF - contention
2 1610 15433 276865 M000 not in wait
----------------------------------------
SO: 0x3c426c0be8, type: 4, owner: 0x3ca22c6ae8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x3ca22c6ae8, name=session, file=ksu.h LINE:12729, pg=0
(session) sid: 1610 ser: 15433 trans: (nil), creator: 0x3ca22c6ae8
flags: (0x8100051) USR/- flags_idl: (0x9) BSY/-/-/-/KIL/-
flags2: (0x40409) -/-/INC
DID: , short-term DID:
txn branch: (nil)
edition#: 100 oct: 2, prv: 0, sql: 0x39ded8dfd8, psql: 0x397ab53378, user: 0/SYS
ksuxds FALSE at location: 0
Cleanup details:
Marked killed = 1 min 34 sec ago
Total Cleanup attempts = 0, Cleanup time = 0 sec, Cleanup timer = 0.000 sec
service name: SYS$BACKGROUND
Current Wait Stack:
Not in wait; last wait ended 9 min 27 sec ago
There are 1 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 400, ser: 11049
wait event: 'enq: WF - contention'
p1: 'name|mode'=0x57460006
p2: '0'=0x46
p3: '0'=0x0
row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0
min_blocked_time: 551 secs, waiter_cache_ver: 15671
*** 2017-09-06 12:54:36.098
current sql: insert into wrh$_sql_bind_metadata (snap_id, dbid, sql_id, name, position, dup_position, datatype, datatype_string, character_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
----------------------------------------
CAUSE
View v$sqlbind_capture corresponds to fixed table X$KQLFBC table which is mainly used to store variables associated with the binding of data. This error can be noticed in large databases using large amount of binding variables.
SOLUTION
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
详情查看文档
Doc ID 2226216.1
还有一种解决方法是跳过收集与该表相关的数据(这个案例是wrh$_sql_bind_metadata记录的是SQL Bind Metadata),使用下面的命令:
alter system set “_awr_disabled_flush_tables” = ‘wrh$_sql_bind_metadata’;
该命令是动态的,不需要重启实例, 禁用多个表时参数用逗号分隔;
最后重启数据库解决,并关闭绑定变量写入
最后重启数据库解决,并关闭绑定变量写入
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108064/viewspace-2156002/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29108064/viewspace-2156002/