dbms_repair包修复损坏的数据块

       dbms_repair包含修复损坏数据块的存储过程，可以检查表和索引数据块损坏的情况，然后标记坏块，再使用表的时候会绕过坏块，但是坏块中的数据会丢失。

---创建测试表空间
SYS@orcl>create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 5m;
Tablespace created.

---创建测试表
SYS@orcl>create table scott.test tablespace test as select * from dba_objects where rownum<=3000;
Table created.

SYS@orcl>select count(*) from scott.test;
  COUNT(*)
----------
      3000

---创建索引

SYS@orcl>create index scott.idx_obj on scott.test(object_name) tablespace users;
Index created.

---查出包含行记录的数据块
SYS@orcl>select distinct dbms_rowid.rowid_block_number(rowid) from scott.test order by 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                 131
                 132
                 133
                 134
                 135
                 136
                 137
                 138
                 139
                 140
                 141

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                 142
                 143
                 145
                 146
                 147
                 148
                 149
                 150
                 151
                 152
                 153

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                 154
                 155
                 156
                 157
                 158
                 159
                 161
                 162
                 163
                 164
                 165

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                 166
                 167
                 168
                 169
                 170
                 171

39 rows selected.


---破坏138、156、163数据块的内容，这里的char是建立的一个包含4096个字节的文件
[oracle@ora ~]$ dd if=char of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=138 count=1
0+1 records in
0+1 records out
4096 bytes (4.1 kB) copied, 2.5423e-05 s, 161 MB/s
[oracle@ora ~]$ dd if=char of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=156 count=1
0+1 records in
0+1 records out
4096 bytes (4.1 kB) copied, 1.8299e-05 s, 224 MB/s
[oracle@ora ~]$ dd if=char of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=163 count=1
0+1 records in
0+1 records out
4096 bytes (4.1 kB) copied, 2.3117e-05 s, 177 MB/s

---清除buffer cache的内容
SYS@orcl>alter system flush buffer_cache;
System altered.

SYS@orcl>select * from scott.test;
…………省略…………
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME            OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP        STATUS    T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
17-SEP-11 17-SEP-11 2011-09-17:09:46:29 VALID    N N N           1


ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 138)
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test01.dbf'

555 rows selected.

---创建Repair 表
SYS@orcl>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.

---创建Orphan Key 表
SYS@orcl>
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.

---检查坏块
SYS@orcl>set serveroutput on
declare
  num_corrupt int;
begin
  num_corrupt :=0;
  dbms_repair.check_object(
  schema_name =>'SCOTT',
  object_name =>'TEST',
  repair_table_name =>'REPAIR_TABLE',
  corrupt_count =>num_corrupt);
  dbms_output.put_line('number corrupt:' || to_char(num_corrupt));
end;
/

number corrupt:3

PL/SQL procedure successfully completed.

SYS@orcl>select object_name, block_id, corrupt_type, marked_corrupt, repair_description from repair_table;

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ------------------------------
TEST          138          6148 TRUE       mark block software corrupt
TEST          156          6148 TRUE       mark block software corrupt
TEST          163          6148 TRUE       mark block software corrupt

---定位坏块
SYS@orcl>set serveroutput on
declare
  cc number;
begin
  dbms_repair.fix_corrupt_blocks(schema_name => 'SCOTT',
  object_name => 'TEST',
  fix_count => cc);
  dbms_output.put_line('Number of blocks fixed: ' || to_char(cc));
end;
/
Number of blocks fixed: 0

PL/SQL procedure successfully completed.

---查询丢失数据的rowid

SYS@orcl>set serveroutput on
declare
   cc number;
begin
  dbms_repair.dump_orphan_keys
  (
     schema_name => 'SCOTT',
     object_name => 'IDX_OBJ',
     object_type => dbms_repair.index_object,
     repair_table_name => 'REPAIR_TABLE',
     orphan_table_name=> 'ORPHAN_KEY_TABLE',
     key_count => cc
  );
  dbms_output.put_line('Number of orphan keys: ' || to_char(cc));
end;
/
Number of orphan keys: 233

PL/SQL procedure successfully completed.

---跳过坏块

SYS@orcl>begin
dbms_repair.skip_corrupt_blocks(schema_name =>'SCOTT',object_name => 'TEST',flags => 1);

end;

/

PL/SQL procedure successfully completed.

SYS@orcl>select * from scott.test;
…………省略…………
OWNER                   OBJECT_NAM SUBOBJECT_NAME
------------------------------ ---------- ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          CREATED    LAST_DDL_
---------- -------------- ------------------- --------- ---------
TIMESTAMP        STATUS  T G S  NAMESPACE EDITION_NAME
------------------- ------- - - - ---------- ------------------------------
                   S_GROUP
      3002          VIEW              17-SEP-11 17-SEP-11
2011-09-17:09:47:11 VALID   N N N       1

2767 rows selected.

---丢失233条记录，丢失记录的rowid可以在ORPHAN_KEY_TABLE表中找到

---重建索引
SYS@orcl>alter index scott.idx_obj rebuild;

Index altered.

    用这种方法可以找回部分数据，也可以找回建了索引的值，但是使用dbv再检查test表空间的数据文件时，仍然会显示有损坏的数据块，这时需要把test表的数据全部导出，再重建表或者表空间，然后再把找回的数据导入数据库，推荐用expdp/impdp命令做，可以彻底消除dbv检查到的坏块。