Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery

Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery (Doc ID 1295294.1)

SYMPTOMS

Dropping a Undo tablespace give message

ORA-01548: active rollback segment

Or

Undo segment shows status as needs recovery

CHANGES

New Undo tablespace was created and a attempt is made to drop old undo tablespace

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

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

SEGMENT_NAME STATUS TABLESPACE_NAME
----------------- ----------- ----------------
_SYSSMU3$ NEEDS RECOVERY UNDO01

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

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

STATUS            NAME                FILE#
------- -------------------------------------------------- ----------
ONLINE     /u01/undo01_01.dbf    56
RECOVER  /u02/undo01_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#= ;

Now find these changes are present in which Archive log

Query 2
---------

SQL> select sequence#,thread#,name from v$archived_log
        where 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;

CHECKPOINT_CHANGE# FILE# STATUS
------------------ ---------- -------
  2103113 4 OFFLINE     77

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

SEQUENCE# THREAD#  NAME
--------------------------------------------------------------------------------

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 

RMAN> recover datafile ;

RMAN> sql 'alter database datafile online' ;

if using sqlplus
-------------

Ensure the archive logs are present on disk

SQL> recover datafile ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile 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 ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile online ;

Option b

=========

If database is in No archive log mode and the redo log has been reused

Open a ticket with oracle Support and explore the options

You can Upload the following trace file while opening the ticket

SQL> alter session set tracefile_identifier='corrupt';

SQL> alter system dump undo header "";

Go to udump

ls -lrt *corrupt*

Upload this trace file

Also upload the alert log file

Please note :- Donot set any underscore or unsupported parameters to drop an active Undo segment as it might causes dictionary inconsistency.

                     Please open a ticket with oracle Support for any queries related to the same

=================================================================

Undo tablespace currupt and no clean backup

 1) Open a SR with Metalink to help with your problem

2) If you want to try something here are some options, but never skip the point 1 previously explained.


Well, you have two possible scenarios, one supported other unsupported.

This is an inconsistency issue..

Solution 
============

*1]Supported Solution:*

Restore-Incomplete recovery the DB from backup or rebuild the DB using an export dump.

*2]Internal (Not-supported) Solution:*

Update undo$ to change the status$ = 1 (undo segment is dropped) for the corrupted undo segment.

Caution:

This is NOT a supported solution. Therefore ensure that a full database cold backup is taken bbefore doing this data-dictionary patching.

Steps :

1) First ensure that a full database backup is taken.

2) Modify/add the following parameters in initSID.ora:

undo_management=manual
job_queue_processes =0
aq_tm_processes=0
systemtrig_enabled=false
3) Shutdown

4) Startup restrict pfile

5) set transaction use rollback segment system;

if set transaction above fails, then not to proceed further.

6) Update undo$ to change the status$ = 1 (undo segment is dropped) for
the corrupt undo segment.

update undo$ set status$ = 1
where us# = <'undo segment id'>
and ts#=<'tablespace id'>
and file#=<'file id'>
and block# = <'block id'>
and status$=2;
OR

update undo$ set status$ = 1
where name = <'corrupt undo segment name'>
and status$=2;

This will update only 1 row. Rollback the update if more than 1 row is updated.

7) Commit;

8) Shutdown abort

9) Set back in init.ora parameters modified in Step 2.
Startup restrict

10) Drop the corrupt undo tablespace using command:

drop tablespace including contents;

11) Create new undo tablespace.
In initSID.ora, set undo_management=auto, undo_tablespace=<'new undo tablespace name'>
Restart the instance.

12) Take a backup.

*Parameter offlinerollback_segments is not useful:*

Trying to drop the corrupt undo segment or the undo tablespace by setting parameters undo management=manual and offline_rollback_segments in initSID.ora will fails with error: ORA-00600 [ktssdrp1].


To see the information about the corrupt block use this query:

select us#, name, ts#, file#, block#,status$ from undo$ where (ts#, file#, block#) not in
(select ts#, file#, block# from seg$ where type# in (1,10)) and status$ > 1;

Warning:

I had the same problem some time ago, and the unsupported solution works great, but never try it without a full backup first.

========================================================================