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

標籤：style   http   color   io   os   使用   ar   strong   檔案   

Oracle中的ORA-01548: active rollback segment ‘_SYSSMU1$‘ found

接上文《Oracle Undo tablespace恢複（無備份）》  http://www.linuxidc.com/Linux/2014-06/103779.htm

RMAN備份與恢複之undo資料表空間丟失 http://www.linuxidc.com/Linux/2014-01/95335.htm

關於Oracle 釋放過度使用的undo資料表空間 http://www.linuxidc.com/Linux/2013-09/90315.htm

Oracle undo的一些理解 http://www.linuxidc.com/Linux/2013-09/89701.htm

Oracle undo 鏡像資料探究 http://www.linuxidc.com/Linux/2013-08/89074.htm

Oracle 復原(ROLLBACK)和撤銷(undo) http://www.linuxidc.com/Linux/2013-08/88792.htm

1、在建立新的undo tablesapce “undotbs2”後，刪除舊的undo tablespace

15:12:49 [email protected] prod>ALTER tablespace undotbs1 offline immediate;

Tablespace altered.

Elapsed: 00:00:00.15

報以下錯誤：

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、通過spfile產生pfile

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.

在initprod.ora 檔案加入以下隱含參數：

_offline_rollback_segments=(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$,_SYSSMU1_3780397527$)

3、重新啟動database，並刪除舊的undo tablespace

[[email protected] 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 [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；

在pfile 刪除_offline_rollback_segments參數；

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>

@至此，undo tablespace 被正常刪除！

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