Oracle – Disabling SKIP_CORRUPT After Using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

corruptionoracleoracle-11g-r2

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?

Best Answer

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.

Related Solutions

Disabling DBMS_SCHEDULER queue

I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.

Postgresql – FATAL: catalog is missing 1 attribute(s) for relid 2662

Upon database corruption, before doing anything else, one should read https://wiki.postgresql.org/wiki/Corruption and take a file system level backup.

You need to restore from backup or, if backup is missing, check corrupted tables for example

select distinct relname 
from pg_class 
where not exists (select 1 from pg_attribute where attrelid = relfilenode)`

select distinct relname 
from pg_class 
where relfilenode > (select max(attrelid) from pg_attribute)

And then your need to restore missing record in pg_attribute (for example create simm table and

copy with q as 
( select attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage,attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attacl, attoptions, attfdwoptions 
  from pg_attribute 
  where attrelid::regclass = your_table::regclass) ) 
 INSERT INTO pg_catalog.pg_attribute select [id from your pgclass missing instead new attrelid], * from q

Then check, that atttypid exists and do vacuum on pg_class and pg_attribute. So, your problem must be solved.

Best Answer

Related Solutions

Disabling DBMS_SCHEDULER queue

Postgresql – FATAL: catalog is missing 1 attribute(s) for relid 2662

Related Question