Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If the Oracle UNDO table space is corrupted, it may be possible to get the error as follows.
ORA-00376: file string cannot be read at this time
When UNDO segments corrupted, the following should be done:
- The undo_management parameter must be MANUAL.
- A new UNDO tablespace must be created.
- The problematic UNDO tablespace should be removed.
- The undo_tablespace parameter must be modified to be the newly created UNDO tablespace.
Correction of corrupted UNDO tablespace step by step:
1- Identify the corrupted segments. The following query can be used to detect these segments.
1 2 3 4 5 6 7 8 9 10 11 12 13 | select segment_name, status from dba_rollback_segs where tablespace_name='<sorunlu undo tablo alanı>' and status = ‘NEEDS RECOVERY’; SEGMENT_NAME STATUS ------------------------------ ---------------- _SYSSMU22$ NEEDS RECOVERY |
2. Close the database.
3- You must change the corresponding pfile’s undo_management parameter to MANUAL and Change the value of the “_offline_rollback_segments” hidden parameter to be problematic segments.
Rows to add to the Pfile file:
1 | UNDO_MANAGEMENT=MANUAL |
# If there is one corrupted segment:
1 | _OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$ |
# If there is more than one corrupted segment:
1 | _OFFLINE_ROLLBACK_SEGMENTS=(‘_SYSSMU22$’,‘_SYSSMU23$’, ‘_SYSSMU24$’) |
4- Open the database in MOUNT mode and with RESTRICT.
1 | SQL > STARTUP RESTRICT MOUNT pfile=<ORACLE_HOME>/dbs/initORCL.ora |
5- Remove all corrupted segments and undo tablespace.
1 | SQL> drop rollback segment "_SYSSMU22$"; |
Rollback segment dropped.
1 | SQL > drop tablespace undotbs including contents and datafiles; |
Tablespace dropped.
6- Create new undo tablespace.
1 | SQL > CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ’/oradata/orcl/UNDOTBS01.DBF’ SIZE 100M ; |
7- Close the database.
1 | SOL> shutdown immediate |
8- Delete the line _OFFLINE_ROLLBACK_SEGMENTS from the rows added in the Pfile, and 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>.
9. Open the database normally from the pfile.
1 | SQL > STARTUP pfile=<ORACLE_HOME>/dbs/initORCL.ora |
If you need to use Spfile after opening you can create the spfile with the help of the following script;
1 | SQL > create spfile from pfile ; |
Thank you so much for these very clear instructions. I am a software developer, not a DBA, with a personal Oracle XE database containing more than 1.5 million horse racing records which are used for statistical analysis. The DB incurred a power outage during an update of the records, causing corruption of the undo tablespace. After struggling to understand and fix this problem, I found your excellent guidance. Thanks again, and yes I will get a new UPS backup to avoid this problem in the future.