DB / Oracle / 备份与迁移

Oracle DBMS_REPAIR 修复损坏块详解

2026.09.06

Oracle DBMS_REPAIR 修复损坏块详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

DBMS_REPAIR 用于检测和修复块损坏[1]：

功能：

  • 检测损坏
  • 标记损坏
  • 跳过损坏
  • 重建空闲列表

详细见：Oracle DBMS_REPAIR 修复损坏块。

2. 块损坏

2.1 类型

  • 物理：介质损坏
  • 逻辑：数据不一致

2.2 检测

-- RMAN
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- 视图
SELECT * FROM v$database_block_corruption;

2.3 错误

  • ORA-01578
  • ORA-01110
  • ORA-00600

3. DBMS_REPAIR 包

3.1 管理表

-- 创建管理表
EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_ADMIN', 1, 1, 'USERS');
EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_KEY', 1, 1, 'USERS');

3.2 创建管理表

-- 1 = CREATE
-- 2 = DELETE
-- 3 = PURGE

DBMS_REPAIR.ADMIN_TABLES(
  table_name => 'REPAIR_ADMIN',
  action => 1,  -- CREATE
  table_type => 1,  -- REPAIR_TABLE
  tablespace => 'USERS'
);

4. 检测

4.1 CHECK_OBJECT

SET SERVEROUTPUT ON;

DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    corrupt_count => v_count
  );
  DBMS_OUTPUT.PUT_LINE('Corrupt blocks: ' || v_count);
END;
/

4.2 查看

SELECT object_name, block_id, corrupt_type, marked_corrupt
FROM repair_admin;

5. 修复

5.1 FIX_CORRUPT_BLOCKS

DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    fix_count => v_count
  );
  DBMS_OUTPUT.PUT_LINE('Fixed blocks: ' || v_count);
END;
/

5.2 标记

- 标记为 CORRUPT
- 跳过这些块
- 查询继续

6. 跳过

6.1 SKIP_CORRUPT_BLOCKS

-- 启用跳过
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    flags => 1  -- SKIP
  );
END;
/

-- 禁用跳过
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    flags => 0  -- NOSKIP
  );
END;
/

6.2 效果

SELECT * FROM scott.employees;
-- 跳过损坏块，不报错

7. DUMP_ORPHAN_KEYS

7.1 概述

  • 索引指向损坏块
  • 孤立键

7.2 转储

DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.DUMP_ORPHAN_KEYS(
    schema_name => 'SCOTT',
    object_name => 'IDX_EMP_ID',
    object_type => 1,  -- INDEX
    repair_table_name => 'REPAIR_ADMIN',
    orphan_table_name => 'ORPHAN_KEY',
    key_count => v_count
  );
  DBMS_OUTPUT.PUT_LINE('Orphan keys: ' || v_count);
END;
/

7.3 查看

SELECT * FROM orphan_key;

7.4 重建索引

ALTER INDEX idx_emp_id REBUILD;

8. REBUILD_FREELISTS

BEGIN
  DBMS_REPAIR.REBUILD_FREELISTS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES'
  );
END;
/

9. SEGMENT_FIX_STATUS

9.1 位图段

BEGIN
  DBMS_REPAIR.SEGMENT_FIX_STATUS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    segment_status => 1
  );
END;
/

10. 完整流程

10.1 创建表

EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_ADMIN', 1, 1, 'USERS');
EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_KEY', 1, 1, 'USERS');

10.2 检测

DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    corrupt_count => v_count
  );
  DBMS_OUTPUT.PUT_LINE('Corrupt: ' || v_count);
END;
/

10.3 修复

DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    fix_count => v_count
  );
END;
/

10.4 跳过

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES',
    flags => 1
  );
END;
/

10.5 索引

-- 孤立键
DECLARE
  v_count NUMBER;
BEGIN
  DBMS_REPAIR.DUMP_ORPHAN_KEYS(
    schema_name => 'SCOTT',
    object_name => 'IDX_EMP_ID',
    object_type => 1,
    repair_table_name => 'REPAIR_ADMIN',
    orphan_table_name => 'ORPHAN_KEY',
    key_count => v_count
  );
END;
/

-- 重建索引
ALTER INDEX idx_emp_id REBUILD;

10.6 验证

SELECT COUNT(*) FROM scott.employees;
-- 跳过损坏块

11. RMAN 块恢复

11.1 优先

- RMAN BLOCKRECOVER
- 优先尝试

11.2 命令

RMAN> RECOVER DATAFILE 5 BLOCK 100, 101;
RMAN> RECOVER CORRUPTION LIST;

12. 其他方法

12.1 ROWID 跳过

-- 找出损坏块
SELECT * FROM v$database_block_corruption;

-- 跳过该块查询
SELECT * FROM scott.employees 
WHERE ROWID NOT IN (CHARTOROWID('AAAB12AAEAAAAAbAAH'));

12.2 重建表

-- 1. 创建新表
CREATE TABLE employees_new AS 
SELECT * FROM employees WHERE ...;

-- 2. 重命名
RENAME employees TO employees_corrupt;
RENAME employees_new TO employees;

13. 常见坑与排错

13.1 ORA-01578

- 块损坏
- DBMS_REPAIR
- RMAN

13.2 数据丢失

- 损坏块数据丢失
- 备份恢复
- 或部分恢复

13.3 索引失效

- 重建索引
- 孤立键处理

14. 最佳实践

  1. RMAN 优先：自动
  2. DBMS_REPAIR 应急：手动
  3. CHECK_OBJECT：检测
  4. FIX_CORRUPT：标记
  5. SKIP_CORRUPT：业务继续
  6. DUMP_ORPHAN_KEYS：索引
  7. 重建索引：完整
  8. 备份恢复：终极
  9. 监控 v$：及时
  10. 测试：可行

15. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “DBMS_REPAIR” https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REPAIR.html