转载自http://blog.csdn.net/wzy0623/article/details/6131822
ora-01578是数据块损坏，有三种基本解决方法，但无论如何，要是没有备份，那么数据丢失就在所难免了。
 
第一种：用rman的blockrecover，前提是有备份
第二种：用10231内部事件 + exp/imp
第三种：用dbms_repair
 
后两种有会丢失数据。下边是别人做的两个实验，我就拿来主意了，只为以后好查。
 
造成数据块损坏的原因通常是由于开启了异步I/O或者增加了写进程，还有可能是硬件引起的，今天模拟一下该问题的发生及修复方法。由于水平有限，那面疏漏，欢迎大家指正。
 
创建测试环境
建立测试表空间：
view source
print?
1 create tablespace test
2 datafile 
3  '/u02/oradata/logdw/test01.dbf' size 180K autoextend off
4 logging
5 segment space management auto
6 extent management local;
创建测试用户并设置相应的权限：
view source
print?
1 create user test
2 identified by test
3 default tablespace test;
4 grant connect to test;
5 grant resource to test;
6 revoke unlimited tablespace from test;
7 alter user test quota unlimited on test;
插入测试数据，直到填满 test01.dbf：
view source
print?
1 declare
2  i number;
3 begin
4    for  i in 1..99999 loop
5    insert into test_table values(i,lpad('Q',30));
6    commit;
7  end loop;
8 end;
view source
print?
01 SQL> declare
02  2    i number;
03  3  begin
04  4  for i in 1..99999 loop
05  5    insert into test_table values(i,lpad('Q',30));
06  6    commit;
07  7    end loop;
08  8  end;
09  9  /
10 declare
11 *
12 ERROR at line 1:
13 ORA-01653: unable to extend table TEST.TEST_TABLE by 8 in tablespace TEST
14 ORA-06512: at line 5
test01.dbf 已经填满了数据后，看看一共插入了多少条数据：
view source
print?
1 select count(*) from test_table;
2  
3  COUNT(*)
4 ----------
5      2356
如果我想再加一个索引是不会成功的，我为该表空间添加多一个数据文件来扩容。
view source
print?
1 alter tablespace test add datafile '/u02/oradata/logdw/test02.dbf' size 180K autoextend off;
扩容后的 test 表空间可以创建索引了：
view source
print?
1 create index idx_test_table on test_table(id);
通过这样一些步骤，现在数据的分布情况是test01.dbf这个数据文件装着数据，test02.dbf装着索引。可以使用查询一下dba_extents 确认一下：
view source
print?
1 select owner,segment_name,segment_type,extent_id,file_id,block_id,bytes  
2 from dba_extents 
3 where owner='TEST';
view source
print?
1 OWNER SEGMENT_NAME    SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID      BYTES
2 ----- --------------- ------------------ ---------- ---------- ---------- ----------
3 TEST  TEST_TABLE      TABLE                       0         15          8      65536
4 TEST  TEST_TABLE      TABLE                       1         15         16      65536
5 TEST  IDX_TEST_TABLE  INDEX                       0         16          8      65536
6 TEST  IDX_TEST_TABLE  INDEX                       1         16         16      65536
一条使用索引的典型SQL：
view source
print?
01 SQL> select count(id) from test_table; 
02  
03 COUNT(ID)
04 ----------
05      2356
06  
07 ----------------------------------------------------------------------------------------
08 | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
09 ----------------------------------------------------------------------------------------
10 |   0 | SELECT STATEMENT      |                |     1 |    13 |     4   (0)| 00:00:01 |
11 |   1 |  SORT AGGREGATE       |                |     1 |    13 |            |          |
12 |   2 |   INDEX FAST FULL SCAN| IDX_TEST_TABLE |  2356 | 30628 |     4   (0)| 00:00:01 |
13 ----------------------------------------------------------------------------------------
自此，我们的测试环境就建立完成了。
 
破坏并修复数据文件
在Linux修改二进制文件的方法可以参考我这篇blog《在 Linux下使用vim配合xxd查看并编辑二进制文件》，这里就不熬述了。不同位置的数据块遭到损坏就会报不同的错误，非常幸运，我一个下午就遇到了3个。
 
使用drop和create修复ORA-01578
关闭数据库后打开test02.dbf ，定位到偏移地址 001e9c0 左右的地方修改一下，要注意偏移的位置，不同的位置可能会引发不同的问题，保存并退出。
使用dbv工具检查一下数据文件：
view source
print?
01 [oracle@logserver logdw]$ dbv file=test02.dbf blocksize=8192
02  
03 DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jun 23 09:16:35 2010
04  
05 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
06  
07 DBVERIFY - Verification starting : FILE = /u02/oradata/logdw/test02.dbf
08 Page 15 is marked corrupt
09 Corrupt block relative dba: 0x0400000f (file 16, block 15)
10 Bad check value found during dbv: 
11 Data in bad block:
12 type: 6 format: 2 rdba: 0x0400000f
13 last change scn: 0x0000.0a71e746 seq: 0x1 flg: 0x04
14 spare1: 0x0 spare2: 0x0 spare3: 0x0
15 consistency value in tail: 0xe7460601
16 check value in block header: 0x46a7
17 computed block checksum: 0x12d1
18  
19 Page 16 is marked corrupt
20 Corrupt block relative dba: 0x04000010 (file 16, block 16)
21 Bad check value found during dbv: 
22 Data in bad block:
23 type: 6 format: 2 rdba: 0x04000010
24 last change scn: 0x0000.0a71e746 seq: 0x2 flg: 0x04
25 spare1: 0x0 spare2: 0x0 spare3: 0x0
26 consistency value in tail: 0xe7460602
27 check value in block header: 0x99f0
28 computed block checksum: 0xadca
29  
30 DBVERIFY - Verification complete
31  
32 Total Pages Examined         : 23
33 Total Pages Processed (Data) : 0
34 Total Pages Failing   (Data) : 0
35 Total Pages Processed (Index): 4
36 Total Pages Failing   (Index): 0
37 Total Pages Processed (Other): 10
38 Total Pages Processed (Seg)  : 0
39 Total Pages Failing   (Seg)  : 0
40 Total Pages Empty            : 7
41 Total Pages Marked Corrupt   : 2
42 Total Pages Influx           : 0
43 Total Pages Encrypted        : 0
44 Highest block SCN            : 175236934 (0.175236934)
此时，数据库可以正常启动，但是当接触到坏块的时候就会报ORA-01578：
view source
print?
01 SQL> startup ; 
02 ORACLE instance started.
03  
04 Total System Global Area 1887350784 bytes
05 Fixed Size                  2214456 bytes
06 Variable Size            1560282568 bytes
07 Database Buffers          301989888 bytes
08 Redo Buffers               22863872 bytes
09 Database mounted.
10 Database opened.
11 SQL> connect test/test
12 SQL> show user;
13 USER is "TEST"
14 SQL> set autotrace on ; 
15 SQL> select count(id) from test_table;  
16 select count(id) from test_table
17                      *
18 ERROR at line 1:
19 ORA-01578: ORACLE data block corrupted (file # 16, block # 15)
20 ORA-01110: data file 16: '/u02/oradata/logdw/test02.dbf'
21  
22 SQL> select count(*) from test_table;
23  
24  COUNT(*)
25 ----------
26      2356
27  
28 -------------------------------------------------------------------------
29 | Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time     |
30 -------------------------------------------------------------------------
31 |   0 | SELECT STATEMENT   |            |     1 |     5   (0)| 00:00:01 |
32 |   1 |  SORT AGGREGATE    |            |     1 |            |          |
33 |   2 |   TABLE ACCESS FULL| TEST_TABLE |  2356 |     5   (0)| 00:00:01 |
34 -------------------------------------------------------------------------
如果查询不用到索引，就不会有问题，证明我们的数据没有问题，仅是损失了索引。由于场景特殊，我们知道肯定是索引损坏了，数据没有丢失的，通常的场景是我们知道损坏所在的文件编号和块编号，但是不知道对应的是什么对象，可以用以下查询定位到对应的对象上：
view source
print?
01 sys$logdw@logserver SQL> SELECT tablespace_name, owner,segment_name,segment_type
02  2  FROM dba_extents
03  3  WHERE file_id = &file
04  4  and &block between block_id AND block_id + blocks - 1 ;
05 Enter value for file: 16
06 old   3: WHERE file_id = &file
07 new   3: WHERE file_id = 16
08 Enter value for block: 15
09 old   4: and &block between block_id AND block_id + blocks - 1
10 new   4: and 15 between block_id AND block_id + blocks - 1
11  
12 TABLESPACE OWNER      SEGMENT_NAME         SEGMENT_TYPE
13 ---------- ---------- -------------------- ------------------
14 TEST       TEST       IDX_TEST_TABLE       INDEX
由于数据没有丢失，我尝试重建索引。
view source
print?
1 SQL> alter index IDX_TEST_TABLE rebuild online ; 
2 alter index IDX_TEST_TABLE rebuild online
3 *
4 ERROR at line 1:
5 ORA-00604: error occurred at recursive SQL level 1
6 ORA-01658: unable to create INITIAL extent for segment in tablespace TEST
空间不足……，于是drop后再create吧：
view source
print?
01 SQL> drop index IDX_TEST_TABLE ; 
02  
03 Index dropped.
04  
05 Elapsed: 00:00:00.57
06 SQL> create index IDX_TEST_TABLE on test_table(id);
07  
08 Index created.
09  
10 Elapsed: 00:00:00.10
我们再看看使用索引的查询：
view source
print?
01 SQL> select count(id) from test_table ;
02  
03 COUNT(ID)
04 ----------
05      2356
06  
07 ----------------------------------------------------------------------------------------
08 | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
09 ----------------------------------------------------------------------------------------
10 |   0 | SELECT STATEMENT      |                |     1 |     4 |     3   (0)| 00:00:01 |
11 |   1 |  SORT AGGREGATE       |                |     1 |     4 |            |          |
12 |   2 |   INDEX FAST FULL SCAN| IDX_TEST_TABLE |  2356 |  9424 |     3   (0)| 00:00:01 |
13 ----------------------------------------------------------------------------------------
索引恢复正常了。
该方法适用于坏块落在非数据区的情况。
 
使用RMAN修复ORA-01578
如果损坏了仅仅两个数据块(8K*2)，导致一个几百兆的索引重建，这样的很不值，我们可以试一下Oracle的blockrecover，但前提是之前有备份。
view source
print?
1 RMAN> blockrecover datafile 16 block 15; 
2 RMAN> blockrecover datafile 16 block 16; 
3 -- 或者组合起来
4 RMAN> blockrecover datafile 16 block 15 datafile 16 block 16; 
5 RMAN> blockrecover datafile 16 block 15,16;
如果坏块很多，例如我们就要写很长很长的命令，此时可以试一下：
view source
print?
1 RMAN> blockrecover corruption list;
这个命令是依靠 v$database_block_corruption 这个表进行工作的。Oracle每当发现一个损坏的数据块就会在这个表中多一条记录，相反地如果没有碰到，就自然不会在这表中显示出来的。
view source
print?
1 sys$logdw@logserver SQL> select * from v$database_block_corruption;
2  
3     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
4 ---------- ---------- ---------- ------------------ ---------
5        16         15          2                  0 CHECKSUM
我们可以借助rman对数据库、文件或表空间进行“扫描”，检查出所有的坏块。
view source
print?
1 RMAN> backup validate database;
2 RMAN> backup validate tablespace test;
3 RMAN> backup validate datafile 16;
如果数据库比较大或者表空间也比较大，backup validate就会长时间占用大量I/O资源，因此范围应该尽量小。
view source
print?
01 RMAN> backup validate datafile 16;
02  
03 Starting backup at 2010-06-23 10:42:23
04 using target database control file instead of recovery catalog
05 allocated channel: ORA_DISK_1
06 channel ORA_DISK_1: SID=71 device type=DISK
07 channel ORA_DISK_1: starting full datafile backup set
08 channel ORA_DISK_1: specifying datafile(s) in backup set
09 input datafile file number=00016 name=/u02/oradata/logdw/test02.dbf
10 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
11 List of Datafiles
12 =================
13 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
14 ---- ------ -------------- ------------ --------------- ----------
15 16   FAILED 0              7            23              175356605 
16  File Name: /u02/oradata/logdw/test02.dbf
17  Block Type Blocks Failing Blocks Processed
18  ---------- -------------- ----------------
19  Data       0              0               
20  Index      2              6               
21  Other      0              10              
22  
23 validate found one or more corrupt blocks
24 See trace file /u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_30130.trc for details
25 Finished backup at 2010-06-23 10:42:26
只要让Oracle直到了哪些是坏块就好办了，是blockrecover即可修复坏块：
view source
print?
01 RMAN> blockrecover corruption list; 
02  
03 Starting recover at 2010-06-23 11:00:49
04 using target database control file instead of recovery catalog
05 allocated channel: ORA_DISK_1
06 channel ORA_DISK_1: SID=136 device type=DISK
07 searching flashback logs for block images
08  
09 finished flashback log search, restored 2 blocks
10  
11 starting media recovery
12 media recovery complete, elapsed time: 00:00:01
13  
14 Finished recover at 2010-06-23 11:10:48
再使用索引查询一下数据：
view source
print?
01 test$logdw@logdw SQL> select count(id) from test_table ;
02  
03 COUNT(ID)
04 ----------
05      2356
06  
07 ----------------------------------------------------------------------------------------
08 | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
09 ----------------------------------------------------------------------------------------
10 |   0 | SELECT STATEMENT      |                |     1 |     4 |     3   (0)| 00:00:01 |
11 |   1 |  SORT AGGREGATE       |                |     1 |     4 |            |          |
12 |   2 |   INDEX FAST FULL SCAN| IDX_TEST_TABLE |  2356 |  9424 |     3   (0)| 00:00:01 |
13 ----------------------------------------------------------------------------------------
此时索引idx_test_table可以用了，一切都是在线做的。
该方法需要有rman的备份，如果日常有rman的备份就不用怕了。
 
使用exp和imp尽力挽救数据
如果没有rman备份呢？以下主要参考盖国强的《Oracle中模拟及修复数据块损坏》
我们再用同样的方法编辑test01.dbf，定位到最后，将数据破坏。
可以预见test_table的数据已经损坏，索引没有损坏。
view source
print?
01 test$logdw@logdw SQL> select count(*) from test_table ; 
02 select count(*) from test_table
03                     *
04 ERROR at line 1:
05 ORA-01578: ORACLE data block corrupted (file # 15, block # 23)
06 ORA-01110: data file 15: '/u02/oradata/logdw/test01.dbf'
07  
08 Elapsed: 00:00:01.93
09 test$logdw@logdw SQL> select count(id) from test_table ;   
10  
11 COUNT(ID)
12 ----------
13      2356
14  
15 ----------------------------------------------------------------------------------------
16 | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
17 ----------------------------------------------------------------------------------------
18 |   0 | SELECT STATEMENT      |                |     1 |     4 |     3   (0)| 00:00:01 |
19 |   1 |  SORT AGGREGATE       |                |     1 |     4 |            |          |
20 |   2 |   INDEX FAST FULL SCAN| IDX_TEST_TABLE |  2356 |  9424 |     3   (0)| 00:00:01 |
21 ----------------------------------------------------------------------------------------
由于没有已经假设没有rman的备份，所以blockrecover已经不可能了，此时可以用vim+xxd重新对test01.dbf进行修改或者借助内部工具 bbed 将坏块修正也是可以的，据说这是Oracle 8i之前的做法，但几乎是mission impossible。 
如果我们将任务定位为可以容忍数据丢失，希望能尽量挽救，减少数据丢失。
view source
print?
01 [oracle@logserver tmp]$ exp test/test file=test_table.dmp tables=test_table         
02  
03 Export: Release 11.2.0.1.0 - Production on Wed Jun 23 14:08:29 2010
04  
05 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
06  
07  
08 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
09 With the Partitioning, OLAP, Data Mining and Real Application Testing options
10 Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
11  
12 About to export specified tables via Conventional Path ...
13 . . exporting table                     TEST_TABLE
14 EXP-00056: ORACLE error 1578 encountered
15 ORA-01578: ORACLE data block corrupted (file # 15, block # 23)
16 ORA-01110: data file 15: '/u02/oradata/logdw/test01.dbf'
17 Export terminated successfully with warnings.
我们使用导出得到一个错误
通过设置内部事件，设置在全表扫描时跳过损坏的数据块
view source
print?
1 ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;
2 ALTER SYSTEM SET EVENTS='10231 trace name context off' ;
exp可以成功执行，将损坏的数据表删除掉，在imp即可挽救部分数据。
在当前的场景中，没有设置内部事件的情况下挽救了 2356 条数据中的 1920 条；而设置了内部事件之后挽救了 2175 条数据。
该方法几乎成了最后的救命稻草了，如果你足够强悍可以试一下用bbed或者其他工具直接对数据文件进行二进制编辑从而手动修复数据文件。
 
小结
遇到ORA-01578后，第一步应首先确定rman备份情况，如果有可用备份，一切都不太糟糕，几条rman命令就可以修复；
如果没有再确定坏块对应的是什么对象，如果是索引等非数据对象，可以考虑drop后再create一次；
如果坏块落在数据上，设置10231内部事件，然后求神拜佛，能救回多少数据只能看人品了。
 
 
 
 
 
 
 
 


Use DBMS_REPAIR to fix physically corrupt data block
Posted by Zahid Qureshi on August 29, 2010.


It is quite often that you get blocks in a data file physically corrupt. As a proactive DBA you should always be ready for such kind of things. e.g. if you have proper backups of the data files you can recover the corrupt block from those backups. IF you have user managed backups, you can recover the whole data file and get the corrupted block recovered with it. If you have an RMAN backup, you can use a powerful RMAN feature called BMR (BLOCK MEDIA RECOVERY). Using BMR you can tell RMAN an individual block to recover from a backup instead of recovering the whole data file. However, there may exist scenarios where you don't have a backup at all. You may have lost it, got it deleted or may be the backup got corrupted itself. This article demonstrate how to repair a corrupt block to make the objects usable, that are using that block.
Bear in mind that repairing a corrupt block using DBMS_REPAIR, does not restore data in those blocks. It is just a mean of marking those blocks as corrupt blocks and skip them when data in the read from the object containing corrupt blocks. The data in the corrupt blocks id lost forever.
Lets first get a block corrupted and see its effects on the objects using that block.
create tablespace ts_corrupt
datafile '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'
size 10m autoextend on next 5m
extent management local
segment space management auto
/


Tablespace created.


create user usr_corrupt identified by usr_corrupt 
default tablespace ts_corrupt
quota unlimited on ts_corrupt
/


User created.


grant connect , resource to usr_corrupt
/


Grant succeeded.


conn usr_corrupt/usr_corrupt
Connected.


create table t_corrupt as select * from all_objects
/


Table created.


create index tc_idx 
on t_corrupt (object_name)
/


Index created.


SQL> exit
We have a tablespace ts_corrupt, which has a table named t_corrupt owned by the user usr_corrupt and there is an index on one of the columns in the table. Lets see where in the data file this table is sitting.
$ sqlplus / as sysdba


select segment_name , header_file , header_block      
from dba_segments
where segment_name = 'T_CORRUPT'
and   owner = 'USR_CORRUPT';


SEGMENT_NAME                 HEADER_FILE HEADER_BLOCK
---------------------------- ----------- ------------
T_CORRUPT                              6           11


SQL> exit
The header of the table is in block 11, so if the block 12 or so are corrupted we will get an error when we try to select data from the table. Lets corrupt block 12 using the "dd" command in Linux.
DISCLAIMER: The dd command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.
$ cd /d01/apps/oradata/oraxpo
$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=12 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 8.9e-05 seconds, 191 kB/s


$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=13 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.0002 seconds, 85 kB/s


$ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=14 << EOF
> Make it Corrupt.
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 5.6e-05 seconds, 304 kB/s
We have injected our crap into the block # 12,13 and 14 of the datafile "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf" using "dd" command. After this command successfully executed these blocks are now corrupt.
$ sqlplus / as sysdba


SQL> alter system flush buffer_cache;


System altered. /* 
   We may need to flush the buffer_cache because if the block 12 is 
   in the buffer_cache already it will not be read from the data file.
*/ SQL> conn usr_corrupt/usr_corrupt 
Connected.
SQL> select * from t_corrupt;
select * from t_corrupt
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'




SQL> exit
The error that we get due to a block corruption is very clear. It will tell you the file and block#.
Repairing a block corruption starts with creating two admin tables "REPAIR_TABLE" and "ORPHAN_TABLE".
 
REPAIR_TABLE:
This table is filled with the information about the corrupt blocks by the DBMS_REPAIR.CHECK_OBJECT procedure.
ORPHAN_KEY_TABLE:
This table is filled with the information about any orphan keys in indexes due to corrupt blocks in the table by the DBMS_REPAIR.DUMP_ORPHAN_KEYS procedure.
 
 
DBMS_REPAIR.ADMIN_TABLES()
performs actions (create,drop,purge) with special tables which are later on used for the corrupt block repair.
 
 
table_name
=> Name of the table to process.
table_type
=> Type of the table being processed. e.g. DBMS_REPAIR.REPAIR_TABLE or DBMS_REPAIR.ORPHAN_TABLE
action
=> the action to perform with the table mentioned in the first argument. e.g. DBMS_REPAIR.CREATE_ACTION, DBMS_REPAIR.DROP_ACTION or DBMS_REPAIR.PURGE_ACTION.
tablespace
=> The tablespace to use when creating the special table.
 
$ sqlplus / as sysdba


BEGIN
  DBMS_REPAIR.ADMIN_TABLES 
  (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'USERS'
  );
END;
/


PL/SQL procedure successfully completed. -- We just created the REPAIR_TABLE in the USERS tablespace. SQL> desc repair_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                 NOT NULL NUMBER
 TABLESPACE_ID                             NOT NULL NUMBER
 RELATIVE_FILE_ID                          NOT NULL NUMBER
 BLOCK_ID                                  NOT NULL NUMBER
 CORRUPT_TYPE                              NOT NULL NUMBER
 SCHEMA_NAME                               NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 BASEOBJECT_NAME                                    VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 CORRUPT_DESCRIPTION                                VARCHAR2(2000)
 REPAIR_DESCRIPTION                                 VARCHAR2(200)
 MARKED_CORRUPT                            NOT NULL VARCHAR2(10)
 CHECK_TIMESTAMP                           NOT NULL DATE
 FIX_TIMESTAMP                                      DATE
 REFORMAT_TIMESTAMP                                 DATE




BEGIN
  DBMS_REPAIR.ADMIN_TABLES 
  (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'USERS'
  );
END;
/


PL/SQL procedure successfully completed. -- We just created the ORPHAN_KEY_TABLE in the USERS tablespace. SQL> desc ORPHAN_KEY_TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEMA_NAME                               NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 IPART_NAME                                         VARCHAR2(30)
 INDEX_ID                                  NOT NULL NUMBER
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 PART_NAME                                          VARCHAR2(30)
 TABLE_ID                                  NOT NULL NUMBER
 KEYROWID                                  NOT NULL ROWID
 KEY                                       NOT NULL ROWID
 DUMP_TIMESTAMP                            NOT NULL DATE
 
DBMS_REPAIR.CHECK_OBJECT()
checks an objects for possible corruptions and report the corruptions into the REPAIR_TABLE.
 
 
schema_name
=> Owner of the object to check.
object_name
=> Name of the object to check.
repair_table_name
=> Name of the REPAIR_TABLE.
corrupt_count
=> An OUT parameter where the number of corrupt blocks are reported.
 
SET SERVEROUTPUT ON
DECLARE 
  vCorruptBlocks INT := 0;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'T_CORRUPT',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  vCorruptBlocks
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
END;
/ Number of blocks corrupted: 3 PL/SQL procedure successfully completed. -- The procedure found three blocks corrupt and has filled the information in REPAIR_TABLE. column object_name format a10
column repair_description format a28
set lines 10000
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;


OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ----------------------------
T_CORRUPT          12         6148 TRUE       mark block software corrupt
T_CORRUPT          13         6148 TRUE       mark block software corrupt
T_CORRUPT          14         6148 TRUE       mark block software corrupt
As you may see in the output above the MARKED_CORRUPT column shows TRUE for all three corruptions. It seems that CHECK_OBJECT procedure is capable of marking the corrupt blocks in the scenario we have demonstrated. However, if the MARKED_CORRUPT column shows FALSE for some corrupted blocks, you may use DBMS_REPAIR.FIX_CORRUPT_BLOCKS procedure to mark the blocks as corrupt.
 
DBMS_REPAIR.FIX_CORRUPT_BLOCKS()
fix any corrupted blocks in specified object by marking them as software corrupt.
 
 
schema_name
=> Owner of the object to fix.
object_name
=> Name of the object to fix.
object_type
=> Type of the object to fix.
repair_table_name
=> Name of the REPAIR_TABLE.
fix_count
=> An OUT parameter where the number of blocks fixed are reported.
 
SET SERVEROUTPUT ON
DECLARE 
  vBlocksFixed INT := 0;
BEGIN 
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME=> 'T_CORRUPT',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT => vBlocksFixed
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks fixed: ' || TO_CHAR(vBlocksFixed));
END;
/ Number of blocks fixed: 0 PL/SQL procedure successfully completed.
Number of blocks fixed are 0 in my case as the corrupt blocks are already marked by the CHECK_OBJECT procedure.
If the corrupted object was a table, then it may be very possible that there was an index on the table which had entries of data that is corrupt in the table now. Those entries are orphan now.
 
DBMS_REPAIR.DUMP_ORPHAN_KEYS()
dump any orphan keys in indexes caused by the corruption in a table.
 
 
schema_name
=> Owner of the object to process.
object_name
=> Name of the object to process.
object_type
=> Type of the object to process. e.g. DBMS_REPAIR.INDEX_OBJECT.
repair_table_name
=> Name of the REPAIR_TABLE.
orphan_table_name
=> Name of the ORPHAN_TABLE.
key_count
=> An OUT parameter where the number of orphan keys dumped are reported.
 
SET SERVEROUTPUT ON
DECLARE 
  vOrphanKeys INT := 0;
BEGIN
  DBMS_REPAIR.DUMP_ORPHAN_KEYS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'TC_IDX',
     OBJECT_TYPE => DBMS_REPAIR.INDEX_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
     KEY_COUNT => vOrphanKeys
  );
  DBMS_OUTPUT.PUT_LINE('Number of orphan keys: ' || TO_CHAR(vOrphanKeys));
END;
/ Number of orphan keys: 241 PL/SQL procedure successfully completed.  -- 241 Orphan keys are dumped to the ORPHAN_TABLE.
Even after the corrupt blocks are marked corrupt, you will not be able to access them unless you set the object to skip any corrupted blocks using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.
SQL> select * from usr_corrupt.t_corrupt;
select * from usr_corrupt.t_corrupt
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'


SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';
select * from usr_corrupt.t_corrupt where object_name = 'DUAL'
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 12)
ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf'
 
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS()
skip any corrupted blocks in specified object when data is read from it.
 
 
schema_name
=> Owner of the object to process.
object_name
=> Name of the object to process.
object_type
=> Type of the object to process.
flags
=> What to do with the object. e.g. DBMS_REPAIR.SKIP_FLAG or DBMS_REPAIR.NOSKIP_FLAG
 
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS 
  (
     SCHEMA_NAME => 'USR_CORRUPT',
     OBJECT_NAME => 'T_CORRUPT',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     FLAGS => DBMS_REPAIR.SKIP_FLAG
  );
END;
/


PL/SQL procedure successfully completed.
Now lets see if our repair works or not.
-- Rebuild the index to be used with the object repaired. SQL> ALTER INDEX USR_CORRUPT.TC_IDX REBUILD;


Index altered.


select owner , table_name , skip_corrupt
from   dba_tables
where  table_name = 'T_CORRUPT';


OWNER                          TABLE_NAME                     SKIP_COR
------------------------------ ------------------------------ --------
USR_CORRUPT                    T_CORRUPT                      ENABLED /*
 The SKIP_CORRUPT column in dba_tables shows whether corrupt blocks are skipped 
 or not on reading data from the object. 
*/ SQL> select count(*) from usr_corrupt.t_corrupt;


  COUNT(*)
----------
     40692


SQL> select * from usr_corrupt.t_corrupt where object_name = 'DUAL';


no rows selected -- The row where "object_name='DUAL'" is lost. 
See also:
Using DBMS_REPAIR to Repair Data Block Corruption
How to perform Block Media Recovery using RMAN