Rescuing Data from Corrupted Tables

This blog post describes how to partially rescue data from corrupted table segments if you find yourself in a miserable position of not having a valid backup at your disposal. At the risk of sounding trivial, you can use alter index rebuild online command for recovering from an index block corruption.

This method inevitably leaves you with a data loss. Therefore, don’t rely on it in production environments. Instead, you should always use well documented and regularly tested RMAN backup & restore scripts to recover from data corruption.

I developed the procedure while dealing with an OS block corruption on less important test databases which didn’t have any backup & restore requirements. In this particular case, it was enough to discard corrupted data from the table.

If you happen to hit such a problem, it is always important to do the root-cause analysis and resolve the underlying issue on the OS level. In this case, the corruption was caused by an OS bug which reared it’s ugly head while Unix administrators tested a migration procedure.

These are the error messages I got when trying to retrieve the corrupted data:

set autotrace traceonly
select * from APP_TABLE ;
ERROR:
ORA-01115: IO error reading block from file  (block # )
ORA-01115: IO error reading block from file 7 (block # 735744)
ORA-27072: File I/O error
Solaris-AMD64 Error: 5: I/O error
Additional information: 4
Additional information: 735744
Additional information: 4294967295

The error messages above tell us that a block couldn’t be retrieved from the file system.

If you see a similar error message in an RMAN log, you can use the following query to obtain the information about the affected object:

select * from dba_extents 
  where file_id = 7 
    and 735747 between block_id and block_id + blocks -1 ;

The anonymous PL/SQL block below is based on the fact that all the rowids can be retrieved from a non-corrupted index. The INDEX hint is used to avoid the full table scan which would stumble over the corrupted blocks. The rowids obtained by index scan are consequently used to query the rows from the table, one at a time. The row will be stored in good_rows table if it can be retrieved. Otherwise, its index key will be stored in the table corrupted_keys. Preferably use the index on the primary key. By doing so you will be able to unambiguously identify discarded rows.  I explicitly defined an exception for the OS read error (ORA-1115). Other exceptions are deliberately left unhandled.

create table good_rows as select * from APP_TABLE where rownum=0 ;
 
create table corrupted_keys as
  select KEY from APP_TABLE where rownum=0 ;
 
declare
  read_file_error EXCEPTION ;
  pragma exception_init(read_file_error,-1115) ;
begin
  for r_row_id in ( select /*+ INDEX(t INDEX_NAME) */ rowid from APP_TABLE t )
  loop
    begin
      insert into good_rows
        select * from APP_TABLE where rowid=r_row_id.rowid  ;
    exception when read_file_error then
      insert into corrupted_keys
        select /*+ INDEX(t INDEX_NAME) */ KEY
          from APP_TABLE t where rowid=r_row_id.rowid ;
    end ;  
  end loop ;
end ; 

Replace the following for your particular case:

• APP_TABLE – corrupted table name
• INDEX_NAME – use preferably the primary key.
• KEY – column(s) in the index

If there are some referential integrity constraints defined, you will have to take care of the dependencies too.

On condition that everything went well, you can finally copy the data from the good_rows table into the original table:

truncate table APP_TABLE ;

insert into APP_TABLE select * from good_rows ;
commit ;