Database is shutdown and can’t start it, asking for Undo recovery while dropping Undo tablespace

Dropping a Undo tablespace give message

The UNDOTBS for our RMAN Catalog database was dropped. Any attempts to switch to a newly created UNDOTBS or removing the old UNDOTBS failed with the following error:

ORA-01548: active rollback segment
Or
Undo segment shows status as needs recovery

Cause

The issue could happen if the datafile on which the undo segments reside is offline and the transaction cannot be rolled backed since the file is offline

Or

This could also happen if there is any issue in the Undo segment itself

Solution

Check if the Undo segment status first

In the above example Undo segment _SYSSMU3$ is in Needs recovery status.
This segment belongs to Undo tablespace UNDO01

Check the status of the datafile present in the tablespace UNDO01

So clearly one file is in Recover status

Option A

If the database is in Archive log mode and you have all the required archive log mode you can do the following :-

Find if you have all the required Archive logs on disk or If using Rman ensure they exist in the backup

Now find these changes are present in which Archive log

Ensure you have all the archive logs starting from this sequence# till the current sequence# in your database

For example

 If using rman

Check if the archive log from this sequence till current sequence is available

if using sqlplus
————-

If the archive logs have been restored to a different location than the Default archive log destination your database is using then specify the same using set source command in sqlplus

 Option B

If your database is in noarchivelog mode than better to open SR with Oracle, if it is test database than you can proceed with below steps

If database database oradata folder size is less or manageable to take its backup while database is shutdown.

Once you took backup of oradata, now you proced for recovery

 Note: Add the following lines to pfile.

Rename spfile.

Start database with restrict session.

Create undo tablespace UNDOTBS2.

Make entry in the pfile

Modify the following parameters in pfile and restart the database.

6 rows selected.

Now the corrupted UNDO tablespace is droped.

Now take database export backup and create new database using dbca and import the database.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply