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

Error noticed while creating tablespace.

  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha_01.dbf' size 5G
  3  autoextend on
  4* maxsize unlimited
SQL> /
create tablespace tbs_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Find the name of tablespace associated with the missing datafile.

SQL> select d.name,t.name
  2  from v$datafile d join v$tablespace t
  3  using(ts#);

NAME
--------------------------------------------------------------------------------
NAME
------------------------------
/home/oracle/BACKUP/undotbs02.dbf
UNDOTBS2

Check Undo Parameters.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2

Shutdown the database, change undo_mangement to Manual and start the database

SQL> shutdown abort;
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

Modify pfile and set undo_management='Manual'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.

Create new undo tablespace, shutdown database, set undo management auto and undo tablespace to newly created tablespace

  1  create undo tablespace undotbs01
  2  datafile '/u01/app/oracle/oradata/orcl/undotbs01_01.dbf' size 10m
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  MANUAL
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2

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

open pfile and modify undo_management='Auto' and undo_tablespace='Undotbs01'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  undotbs01

Now create tablespace will create successfully

  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha01.dbf' size 1G
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.

Following command failed due to existing rollback segment on the old undo tablespace.

SQL> grant dba to usr_moha;
grant dba to usr_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'

So tried to drop old undo tablespace

SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU12_3370723747$' found, terminate
dropping tablespace

Shutdown database and add following parameter
This parameter is oracle's hidden and undocumented parameters. So use at your own risk.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.
vi initorcl.ora
*._offline_rollback_segments=(_SYSSMU12_3370723747$)

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU13_3758039048$' found, terminate
dropping tablespace

Append _SYSSMU13_3758039048$ to the end of the _offline_rollback_segments. Keep going until the the rollback segment and drop tablespace successfully

*._offline_rollback_segments=(_SYSSMU32_3855527180$,_SYSSMU33_2662784219$,_SYSSMU34_2457069885$,_SYSSMU35_1632431741$,_SYSSMU36_3409971511$,_SYSSMU37_2689280970$,_SYSSMU38_389146150$,_SYSSMU39_3304332257$,_SYSSMU40_2103468367$,_SYSSMU41_2387847232$,
_SYSSMU12_3370723747$
,_SYSSMU13_3758039048$
,_SYSSMU14_2103326955$
,_SYSSMU15_3693451306$
,_SYSSMU16_3316561802$
,_SYSSMU17_2198165518$
,_SYSSMU18_3012613150$
,_SYSSMU19_1884560376$
,_SYSSMU20_2031163871$
,_SYSSMU21_1352513242$
)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup;

ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.

SQL> drop tablespace UNDOTBS2;

Tablespace dropped.

Finally remove all the parameters and restart the database.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  undotbs01
SQL> show parameter rollback

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments      string  _SYSSMU32_3855527180$, _SYSSMU
       33_2662784219$, _SYSSMU34_2457
       069885$, _SYSSMU35_1632431741$
       , _SYSSMU36_3409971511$, _SYSS
       MU37_2689280970$, _SYSSMU38_38
       9146150$, _SYSSMU39_3304332257
       $, _SYSSMU40_2103468367$, _SYS
       SMU41_2387847232$, _SYSSMU12_3
       370723747$, _SYSSMU13_37580390
       48$, _SYSSMU14_2103326955$, _S
       YSSMU15_3693451306$, _SYSSMU16

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
       _3316561802$, _SYSSMU17_219816
       5518$, _SYSSMU18_3012613150$,
       _SYSSMU19_1884560376$, _SYSSMU
       20_2031163871$, _SYSSMU21_1352
       513242$


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

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter offline_rollback_segments;
SQL> grant dba to usr_moha;

Grant succeeded.

SQL>