Whenever we face any DBA interviews we have to go through some intersting topics interviewers ask. Undo Management is the most important part of DBA’s job responsibilities.

This article will help you out to solve the issue when “Undo Segment is in Needs Recovery or Undo Segments are corrupted” 


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

Dropping a Undo tablespace give a message

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


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


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


Check if the Undo segment status first

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in (‘ONLINE’, ‘OFFLINE’) ;

—————– ———– —————-

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

Check the status of the datafile present in the tablespace UNDO02

SQL> select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name=’UNDO02′ );

——- ————————————————– ———-
ONLINE /u01/UNDO02_01.dbf 56
RECOVER /u02/UNDO02_03.dbf 77

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

Query 1

SQL> Select checkpoint_change# from v$datafile_header where file_id=<file# in Recover status from previous query> ;

Now find these changes are present in which Archive log
Query 2

SQL> select sequence#,thread#,name from v$archived_log

where <checkpoint_change# from query 1> between first_change# and next_change# ;

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

For example

SQL> select checkpoint_change#,file#,status from v$datafile_header where file#=77;

—————— ———- ——-
2103113 4 OFFLINE 77

SQL>Select sequence#,thread#,name from v$archived_log where 2103113 between first_change# and next_change# ;


96 1 /u01/arch/O1_MF_1_96_6OKHP.Arc

If using rman

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

RMAN> list backup of archivelog from sequence <No listed in query2>

RMAN> recover datafile <fileno> ;

RMAN> sql ‘alter database datafile <fileno> online’ ;

if using sqlplus

Ensure the archive logs are present on disk

SQL> recover datafile <fileno> ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile <fileno> online ;

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

SQL> set logsource “/u01/arch/newlocation” ;

SQL> recover datafile <fileno> ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile <fileno> online ;

SQL> drop rollback segment “_SYSSMX9$”;

Rollback segment dropped.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> select name from v$tablespace;









6 rows selected.

Now the corrupted UNDO tablespace is droped.

SQL> select * from v$rollname;

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:

Telegram Channel: https://t.me/helporacle

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

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.