NOLOGGING and Corruption

I got Block corruption error while gathering the stats. This will show how to find the corrupted segment.

SQL> execute dbms_stats.gather_database_stats;
BEGIN dbms_stats.gather_database_stats; END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 16270)
ORA-01110: data file 9: '/oracle/STGDB/data02/int_tab_1m_a_01'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_STATS", line 18569
ORA-06512: at "SYS.DBMS_STATS", line 19054
ORA-06512: at "SYS.DBMS_STATS", line 19211
ORA-06512: at "SYS.DBMS_STATS", line 19170
ORA-06512: at line 1


SELECT SEGMENT_TYPE,OWNER'.'SEGMENT_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = 9 AND 16270 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;
SEGMENT_TYPE
------------------
OWNER'.'SEGMENT_NAME
--------------------------------------------------------------------------------
INDEX
STGDB.AUDITENTRY_IX02

For now, just rebuild the index without nologging. However, that's addressing the symptom, not the cause, and you must learn the root cause of the corruption.

If you did not do a recovery on this index, open an SR IMMEDIATELY and Oracle technical Support. They can use the block editor (BBED) utility to inspect the data blocks and see EXACTLY why they became corrupted. You can also inspect the blocks with BBED yourself.