ORA-01548 in Oracle: active rollback segment & #39; _ SYSSMU1 $ & #39; found, ora-01548_syssmu1

ORA-01548 in Oracle: active rollback segment '_ SYSSMU1 $' found, ora-01548_syssmu1

ORA-01548 in Oracle: active rollback segment '_ SYSSMU1 $' found

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

Undo tablespace loss http://www.linuxidc.com/Linux/2014-01/95335.htm for RMAN backup and recovery

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

Oracle undo some understanding http://www.linuxidc.com/Linux/2013-09/89701.htm

Oracle undo image data inquiry http://www.linuxidc.com/Linux/2013-08/89074.htm

Oracle ROLLBACK and 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 SYS @ prod> ALTER tablespace undotbs1 offline immediate;

Tablespace altered.

Elapsed: 00:00:00. 15

The following error is reported:

At 15:12:59 SYS @ prod> drop tablespace undotbs1 including contents and datafiles;

Drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_ syssmu%3780%27 $ 'found, terminate dropping tablespace

Elapsed: 00:00:00. 05

2. Generate pfile through spfile

15:13:08 SYS @ prod> create pfile from spfile;

File created.

Elapsed: 00:00:00. 11

15:14:12 SYS @ prod> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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

_ Offline_rollback_segments = (_ parts $, _ SYSSMU7_3286610060 $, _ parts $, _ parts $, _ SYSSMU4_1152005954 $, _ parts $, _ parts $, _ syssmuw.378038527 $)

3. Restart the database and delete the old undo tablespace.

[Oracle @ rh6 dbs] $ sqlplus '/as sysdba'

SQL * Plus: Release 11.2.0.1.0 Production on Thu Jun 26 16:32:49 2014

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

Connected to an idle instance.

16:32:49 SYS @ prod> create spfile from pfile;

File created.

Elapsed: 00:00:00. 04

16:32:52 SYS @ 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.

At 16:33:06 SYS @ prod> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:00. 59

16:33:17 SYS @ prod> shutdown immediate;

Delete the _ offline_rollback_segments parameter in pfile;

16:33:56 SYS @ prod> create spfile from pfile;

File created.

Elapsed: 00:00:00. 05

16:33:59 SYS @ 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 SYS @ prod> select count (*) from scott. emp;

 

COUNT (*)

----------

14

 

Elapsed: 00:00:00. 05

16:34:20 SYS @ 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 SYS @ prod>

@ Now, undo tablespace is deleted normally!