Dealing with corruption in an IOT: the practical hands-on approach

If you find yourself with a corrupted block in an Oracle database you should go immediately to My Oracle Support (MOS) and read note 1088018.1. There you will find the appropriate MOS notes you should read if your problem is related to table, index, or LOB corruption. If you're really unlucky you might have Undo or Redo corruption or even Dictionary or Controlfile corruption. In these cases this note will help you as well.

That's why this is called a Master Note.

But if you crash into a block corruption inside an IOT, you basically have to give a step back and read again and again the part of the note that says:

IOT

** Contact Oracle Support **

Generic notes about these objects: * None Public*

If you're not very aware of what's underneath an Index Organized Table (IOT) you would know that you can't reorganize the index of these tables because the index ... is the table!

So what can you do?

Well, I say you should open a Service Request and ask for help in trying to recover this corruption inside the IOT.

If you can afford loosing some data of this IOT, after you open your SR you can create an image of this IOT and point your app towards this. This is meant to be a temporary practical approach, so you can provision the data inside the corrupted IOT to your users.

Here's the deal:

1. Mark the IOT so it skips the corrupted blocks

2. Build, rebuild or create another structure by scanning the corrupted IOT successfully

3. Disable the corrupted block skipping

4. Point your users temporary to the non-corrupted data if they need asap

Here's the code:

1. Marking the IOT to skip corrupted blocks can't be done with events. They simply don't work with IOTs:

alter session set events '10231 trace name context forever, level 10';

=> for tablescans

alter session set events '10232 trace name context forever, level 10';

=> to dump to trace files...

alter session set events '10233 trace name context forever, level 10';

=> for index scans

IOTs are not tables and are not indexes, so none of those events will make it. So what can you do? Use the DBMS_REPAIR package, that it will work like a charm:

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('MY_SCHMEA','MY_IOT');

PL/SQL procedure successfully completed.

2. Rebuild the IOT (this will make you loose those corrupted entries for good) or CTAS it. I've tested with MOVE and it worked fine:

SQL> alter table MY_SCHEMA.MY_IOT move;

Table altered.

3. Now disable the corrupted block skipping using the package again:

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('MY_SCHEMA','MY_IOT',flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

4. Now you're good to go and use the info that was not affected by the corruption while MOS people are helping you do a more deep analysis of the problem and provide an action plan to solve it.

LMC