Event 10231 允许Oracle 在全表扫描时可以忽略某些损坏的数据块来检索数据，它允许用户执行 export 或 "create table as select" 操作来找回在数据文件中那些没有损坏的数据。在损坏快上的数据将被丢失。

如下所示：

       
SQL> select count(*) from bbed;
select count(*) from bbed
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 27, block # 5311)
ORA-01110: data file 27: '/disk/oracle/oradata/pics/bbed.dbf'

说明datafile file#=27 fileName=/disk/oracle/oradata/pics/bbed.dbf 文件

SQL> column name format a50
SQL> column file# format 999
SQL>  SELECT file#, NAME  FROM v$datafile WHERE file# = 27;

FILE# NAME
----- --------------------------------------------------
   27 /disk/oracle/oradata/pics/bbed.dbf

使用 Event 10231

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;

System altered.

设置Skip corrupted blocks.

SQL> select * from bbed;
NAME                                                      AGE
-------------------------------------------------- ----------
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1
lis                                                         1

ERROR:
ORA-01578: ORACLE data block corrupted (file # 27, block # 3806)
ORA-01110: data file 27: '/disk/oracle/oradata/pics/bbed.dbf'

2256030 rows selected.

------在坏块处中断。

Export table bbed data

[oracle@hi1-ibmsv603 pics]$ exp lis/lis file=bbed.dmp tables=bbed;
......     
About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           BBED    2620835 rows exported
Export terminated successfully without warnings.

drop table bbed 然后重建

   SQL> truncate table bbed;
 
Table truncated.
 
SQL> drop table bbed;
 
Table dropped.  
 

SQL> create table bbed (name varchar2(50),age number(10)) tablespace bbed;
 
Table created.

imp data to table bbed

import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into LIS
. . importing table                         "BBED"    2620835 rows imported
Import terminated successfully without warnings.

原bbed.count(*)=2621440 rows 现在 bbed.count(*)=2620835 rows

数据有丢失。

Note：

如果清楚知道是哪个Schema.object_type 在datafile 上有数据块损坏直接可以使用

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS Procedure

---请参考dbms_repair package

This procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object.

begin
 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('LIS','BBED');
END;

or

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
flags IN BINARY_INTEGER DEFAULT SKIP_FLAG);

忽略后将数据导出后重建object, 然后使用noskip_flag 恢复正常扫描

begin
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('LIS','BBED',flags=>dbms_repair.noskip_flag);
END

结束。

[@more@]

Oracle event 10231