Ora-01548:active rollback segment ' _syssmu1$ ' found in Oracle

Ora-01548:active rollback segment ' _syssmu1$ ' found in Oracle

"Oracle Undo tablespace Recovery (no backup)" above http://www.linuxidc.com/Linux/2014-06/103779.htm

Undo Table Space Loss for Rman backup and recovery http://www.linuxidc.com/Linux/2014-01/95335.htm

About Oracle release over-use undo tablespace http://www.linuxidc.com/Linux/2013-09/90315.htm

Some understanding of Oracle undo Http://www.linuxidc.com/Linux/2013-09/89701.htm

Oracle Undo Mirrored Data Exploration http://www.linuxidc.com/Linux/2013-08/89074.htm

Oracle rollback (ROLLBACK) and undo (undo) http://www.linuxidc.com/Linux/2013-08/88792.htm

1. After creating a new undo Tablesapce "Undotbs2", delete the old undo Tablespace

15:12:49 [email protected] Prod>alter tablespace undotbs1 offline immediate;

Tablespace altered.

elapsed:00:00:00.15

Report the following error:

15:12:59 [email protected] Prod>drop tablespace UNDOTBS1 including contents and datafiles;

Drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

Ora-01548:active rollback segment ' _syssmu1_3780397527$ ' found, terminate dropping tablespace

elapsed:00:00:00.05

2. Generate Pfile through SPFile

15:13:08 [email protected] prod>create pfile from SPFile;

File created.

elapsed:00:00:00.11

15:14:12 [email protected] prod>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Add the following implicit parameters to the Initprod.ora file:

_offline_rollback_segments= (_syssmu10_3550978943$,_syssmu9_1424341975$,_syssmu8_2012382730$,_syssmu7_ 3286610060$,_syssmu6_2443381498$,_syssmu5_1527469038$,_syssmu4_1152005954$,_syssmu3_2097677531$,_syssmu2_ 2232571081$,_syssmu1_3780397527$)

3. Restart database and delete the old undo Tablespace

[Email protected] dbs]$ sqlplus '/as sysdba '

Sql*plus:release 11.2.0.1.0 Production on Thu June 26 16:32:49 2014

Copyright (c) 1982, Oracle. All rights reserved.

Connected to an idle instance.

16:32:49 [email protected] prod>create spfile from Pfile;

File created.

elapsed:00:00:00.04

16:32:52 [email protected] Prod>startup

ORACLE instance started.

Total System Global area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 775948320 bytes

Database buffers 54525952 bytes

Redo buffers 2412544 bytes

Database mounted.

Database opened.

16:33:06 [email protected] Prod>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

elapsed:00:00:00.59

16:33:17 [email protected] prod>shutdown immediate;

Delete the _offline_rollback_segments parameter in Pfile;

16:33:56 [email protected] prod>create spfile from Pfile;

File created.

elapsed:00:00:00.05

16:33:59 [email protected] Prod>startup

ORACLE instance started.

Total System Global area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 775948320 bytes

Database buffers 54525952 bytes

Redo buffers 2412544 bytes

Database mounted.

Database opened.

16:34:17 [email protected] prod>select count (*) from scott.emp;

COUNT (*)

----------

14

elapsed:00:00:00.05

16:34:20 [email protected] prod>select tablespace_name,status from Dba_tablespaces;

Tablespace_name STATUS

------------------------------ ---------

SYSTEM ONLINE

Sysaux ONLINE

TEMP ONLINE

USERS ONLINE

UNDOTBS2 ONLINE

EXAMPLE ONLINE

TBS1 ONLINE

7 rows selected.

elapsed:00:00:00.06

16:34:28 [email protected] prod>

At this point, undo tablespace is deleted normally!

Ora-01548:active rollback segment ' _syssmu1$ ' found in Oracle