Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found,ora-01548_syssmu1
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 SYS@ prod>ALTER tablespace undotbs1 offline immediate;
Tablespace altered.
Elapsed: 00:00:00.15
報以下錯誤:
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、通過spfile產生pfile
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.
在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
[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, 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;
在pfile 刪除_offline_rollback_segments參數;
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>
@至此,undo tablespace 被正常刪除!