作者：bytehouse Oracle ACE、PostgreSQL ACE
10+年数据库架构与运维实战经验
公众号：bytehouse
墨天轮专栏：bytehouse
CSDN：Young DBA
接上期： Every Day of a DBA，第148期: Oracle 数据文件逻辑坏块和物理坏块的处理， 数据库可以open，但是告警日志依然提示错误，导致数据库hang ，无法使用。
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=36 AND 516367 BETWEEN block_id AND block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------------------------
RM SERVER_PUBLISH_METRIC TABLE
SQL>
step 2.修复方案二选一
- 有完整RMAN备份：恢复坏块（不影响整库）
rman target /
RECOVER DATAFILE 36 BLOCK 516367;
- 无备份：导出该段对象，重建表/表空间；或临时设置event跳过坏块（仅应急，数据丢失）
ALTER SYSTEM SET event='10231 trace name context forever,level 10' scope=spfile sid='*';
-- 临时当场生效（仅当前实例，重启失效，不用写 spfile）
ALTER SYSTEM SET event='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, value FROM gv$parameter WHERE name='undo_tablespace';
分别为两个实例创建独立Undo表空间
CREATE UNDO TABLESPACE undo_tbs1
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE UNDO TABLESPACE undo_tbs2
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
分别写入spfile，指定实例绑定对应undo
不能用 scope=both，只能 scope=spfile，改完需要重启实例生效
-- 实例wnjj1绑定undo_tbs1
ALTER SYSTEM SET undo_tablespace=undo_tbs1 SCOPE=SPFILE SID='wnjj1';
-- 实例wnjj2绑定undo_tbs2
ALTER SYSTEM SET undo_tablespace=undo_tbs2 SCOPE=SPFILE SID='wnjj2';
e. 重启两个实例使配置生效
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
