基於RMAN實現壞塊介質恢複(blockrecover)

對於物理損壞的資料區塊，我們可以通過RMAN塊介質恢複(BLOCK MEDIA RECOVERY)功能來完成受損塊的恢複，而不需要恢複整個資料庫或所有檔案來修複這些少量受損的資料區塊。恢複整個資料庫或資料檔案那不是大炮用來打蚊子，有點不值得！但前提條件是你得有一個可用的RMAN備份存在，因此，無論何時備份就是一切。本文示範了產生壞塊即使用RMAN實現壞塊恢複的全過程。

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

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

RMAN備份策略制定參考內容

RMAN備份學習筆記

OracleDatabase Backup加密 RMAN加密

1、建立示範環境

SQL> select * from v$version where rownum<2;

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

--建立用於示範的data file
SQL> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;

SQL> conn scott/tiger;

--基於新的資料檔案建立對象tb_tmp
SQL> create table tb_tmp tablespace tbs_tmp as select * from dba_objects;

SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP';

  FILE_ID FILE_NAME
---------- ------------------------------------------------------------
        6 /u02/database/usbo/oradata/tbs_tmp.dbf

--表對象tb_tmp上的資訊，包含對應的檔案資訊，頭部塊，總塊數
SQL> select segment_name , header_file , header_block,blocks     
  2  from dba_segments
  3  where segment_name = 'TB_TMP' and owner='SCOTT';

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

--首先使用rman備份對應的資料檔案
$ $ORACLE_HOME/bin/rman target /
RMAN> backup datafile 6 tag=health;

Starting backup at 2013/08/28 17:03:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
channel ORA_DISK_1: starting piece 1 at 2013/08/28 17:03:16
channel ORA_DISK_1: finished piece 1 at 2013/08/28 17:03:17
piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013/08/28 17:03:17
RMAN> exit

2、單塊資料區塊損壞的恢複處理

--下面使用了linux內建的dd命令來損壞單塊資料區塊
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 <<EOF
> Corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000184519 seconds, 92.1 kB/s

--清空buffer cache
SQL> alter system flush buffer_cache;

--查詢表對相 tb_tmp,收到ORA-01578
SQL> select count(*) from tb_tmp;
select count(*) from tb_tmp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'

--查詢檢視v$database_block_corruption，提示有壞塊，注意該視圖可能不會返回任何資料，如無返回，先執行backup validate
SQL> select * from v$database_block_corruption;

    FILE#    BLOCK#    BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        6        129          1                  0 CORRUPT

--也可以使用dbv工具來校正壞塊，參考:  

--下面使用blockrecover來恢複壞塊     
RMAN> blockrecover datafile 6 block 130;

Starting recover at 2013/08/28 17:22:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp
channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 2013/08/28 17:22:31

--重新查詢表tb_emp正常
SQL> select count(*) from tb_tmp;

  COUNT(*)
----------
    72449

1 2 3 下一頁