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/
