jump to navigation

ORA-01548: active rollback segment ‘_SYSSMU1_xxxx’ found, terminate dropping tablespace while deleting UNDOTBS 9 November 2010

Posted by David Alejo Marcos in Oracle 11.2, SQL - PL/SQL.
Tags: ,
12 comments

Last week I had to solve a strange error on one of our production databases. Luckily for us, it was the RMAN catalog.

I said luckily for us because this database is internal to us. The same problem on a production database  serving our clients could have been disastrous as we have tight deadlines.

The problem:

The UNDOTBS for our RMAN Catalog database was dropped. Any attempts to switch to a newly created UNDOTBS or removing the old UNDOTBS failed with the following error:

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

The solution:

Our RMAN Catalog has a standby database for protection. The first thing I did was to stop the standby database from applying more archivelogs as the UNDOTBS01 existed. This would have allowed me to perform a failover if necessary.

As I mentioned before, I created a new UNDOTBS3 to check if I could modify the undo_tablespace parameter, but it did not work:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs3;
System altered.

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

Tried to delete the rollback segment, but it did not work:

SQL> drop rollback segment '_SYSSMU1_1255349037$';
drop rollback segment '_SYSSMU1_1255349037$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name

In fact, it did not even exist:

SQL> select * from v$rollname ;
USN NAME
---------- ------------------------------
0 SYSTEM
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$

So, how to fix it?

I did execute the following steps:

1.- Create a copy of the spfile:

create pfile from spfile;

So I could rename the spfile and work with this init.ora file until I fixed the problem.

2.- Stop the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

3.- Amended the init.ora file with the following:

*.undo_management='MANUAL'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS3'
*._offline_rollback_segments=_SYSSMU1_1255349037$

4.- Start database up and try to delete the UNDOTBS01>

SQL> drop tablespace undotbs01 including contents and datafiles;
drop tablespace undotbs01 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU3_1255349037$' found, terminate dropping tablespace

As you can see, the rollback segment is different, it changed from _SYSSMU1_1255349037$ to _SYSSMU3_1255349037$ (the difference is the digit after SYSSMU).

After several tries, I realised I had to add as many entries to _offline_rollback_segments as entries I had on v$rollname where NAME LIKE ‘%SYSSMU%’ (10 in my case).

so I stopped the database again and made the necessary changes:

5.- Edit init.ora file and add as many entries as rows has V$rollname where name like ‘%SYSSMU%’:

*._offline_rollback_segments=(_SYSSMU1_1255349037$,_SYSSMU2_1255349037$,
_SYSSMU3_1255349037$,_SYSSMU4_1255349037$,
_SYSSMU5_1255349037$,_SYSSMU6_1255349037$,
_SYSSMU7_1255349037$,_SYSSMU8_1255349037$,
_SYSSMU9_1255349037$,_SYSSMU10_1255349037$)

6.- Start up the database and drop UNDOTBS01

SQL> drop tablespace undotbs01 including contents and datafiles;
Tablespace dropped.

7.- and create UNDOTBS01:

SQL> CREATE UNDO TABLESPACE "UNDOTBS01" DATAFILE '/u01/oradata/CATALOG/undo01.dbf' SIZE 1024M;
Tablespace created.

7.- Shutdown the database, rename (or delete) the init.ora file and start up the database using the spfile.

8.- Set UNDOTBS01 as the UNDO tablespace:

SQL> alter system set undo_tablespace=undotbs01 scope=both;

9.- Check parameters:

SQL> show parameter undo

NAME                                                   TYPE              VALUE
———————————— ———– ——————————
undo_management                      string            AUTO
undo_retention                             integer         1800
undo_tablespace                          string             UNDOTBS01

10.- and drop UNDOTBS3:

SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.

As soon as the database was running as normal I executed a backup to ensure all was ok.

After the verification, I enabled the recovery to keep the standby database in synch with the primary.

Note.- It is not necessary to add the full name _SYSSMU1_1255349037$ to _offline_rollback_segments. Only the first part (SYSSMU1) would have been enough.

_offline_rollback_segments=(_SYSSMU1_1255349037$,_SYSSMU2_1255349037$) is the same than

_offline_rollback_segments=(_SYSSMU1,_SYSSMU2)

As always, comments are welcome.