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

來源:互聯網
上載者:User

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 被正常刪除!




相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.