Recover from missing UNDO Tablespace

Step 1. Symptoms when trying to drop a undo tablespace

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

Step 2. File which rollback segments to remove

 SQL> SELECT segment_name, tablespace_name, status
        FROM sys.dba_rollback_segs;  2
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU3_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU4_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU5_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU6_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU7_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU8_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU9_1282527427$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU10_1282527427$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11_1282528448$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU12_1282528448$          UNDOTBS1                       NEEDS RECOVERY
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU13_1282528448$          UNDOTBS1                       OFFLINE
_SYSSMU14_1282528448$          UNDOTBS1                       OFFLINE
_SYSSMU15_1282528448$          UNDOTBS1                       OFFLINE
_SYSSMU16_1282528448$          UNDOTBS1                       OFFLINE
_SYSSMU17_1282528448$          UNDOTBS1                       OFFLINE
_SYSSMU18_1287968607$          UNDOTBS1                       OFFLINE
_SYSSMU19_1368756281$          UNDOTBS1                       OFFLINE
_SYSSMU20_1368756281$          UNDOTBS1                       OFFLINE
_SYSSMU21_1368756883$          UNDOTBS1                       OFFLINE

Step 3. Create a pfile from spfile and modify the contents

*.undo_management='MANUAL'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*._offline_rollback_segments=(_SYSSMU3_1282527427$,_SYSSMU4_1282527427$,_SYSSMU5_1282527427$,_SYSSMU6_1282527427$,_SYSSMU7_1282527427$,_SYSSMU8_1282527427$,_1282527427$,_SYSSMU10_1282527427$,_SYSSMU11_1282528448$,_SYSSMU12_1282528448$)

Step 4. Drop all the relevant rollback segments
SQL> drop rollback segment "_SYSSMU6_1282527427$";
drop rollback segment "_SYSSMU7_1282527427$";
drop rollback segment "_SYSSMU8_1282527427$";
Rollback segment dropped.
SQL>
Rollback segment dropped.
SQL>
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU9_1282527427$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU10_1282527427$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU11_1282528448$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU12_1282528448$";
Rollback segment dropped.

Step 5. Drop the tablespace in question, then create a new copy
SQL> drop tablespace UNDOTBS1 including contents AND DATAFILES;
Tablespace dropped.
SQL> create undo tablespace undotbs3 datafile '/databases/TGFLXRU/data02/undotbs03.dbf' size 5G;

Step 6. Drop the tablespace in question, then create a new copy
Recreate the spfile with the following and restart using spfile
 
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS3'

Leave a Comment Cancel reply