{"id":1207,"date":"2016-12-06T18:27:33","date_gmt":"2016-12-06T18:27:33","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=1207"},"modified":"2018-08-21T19:04:21","modified_gmt":"2018-08-21T19:04:21","slug":"rescuing-data-corrupted-tables","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/rescuing-data-corrupted-tables\/","title":{"rendered":"Rescuing Data from Corrupted Tables"},"content":{"rendered":"

This blog post describes how to partially rescue data from corrupted table segments if you\u00a0find yourself in a miserable\u00a0position\u00a0of not having\u00a0a valid backup at your disposal. At the risk of sounding trivial, you can use alter index rebuild online<\/em> command for recovering from an index block corruption.<\/p>\n

This\u00a0method\u00a0inevitably\u00a0leaves you\u00a0with a data loss. Therefore, don’t\u00a0rely on it\u00a0in production environments. Instead, you should always\u00a0use well documented and regularly tested RMAN backup & restore\u00a0scripts\u00a0to recover from data corruption.<\/strong><\/p>\n

I developed the procedure\u00a0while dealing with\u00a0an OS\u00a0block\u00a0corruption on\u00a0less important\u00a0test\u00a0databases which didn’t have any backup & restore requirements. In this particular case, it was enough to discard corrupted data from the table.<\/p>\n

If you happen to hit such a problem, it is always important to do\u00a0the root-cause analysis and resolve the underlying issue on the OS level. In this case, the corruption was caused by an OS bug which\u00a0reared it’s ugly\u00a0head\u00a0while\u00a0Unix administrators tested a migration procedure.<\/p>\n

These are the error messages I got when trying to retrieve the corrupted data:<\/p>\n

set autotrace traceonly\r\nselect * from APP_TABLE ;\r\nERROR:\r\nORA-01115: IO error reading block from file  (block # )\r\nORA-01115: IO error reading block from file 7 (block # 735744)\r\nORA-27072: File I\/O error\r\nSolaris-AMD64 Error: 5: I\/O error\r\nAdditional information: 4\r\nAdditional information: 735744\r\nAdditional information: 4294967295<\/code><\/pre>\n
The error messages above tell us that\u00a0a block couldn’t be retrieved from the file system.<\/p>\n
If you see\u00a0a similar\u00a0error message in an RMAN log, you can use the following query to\u00a0obtain the\u00a0information about the\u00a0affected\u00a0object:<\/p>\n
select * from dba_extents \r\n  where file_id = 7 \r\n    and 735747 between block_id and block_id + blocks -1 ;\r\n<\/code><\/pre>\n
The\u00a0anonymous PL\/SQL block below\u00a0is based on the fact that all the rowids<\/em> can be retrieved from a non-corrupted index. The INDEX<\/em> hint\u00a0is used to avoid\u00a0the full table scan which would stumble over the corrupted blocks. The rowids<\/em>\u00a0obtained by index scan\u00a0are\u00a0consequently used\u00a0to query the rows from the table, one at a time. The row\u00a0will be\u00a0stored in good_rows<\/em> table if it can be retrieved. Otherwise,\u00a0its index key\u00a0will be\u00a0stored in the table corrupted_keys. <\/em>Preferably use the index on the primary key. By doing so you will be able to unambiguously identify discarded\u00a0rows.\u00a0\u00a0I explicitly defined an exception for the OS read error (ORA-1115<\/em>). Other exceptions are deliberately\u00a0left unhandled.<\/p>\n
create table good_rows as select * from APP_TABLE where rownum=0 ;\r\n \r\ncreate table corrupted_keys as\r\n  select KEY from APP_TABLE where rownum=0 ;\r\n \r\ndeclare\r\n  read_file_error EXCEPTION ;\r\n  pragma exception_init(read_file_error,-1115) ;\r\nbegin\r\n  for r_row_id in ( select \/*+ INDEX(t INDEX_NAME) *\/ rowid from APP_TABLE t )\r\n  loop\r\n    begin\r\n      insert into good_rows\r\n        select * from APP_TABLE where rowid=r_row_id.rowid  ;\r\n    exception when read_file_error then\r\n      insert into corrupted_keys\r\n        select \/*+ INDEX(t INDEX_NAME) *\/ KEY\r\n          from APP_TABLE t where rowid=r_row_id.rowid ;\r\n    end ;  \r\n  end loop ;\r\nend ; <\/code><\/pre>\n
Replace the following\u00a0for your particular case:<\/p>\n