Shulou(Shulou.com)06/01 Report--

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

Oracle Undo tablespace recovery (no backup)

http://tiany.blog.51cto.com/513694/1431287

1. After creating the new undo tablespace "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 errors are reported:

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 '_SYSSMU1_3780397527$' 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=(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$,_SYSSMU1_3780397527$)

Restart database and delete old undo tablespace

[oracle@rh7 dbs]$ sqlplus '/as sysdba'

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

Copyright (c) 1982, 2009, 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.

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>

@ So far, undo tablespace has been deleted normally!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.