How to Replace a Corrupted Undo Tablespace in Oracle RAC Database

Learn step-by-step how to replace a corrupted or problematic undo tablespace in Oracle Database, including creating a new undo tablespace and updating the spfile safely

---

Undo tablespaces are critical in Oracle Database for managing transactions and rollback segments. Occasionally, an undo tablespace may become corrupted, offline, or unusable, causing transaction failures. This blog guides you through safely replacing a corrupted undo tablespace in Oracle 11g/12c/19c using SQL*Plus, ensuring minimal downtime and risk.

---

Create a PFILE from the Existing SPFILE

Creating a PFILE allows you to make temporary parameter changes without affecting the current SPFILE:

SQL> CREATE PFILE='/u01/MiscFiles/initTempJCDB1.ora' FROM SPFILE;

---

Shutdown the Database

Shut down the database instance to make changes:

SQL> SHUTDOWN IMMEDIATE;

---

Update PFILE Parameters

Edit the PFILE (initTempJCDB1.ora) to include:

*.undo_management = manual
*.event='10513 trace name context forever, level 2'

These settings ensure manual undo management and detailed tracing during recovery.

---

Startup in Restricted Mode Using PFILE

Start the database in restricted mode with the edited PFILE:

SQL> STARTUP RESTRICT PFILE='/u01/MiscFiles/initTempJCDB1.ora';

SQL> CREATE PFILE='/u01/MiscFiles/initTempJCDB122.ora' FROM SPFILE;

---

Verify Undo Segments Status

Check all undo segments to ensure they are offline:

SQL> SELECT tablespace_name, status, segment_name
     FROM dba_rollback_segs
     WHERE status != 'OFFLINE';

⚠ Critical: If any undo segment shows PARTLY AVAILABLE or NEEDS RECOVERY, contact Oracle Support before proceeding.

IF ALL OFFILINE ONLY THEN CONTINUE TO THE NEXT STEP

---

Create a New Undo Tablespace

Create a new undo tablespace to replace the corrupted one:

SQL> CREATE UNDO TABLESPACE UNDOTBS11 
     DATAFILE '+DATA/jcdb/datafile/undotbs12.262.7685046592' 
     SIZE 2048M AUTOEXTEND ON NEXT 120M MAXSIZE UNLIMITED;

---

Drop the Old Undo Tablespace

Once the new undo tablespace is created and verified, drop the old one:

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

---

Shutdown and Startup Database

Shutdown the database:

SQL> SHUTDOWN IMMEDIATE;

---

Startup Using Original SPFILE

Startup the database in NOMOUNT mode if needed:

SQL> STARTUP NOMOUNT;

---

Update SPFILE with New Undo Tablespace

Set the new undo tablespace in the SPFILE:

SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS11 SCOPE=BOTH SID='JCDB1';

---

Step 11: Restart the Database

Shutdown and start the database normally using the updated SPFILE:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

---

Conclusion

Replacing a corrupted undo tablespace involves carefully creating a new tablespace, updating the SPFILE, and ensuring all undo segments are offline. Following these steps helps maintain database integrity and ensures smooth operations without downtime.

---