oracle 11203 ora32701,ORA-32701

环境

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

该命令是动态的，不需要重启实例, 禁用多个表时参数用逗号分隔；

最后重启数据库解决，并关闭绑定变量写入