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

相關文章