ITPub博客

首页 > 数据库 > Oracle > ORA-32701

ORA-32701

原创 Oracle 作者:fei890910 时间:2018-06-12 10:38:17 0 删除 编辑
环境
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: jdk环境变量
请登录后发表评论 登录
全部评论

注册时间:2013-08-15

  • 博文量
    120
  • 访问量
    747497