Every Day of a DBA，第149期: ORA-32701: Possible hangs up to hang ID=78 detected

用户3107127

发布于 2026-07-09 16:08:45

作者：bytehouse Oracle ACE、PostgreSQL ACE 10+年数据库架构与运维实战经验公众号：bytehouse 墨天轮专栏：bytehouse CSDN：Young DBA

接上期： Every Day of a DBA，第148期: Oracle 数据文件逻辑坏块和物理坏块的处理，后续的处理。

1.错误描述

远程连接ORA-03135断开、实例卡顿；导致业务应用无法链接数据库

Sat Jul 04 22:51:39 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_smon_10514.trc  (incident=548854):
ORA-01578: ORACLE data block corrupted (file # 36, block # 516367)
ORA-01110: data file 36: '+DATA/wnjj/datafile/rm_data.298.1237758031'
ORACLE Instance wnjj2 (pid = 50) - Error 1578 encountered while recovering transaction (38, 22) on object 150810.
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_smon_10514.trc:
ORA-01578: ORACLE data block corrupted (file # 36, block # 516367)
ORA-01110: data file 36: '+DATA/wnjj/datafile/rm_data.298.1237758031'
Thread 2 cannot allocate new log, sequence 7156486
Checkpoint not complete
  Current log# 3 seq# 7156485 mem# 0: +DATA/wnjj/onlinelog/group_3.265.1138913085
Sat Jul 04 22:51:39 2026
Dumping diagnostic data in directory=[cdmp_20260704225139], requested by (instance=2, osid=10514 (SMON)), summary=[abnormal process termination].
Thread 2 advanced to log sequence 7156486 (LGWR switch)
  Current log# 4 seq# 7156486 mem# 0: +DATA/wnjj/onlinelog/group_4.266.1138913085
Thread 2 cannot allocate new log, sequence 7156487
Checkpoint not complete
  Current log# 4 seq# 7156486 mem# 0: +DATA/wnjj/onlinelog/group_4.266.1138913085
Sat Jul 04 22:52:39 2026
Sweep [inc][548854]: completed
Sat Jul 04 22:54:20 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_dia0_10417.trc  (incident=548869):
ORA-32701: Possible hangs up to hang ID=75 detected
Sat Jul 04 22:54:44 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_dia0_10417.trc  (incident=548870):
ORA-32701: Possible hangs up to hang ID=78 detected
Sat Jul 04 22:54:48 2026
Sweep [inc][548870]: completed
Sweep [inc][548869]: completed
Sat Jul 04 22:54:58 2026
Thread 2 advanced to log sequence 7156487 (LGWR switch)
  Current log# 3 seq# 7156487 mem# 0: +DATA/wnjj/onlinelog/group_3.265.1138913085
Sat Jul 04 22:54:59 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_ora_187828.trc  (incident=548834):
ORA-01578: ORACLE ˽¾ݿꌰ»µ (τ¼þºà36, ¿黅 516367)
ORA-01110: ˽¾ގļþ 36: '+DATA/wnjj/datafile/rm_data.298.1237758031'
Thread 2 cannot allocate new log, sequence 7156488
Checkpoint not complete
  Current log# 3 seq# 7156487 mem# 0: +DATA/wnjj/onlinelog/group_3.265.1138913085
Sat Jul 04 22:55:10 2026
Dumping diagnostic data in directory=[cdmp_20260704225510], requested by (instance=1, osid=16740 (SMON)), summary=[abnormal process termination].
Sat Jul 04 22:55:19 2026
Thread 2 advanced to log sequence 7156488 (LGWR switch)
  Current log# 4 seq# 7156488 mem# 0: +DATA/wnjj/onlinelog/group_4.266.1138913085
Thread 2 cannot allocate new log, sequence 7156489
Checkpoint not complete
  Current log# 4 seq# 7156488 mem# 0: +DATA/wnjj/onlinelog/group_4.266.1138913085
Sat Jul 04 22:55:29 2026
Thread 2 advanced to log sequence 7156489 (LGWR switch)
  Current log# 3 seq# 7156489 mem# 0: +DATA/wnjj/onlinelog/group_3.265.1138913085
Thread 2 cannot allocate new log, sequence 7156490
Checkpoint not complete
  Current log# 3 seq# 7156489 mem# 0: +DATA/wnjj/onlinelog/group_3.265.1138913085
Sat Jul 04 22:55:48 2026
Sweep [inc][548834]: completed
Sat Jul 04 22:58:07 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_dia0_10417.trc  (incident=548871):
ORA-32701: Possible hangs up to hang ID=90 detected
Incident details in: /u01/app/oracle/diag/rdbms/wnjj/wnjj2/incident/incdir_548871/wnjj2_dia0_10417_i548871.trc
Sat Jul 04 22:58:33 2026
Errors in file /u01/app/oracle/diag/rdbms/wnjj/wnjj2/trace/wnjj2_dia0_10417.trc  (incident=548872):
ORA-32701: Possible hangs up to hang ID=90 detected
Incident details in: /u01/app/oracle/diag/rdbms/wnjj/wnjj2/incident/incdir_548872/wnjj2_dia0_10417_i548872.trc
Sun Jul 05 02:00:00 2026
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Sun Jul 05 06:00:00 2026
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Jul 05 06:00:00 2026
Starting background process VKRM
Sun Jul 05 06:00:00 2026
VKRM started with pid=655, OS id=22937

2.日志分析

数据文件物理坏块 ORA-01578 + ORA-01110

ORA-01578: ORACLE 数据块损坏 (文件号36, 块516367)
ORA-01110: 数据文件 36: '+DATA/wnjj/datafile/rm_data.298.1237758031'

实例进程挂起告警 ORA-32701 + SMON异常转储

ORA-32701: Possible hangs up to hang ID=xx detected
Dumping diagnostic data ... summary=[abnormal process termination]

诱因

数据文件坏块导致大量会话卡死、队列等待；
日志切换阻塞引发数据库全局资源等待；
SMON后台进程异常终止，实例内部通信异常，监听转发的连接无法被数据库处理，直接断开。

3.故障处理

step 1.检查坏块范围

-- 11g 扫描坏块
SELECT*FROM v$database_block_corruption;
-- 定位坏块所属对象


SQL>SELECT owner,segment_name,segment_type 
FROM dba_extents 
WHERE file_id=36AND516367BETWEEN block_id AND block_id+blocks-1;  

OWNER    SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------------------------
RMSERVER_PUBLISH_METRICTABLE

SQL>

step 2.修复方案二选一

有完整RMAN备份：恢复坏块（不影响整库）

rman target /
RECOVER DATAFILE 36 BLOCK 516367;

无备份：导出该段对象，重建表/表空间；或临时设置event跳过坏块（仅应急，数据丢失）

ALTERSYSTEMSETevent='10231 trace name context forever,level 10' scope=spfile sid='*';

-- 临时当场生效（仅当前实例，重启失效，不用写 spfile）
ALTERSYSTEMSETevent='10231 trace name context forever,level 10';

关闭跳过坏块 event（恢复默认）

-- 内存当场关闭
ALTER SYSTEM SET event='10231 trace name context off';
-- 从spfile永久删除该event
ALTER SYSTEM RESET event scope=spfile sid='*';

删除该对象

drop table SERVER_PUBLISH_METRIC;

删除对象后，关闭 event=10231 ，数据库不能正常使用

BIN$xxx = drop 后回收站对象，仅删表不 PURGE，元数据残留，SMON 事务恢复一定会访问它对应的 Undo 坏块 file36 block516367；
你重置 event 重启后，10231 失效，SMON 每次读到坏块就抛 ORA-01578、dump 大量 cdmp/trc，IO 打满，数据库内核阻塞，所以必卡顿、远程连接直接 ORA-03135；
当前活跃事务全部在 file5，和坏块无关，卡顿根源是后台SMON异步清理旧崩溃事务+回收站对象触发坏块，不是前台业务事务。

a.清理残留损坏事务、回滚段残留（删表无法自动清旧事务）

SQL> SELECT obj#, name FROM obj$ WHERE obj#=150810;

      OBJ# NAME
---------- ------------------------------
    150810 BIN$VdVST1P7td3gYy5go1zlpQ==$0

SQL> SELECT xid, status, ubafil, ubablk FROM v$transaction;

XID STATUS       UBAFIL UBABLK
---------------- ---------------- ---------- ----------
4C0003006AF87700 ACTIVE    5 158165
76001F00B8A22200 ACTIVE    5 167206
50001200EC673800 ACTIVE    5  94160
7E001400E03A2C00 ACTIVE    5 164961
75000E0040FC0A00 ACTIVE    5   6791
4B001C0003407400 ACTIVE    5 148087
7D001B0012AB2E00 ACTIVE    5 166089
140020009DEA9A00 ACTIVE    5 150525
57000F002A805600 ACTIVE    5 155627
7400000009CE1400 ACTIVE    5   3569
79001800B2481500 ACTIVE    5   1949

11 rows selected.

SQL>

b.清空回收站，彻底清除BIN$残留对象

SQL>PURGE RECYCLEBIN;

Recyclebin purged.

SQL>SELECT obj#, name FROM obj$ WHERE obj#=150810;

      OBJ# NAME
----------------------------------------
    150810 BIN$VdVST1P7td3gYy5go1zlpQ==$0

SQL>

查询无结果代表回收站对象彻底清理，减少SMON扫描触发坏块的频率，但Undo里损坏块物理存在，只是少触发几次报错，不能彻底解决。

c.强制彻底清理回收站元数据（规避 SMON 反复扫描）

-- 1. 针对当前用户回收站
PURGE TABLE "BIN$VdVST1P7td3gYy5go1zlpQ==$0";

-- 2. 全库回收站强制清理
PURGE DBA_RECYCLEBIN;
-- 3. 验证
SELECT obj#, name FROM obj$ WHERE obj#=150810;

执行完再看查询结果，若无记录代表元数据清理完毕。

d.坏块在旧Undo表空间文件36，新建Undo让所有新事务完全避开该文件：

查看当前undo配置

show parameter undo_tablespace
SELECT  name,valueFROM gv$parameter WHERE name='undo_tablespace';

分别为两个实例创建独立Undo表空间

CREATEUNDOTABLESPACE undo_tbs1
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATEUNDOTABLESPACE undo_tbs2
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

分别写入spfile，指定实例绑定对应undo

不能用 scope=both，只能 scope=spfile，改完需要重启实例生效

-- 实例wnjj1绑定undo_tbs1
ALTERSYSTEMSET undo_tablespace=undo_tbs1 SCOPE=SPFILE SID='wnjj1';

-- 实例wnjj2绑定undo_tbs2
ALTERSYSTEMSET undo_tablespace=undo_tbs2 SCOPE=SPFILE SID='wnjj2';

e. 重启两个实例使配置生效

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-05，如有侵权请联系 cloudcommunity@tencent.com 删除

事务