DBMS_REPAIR 修复坏块脚本

黑獭 2024-06-24

156

DBM

介绍

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 onDECLARE 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_DESCRIPTIONfrom 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/ttConnected.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 a15SQL>  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/3738ITPUB：https://blog.itpub.net/69924215/

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

DBMS_REPAIR 修复坏块脚本

介绍

评论