Repair UNDO Log Corruption

In rare cases (usually DBA error) the Oracle UNDO tablespace can become corrupted.

This manifests with this error: ORA-00376: file xx cannot be read at this time

In cases of UNDO log corruption, you must:

• Change the undo_management parameter from “AUTO” to “MANUAL”
• Create a new UNDO tablespace
• Drop the old UNDO tablespace

Dropping the corrupt UNDO tablespace can be tricky and you may get the message:

ORA-00376: file string cannot be read at this time

To drop a corrupt UNDO tablespace:

1. Identify the bad segment:
   

   select
   segment_name,
   status
   from
   dba_rollback_segs
   where
   tablespace_name='undotbs_corrupt'
   and
   status = ‘NEEDS RECOVERY’;
   

   
   SEGMENT_NAME STATUS
   ----------------------------------------------
   _SYSSMU22$ NEEDS RECOVERY

2. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:
   _OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$
3. Bounce database, nuke the corrupt segment and tablespace:
   
   SQL> drop rollback segment "_SYSSMU22$";
   Rollback segment dropped.
   
   SQL > drop tablespace undotbs including contents and datafiles;
   Tablespace dropped.