How to fix corrupt UNDO tablespace ?

If the Oracle UNDO tablespace is corrupted, it is possible to receive errors such as the one below.

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

In summary, when UNDO segments are damaged, the following should be done:

• The undo_management parameter must be changed from AUTO to MANUAL.
• A new UNDO tablespace must be created.
• The problematic UNDO tablespace must be removed.
• The undo_management parameter, which is MANUAL, should be changed to AUTO and the undo_tablespace parameter should be changed to the newly created UNDO tablespace.

Fixing corrupt UNDO tablespace:

1-    Detect bad segments. The following query can be used to detect bad segments.

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='<problem undo tablespace>'
and
status = 'NEEDS RECOVERY';

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

2-    Close the database.
3-    Change the relevant pfile so that undo_management is MANUAL and the _offline_rollback_segments value from the hidden parameters is the problematic segments. Open the database in MOUNT mode and RESTRICT.
Lines to be added to the pfile:

UNDO_MANAGEMENT=MANUAL

# If there is only one bad segment:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$

# If there is more than one damaged segment:

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU22$,'_SYSSMU23$, '_SYSSMU24$)

SQL > STARTUP RESTRICT MOUNT pfile=<ORACLE_HOME>/dbs/initORCL.ora

4-    Remove all bad segments and undo tablespace.

SQL> drop rollback segment "_SYSSMU22$";

Rollback segment dropped.

SQL > drop tablespace undotbs including contents and datafiles;

Table space dropped.

5-    Create a new undo tablespace.

SQL > CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/orcl/UNDOTBS01.DBF' SIZE 100M ;

6-    Close the database.

LEFT> shutdown immediate

7-    Delete the _OFFLINE_ROLLBACK_SEGMENTS line from the added lines in the Pfile, make UNDO_MANAGEMENT=AUTO, if the name of the newly created undo tablespace is different from the deleted one, change it to undo_tablespace=<NEW_UNDOTBS1>.

8-    Open the database normally from pfile.

SQL > STARTUP pfile=<ORACLE_HOME>/dbs/initORCL.ora

If you need to use Spfile, after opening it;

SQL > create spfile from pfile ;

You can create spfile with the command.