Oracle中基于RMAN如何實現壞塊介質恢復

168次閱讀

共計 4689 個字符，預計需要花費 12 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 Oracle 中基于 RMAN 如何實現壞塊介質恢復，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

對于物理損壞的數據塊，在有備份的情況下，我們可以通過 RMAN 塊介質恢復 (BLOCK MEDIA RECOVERY) 功能來完成受損塊的恢復，
而不需要恢復整個數據庫或所有文件來修復這些少量受損的數據塊。但前提條件是你得有一個可用的 RMAN 備份存在，
因此，無論何時備份就是一切。本篇我們來模擬產生一個壞塊，然后使用 RMAN 實現壞塊恢復。

說明：
一般出現壞塊的時候，都是業務訪問到這個壞塊的時候報如下的錯誤：
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 130)
ORA-01110: data file 18: /ora11gSource/ora11g/tbs_tmp.dbf

操作：
1 創建用于演示的表空間
create tablespace tbs_tmp datafile /ora11gSource/ora11g/tbs_tmp.dbf size 10m autoextend on;

2 基于新的數據文件創建對象 tb_tmp
conn scott/tiger;
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
———- ————————————————————
18 /ora11gSource/ora11g/tbs_tmp.dbf

3 表對象 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_N HEADER_FILE HEADER_BLOCK BLOCKS
——— ———– ———— ———-
TB_TMP 18 130 1280

4 使用 rman 對該數據文件進行一次備份
$ $ORACLE_HOME/bin/rman target /
RMAN backup datafile 18 tag=health;

Starting backup at 12-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 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=00018 name=/ora11gSource/ora11g/tbs_tmp.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUN-18
channel ORA_DISK_1: finished piece 1 at 12-JUN-18
piece handle=/ora11gSource/zhida/ORA11G/backupset/2018_06_12/o1_mf_nnndf_HEALTH_fkz35h7r_.bkp tag=HEALTH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-JUN-18

5 人為制造一個單塊數據塊的損壞
使用 linux 自帶的 dd 命令來損壞單塊數據塊
ora11g[redora]/home/ora11g dd of=/ora11gSource/ora11g/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, 5.9505e-05 seconds, 286 kB/s

6 觸發壞塊所在對象
清空 buffer cache
conn scott/tiger;
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 # 18, block # 130)
ORA-01110: data file 18: /ora11gSource/ora11g/tbs_tmp.dbf
查詢視圖 v$database_block_corruption，提示有壞塊，注意該視圖可能不會返回任何數據，如無返回，先執行 backup validate
SQL select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
18 130 1 0 CORRUPT

7 使用 dbv 工具來校驗壞塊
ora11g[redora]/home/ora11g dbv file=/ora11gSource/ora11g/tbs_tmp.dbf feedback=1000
DBVERIFY: Release 11.2.0.4.0 – Production on Wed Jun 13 10:25:13 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY – Verification starting : FILE = /ora11gSource/ora11g/tbs_tmp.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x04800082 (file 18, block 130)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x65747075
last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x8d8d2301
check value in block header: 0xc50a
block checksum disabled
..
DBVERIFY – Verification complete
Total Pages Examined : 1536
Total Pages Processed (Data) : 1196
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 154
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 185
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 939342345 (3332.939342345)

8 下面使用 blockrecover 來恢復壞塊
RMAN blockrecover datafile 18 block 130;

Starting recover at 13-JUN-18
using channel ORA_DISK_1
searching flashback logs for block images until SCN 14311770329033
finished flashback log search, restored 1 blocks

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

Finished recover at 13-JUN-18

9 驗證修復效果
再次查詢表 tb_emp 正常
SQL select count(*) from tb_tmp;

COUNT(*)
———-
72449

ora11g[redora]/home/ora11g dbv file=/ora11gSource/ora11g/tbs_tmp.dbf feedback=1000
DBVERIFY: Release 11.2.0.4.0 – Production on Wed Jun 13 10:26:31 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY – Verification starting : FILE = /ora11gSource/ora11g/tbs_tmp.dbf
..
DBVERIFY – Verification complete
Total Pages Examined : 1536
Total Pages Processed (Data) : 1196
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 155
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 185
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 939342345 (3332.939342345)