Block recovery based on RMAN

Keywords: Database SQL rman Oracle

This paper refers to: Le Shami's World


For physically damaged data blocks, we can complete the recovery of damaged blocks through the RMAN Block Media Recovery (BLOCK MEDIA RECOVERY) function without requiring the whole recovery. data base Or all files to repair these small amounts of damaged data blocks. Restoring the entire database or data file is not a cannon for mosquitoes, it's not worth it! But the prerequisite is that you have an available RMAN backup, so backup is everything at any time. This paper demonstrates the whole process of recovering bad blocks by using RMAN when bad blocks are generated.

 

1. Create a demo environment

  1. SQL> select * from v$version where rownum<2;  
  2.   
  3. BANNER  
  4. --------------------------------------------------------------------------------  
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  6.   
  7. --Create a demo data file  
  8. SQL> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;  
  9.   
  10. SQL> conn scott/tiger;  
  11.   
  12. --Creating Objects Based on New Data Files tb_tmp  
  13. SQL> create table tb_tmp tablespace tbs_tmp as select * from dba_objects;  
  14.   
  15. SQL> col file_name format a60  
  16. SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP';  
  17.   
  18.    FILE_ID FILE_NAME  
  19. ---------- ------------------------------------------------------------  
  20.          6 /u02/database/usbo/oradata/tbs_tmp.dbf  
  21.   
  22. --Table object tb_tmp The information above, including the corresponding file information, header block, total number of blocks  
  23. SQL> select segment_name , header_file , header_block,blocks        
  24.   2  from dba_segments  
  25.   3  where segment_name = 'TB_TMP' and owner='SCOTT';  
  26.   
  27. SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK     BLOCKS  
  28. ------------------------------ ----------- ------------ ----------  
  29. TB_TMP                                   6          130       1152  
  30.   
  31. --use first rman Back up the corresponding data files  
  32. $ $ORACLE_HOME/bin/rman target /  
  33. RMAN> backup datafile 6 tag=health;  
  34.   
  35. Starting backup at 2013/08/28 17:03:15  
  36. allocated channel: ORA_DISK_1  
  37. channel ORA_DISK_1: SID=24 device type=DISK  
  38. channel ORA_DISK_1: starting full datafile backup set  
  39. channel ORA_DISK_1: specifying datafile(s) in backup set  
  40. input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf  
  41. channel ORA_DISK_1: starting piece 1 at 2013/08/28 17:03:16  
  42. channel ORA_DISK_1: finished piece 1 at 2013/08/28 17:03:17  
  43. piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH comment=NONE  
  44. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  45. Finished backup at 2013/08/28 17:03:17  
  46. RMAN> exit  

2. Recovery Processing of Damaged Single Data Block

  1. --The following uses linux Self contained dd Command to damage a single block of data  
  2. [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 <<EOF  
  3. > Corrupted block!  
  4. > EOF  
  5. 0+1 records in  
  6. 0+1 records out  
  7. 17 bytes (17 B) copied, 0.000184519 seconds, 92.1 kB/s  
  8.   
  9. --empty buffer cache  
  10. SQL> alter system flush buffer_cache;  
  11.   
  12. --Query Table Relativity tb_tmp,Received ORA-01578  
  13. SQL> select count(*) from tb_tmp;  
  14. select count(*) from tb_tmp  
  15. *  
  16. ERROR at line 1:  
  17. ORA-01578: ORACLE data block corrupted (file # 6, block # 130)  
  18. ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'  
  19.   
  20. --Query view v$database_block_corruption，Note that the view may not return any data. If no data is returned, execute first. backup validate  
  21. SQL> select * from v$database_block_corruption;  
  22.   
  23.      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO  
  24. ---------- ---------- ---------- ------------------ ---------  
  25.          6        129          1                  0 CORRUPT  
  26.   
  27. --It can also be used dbv Tools to check bad blocks, reference: http://blog.csdn.net/robinson_0612/article/details/6530890     
  28.   
  29. --Use below blockrecover To recover the bad block        
  30. RMAN> blockrecover datafile 6 block 130;  
  31.   
  32. Starting recover at 2013/08/28 17:22:25  
  33. using target database control file instead of recovery catalog  
  34. allocated channel: ORA_DISK_1  
  35. channel ORA_DISK_1: SID=24 device type=DISK  
  36.   
  37. channel ORA_DISK_1: restoring block(s)  
  38. channel ORA_DISK_1: specifying block(s) to restore from backup set  
  39. restoring blocks of datafile 00006  
  40. channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp  
  41. channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH  
  42. channel ORA_DISK_1: restored block(s) from backup piece 1  
  43. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01  
  44.   
  45. starting media recovery  
  46. media recovery complete, elapsed time: 00:00:03  
  47.   
  48. Finished recover at 2013/08/28 17:22:31  
  49.   
  50. --Query the table again tb_emp normal  
  51. SQL> select count(*) from tb_tmp;  
  52.   
  53.   COUNT(*)  
  54. ----------  
  55.      72449  

3. Recovery Processing of Damaged Multi-Block Data

  1. --Use below linux dd Command Damage to Discontinuous Blocks  
  2. [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=133 <<EOF  
  3. > New corrupted block!  
  4. > EOF  
  5. 0+1 records in  
  6. 0+1 records out  
  7. 21 bytes (21 B) copied, 0.000182835 seconds, 115 kB/s  
  8. [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=143 <<EOF   
  9. > New corrupted block!  
  10. > EOF  
  11. 0+1 records in  
  12. 0+1 records out  
  13. 21 bytes (21 B) copied, 0.000115527 seconds, 182 kB/s  
  14. [oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=153 <<EOF   
  15. > New corrupted block!  
  16. > EOF  
  17. 0+1 records in  
  18. 0+1 records out  
  19. 21 bytes (21 B) copied, 0.000335781 seconds, 62.5 kB/s  
  20.   
  21. SQL> alter system flush buffer_cache;  
  22.   
  23. --The following prompt block 133 is damaged. Note that we have damaged many data blocks, but when querying, we start with the smallest number of blocks. If there are bad blocks after 133 is repaired, we continue to prompt the bad blocks after 133.  
  24. SQL> select count(*) from scott.tb_tmp;  
  25. select count(*) from scott.tb_tmp  
  26. *  
  27. ERROR at line 1:  
  28. ORA-01578: ORACLE data block corrupted (file # 6, block # 133)  
  29. ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'  
  30.   
  31. --Query view v$database_block_corruption No record  
  32. SQL> select * from v$database_block_corruption;  
  33.   
  34. no rows selected  
  35.   
  36. --Use below backup validate To verify data files  
  37. RMAN> backup validate datafile 6;  
  38.   
  39. Starting backup at 2013/08/29 09:42:04  
  40. using target database control file instead of recovery catalog  
  41. allocated channel: ORA_DISK_1  
  42. channel ORA_DISK_1: SID=22 device type=DISK  
  43. channel ORA_DISK_1: starting full datafile backup set  
  44. channel ORA_DISK_1: specifying datafile(s) in backup set  
  45. input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf  
  46. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  47. List of Datafiles  
  48. =================  
  49. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN  
  50. ---- ------ -------------- ------------ --------------- ----------  
  51. 6    FAILED 0              223          1408            838489       --field Status by FAILED  
  52.   File Name: /u02/database/usbo/oradata/tbs_tmp.dbf  
  53.   Block Type Blocks Failing Blocks Processed  
  54.   ---------- -------------- ----------------  
  55.   Data       0              1029              
  56.   Index      0              0                 
  57.   Other      3              156             --There are 3. Blocks Failing  
  58.   
  59. validate found one or more corrupt blocks  
  60. See trace file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_27874.trc for details  
  61. Finished backup at 2013/08/29 09:42:06  
  62.   
  63. --Query again v$database_block_corruption，It shows three damaged blocks.  
  64. SQL> select * from v$database_block_corruption;  
  65.   
  66.      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO  
  67. ---------- ---------- ---------- ------------------ ---------  
  68.          6        153          1                  0 CORRUPT  
  69.          6        143          1                  0 CORRUPT  
  70.          6        133          1                  0 CORRUPT  
  71.   
  72. --The following is used directly blockrecover corruption list To restore, all the bad blocks that have just been checked will be restored  
  73. RMAN> blockrecover corruption list;    
  74.   
  75. Starting recover at 2013/08/29 10:05:24  
  76. using channel ORA_DISK_1  
  77.   
  78. channel ORA_DISK_1: restoring block(s)  
  79. channel ORA_DISK_1: specifying block(s) to restore from backup set  
  80. restoring blocks of datafile 00006  
  81. channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp  
  82. channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH  
  83. channel ORA_DISK_1: restored block(s) from backup piece 1  
  84. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01  
  85.   
  86. starting media recovery  
  87. media recovery complete, elapsed time: 00:00:03  
  88.   
  89. Finished recover at 2013/08/29 10:05:28  
  90.   
  91. --Verification result  
  92. SQL> select count(*) from scott.tb_tmp;  
  93.   
  94.   COUNT(*)  
  95. ----------  
  96.      72449  

4. Object Location and Impact of Bad Blocks

  1. --Next, we query the object on block number 163.  
  2. SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,  
  3.   2  dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id  
  4.   3  from scott.tb_tmp where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;  
  5.   
  6.  OBJECT_ID    FILE_ID   BLOCK_ID OWNER        OBJECT_NAME                     OBJECT_ID  
  7. ---------- ---------- ---------- ------------ ------------------------------ ----------  
  8.      74555          6        163 SYS          GV_$QUEUEING_MTH                     2439  
  9.      74555          6        163 PUBLIC       GV$QUEUEING_MTH                      2440  
  10.   
  11. --Using the above method, we lost blocks 163, 173, which are not listed here.  
  12.   
  13. a,It is impossible to aggregate and aggregate bad block objects.       
  14. SQL> select count(*) from scott.tb_tmp;  
  15. select count(*) from scott.tb_tmp  
  16. *  
  17. ERROR at line 1:  
  18. ORA-01578: ORACLE data block corrupted (file # 6, block # 163)  
  19. ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'  
  20.   
  21. b,Records on bad blocks cannot be queried  
  22. --We use a query based on previous queries OBJECT_ID To query  
  23. SQL> select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440);  
  24. select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440)  
  25.                                               *  
  26. ERROR at line 1:  
  27. ORA-01578: ORACLE data block corrupted (file # 6, block # 163)  
  28. ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'  
  29.   
  30. --In the following queries, the data on the damaged block can not be queried, but it can still be queried for the undamaged block. Objects on block 161 for the following query  
  31. SQL> select owner,object_name,object_id from scott.tb_tmp   
  32.   2  where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;  
  33.   
  34. OWNER                          OBJECT_NAME                     OBJECT_ID  
  35. ------------------------------ ------------------------------ ----------  
  36. PUBLIC                         GV$RECOVERY_LOG                      2285  
  37. SYS                            GV_$ARCHIVE_GAP                      2286  
  38.   
  39. --Author : Robinson Cheng  
  40. --Blog   : http://blog.csdn.net/robinson_0612  
  41.        
  42. c,Locate the object corresponding to the damaged block  
  43. SQL> run get_obj_name_from_corrupt_block  
  44.   1  SELECT tablespace_name,  
  45.   2         segment_type,  
  46.   3         owner,  
  47.   4         segment_name,  
  48.   5         partition_name  
  49.   6    FROM dba_extents  
  50.   7*  WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1  
  51. Enter value for file_id: 6  
  52. Enter value for block_id: 133  
  53. old   7:  WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1  
  54. new   7:  WHERE file_id = 6 AND 133 BETWEEN block_id AND block_id + blocks - 1  
  55.   
  56. TABLESPACE_NAME                SEGMENT_TYPE       OWNER          SEGMENT_NAME      PARTITION_NAME  
  57. ------------------------------ ------------------ -------------- ----------------- -----------------  
  58. TBS_TMP                        TABLE              SCOTT          TB_TMP   
  59.   
  60. d,Damaged data files cannot be backed up by default, as follows  
  61. RMAN> backup datafile 6 tag='corruption';                                                      
  62.                                                                                                         
  63. Starting backup at 2013/08/29 10:37:32                                                         
  64. using channel ORA_DISK_1                                                                       
  65. channel ORA_DISK_1: starting full datafile backup set                                          
  66. channel ORA_DISK_1: specifying datafile(s) in backup set                                       
  67. input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf                   
  68. channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:37:32                                    
  69. RMAN-00571: ===========================================================                        
  70. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============                        
  71. RMAN-00571: ===========================================================                        
  72. RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/29/2013 10:37:33             
  73. ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/database/usbo/oradata/tbs_tmp.dbf   
  74.   
  75. --You need to set the number of blocks allowed to be damaged before backing up  
  76. RMAN> run{  
  77. 2> set maxcorrupt for datafile 6 to 2;  
  78. 3> backup datafile 6 tag='corruption';  
  79. 4> }  
  80.   
  81. executing command: SET MAX CORRUPT  
  82.   
  83. Starting backup at 2013/08/29 10:41:24  
  84. using channel ORA_DISK_1  
  85. channel ORA_DISK_1: starting full datafile backup set  
  86. channel ORA_DISK_1: specifying datafile(s) in backup set  
  87. input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf  
  88. channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:41:25  
  89. channel ORA_DISK_1: finished piece 1 at 2013/08/29 10:41:26  
  90. piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_29/o1_mf_nnndf_CORRUPTION_91xf6o18_.bkp tag=CORRUPTION comment=NONE  
  91. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  92. Finished backup at 2013/08/29 10:41:26         
  93.   
  94. --Looking at the backup information below, you should restore the backup after repairing the bad block to avoid the previously available backup being caused by the retention policy. aged out  
  95. RMAN> list backup summary;  
  96.   
  97. List of Backups  
  98. ===============  
  99. Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag  
  100. ------- -- -- - ----------- ------------------- ------- ------- ---------- ---  
  101. 1       B  F  A DISK        2013/08/28 17:03:17 1       1       NO         HEALTH  
  102. 3       B  F  A DISK        2013/08/29 10:41:25 1       1       NO         CORRUPTION  

5, postscript
a. For damaged data blocks, only the data on the bad blocks can not be queried or read, and the data on the other normal blocks can still be used.
b. When aggregating damaged table objects and other related operations, errors are received, because the data on the bad block can not be counted. If you aggregate index columns, the index is returned normally if it is not damaged.
c. Block media recovery can be realized based on the backup files available in RMAN. Its data files need no offline, and have the least overhead and impact.
d. For damage to multiple data blocks, first perform backup validate to verify the database or the corresponding data file to mark the damaged block, then fill in v$database_block_corruption and subsequent recovery.
e. In the case of using backup validate check, the block recovery corruption list can be used directly to recover all bad blocks at one time.
f. By default, data files with bad blocks can not be backed up successfully, which will also lead to the failure of automatic backup scripts.
g. By default, data files with bad blocks can not be backed up successfully, which will also lead to the failure of automatic backup scripts.

Posted by walkonet on Mon, 08 Apr 2019 12:39:30 -0700

Hot Keywords

©2024 Programmer Group