Oracle数据块损坏解决办法

背景：

1、PDM（Windchill）系统

2、exp数据错误，没有rman备份数据

3、数据库trc文件报错

Dump file d:\oracle\admin\wind\udump\wind_ora_5224.trc
Tue Jan 27 21:04:42 2015
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: wind

Redo thread mounted by this instance: 1

Oracle process number: 11

Windows thread id: 5224, image: ORACLE.EXE


*** SESSION ID:(10.7) 2015-01-27 21:04:42.000
***
Corrupt block relative dba: 0x0141e21f (file 5, block 123423)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0141e21f
last change scn: 0x0000.0f9e3838 seq: 0x1 flg: 0x06
consistency value in tail: 0x00000000
check value in block header: 0xe8ee, computed block checksum: 0x768a
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0141e21f (file 5, block 123423) found same corrupted data

解决办法：

1、首先通过命令定位数据块对应表

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 5 and 123423 between block_id AND block_id + blocks - 1;

SQL> conn ob62/ob62
已连接。
SQL> select count(1) from stringvalue;
select count(1) from stringvalue
                    *
ERROR 位于第 1 行:
ORA-01578: ORACLE 数据块损坏（文件号5，块号123423）
ORA-01110: 数据文件 5: 'D:\ORACLE\ORADATA\WIND\USERS01.DBF'

2、执行语句ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10'; 此语句表示全表扫描的时候跳过坏块

3、exp相关表数据

4、删除坏块对应表数据

5、删除表数据

6、imp备份表数据

7、启动系统