使用dbms_repair修复受损数据块
1、根据在全表扫描中出现的受损数据块的绝对文件号和数据块号的提示确定受损对象:
select segment_name,segment_type,owner,tablespace_name,partition_name
from dba_extents
where file_id = &AFN and &BL between block_id and block_id + blocks - 1;
2、使用dbms_repair包创建两个数据表:
declare
begin
dbms_repair.admin_tables(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.REPAIR_TABLE,
action => dbms_repair.CREATE_ACTION,
tablespace => 'TESTDATA');
end;
declare
begin
dbms_repair.admin_tables(table_type => dbms_repair.ORPHAN_TABLE,
action => dbms_repair.CREATE_ACTION,
tablespace => 'TESTDATA');
end;
3、检查受损对象,并将检查结果存入之前创建的表中:
set serveroutput on;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object(schema_name => 'TEST',
object_name => 'B',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count:'||to_char(rpr_count));
end;
4、查看检查结果:
select t.object_name,
t.block_id,
t.corrupt_type,
t.marked_corrupt,
t.corrupt_description,
t.repair_description
from repair_table t;
5、如果受损数据块还没有被标记为“software corrupt”,使用dbms_repair包将受损数据块标记为”software corrupt”:
set serveroutput on;
declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks(schema_name => 'TEST',
object_name => 'B',
object_type => dbms_repair.TABLE_OBJECT,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_count);
dbms_output.put_line('fix_count:' || to_char(fix_count));
end;
6、查看有多少索引项指向了坏块:
select index_name
from dba_indexes
where table_name in (select distinct object_name from repair_table);
set serveroutput on;
declare
key_count int;
begin
key_count := 0;
dbms_repair.dump_orphan_keys(schema_name => 'TEST',
object_name => 'TEST_PK',
object_type => dbms_repair.INDEX_OBJECT,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => key_count);
dbms_output.put_line('orphan key count:' || to_char(key_count));
end;
select index_name,count(1) from orphan_key_table group by index_name;
7、 使用dbms_repair包将受损数据块设置为skip,或使用10231事件,使在全表扫描中屏蔽ora-1578错误:
begin
dbms_repair.skip_corrupt_blocks(schema_name => 'TEST',
object_name => 'B',
object_type => dbms_repair.TABLE_OBJECT,
flags => dbms_repair.SKIP_FLAG);
end;
8、 重建表和索引:
使用CTAS方法重建表,或者使用dbms_repair包重建对象的freelists
declare
begin
dbms_repair.rebuild_freelists(schema_name => 'TEST',
object_name => 'B',
object_type => dbms_repair.TABLE_OBJECT);
end;
使用alter index indexname rebuild online命令重建索引