ORA-01578: ORACLE data block corrupted

 Recently we saw below error in our alert log.

Errors in file

 /oracle/app/diag/rdbms/VIJAY/trace/VIJAY_ora_70454.trc  (incident=2300411):
ORA-01578: ORACLE data block corrupted (file # 182, block # 12483)
ORA-01110: data file 182: '/oradata1/VIJAY/audsys_ts_01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

To fix the above error we followed below steps ;

SQL> select owner,segment_name,segment_type,tablespace_name from dba_extents 
     where file_id=182 and 12483 between block_id AND block_id+blocks-1;
 
OWNER           SEGMENT_NAME          SEGMENT_TYPE       TABLESPACE_NAME
--------------- ------------------- ------------------ -----------------
JAIDBA         AUD$UNIFIED            TABLE              AUDSYS_TS

 

SQL> select count(*) from JAIDBA.AUD$UNIFIED;
select count(*) from JAIDBA.AUD$UNIFIED
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 182, block # 12483)
ORA-01110: data file 182: '/oradata1/VIJAY/audsys_ts_01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

  

SQL> BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'JAIDBA',

OBJECT_NAME => 'AUD$UNIFIED',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

/  2    3    4    5    6    7    8

 

PL/SQL procedure successfully completed.

 

SQL> select SKIP_CORRUPT
from dba_tables
where owner = 'JAIDBA'
and table_name = 'AUD$UNIFIED';
  2    3    4
SKIP_COR
--------
ENABLED

 

SQL> alter table JAIDBA.AUD$UNIFIED move;
 
Table altered.

 

SQL> select count(*) from JAIDBA.AUD$UNIFIED;
 
  COUNT(*)
----------
         0