索引坏块修复

帮朋友处理数据库问题发来如下信息

2019.04.25 16:10:02.905 [pool-2-thread-1] ERROR [com.alibaba.druid.filter.logging.Log4j2Filter.statementLogError:152] - {conn-10005, pstmt-20008} execute error. insert into TB_MZ_SFMXB (YLJGDM, SFMXID, GRBSLX,
GRBSH, JZLSH, BTFMXID,
TFBZ, SFCJBM, SFXMLBBM,
FYFSSJ, SYJSID, YZMXID,
SFJSSJ, KDKSBM, KDKSMC,
KDYSBH, KDYSXM, ZXKSBM,
ZXKSMC, ZXRYBH, ZXRYXM,
MXXMBM, MXXMMC, MXXMDW,
MXXMDJ, XMFLBM, XMFLMC,
MXXMSL, MXXMYSJE, MXXMSSJE,
XGBZ, FYSRGLBM, FYSRGLMC
)
values (?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?, ?
) java.sql.SQLException: ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 2421495)
ORA-01110: 数据文件 5: 'E:\ORACLE\ORADATA\ORCL\SYDB01.DBF'
ORA-06512: 在 "SYDB_WJW.UMTS_TODO33021I", line 8
ORA-04088: 触发器 'SYDB_WJW.UMTS_TODO33021I' 执行过程中出错

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714) ~[ojdbc6.jar:11.2.0.4.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378) ~[ojdbc6.jar:11.2.0.4.0]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterChainImpl.pre
加载更多

检查alter日志

Hex dump of (file 5, block 2421495) in trace file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_2744.trc
Corrupt block relative dba: 0x0164f2f7 (file 5, block 2421495)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0164f2f7
 last change scn: 0x05f2.53c28ee4 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb3fa0601
 check value in block header: 0xc7e0
 computed block checksum: 0x3d1c
Reading datafile 'E:\ORACLE\ORADATA\ORCL\SYDB01.DBF' for corruption at rdba: 0x0164f2f7 (file 5, block 2421495)
Reread (file 5, block 2421495) found same corrupt data
Fri Apr 26 11:17:06 2019
Corrupt Block Found
         TSN = 7, TSNAME = SYDB
         RFN = 5, BLK = 2421495, RDBA = 23393015
         OBJN = 73958, OBJD = 73958, OBJECT = IDX_UNIMAS_DC350B_EHCAA_50, SUBOBJECT = 
         SEGMENT OWNER = SYDB_WJW, SEGMENT TYPE = Index Segment
Fri Apr 26 11:17:06 2019
Hex dump of (file 5, block 2425079) in trace file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_2284.trc
Corrupt block relative dba: 0x016500f7 (file 5, block 2425079)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x016500f7
 last change scn: 0x05f2.54389230 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x280c0601
 check value in block header: 0xcec4
 computed block checksum: 0xb83c
Reading datafile 'E:\ORACLE\ORADATA\ORCL\SYDB01.DBF' for corruption at rdba: 0x016500f7 (file 5, block 2425079)
Reread (file 5, block 2425079) found same corrupt data

 

先确认数据库损损坏情况，没有发现物理文件损坏，检查数据库损坏对象

select segment_name,partition_name,segment_type,owner,tablespace_name
from sys.dba_extents
where file_id=5
and 2421495 between block_id and block_id + blocks-1;

 

两个损坏对象均在索引上，松了口气， 重建索引后 查找坏块消失

 比较奇怪的是通过dbv检测仍然出现坏块

C:\Users\Administrator>dbv file=E:\oracle\oradata\orcl\SYDB01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on 星期五 4月 26 11:27:47 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = E:\ORACLE\ORADATA\ORCL\SYDB01.DBF
页 2421495 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x0164f2f7 (file 5, block 2421495)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0164f2f7
 last change scn: 0x05f2.53c28ee4 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb3fa0601
 check value in block header: 0xc7e0
 computed block checksum: 0x3d1c

DBVERIFY - 验证完成

检查的页总数: 3932160
处理的页总数 (数据): 2055861
失败的页总数 (数据): 0
处理的页总数 (索引): 467781
失败的页总数 (索引): 0
处理的页总数 (其他): 7827
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 1400690
标记为损坏的总页数: 1
流入的页总数: 1
加密的总页数        : 0
最高块 SCN            : 3982908671 (1979.3982908671)