Oracle processing of corrupted data blocks

Have you ever encountered such a situation in actual operations? Sometimes, Oracle data files only damage the relevant data blocks while other items are intact. This is mainly because of intermittent or random I/O errors or memory block errors. In this case, you do not need to restore the entire data file so that the datafile is still online. You can perform the following Oracle operations to process corrupted data blocks.

** After Oracle 9i, you can use the blockrecover command BMR ):

You can use view v $ database_block_corruption uption to obtain information about the damaged data blocks. This view records two types of block upload uption: physical and logical.

@ Physical partition uption: The data block cannot be identified. checksum, header, and footer verification errors

@ Logical partition uption: The Verification of chechsum, header, and footer is correct, but the content of the data block is inconsistent.

Note: BMR cannot be used to restore the logical uption. In the backup command, checksum check is enabled by default and can be disabled by the NOCHECKSUM option attribute. LOGICAL verification is disabled by default. You can add check logical to the backup, restore, recover, and validate commands to enable it.

The specific operation method is as follows:

 
 

  
  
rman> blockrecover datafile n1 block n1b1,
n1b2 datafile n2 block n2b1,n2b2; 
 
 

** User-managed backup and recovery methods. If you want to restore the database in which Oracle processes corrupted data, you can only restore the entire data file.

 
 

  
  
sql> alter database datafile 2 offline;  
  
  
sql> host ‘cp /backup/datafile1.bak /opt/demo/datafile1.dt’;  
  
  
sql> recover automatic datafile 2;  
  
  
sql> alter database datafile 2 online;  
 
 

** If tables with partition uption blocks are not frequently used, you can block these blocks if they are tolerable. This requires the dbms_repair package. The details are as follows:

@ Create repair table used to store damaged block information of tables, table partitions, or indexes ).

 
 

  
  
sql> exec dbms_repair.admin_tables
(’REPAIR_TABLE’, DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACTION); 
 
 

@ Determine the number of corrupted data blocks processed by Oracle

 
 

  
  
sql> var cc number  
  
  
sql> exec dbms_repair.check_object
(’TBSPNAME’, ‘TABNAME’, corrupt_count=>:cc);  
  
  
sql> print cc  
 
 

@ Mark the damaged block.

 
 

  
  
sql> var fc number  
  
  
sql> exec dbms_repair.fix_corrupt_blocks
(’TBSPNAME’, ‘TABNAME’,fix_count=>:fc);  
  
  
sql> print fc;  
 
 

@ Skip damaged blocks. This operation can continue to execute the query SQL statement that involves damaged blocks, skip the blocks, but the error message will still be displayed for DML.

 
 

  
  
sql> exec dbms_repair.skip_corrupt_blocks(’TBSPNAME’, ‘TABNAME’); 
 
 

@ Confirm the Index Entry pointing to Oracle to process corrupted data blocks. After the damaged blocks of the table are identified above, there may still be indexes pointing to the damaged data blocks in Oracle processing. These indexes are called "isolated key values ". You can use the dbms_repair package to manage the isolated key values:

 
 

  
  
sql> exec dbms_repair.admin_tables
(’ORPHAN_TAB’, DBMS_REPAIR, ORPHAN_TABLE, DBMS_REPAIR, CREATE_ACTION);  
  
  
sql> var kc number  
  
  
sql> exec dbms_repair.dump_orphar_keys
(’TBSPNAME’,'INDEXNAME’, orphan_table_name=> ‘ORPHAN_TAB’, key_count=> :kc);  
  
  
sql> print kc;  
 
 

The above content describes how Oracle processes corrupted data blocks, hoping to help you in this regard.