介绍

oracle的坏块修复主要有rman和DBMS_REPAIR包两种方式（不考虑bbed）,现在我们就DBMS_REPAIR脚本方式修复进行测试。

DBMS_REPAIR修复脚本


REM Create the repair table in a given tablespace:





BEGIN

  DBMS_REPAIR.ADMIN_TABLES (

  TABLE_NAME => 
'REPAIR_TABLE',

  TABLE_TYPE => dbms_repair.repair_table,

  ACTION => dbms_repair.create_action,

  TABLESPACE => 
'&tablespace_name');

END;

/





REM Identify corrupted blocks 
for schema.object (it also can be done at partition level with parameter PARTITION_NAME):





set serveroutput on


DECLARE num_corrupt INT;

BEGIN

  num_corrupt := 
0;

  DBMS_REPAIR.CHECK_OBJECT (

  SCHEMA_NAME => 
'&schema_name',

  OBJECT_NAME => 
'&object_name',

  REPAIR_TABLE_NAME => 
'REPAIR_TABLE',

  corrupt_count => num_corrupt);

  DBMS_OUTPUT.PUT_LINE(
'number corrupt: ' || TO_CHAR (num_corrupt));

END;

/





REM Optionally display any corrupted block identified by check_object:





select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION

from REPAIR_TABLE;





REM Mark the identified blocks 
as corrupted ( Soft Corrupt - reference Note 
1496934.1 )


DECLARE num_fix INT;

BEGIN

  num_fix := 
0;

  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

  SCHEMA_NAME => 
'&schema_name',

  OBJECT_NAME=> 
'&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  REPAIR_TABLE_NAME => 
'REPAIR_TABLE',

  FIX_COUNT=> num_fix);

  DBMS_OUTPUT.PUT_LINE(
'num fix: ' || to_char(num_fix));

END;

/





REM Allow future DML statements to skip the corrupted blocks:





BEGIN

  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  SCHEMA_NAME => 
'&schema_name',

  OBJECT_NAME => 
'&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  FLAGS => dbms_repair.SKIP_FLAG);

END;

/

测试步骤：

创建测试环境：


SQL> create tablespace tt  datafile   size 
10M  autoextend 
on;





SQL> create user tt identified 
by tt 
default tablespace tt;





User created.





SQL> grant dba to tt;





Grant succeeded.





SQL> conn tt/tt

Connected.

SQL> create table testtest 
as 
select * 
from dba_objects;





Table created.





SQL> 
create index i_test 
on 
testtest(
object_id);





Index created.





SQL> col SEGMENT_NAME format a15

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





SEGMENT_NAME  HEADER_FILE HEADER_BLOCK     BLOCKS

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

TESTTEST    
201       
130       
1536

模拟坏块：

[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=266 count=1u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=650 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000326904 s, 25.1 MB/s[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=520 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000262302 s, 31.2 MB/s[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=650 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000314903 s, 26.0 MB/s

查看测试数据：



select * 
from tt.testtest;













ERROR:





ORA-01578: ORACLE data block corrupted (file 
# 201, block # 266)





ORA-01110: data file 201:





'/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf'

通过脚本进行修复


SQL> 
@repari.sh





Enter value 
for tablespace_name: TT





old   
6:   

TABLESPACE => 
'&tablespace_name');






new   
6:   

TABLESPACE => 
'TT');













PL/SQL procedure successfully completed.













Enter value 
for schema_name: TT





old   
5:   

SCHEMA_NAME => 
'&schema_name',






new   
5:   

SCHEMA_NAME => 
'TT',





Enter value 
for object_name: TESTTEST





old   
6:   

OBJECT_NAME => 
'&object_name',






new   
6:   

OBJECT_NAME => 
'TESTTEST',






number corrupt: 
3













PL/SQL procedure successfully completed.





















  BLOCK_ID CORRUPT_TYPE





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





CORRUPT_DESCRIPTION





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





       
266    
6148





















       
520    
6148





















       
650    
6148





























Enter value 
for schema_name: TT





old   
5:   

SCHEMA_NAME => 
'&schema_name',






new   
5:   

SCHEMA_NAME => 
'TT',





Enter value 
for object_name: TESTTEST





old   
6:   

OBJECT_NAME=> 
'&object_name',






new   
6:   

OBJECT_NAME=> 
'TESTTEST',





num fix: 
0













PL/SQL procedure successfully completed.













Enter value 
for schema_name: TT





old   
3:   

SCHEMA_NAME => 
'&schema_name',






new   
3:   

SCHEMA_NAME => 
'TT',





Enter value 
for object_name: TESTTEST





old   
4:   

OBJECT_NAME => 
'&object_name',






new   
4:   

OBJECT_NAME => 
'TESTTEST',













PL/SQL procedure successfully completed.

查询修复后的数据

select * from


SQL> 

select 
count(
*) 
from tt.TESTTEST;





  COUNT(*)

----------

     
73125

参考文档：

DBMS_REPAIR SCRIPT (Doc ID 556733.1)






————————————————————————————

微信公众号：天高弋猎

墨天轮：https:
//https://www.modb.pro/u/3738

ITPUB：https:
//blog.itpub.net/69924215/

ITPub博客

DBMS_REPAIR修复坏块脚本

介绍

最新文章