Can not drop UNDOSTBS

Hi, I'm a junior DBA .

     I have 1 problem.

I have create a new undo tablespace and set it to undo tablespace of instance

ora11> alter system set

undo_tablespace=undotbs2;

but we can not drop the original one:

ora11> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU2_6654314$’ found, terminate dropping tablespace

 

how can solved this problem?

 

 -------------------------------------------------------------------------------------------------------------------------------

For example:

 

1) find all active rollback segment in the undo tablespace to be dropped.

sys@anar> select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′

 

SEGMENT_NAME TABLESPACE_NAME STATUS

—————————— —————————— —————-

_SYSSMU10_820739558$ UNDOTBS1 OFFLINE

_SYSSMU9_2448906239$ UNDOTBS1 OFFLINE

_SYSSMU8_3066916762$ UNDOTBS1 OFFLINE

_SYSSMU7_892861194$ UNDOTBS1 OFFLINE

_SYSSMU6_1956589931$ UNDOTBS1 OFFLINE

_SYSSMU5_2919322705$ UNDOTBS1 OFFLINE

_SYSSMU4_3876247569$ UNDOTBS1 OFFLINE

_SYSSMU3_4245574747$ UNDOTBS1 OFFLINE

for eaxmple :_SYSSMU2_6654314$ UNDOTBS1 PARTLY AVAILABLE

2) set a parameter including all active rollback segments in init.ora file

_offline_rollback_segments=(_SYSSMU2_6654314$,…..)

3) shutdown database

4) Mount the database using pfile

sys@anar> startup mount pfile=’initanar.ora’

5) offline undo datafile for drop

sys@anar> alter database datafile ‘/app/oracle/oradata/anar/undotbs1.dbf’ offline drop;

6) open database

sys@anar> alter database open;

7)drop the undo segment

sys@anar> drop rollback segment “_SYSSMU2_6654314$”;

8)Add a new undo tablespace and set it as instance’s undo tablespace

sys@anar> alter system set undo_tablespace=undotbs2;

9) drop original undo tablespace

sql>Drop Tablespace undotbs1 including contents and datafiles;

10) remove the _offline_rollback_segments parameter fron pfile

 

参考：https://community.oracle.com/message/11232507#11232507

----------------------

(1) Take a Backup.
(2) Shutdown the DB 
(2) Set UNDO_MANAGEMENT=MANUAL
(3) Set parameter OFFLINEROLLBACK_SEGMENT = (_SYSSMU1, _SYSSMU2...) etc for all the segments that were in the original UNDO tablespace. I checked v$rollname for the name of those active ones in the replacement UNDO TBS 
(4) Open the DB
(5) Drop each RBS individually using 'DROP ROLLBACK SEGMENT "seg_name".
(6) Drop the UNDO tablespace.
(7) Shutdown the DB
(8) Comment out or remove the parameter OFFLINEROLLBACK_SEGMENT 
(9) Set UNDO_MANAGEMENT back to AUTO
(10) Ensure the UNDO_TABLESPACE is set to your new UNDO TBS name
(11) Restart the DB
(12) Check the alert log for any errors - if all looks pukka then shutdown and take another backup. (You know it makes sense)

OFFLINEROLLBACK_SEGMENT is undocumented and support warn that its use should be restricted to those occasions when support ask you to use it. Be warned!

Edited by: Vazha_Mantua on 05-May-2009 06:14

Edited by: Vazha_Mantua on 05-May-2009 06:15

参考：https://community.oracle.com/message/3449574#3449574