對于物理損壞的數據塊，我們可以通過RMAN塊介質恢復(BLOCK MEDIA RECOVERY)功能來完成受損塊的恢復，而不需要恢復整個數據庫或所有文件來修復這些少量受損的數據塊?；謴驼麄€數據庫或數據文件那不是大炮用來打蚊子，有點不值得！但前提條件是你得有一個可用的RMAN備份存在，因此，無論何時備份就是一切。本文演示了產生壞塊即使用RMAN實現壞塊恢復的全過程。
SQL> COL SEGMENT_NAME FOR A15 SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'TB' and owner='SYS';
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 27 22:39:49 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDB (DBID=3282897732)
RMAN> backup datafile 7;
Starting backup at 27-NOV-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf channel ORA_DISK_1: starting piece 1 at 27-NOV-17 channel ORA_DISK_1: finished piece 1 at 27-NOV-17 piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-NOV-17
RMAN>
2、單塊數據塊損壞的恢復處理 --下面使用了linux自帶的dd命令來損壞單塊數據塊 [oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.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.000124852 s, 136 kB/s [oracle@wang ~]$ --清空buffer cache SQL> alter system flush buffer_cache;
System altered.
--查詢表tb，收到ORA-01578 SQL> select count(*) from tb; select count(*) from tb * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 130) ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--查詢視圖v$database_block_corruption，提示有壞塊，注意該視圖可能不會返回任何數據，如無返回，先執行backup validate V$DATABASE_BLOCK_CORRUPTION： V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.
Starting backup at 27-NOV-17 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.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 ---- ------ -------------- ------------ --------------- ---------- 7 FAILED 0 138 1536 3821836 File Name: /u01/app/oracle/oradata/DBdb/tbs.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 1240 Index 0 0 Other 3 158 --有3個Blocks Failing
validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_17497.trc for details Finished backup at 27-NOV-17
RMAN>
--再次查詢v$database_block_corruption，表明有3個損壞的塊 SQL> select * from v$database_block_corruption;
channel ch2: restoring block(s) channel ch2: specifying block(s) to restore from backup set restoring blocks of datafile 00007 channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 channel ch2: restored block(s) from backup piece 1 channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s) channel ch2: specifying block(s) to restore from backup set restoring blocks of datafile 00007 channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 channel ch2: restored block(s) from backup piece 1 channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery media recovery complete, elapsed time: 00:00:03
Finished recover at 27-NOV-17
Starting recover at 27-NOV-17
channel ch2: restoring block(s) channel ch2: specifying block(s) to restore from backup set restoring blocks of datafile 00007 channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 channel ch2: restored block(s) from backup piece 1 channel ch2: block restore complete, elapsed time: 00:00:01
starting media recovery media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-17
released channel: ch2
--驗證，檢查： SQL> select * from v$database_block_corruption;
no rows selected.
SQL> SQL> select count(*) from tb;
COUNT(*) ---------- 87046
4、壞塊的對象定位與影響 SQL> col object_name for a25 SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;
--使用上面的方法，損塊塊163，173:
[oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=163 <<eof > New03 corrupted block! > EOF 0+1 records in 0+1 records out 23 bytes (23 B) copied, 3.9521e-05 s, 582 kB/s [oracle@wang ~]$ [oracle@wang ~]$ dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=173 <<eof > New04 corrupted block! > EOF 0+1 records in 0+1 records out 23 bytes (23 B) copied, 6.0101e-05 s, 383 kB/s [oracle@wang ~]$
a、對于壞塊對象無法進行聚合匯總等操作: SQL> select count(*) from tb; select count(*) from tb * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 163) ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
b、對于壞塊上的記錄無法被查詢 --我們使用基于之前查詢到的OBJECT_ID來查詢 SQL> select owner,object_name,object_id from tb where object_id in(2364,2365); select owner,object_name,object_id from tb where object_id in(2364,2365) * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 163) ORA-01110: data file 7: '/u01/app/oracle/oradata/DBdb/tbs.dbf'
--如下面的查詢，位于損壞塊上(163的數據無法被查詢到，但對于未損壞的依舊可以查詢。下面的查詢時塊161上的對象 SQL> select owner,object_name,object_id from tb where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;
c、定位受損塊所對應的對象 select tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_idBETWEEN block_id AND block_id + blocks -1;
--查詢如下： SQL> SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1; Enter value for file_id: 7 Enter value for block_id: 163 old 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks -1 new 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = 7 AND 163 BETWEEN block_id AND block_id + blocks -1
--需要設定允許損壞塊的數量之后才能進行備份 run{ set maxcorrupt for datafile 7 to 2; backup datafile 7 tag='corruption'; } 執行如下： RMAN> run{ 2> set maxcorrupt for datafile 7 to 2; 3> backup datafile 7 tag='corruption'; 4> }
executing command: SET MAX CORRUPT
Starting backup at 27-NOV-17 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf channel ORA_DISK_1: starting piece 1 at 27-NOV-17 channel ORA_DISK_1: finished piece 1 at 27-NOV-17 piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_CORRUPTION_f1rcshjk_.bkp tag=CORRUPTION comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 27-NOV-17
RMAN>
--查看備份信息如下，應在修復壞塊后重新備份以避免由于保留策略導致先前可用的備份被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 27-NOV-17 1 1 NO TAG20171127T224014 2 B F A DISK 27-NOV-17 1 1 NO CORRUPTION
