如何使用DBMS_REPAIR检测和修补数据坏块 （3）

     来源：赛迪网    作者：xiaoqiao

下面的查询可以用来从坏块中抢救数据。

建立一个临时表(temp_t1)以方便数据的插入：

SQL> create table temp_t1 as

2 select * from system.t1

3 where dbms_rowid.rowid_block_number(rowid) = 3

4 and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;

Table created.

SQL> select col1 from temp_t1;

COL1

----------

2

3

四、使用DBMS_REPAIR.FIX_CORRUPT_BLOCKS来标识坏块

FIX_CORRUPT_BLOCKS procedure用来根据repair table中的信息修正指定objects中的坏块。当这个块被标识为坏了以后，做全表扫描将引起ORA-1578错。

SQL> declare

2 fix_count int;

3 begin

4 fix_count := 0;

5 dbms_repair.fix_corrupt_blocks (

6 schema_name => 'SYSTEM',

7 object_name => 'T1',

8 object_type => dbms_repair.table_object,

9 repair_table_name => 'REPAIR_TABLE',

10 fix_count => fix_count);

11 dbms_output.put_line('fix count: ' || to_char(fix_count));

12 end;

13 /

fix count: 1

PL/SQL procedure successfully completed.

查询repair_table可以看到block 3已经被标识：

SQL> select object_name, block_id, marked_corrupt

2 from repair_table;

OBJECT_NAME BLOCK_ID MARKED_COR

------------------------------ ---------- ----------

T1 3 TRUE

这时再对table t1做全表扫描，ORA-1578将会出现。

SQL> select * from system.t1;

select * from system.t1

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 3)

ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'

五、使用DBMS_REPAIR.DUMP_ORPHAN_KEYS来修补相关的index

DUMP_ORPHAN_KEYS将会显示指向数据坏块中记录的index entries

下列查询显示与坏块相关的index。

SQL> select index_name from dba_indexes

2 where table_name in (select distinct object_name from repair_table);

INDEX_NAME

------------------------------

T1_PK

SQL> @dumpOrphanKeys

SQL> set serveroutput on

SQL>

SQL> declare

2 key_count int;

3 begin

4 key_count := 0;

5 dbms_repair.dump_orphan_keys (

6 schema_name => 'SYSTEM',

7 object_name => 'T1_PK',

8 object_type => dbms_repair.index_object,

9 repair_table_name => 'REPAIR_TABLE',

10 orphan_table_name => 'ORPHAN_KEY_TABLE',

11 key_count => key_count);

12 dbms_output.put_line('orphan key count: ' || to_char(key_count));

13 end;

14 /

orphan key count: 3

PL/SQL procedure successfully completed.

orphan_key_table的结构如下：

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

下列查询显示t1_pk index中有3个index entries与坏块有关：

SQL> select index_name, count(*) from orphan_key_table

2 group by index_name;

INDEX_NAME COUNT(*)

------------------------------ ----------

T1_PK 3