DBMS_REPAIR 修复坏块脚本

数据运维 2024-06-24 醒在深海的猫 手机阅读

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 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

模拟坏块：

 

 

DBMS_REPAIR 修复坏块脚本-每日运维

[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt<em>m7kvq4fy</em>.dbf bs=8192 conv=notrunc seek=266 count=1 u02/app/oracle/oradata/TEST/datafile/o1_mf_tt<em>m7kvq4fy</em>.dbf bs=8192 conv=notrunc seek=650 count=11+0 records in 1+0 records out 8192 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<em>m7kvq4fy</em>.dbf bs=8192 conv=notrunc seek=520 count=1 1+0 records in 1+0 records out 8192 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<em>m7kvq4fy</em>.dbf bs=8192 conv=notrunc seek=650 count=1 1+0 records in 1+0 records out 8192 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<em>m7kvq4fy</em>.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.

查询修复后的数据

 

DBMS_REPAIR 修复坏块脚本-每日运维

select  * from

DBMS_REPAIR 修复坏块脚本-每日运维

SQL> select count(<em>) from tt.TESTTEST; COUNT(</em>) ---------- 73125

 

参考文档：

DBMS_REPAIR SCRIPT (Doc ID 556733.1)

———————————————————————————— 微信公众号：天高弋猎 墨天轮：<a href="https://https://www.modb.pro/u/3738">https://https://www.modb.pro/u/3738</a> ITPUB：<a href="https://blog.itpub.net/69924215/">https://blog.itpub.net/69924215/</a>