環境

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’;

該命令是動態的，不需要重啟例項, 禁用多個表時引數用逗號分隔；

最後重啟資料庫解決，並關閉繫結變數寫入

ORA-32701

相關文章