Oracle中如何使用 DBMS_REPAIR 修復壞塊

這篇文章主要為大家展示了“Oracle中如何使用 DBMS_REPAIR 修復壞塊”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Oracle中如何使用 DBMS_REPAIR 修復壞塊”這篇文章吧。

對于Oracle數據塊物理損壞的情形，在我們有備份的情況下可以直接使用備份來恢復。對于通過備份恢復，Oracel為我們提供了很多種方式，冷備，基于用戶管理方式，RMAN方式等等。對于這幾種方式我們需要實現基于數據庫以及文件級別的恢復。RMAN同時也提供了基于塊介質方式的恢復。也就是說我們根本不需要還原數據文件，而是直接從備份文件基于塊來提取以實現聯機恢復。可參考基于RMAN實現壞塊介質恢復(blockrecover) 。這是比較理想的情形。如果沒有任何備份怎么辦？我們可以使用Oracle自帶的DBMS_REPAIR包來實現修復。注意，不要被文章題目有所誤導。這里的修復是有損修復也就是說將受損的數據塊標記為壞塊，不對其進行訪問而已。就好比我們磁盤有壞道，找個磁盤修復工具將壞道標出來不使用，同理。那受損的數據豈不是無力回天啦，嗚嗚......要記得隨時備份阿。

1、DBMS_REPAIR包所含的過程
  Procedure_Name       Description
  -----------------    ------------------------------------
  ADMIN_TABLES         Provides administrative functions (create, drop, purge) for repair or orphan key tables.
                       Note: These tables are always created in the SYS schema.
  CHECK_OBJECT         Detects and reports corruptions in a table or index
  DUMP_ORPHAN_KEYS     Reports on index entries that point to rows in corrupt data blocks
  FIX_CORRUPT_BLOCKS   Marks blocks as software corrupt that have been previously identified as corrupt by the CHECK_OBJECT procedure
  REBUILD_FREELISTS    Rebuilds the free lists of the object
  SEGMENT_FIX_STATUS   Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO
  SKIP_CORRUPT_BLOCKS  When used, ignores blocks marked corrupt during table and index scans.
                       If not used, you get error ORA-01578 when encountering blocks marked corrupt.

2、DBMS_REPAIR的一些局限性
  Tables with LOB data types, nested tables, and varrays are supported, but the out-of-line columns are ignored.
  Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.
  Index-organized tables and LOB indexes are not supported.
  The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
  The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long.


3.創建演示環境
--創建表空間  
SQL> show user;
USER is "SCOTT"
SQL> create tablespace tbs_tmp datafile '/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf' size 10m autoextend on;

Tablespace created.

--創建表對象tb_obj及其索引  
SQL> create table tb_obj tablespace tbs_tmp as select * from dba_objects;

Table created.
 
SQL> create index i_tb_obj on tb_obj(object_id);  

Index created.

--表段上的相關信息  
SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name ='TB_OBJ';  

SEGMENT_NAME                                                                      HEADER_FILE HEADER_BLOCK     BLOCKS
--------------------------------------------------------------------------------- ----------- ------------ ----------
TB_OBJ                                                                                      6          130       1280

--使用linux自帶的dd命令來損壞數據塊  
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf bs=8192 conv=notrunc seek=131 <<eof     
> Corrupt me!
> EOF
0+1 records in
0+1 records out
13 bytes (13 B) copied, 0.000117639 s, 111 kB/s
[oracle@wang ~]$


--下面的查詢收到了錯誤提示
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

SQL>

以上是“Oracle中如何使用 DBMS_REPAIR 修復壞塊”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！