Repair UNDO log corruption Don Burleson

For additional information on handling corruption, see the BC expert notes on corruption and see MOSC Note 1088018.1 - Handling Oracle Database Corruption Issues.

Also see how to  fix system managed undo segments using the _corrupt_rollback_segments parameter.

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

If you are getting an ORA-00600 error on undo, see fix system managed undo segments using the _corrupt_rollback_segments parameter.

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.