Repair UNDO log corruption Don Burleson

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.