Friday, January 29, 2010

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

Problem Description
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS;
drop Tablespace UNDOTBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace

Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.
SQL>Create PFILE from SPFILE;

2)Edit pfile and set undo management to manual.
undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)

4)Mount the database using new pfile.
Startup mount pfile='fullpath to pfile'

5)Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;

6)Open the database.
sql>Alter Database Open;

7)Drop the undo segments,

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

9)Add the new undo tablespace.

10) Shutdown Immediate;

11) 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.

12) Startup the Database.


Theoretical solution

So, why would anyone be dropping the current UNDO tablespace? Most likely to create a new UNDO tablespace to replace it, so why go through all of your listed gyrations when one can:

SQL> create undo tablespace undotbs2
2 datafile '/u01/data/undotbs2_01.dbf' size 2048M,
3 '/u02/data/undotbs2_02.dbf' size 2048M
3 blocksize 8192
4 extent management local autoallocate;

Tablespace created.

SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
...

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

As you need an UNDO tablespace present it only makes sense to create the new UNDO tablespace before dropping the old one. And, as you need to shutdown the database to clear all old transactions from the current, defective UNDO tablespace you're assured of having no active segments if you set the new UNDO tablespace as THE UNDO tablespace before you shutdown and restart the database.

I can't understand why you make simple topics complicated.

No comments:

Post a Comment