The undo tablespace cannot be opened because it is damaged.

The undo tablespace cannot be opened because it is damaged.

When the undo tablespace file in the database is corrupted or the undo tablespace file is missing, the database cannot be opened.

Both cases can be considered as one case, and the solution is the same.

When starting the database, the following error occurs:

SQL> startup

Oracle instance started.

Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'undotbs1 'does not exist or of wrong type
Process ID: 4098

Session ID: 1 Serial number: 5

It is obvious that the undo tablespace cannot be opened because it is damaged or lost.

The solution is simple: Create an undo tablespace and point undo_tablespace to the new undo tablespace. However,... Creating a tablespace can only be performed when the database is open.

So ..

Because there are still rollback segments in the oracle system tablespace, We can first let oracle use the rollback segment to open the database, and then we can create the undo tablespace.

Solution:

1. Start the database to mount

2. alter system set undo_management = manual scope = spfile;

3. shutdown immediate

4. startup and create an undo tablespace new_undotbsxx

5. Modify the undo_tablespace parameter to point to new_undotbsxx

6. Modify the undo_manageme parameter. Alter system set undo_management = auto scope = spfile;

SQL> alter system set undo_management = manual scope = spfile;

System altered.

Then open the database ---

SQL> startup
ORACLE instance started.

Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
Database opened.

-- The database has been successfully opened.

View -- undo_management

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string MANUAL
Undo_retention integer 900
Undo_tablespace string UNDOTBS11

Now you can create a new undo_tablespace and point the undo_tablespace parameter to the past.

SQL> create undo tablespace undotbs11 datafile '/u01/app/oracle/oradata/orcl/undotbs11.dbf' size 100 m;

Tablespace created.

-- Then point undo_tablespace to the newly created undo tablespace.

SQL> alter system set undo_tablespace = undotbs11 scope = spfile;

System altered.

-- Remember to change undo_management back.

SQL> alter system set undo_management = auto scope = spfile;

System altered.

Close the database, and then open

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
Database opened.

-- No problem. You can delete the previously corrupted undo tablespace data file.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Recommended reading:

[Oracle] What should I do if undo is damaged without backup?

How to handle UNDO tablespace failures

Undo tablespace faults and ORA-01548 Processing

Recovery of undo tablespace loss under RAC

UNDO tablespace Backup Recovery