使用 DBMS_REPAIR 修複壞塊

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

使用RMAN的Duplicate功能建立物理DataGuard

Oracle基礎教程之通過RMAN複製資料庫

RMAN備份策略制定參考內容

RMAN備份學習筆記

OracleDatabase Backup加密 RMAN加密

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、建立示範環境

--當前環境
sys@USBO> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--建立資料表空間
sys@USBO> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;

--建立表對象tb_obj及其索引
sys@USBO> create table tb_obj tablespace tbs_tmp as select * from dba_objects;

sys@USBO> create index i_tb_obj on tb_obj(object_id);

--表段上的相關資訊
sys@USBO> select segment_name , header_file , header_block,blocks
  2  from dba_segments where segment_name ='TB_OBJ';

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

--使用linux內建的dd命令來損壞資料區塊
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=131 <<EOF
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.000209854 seconds, 57.2 kB/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=141 <<EOF
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.00019939 seconds, 60.2 kB/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=151 <<EOF
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 2.1672e-05 seconds, 554 kB/s

sys@USBO> alter system flush buffer_cache;

--下面的查詢收到了錯誤提示
sys@USBO> 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: '/u02/database/usbo/oradata/tbs_tmp.dbf'

 

 

1 2 下一頁