Sami

The problem you are encountering is that when you lost the undo datafile, it still contained one or more transactions that needed to be rolled back to bring the database into a consistent state. The undo is no longer available since the datafile is not there and hence you still (1 month later) have a logical inconsistency in your data.

It is possible to find out which transaction(s) are being rolled back by setting event 10015 in the (s)pfile and re-starting the database. This will produce a trace file which contains a transaction ID and and object # which you can then query from dba_objects (object_id) to determine the affected object.

At this stage you may have to export/drop/import the affected table to clear the requirement to apply the undo.

All of this should be done under the supervision of Oracle support as I would not like to be responsible for getting you into trouble J.

Env: Oracle 9.2.0.6, 3 Node RAC, Sun OS 2.9

We want to drop this undo tablespace UNDOTBS1 which is not part on any instance.
But when we try to drop the tablespace it is throwing "ORA-01548:
active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace". Is there a way to overcome this?

The datafile for UNDOTBS1 segments got corrupted and in "NEEDS
RECOVERY" state for more than a month. We created new undo tablespace
(UNDOTBS1_NEW) that time and UNDOTBS1 tablespace not required any
more.

Alert_log
=======
Tue Jun 6 13:22:40 2006
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
.
.
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Tue Jun 6 13:22:41 2006
Errors in file /opt/oracle/admin/pwpb4/bdump/pwpb41_smon_26754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/oradata/pwpb4/undo01.dbf'


SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1_NEW

SQL>select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1
_SYSSMU11$ ONLINE UNDOTBS2
_SYSSMU12$ ONLINE UNDOTBS2
_SYSSMU13$ ONLINE UNDOTBS2
_SYSSMU14$ ONLINE UNDOTBS2
_SYSSMU15$ ONLINE UNDOTBS2
_SYSSMU16$ ONLINE UNDOTBS2
_SYSSMU17$ ONLINE UNDOTBS2
_SYSSMU18$ ONLINE UNDOTBS2
_SYSSMU19$ ONLINE UNDOTBS2
_SYSSMU20$ ONLINE UNDOTBS2
_SYSSMU21$ ONLINE UNDOTBS3
_SYSSMU22$ ONLINE UNDOTBS3
_SYSSMU23$ ONLINE UNDOTBS3
_SYSSMU24$ ONLINE UNDOTBS3
_SYSSMU25$ ONLINE UNDOTBS3
_SYSSMU26$ ONLINE UNDOTBS3
_SYSSMU27$ ONLINE UNDOTBS3
_SYSSMU28$ ONLINE UNDOTBS3
_SYSSMU29$ ONLINE UNDOTBS3
_SYSSMU30$ ONLINE UNDOTBS3
_SYSSMU31$ ONLINE UNDOTBS1_NEW
_SYSSMU32$ ONLINE UNDOTBS1_NEW
_SYSSMU33$ ONLINE UNDOTBS1_NEW
_SYSSMU34$ ONLINE UNDOTBS1_NEW
_SYSSMU35$ ONLINE UNDOTBS1_NEW
_SYSSMU36$ ONLINE UNDOTBS1_NEW
_SYSSMU37$ ONLINE UNDOTBS1_NEW
_SYSSMU38$ ONLINE UNDOTBS1_NEW
_SYSSMU39$ ONLINE UNDOTBS1_NEW
_SYSSMU40$ ONLINE UNDOTBS1_NEW

41 rows selected.

SQL> select file_name,TABLESPACE_NAME,status from dba_data_files
where TABLESPACE_NAME like 'UNDO%'
2 ;

FILE_NAME TABLESPACE_NAME STATUS
----------------------------------------
------------------------------ ---------
/u06/oradata/pwpb4/undo01.dbf UNDOTBS1
AVAILABLE
/u06/oradata/pwpb4/undo02.dbf UNDOTBS2
AVAILABLE
/u06/oradata/pwpb4/undo03.dbf UNDOTBS3
AVAILABLE
/u06/oradata/pwpb4/undo01_new.dbf UNDOTBS1_NEW
AVAILABLE

SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace
--
//www.freelists.org/webpage/oracle-l