oracle故障恢复实验(4)-单个数据块损坏和多个数据块损坏(DBMS_REPAIR)

利用完rman对数据块进行恢复后再利用dbms_repair存储过程对数据块损坏进行修复，有时候场景中没有备份，扫描整个数据文件，发现并标注出坏块，做这个实验我用的之前的test表，test表空间，再新建索引index_test，

1.建立索引

SQL> create index index_test on test(object_id);

2.查询表信息

SQL> set line 200

SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'TEST';

SQL> select * from dba_extents where segment_name='TEST';

3.模拟坏块

[root@oracle11g ~]# dd if=/dev/zero of=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf bs=8192 conv=notrunc seek=1468 count=1

[root@oracle11g ~]# dd if=/dev/zero of=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf bs=8192 conv=notrunc seek=1668 count=1

[root@oracle11g ~]# dd if=/dev/zero of=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf bs=8192 conv=notrunc seek=2468 count=1

4.清空缓存

SQL> alter system flush buffer_cache;

5.查询数据报错

SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 1468)
ORA-01110: data file 14: '/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf'

使用dbv工具发现数据块错误

dbv file=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf blocksize=8192;

告警日志同样出现错误

这个时候没有备份，想要完整修复数据块

6.创建 一张repair 表，用于记录需要被修复的表

begin
dbms_repair.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'USERS');
end;
/

SQL> select owner,object_name,object_type from dba_objects where object_name like '%REPAIR_TABLE';

7.使用dbms_repair.check_object存储过程进行坏块检测

SQL> set serveroutput on size 100000;
declare
num_corrupt int;
begin
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
schema_name =>'SYS',
object_name =>'TEST',
repair_table_name =>'REPAIR_TABLE',
corrupt_count =>num_corrupt);
dbms_output.put_line('number corrupt:' || to_char(num_corrupt));
end;
12 /
number corrupt:3

PL/SQL procedure successfully completed.

有三个坏块被检查出来

查询坏块信息

SQL> select object_name, block_id, corrupt_type, marked_corrupt, repair_description from repair_table;

通过运行dbms_repair.check_object，将坏块信息存放到了repair_table表中，其中有个字段marked_corrupt，用于标识该块是否被标识为坏块，当被标识为true时，即该块被标识为坏块。

8.使用dbms_repair.fix_corrupt_blocks坏块修复标识

declare
fix_block_count int;
begin
fix_block_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'SYS',
object_name => 'TEST',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_block_count);
dbms_output.put_line('fix blocks count: ' ||
to_char(fix_block_count));
end;
14 /
fix blocks count: 0

PL/SQL procedure successfully completed.

fix blocks count=0，即在上一步进行check_object时已经进行了坏块标识 ，然后修复。这个时候当进行全表扫描，仍然会查询到坏块而报错。

9.使用dbms_repair.dump_orphan_keys过程来保存坏块的索引键值，然后再执行skip_corrupt_blocks过程之后，我们才能重建索引，不然重建索引时新的索引仍然会引用坏块。首先要建立ORPHAN_KEY_TABLE，此表就是用来存放坏块的索引键值，先创建 Orphan Key 表，用于记录在表块损坏后那些孤立索引，也就是指向坏块的那些索引

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.

执行过程dbms_repair.dump_orphan_keys将坏块键值存放到上面所创建的表中

declare
schema_name => 'SYS',
object_name => 'INDEX_TEST',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' || to_char(orph_count));
end;
orph_count int;
2 3 begin
orph_count:= 0;
dbms_repair.dump_orphan_keys (
schema_name => 'SYS',
object_name => 'INDEX_TEST',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' || to_char(orph_count));
end;
14 /
orphan-index entries: 211

PL/SQL procedure successfully completed.

object_name 是索引名，不是 table_name，这里 dump 的是损坏的索引信息.如果表有多个索引，则为每个索引执行 DUMP_ORPHAN_KEYS 操作

查询orphan_key_table结果与上面dbms_repair.dump_orphan_keys返回结果一致

SQL> select index_name, count(*) from orphan_key_table group by index_name;

INDEX_NAME COUNT(*)
------------------------------ ----------
INDEX_TEST 211

10.执行skip_corrupt_blocks过程跳过坏块

declare

begin

dbms_repair.skip_corrupt_blocks (

schema_name => 'SYS',

object_name => 'TEST',

object_type => dbms_repair.table_object,

 flags => dbms_repair.skip_flag);

end;

 /

SQL> select table_name, skip_corrupt from dba_tables where table_name = 'TEST';

TABLE_NAME  SKIP_COR
------------------------------ --------
TEST                ENABLED

11.查询对比：使用索引查询和count(*)查询结果不一样

SQL> select count(*) from test;---非索引

COUNT(*)
----------
86257

SQL> select count(object_id) from test;--索引

COUNT(OBJECT_ID)
----------------
86468

对比丢失了211条记录，丢失记录的 rowid 可以在 ORPHAN_KEY_TABLE 表中找到

对索引drop再create

SQL> drop index index_test;

Index dropped.

SQL> create index index_test on test(object_id);

Index created.

SQL> select count(object_id) from test;

COUNT(OBJECT_ID)
----------------
86257

SQL> select count(*) from test;---非索引

COUNT(*)
----------
86257

所以最后得到的结果是使用dbms_repair并不是将数据文件坏块恢复到完好状态，而是将这些坏块进行标记从而跳过坏块以便对表可以使用查询和DML操作，结果还是会丢失掉少数数据，通过V$DATABASE_BLOCK_CORRUPTION和dbv工具还是能看到坏块信息

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
14 1468 1 0 ALL ZERO

最后还是利用备份将坏块修复好，所以备份非常非常重要~~~