Thursday , November 21 2024

Correction of Corrupted UNDO tablespace.

 

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.

 

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:

 

# If there is one corrupted segment:

 

# If there is more than one corrupted segment:

 

4- Open the database in MOUNT mode and with RESTRICT.

 

5- Remove all corrupted segments and undo tablespace.

Rollback segment dropped.

 

Tablespace dropped.

 

6- Create new undo tablespace.

 

7- Close the database.

 

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.

 

If you need to use Spfile after opening you can create the spfile with the help of the following script;

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

One comment

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *