1

Recently I was involved in recovering data after several blocks in production DB got corrupted.

Disclaimer: I'm by no means a competent DBA, but rather a PL/SQL developer with quite a bit (I guess) of experience, so maybe I'm missing something quite obvious here. Sorry for that.

DBMS_REPAIR package provides the procedure SKIP_CORRUPT_BLOCKS which is pretty much self-explanatory (allows accessing table despite it having corrupted blocks).

After it is used you can check DBA_TABLES.SKIP_CORRUPT column (which becomes ENABLED instead of being DISABLED by default).

Now that the dust has settled, all the data has been recovered and, hopefully, lessons has been learnt, I can't seem to find a way to switch it back without recreating the table (if some day another block in this segment gets corrupted, I'd rather have the queries fail than silently skip rows from corrupted blocks).

Is there a way to disable SKIP_CORRUPT without recreating the table?

2

You can disable it with the same procedure.

begin
  dbms_repair.skip_corrupt_blocks
  (
    schema_name => '...',
    object_name => '...',
    object_type => dbms_repair.table_object,
    flags=> dbms_repair.noskip_flag
  );
end;
/

dbms_repair.skip_flag enables, dbms_repair.noskip_flag disables this behaviour.

  • Wow! Shame on me, I've no idea how I missed that in docs. Thanks – That young man Dec 28 '17 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.