oracle ora-01001,ORA-01578 ORA-01110 ORACLE坏块解决方法

转载自http://blog.csdn.net/wzy0623/article/details/6131822ora-01578是数据块损坏，有三种基本解决方法，但无论如何，要是没有备份，那么数据丢失就在所难免了。第一种：用rman的blockrecover，前提是有备份第二种：用10231内部事件 + exp/imp第三种：用dbms_repair后两种有会丢失数据。下边是别人做的两个实验，我就

weixin_39923599

1002人浏览 · 2021-04-09 23:15:58

weixin_39923599 · 2021-04-09 23:15:58 发布

转载自http://blog.csdn.net/wzy0623/article/details/6131822

ora-01578是数据块损坏，有三种基本解决方法，但无论如何，要是没有备份，那么数据丢失就在所难免了。

第一种：用rman的blockrecover，前提是有备份

第二种：用10231内部事件 + exp/imp

第三种：用dbms_repair

后两种有会丢失数据。下边是别人做的两个实验，我就拿来主意了，只为以后好查。

造成数据块损坏的原因通常是由于开启了异步I/O或者增加了写进程，还有可能是硬件引起的，今天模拟一下该问题的发生及修复方法。由于水平有限，那面疏漏，欢迎大家指正。

创建测试环境

建立测试表空间：

view source

print?

1create tablespace test

2datafile

3 '/u02/oradata/logdw/test01.dbf' size 180K autoextend off

4logging

5segment space management auto

6extent management local;

创建测试用户并设置相应的权限：

view source

print?

1create user test

2identified by test

3default tablespace test;

4grant connect to test;

5grant resource to test;

6revoke unlimited tablespace from test;

7alter user test quota unlimited on test;

插入测试数据，直到填满 test01.dbf：

view source

print?

1declare

2 i number;

3begin

4 for i in 1..99999 loop

5 insert into test_table values(i,lpad('Q',30));

6 commit;

7 end loop;

8end;

view source

print?

01SQL> 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 /

10declare

11*

12ERROR at line 1:

13ORA-01653: unable to extend table TEST.TEST_TABLE by 8 in tablespace TEST

14ORA-06512: at line 5

test01.dbf 已经填满了数据后，看看一共插入了多少条数据：

view source

print?

1select count(*) from test_table;

3 COUNT(*)

4----------

5 2356

如果我想再加一个索引是不会成功的，我为该表空间添加多一个数据文件来扩容。

view source

print?

1alter tablespace test add datafile '/u02/oradata/logdw/test02.dbf' size 180K autoextend off;

扩容后的 test 表空间可以创建索引了：

view source

print?

1create index idx_test_table on test_table(id);

通过这样一些步骤，现在数据的分布情况是test01.dbf这个数据文件装着数据，test02.dbf装着索引。可以使用查询一下dba_extents 确认一下：

view source

print?

1select owner,segment_name,segment_type,extent_id,file_id,block_id,bytes

2from dba_extents

3where owner='TEST';

view source

print?

1OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID BYTES

2----- --------------- ------------------ ---------- ---------- ---------- ----------

3TEST TEST_TABLE TABLE 0 15 8 65536

4TEST TEST_TABLE TABLE 1 15 16 65536

5TEST IDX_TEST_TABLE INDEX 0 16 8 65536

6TEST IDX_TEST_TABLE INDEX 1 16 16 65536

一条使用索引的典型SQL：

view source

print?

01SQL> select count(id) from test_table;

03 COUNT(ID)

04----------

05 2356

07----------------------------------------------------------------------------------------

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

03DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jun 23 09:16:35 2010

07DBVERIFY - Verification starting : FILE = /u02/oradata/logdw/test02.dbf

08Page 15 is marked corrupt

09Corrupt block relative dba: 0x0400000f (file 16, block 15)

10Bad check value found during dbv:

11Data 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

19Page 16 is marked corrupt

20Corrupt block relative dba: 0x04000010 (file 16, block 16)

21Bad check value found during dbv:

22Data 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

30DBVERIFY - Verification complete

32Total Pages Examined : 23

33Total Pages Processed (Data) : 0

34Total Pages Failing (Data) : 0

35Total Pages Processed (Index): 4

36Total Pages Failing (Index): 0

37Total Pages Processed (Other): 10

38Total Pages Processed (Seg) : 0

39Total Pages Failing (Seg) : 0

40Total Pages Empty : 7

41Total Pages Marked Corrupt : 2

42Total Pages Influx : 0

43Total Pages Encrypted : 0

44Highest block SCN : 175236934 (0.175236934)

此时，数据库可以正常启动，但是当接触到坏块的时候就会报ORA-01578：

view source

print?

01SQL> startup ;

02ORACLE instance started.

04Total System Global Area 1887350784 bytes

05Fixed Size 2214456 bytes

06Variable Size 1560282568 bytes

07Database Buffers 301989888 bytes

08Redo Buffers 22863872 bytes

09Database mounted.

10Database opened.

11SQL> connect test/test

12SQL> show user;

13USER is "TEST"

14SQL> set autotrace on ;

15SQL> select count(id) from test_table;

16select count(id) from test_table

17 *

18ERROR at line 1:

19ORA-01578: ORACLE data block corrupted (file # 16, block # 15)

20ORA-01110: data file 16: '/u02/oradata/logdw/test02.dbf'

22SQL> select count(*) from test_table;

24 COUNT(*)

25----------

26 2356

28-------------------------------------------------------------------------

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?

01sys$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 ;

05Enter value for file: 16

06old 3: WHERE file_id = &file

07new 3: WHERE file_id = 16

08Enter value for block: 15

09old 4: and &block between block_id AND block_id + blocks - 1

10new 4: and 15 between block_id AND block_id + blocks - 1

12TABLESPACE OWNER SEGMENT_NAME SEGMENT_TYPE

13---------- ---------- -------------------- ------------------

14TEST TEST IDX_TEST_TABLE INDEX

由于数据没有丢失，我尝试重建索引。

view source

print?

1SQL> alter index IDX_TEST_TABLE rebuild online ;

2alter index IDX_TEST_TABLE rebuild online

4ERROR at line 1:

5ORA-00604: error occurred at recursive SQL level 1

6ORA-01658: unable to create INITIAL extent for segment in tablespace TEST

空间不足……，于是drop后再create吧：

view source

print?

01SQL> drop index IDX_TEST_TABLE ;

03Index dropped.

05Elapsed: 00:00:00.57

06SQL> create index IDX_TEST_TABLE on test_table(id);

08Index created.

10Elapsed: 00:00:00.10

我们再看看使用索引的查询：

view source

print?

01SQL> select count(id) from test_table ;

03 COUNT(ID)

04----------

05 2356

07----------------------------------------------------------------------------------------

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?

1RMAN> blockrecover datafile 16 block 15;

2RMAN> blockrecover datafile 16 block 16;

3-- 或者组合起来

4RMAN> blockrecover datafile 16 block 15 datafile 16 block 16;

5RMAN> blockrecover datafile 16 block 15,16;

如果坏块很多，例如我们就要写很长很长的命令，此时可以试一下：

view source

print?

1RMAN> blockrecover corruption list;

这个命令是依靠 v$database_block_corruption 这个表进行工作的。Oracle每当发现一个损坏的数据块就会在这个表中多一条记录，相反地如果没有碰到，就自然不会在这表中显示出来的。

view source

print?

1sys$logdw@logserver SQL> select * from v$database_block_corruption;

3 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

4---------- ---------- ---------- ------------------ ---------

5 16 15 2 0 CHECKSUM

我们可以借助rman对数据库、文件或表空间进行“扫描”，检查出所有的坏块。

view source

print?

1RMAN> backup validate database;

2RMAN> backup validate tablespace test;

3RMAN> backup validate datafile 16;

如果数据库比较大或者表空间也比较大，backup validate就会长时间占用大量I/O资源，因此范围应该尽量小。

view source

print?

01RMAN> backup validate datafile 16;

03Starting backup at 2010-06-23 10:42:23

04using target database control file instead of recovery catalog

05allocated channel: ORA_DISK_1

06channel ORA_DISK_1: SID=71 device type=DISK

07channel ORA_DISK_1: starting full datafile backup set

08channel ORA_DISK_1: specifying datafile(s) in backup set

09input datafile file number=00016 name=/u02/oradata/logdw/test02.dbf

10channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

11List of Datafiles

12=================

13File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

14---- ------ -------------- ------------ --------------- ----------

1516 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

23validate found one or more corrupt blocks

24See trace file /u01/app/oracle/diag/rdbms/logdw/logdw/trace/logdw_ora_30130.trc for details

25Finished backup at 2010-06-23 10:42:26

只要让Oracle直到了哪些是坏块就好办了，是blockrecover即可修复坏块：

view source

print?

01RMAN> blockrecover corruption list;

03Starting recover at 2010-06-23 11:00:49

04using target database control file instead of recovery catalog

05allocated channel: ORA_DISK_1

06channel ORA_DISK_1: SID=136 device type=DISK

07searching flashback logs for block images

09finished flashback log search, restored 2 blocks

11starting media recovery

12media recovery complete, elapsed time: 00:00:01

14Finished recover at 2010-06-23 11:10:48

再使用索引查询一下数据：

view source

print?

01test$logdw@logdw SQL> select count(id) from test_table ;

03 COUNT(ID)

04----------

05 2356

07----------------------------------------------------------------------------------------

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?

01test$logdw@logdw SQL> select count(*) from test_table ;

02select count(*) from test_table

03 *

04ERROR at line 1:

05ORA-01578: ORACLE data block corrupted (file # 15, block # 23)

06ORA-01110: data file 15: '/u02/oradata/logdw/test01.dbf'

08Elapsed: 00:00:01.93

09test$logdw@logdw SQL> select count(id) from test_table ;

11 COUNT(ID)

12----------

13 2356

15----------------------------------------------------------------------------------------

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

03Export: Release 11.2.0.1.0 - Production on Wed Jun 23 14:08:29 2010

08Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

09With the Partitioning, OLAP, Data Mining and Real Application Testing options

10Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

12About to export specified tables via Conventional Path ...

13. . exporting table TEST_TABLE

14EXP-00056: ORACLE error 1578 encountered

15ORA-01578: ORACLE data block corrupted (file # 15, block # 23)

16ORA-01110: data file 15: '/u02/oradata/logdw/test01.dbf'

17Export terminated successfully with warnings.

我们使用导出得到一个错误

通过设置内部事件，设置在全表扫描时跳过损坏的数据块

view source

print?

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

2ALTER 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.