本文轉自：樂沙彌的世界 對於物理損壞的數據塊，我們可以通過RMAN塊介質恢復(BLOCK MEDIA RECOVERY)功能來完成受損塊的恢復，而不需要恢復整個資料庫或所有文件來修複這些少量受損的數據塊。恢復整個資料庫或數據文件那不是大炮用來打蚊子，有點不值得！但前提條件是你得有一個可用的RMAN備 ...
本文轉自：樂沙彌的世界
對於物理損壞的數據塊，我們可以通過RMAN塊介質恢復(BLOCK MEDIA RECOVERY)功能來完成受損塊的恢復，而不需要恢復整個資料庫或所有文件來修複這些少量受損的數據塊。恢復整個資料庫或數據文件那不是大炮用來打蚊子，有點不值得！但前提條件是你得有一個可用的RMAN備份存在，因此，無論何時備份就是一切。本文演示了產生壞塊即使用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工具來校驗壞塊，參考: http://blog.csdn.net/robinson_0612/article/details/6530890 --下麵使用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
3、多塊數據塊損壞的恢復處理
--下麵使用linux dd命令對不連續塊損壞 [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=133 <<EOF > New corrupted block! > EOF 0+1 records in 0+1 records out 21 bytes (21 B) copied, 0.000182835 seconds, 115 kB/s [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=143 <<EOF > New corrupted block! > EOF 0+1 records in 0+1 records out 21 bytes (21 B) copied, 0.000115527 seconds, 182 kB/s [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=153 <<EOF > New corrupted block! > EOF 0+1 records in 0+1 records out 21 bytes (21 B) copied, 0.000335781 seconds, 62.5 kB/s SQL> alter system flush buffer_cache; --下麵提示塊133被損壞，註意我們損壞了多塊數據塊，但查詢時，從塊號最小的開始提示，如133被修複後還有壞塊則繼續提示133之後的壞塊 SQL> select count(*) from scott.tb_tmp; select count(*) from scott.tb_tmp * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 133) ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf' --查詢視圖v$database_block_corruption無任何記錄 SQL> select * from v$database_block_corruption; no rows selected --下麵使用backup validate來校驗數據文件 RMAN> backup validate datafile 6; Starting backup at 2013/08/29 09:42:04 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 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: backup set complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 6 FAILED 0 223 1408 838489 --欄位Status為FAILED File Name: /u02/database/usbo/oradata/tbs_tmp.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 1029 Index 0 0 Other 3 156 --有3個Blocks Failing validate found one or more corrupt blocks See trace file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_27874.trc for details Finished backup at 2013/08/29 09:42:06 --再次查詢v$database_block_corruption，表明有3個損壞的塊 SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 6 153 1 0 CORRUPT 6 143 1 0 CORRUPT 6 133 1 0 CORRUPT --下麵直接使用blockrecover corruption list來恢復，如下所有剛剛被校驗的壞塊都會被恢復 RMAN> blockrecover corruption list; Starting recover at 2013/08/29 10:05:24 using channel ORA_DISK_1 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/29 10:05:28 --校驗結果 SQL> select count(*) from scott.tb_tmp; COUNT(*) ---------- 72449
4、壞塊的對象定位與影響
--下麵我們查詢塊號為163上的對象 SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id, 2 dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id 3 from scott.tb_tmp where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2; OBJECT_ID FILE_ID BLOCK_ID OWNER OBJECT_NAME OBJECT_ID ---------- ---------- ---------- ------------ ------------------------------ ---------- 74555 6 163 SYS GV_$QUEUEING_MTH 2439 74555 6 163 PUBLIC GV$QUEUEING_MTH 2440 --使用上面的方法，我們損塊塊163，173，此處不再列出 a、對於壞塊對象無法進行聚合彙總等操作 SQL> select count(*) from scott.tb_tmp; select count(*) from scott.tb_tmp * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 163) ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf' b、對於壞塊上的記錄無法被查詢 --我們使用基於之前查詢到的OBJECT_ID來查詢 SQL> select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440); select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440) * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 163) ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf' --如下麵的查詢，位於損壞塊上的數據無法被查詢到，但對於未損壞的依舊可以查詢。下麵的查詢時塊161上的對象 SQL> select owner,object_name,object_id from scott.tb_tmp 2 where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3; OWNER OBJECT_NAME OBJECT_ID ------------------------------ ------------------------------ ---------- PUBLIC GV$RECOVERY_LOG 2285 SYS GV_$ARCHIVE_GAP 2286 --Author : Robinson Cheng --Blog : http://blog.csdn.net/robinson_0612 c、定位受損塊所對應的對象 SQL> run get_obj_name_from_corrupt_block 1 SELECT tablespace_name, 2 segment_type, 3 owner, 4 segment_name, 5 partition_name 6 FROM dba_extents 7* WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1 Enter value for file_id: 6 Enter value for block_id: 133 old 7: WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1 new 7: WHERE file_id = 6 AND 133 BETWEEN block_id AND block_id + blocks - 1 TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME ------------------------------ ------------------ -------------- ----------------- ----------------- TBS_TMP TABLE SCOTT TB_TMP d、對於損壞的數據文件，預設情況下，不能對其進行備份，如下 RMAN> backup datafile 6 tag='corruption'; Starting backup at 2013/08/29 10:37:32 using channel ORA_DISK_1 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/29 10:37:32 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/29/2013 10:37:33 ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/database/usbo/oradata/tbs_tmp.dbf --需要設定允許損壞塊的數量之後才能進行備份 RMAN> run{ 2> set maxcorrupt for datafile 6 to 2; 3> backup datafile 6 tag='corruption'; 4> } executing command: SET MAX CORRUPT Starting backup at 2013/08/29 10:41:24 using channel ORA_DISK_1 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/29 10:41:25 channel ORA_DISK_1: finished piece 1 at 2013/08/29 10:41:26 piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_29/o1_mf_nnndf_CORRUPTION_91xf6o18_.bkp tag=CORRUPTION comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2013/08/29 10:41:26 --查看備份信息如下，應在修複壞塊後重新備份以避免由於保留策略導致先前可用的備份被aged out RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- ------------------- ------- ------- ---------- --- 1 B F A DISK 2013/08/28 17:03:17 1 1 NO HEALTH 3 B F A DISK 2013/08/29 10:41:25 1 1 NO CORRUPTION
5、後記
a、對於受損的數據塊，僅僅壞塊上的數據無法被查詢或讀取，其餘正常塊的數據依舊可以使用。
b、對於受損的表對象進行聚合等相關運算時收到錯誤提示，因為壞塊上的數據無法被統計。如果你聚合的是索引列，索引未損壞的情形則可正常返回。
c、可以基於RMAN可用的備份文件實現塊介質恢復，其數據文件無需offline，開銷最小，影響最小。
d、對於多個數據塊的損壞，先執行backup validate校驗資料庫或相應的數據文件以便標記受損的壞塊後，填充v$database_block_corruption以及後續恢復。
e、對於使用backup validate 校驗後的情形，壞塊恢復時可以直接使用blockrecover corruption list一次性恢復所有的壞塊。
f、預設情況下，存在壞塊的數據文件無法成功備份，也會導致自動備份腳本失敗。
