Wednesday, October 10, 2012

How To Backup Table/Index When It has Blocks Corruption

You should have ora600 error in your alert logs
There are many notes of how to deal with ora600 ,see note   1088018.1  68013.1 556733.1
My note here is how to backup the table/index when there is block corruption.
In this case, you can claim back blocks which are not corrupted and keep them safe.

Step 1: How to get  corrupted rowid from block number:
You can get block number from alert or trace files
Use sql below to get rowid:
select rowid from ucm_search.idctext2 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) = 527989

Step 2: After we find the rowid of table, we can select most of rows into backup table
The sql we use is like

create table ucm_search.idctext2_bak from select * from ucm_search.idctext2 where rowid not in ('AAAuMEAAjAACA51AAB','AAAuMEAAjAACA51AAA');


Step 3: You can rename it to original name to keep system running
Sql like:
ALTER TABLE  idctext2_bak rename to idctext2;

No comments: