Drop UNDO tablespace

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Tue, 6 Jun 2006 09:45:34 -0400
Message-ID: <f09dd6280606060645g24d26cb0q3db12ed20c357b4c@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 06 2006 - 08:45:34 CDT