Oracle中如何使用 DBMS_REPAIR 修复坏块

–下面的查询收到了错误提示
SQL> select count(*) from tb_obj;
select count(*) from tb_obj
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf’

SQL>

4.使用DBMS_REPAIR修复坏块
Step a 创建相应的表对象 :

–使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象，用于记录需要被修复的表
SQL>BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => ‘REPAIR_TABLE’,
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => ‘USERS’);
END;
/

PL/SQL procedure successfully completed.

SQL>

–使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象，用于记录在表块损坏后那些孤立索引，也就是指向坏块的那些索引
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
TABLE_NAME => ‘ORPHAN_KEY_TABLE’,
TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
ACTION => DBMS_REPAIR.CREATE_ACTION,
TABLESPACE => ‘USERS’
);
END;
/

PL/SQL procedure successfully completed.

Step b 校验受损的对象 :
–使用DBMS_REPAIR.CHECK_OBJECT来检测对象上受损的情形，并返回受损块数
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => ‘SCOTT’,
OBJECT_NAME => ‘TB_OBJ’,
REPAIR_TABLE_NAME => ‘REPAIR_TABLE’,
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE(‘number corrupt: ‘ || TO_CHAR (num_corrupt));
END;
/

number corrupt: 1

PL/SQL procedure successfully completed.

–下面我们可以从repair_table查询到受损的块
–从下面的查询中可以看出列marked_corrupt全部为true，表明我们在CHECK_OBJECT已经标注了坏块
COLUMN object_name FORMAT a10
COLUMN repair_description FORMAT a28
SET LINES 10000
SELECT object_name, block_id, corrupt_type,marked_corrupt,repair_description FROM repair_table;

OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
———- ———- ———— ———- —————————-
TB_OBJ 131 6148 TRUE mark block software corrupt

Step c 标记坏块
–过程FIX_CORRUPT_BLOCKS用于标记坏块，在这个演示中，我们在CHECK_OBJECT已经被标注了，如没有执行下面的过程
–由于上一步已经标注，所以下面的输出为0
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => ‘SCOTT’,
OBJECT_NAME=> ‘TB_OBJ’,
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;
/

num fix: 0

PL/SQL procedure successfully completed.

Step d DUMP孤立的索引键值
–使用DUMP_ORPHAN_KEYS过程将那些指向坏块的索引键值填充到ORPHAN_KEY_TABLE，I_TB_OBJ索引名
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => ‘SCOTT’,
OBJECT_NAME => ‘I_TB_OBJ’,
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => ‘REPAIR_TABLE’,
ORPHAN_TABLE_NAME=> ‘ORPHAN_KEY_TABLE’,
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE(‘orphan key count: ‘ || TO_CHAR(num_orphans));
END;
/

orphan key count: 88

PL/SQL procedure successfully completed.

–下面的查询可以看到正好等于上面返回的数量也就是88条记录
SQL> select count(*) from orphan_key_table;

COUNT(*)
———-
88

–验证对象是否可以查询，下面的结果显示依旧无法查询
SQL> show user
USER is “SCOTT”
SQL> SELECT COUNT(*) FROM TB_OBJ;
SELECT COUNT(*) FROM TB_OBJ
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf’

SQL>

Step e 跳过坏块
–使用SKIP_CORRUPT_BLOCKS来告知Oracle哪些坏块需要被跳过
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => ‘SCOTT’,
OBJECT_NAME => ‘TB_OBJ’,
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/

PL/SQL procedure successfully completed.

–由于索引键上存在孤立索引，因此我们重建索引
SQL> alter index scott.i_tb_obj rebuild;

Index altered.

–验证结果
SQL> show user
USER is “SCOTT”
SQL>
SQL> select count(*) from tb_obj;

COUNT(*)
———-
86952