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.