Monday, March 12, 2012

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

While trying to recreate undotbs to flip with a new one , ran into this unseen error.
Created a new UNDO, made it the default UNDO for the database, now while trying to drop the old undo it fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS1;
drop Tablespace UNDOTBS
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

 Following steps were performed:

1.    Figure out the rollback segments still occupying UNDOTBS1
SQL> select * from v$rollname ;
---------- ------------------------------
18 _SYSSMU18_650722988$
19 _SYSSMU19_176786070$
20 _SYSSMU20_2971238361$
21 _SYSSMU21_1337078425$
22 _SYSSMU22_378998127$
23 _SYSSMU23_2828850578$
24 _SYSSMU24_3342375805$
25 _SYSSMU25_1071547613$
26 _SYSSMU26_357778769$
27 _SYSSMU27_3256159541$

2.    Create pfile if you started with database with spfile.

3.    Edit pfile and set undo management to manual.
undo_management = manual

4.    Put the entries of the undo segments in the pfile by using the following statement in the pfile:


5.    Mount the database using new pfile.
Startup mount pfile='/$ORACLE_HOME/dbs/'

6.    Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;

7.    Open the database.
sql>Alter Database Open;

8.    Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";
9.    Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;

10. Add the new undo tablespace.

11. Shutdown Immediate;

12. Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

13. Startup the Database.

No comments: