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
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
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
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
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.
Table altered.
SQL> select count(*) from JAIDBA.AUD$UNIFIED;
COUNT(*)
----------
0
COUNT(*)
----------
0
