Problem in dropping UNDO Tablespace:

Problem in dropping UNDO Tablespace:



Error: ORA-01548: active rollback segment ‘_SYSSMU1_3780397527$’ found, terminate dropping tablespace :

When, I was taking RMAN backup in my test environment I feel that, backupset size is very large and because I have set the retention of last 2 backups, therefore every time I am facing problem of storage in my FRA. Therefore I have decided to remove some unused tablespaces that I have created for some practical purpose. I also observed that the size of my existing UNDO tablespace (UNDOTBS1) is become approx. 2.8GB so I have also decided to replace this UNDO tablespace with new UNDO tablespace ( UNDOTBSN01) and drop this existing tablespace i.e (UNDOTBS1). So my this blog post will demonstrate you, what are the steps I have adopted and what are the problems I have faced to replace my current UNDO tablespace i.e. UNDOTBS1 with new current UNDO tablespace i.e. UNDOTBSN01.
Database Version : 11.2.0.1

 Checking Before change:

 RMAN> report schema;
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs      Datafile Name

---- -------- -------------------- -------------------------------

1    730      SYSTEM               ***       /u01/app/oracle/oradata/orcl/system01.dbf

2    730      SYSAUX               ***      /u01/app/oracle/oradata/orcl/sysaux01.dbf

3    2340     UNDOTBS1             ***      /u01/app/oracle/oradata/orcl/UNDOTBSN01.dbf

4    115      USERS                ***     /u01/app/oracle/oradata/orcl/users01.dbf

5    100      EXAMPLE              ***     /u01/app/oracle/oradata/orcl/example01.dbf

6    730      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux02.dbf

7    730      SYSTEM               ***     /u01/app/oracle/oradata/orcl/system02.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace        Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    100      TEMP1              32767     /u01/app/oracle/oradata/orcl/temp01
SQL> show parameter undo
NAME                                 TYPE           VALUE
—————————    ——— ———–
undo_management                string      AUTO
undo_retention                       integer     900
undo_tablespace                     string      UNDOTBS1

Procedure:

 Step1:

I have created a new UNDO tablespace with name UNDOTBSN01.
 SQL> create undo tablespace UNDOTBSN01 datafile ‘/u01/app/oracle/oradata/orcl/undotbsn1.dbf’ size 500M autoextend on next 10M maxsize unlimited;
Tablespace created.

Step2:

I have set this new UNDO tablespace UNDOTBSN01 as , my database’s default UNDO tablespace.
 SQL> alter system set undo_tablespace=UNDOTBSN01 scope=spfile;
System altered.

Step3:

I have shutdown my database to release all pending transaction in my database.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step4:

I have started again my database.
SQL> startup
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             603981816 bytes
Database Buffers          226492416 bytes
Redo Buffers                6590464 bytes
Database mounted.
Database opend.

Step5:

Checked again, to see whether my change is reflecting or not and found it is reflecting.
SQL> show parameter undo
NAME                                 TYPE                  VALUE
—————– ———— ——————
undo_management            string            AUTO
undo_retention                 integer            900
undo_tablespace                 string            UNDOTBSN01

Step6:

 Now I have decided to drop my old UNDO tablespace i.e UNDOTBS1 , and I faced following problem “ORA-01548: active rollback segment ‘_SYSSMU1_3780397527$’ found, terminate dropping tablespace”
SQL> 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

Step7:

When I have checked dba_rollback_segs data dictionaryI found that some of the rollback segments are partly available those are associated with old UNDO tablespace i.e. UNDOTBS1 and I am getting error for one of them i.e.  ‘_SYSSMU1_3780397527$’.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS                                      TABLESPACE_NAME
——————— ——— ————- ——————————
SYSTEM                                           ONLINE                                   SYSTEM
_SYSSMU1_3780397527$           PARTLY AVAILABLE           UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE                                   UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE                                  UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE                                  UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE                                 UNDOTBS1
_SYSSMU6_2443381498$           PARTLY AVAILABLE          UNDOTBS1
_SYSSMU7_3286610060$           PARTLY AVAILABLE        UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE                                UNDOTBS1
_SYSSMU9_1424341975$           PARTLY AVAILABLE          UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE                               UNDOTBS1
_SYSSMU11_3715213117$            ONLINE                                 UNDOTBSN01
_SYSSMU12_3563522984$          ONLINE                                UNDOTBSN01
_SYSSMU13_1284218525$          ONLINE                                 UNDOTBSN01
_SYSSMU14_1624181735$          ONLINE                                 UNDOTBSN01
_SYSSMU15_1102482685$          ONLINE                               UNDOTBSN01
_SYSSMU16_1909532494$          ONLINE                               UNDOTBSN01
_SYSSMU17_3453924897$          ONLINE                               UNDOTBSN01
_SYSSMU18_4116382225$          ONLINE                               UNDOTBSN01
_SYSSMU19_606215510$           ONLINE                               UNDOTBSN01
_SYSSMU20_1963701883$          ONLINE                             UNDOTBSN01
21 rows selected.

 Step8:

When I have checked from V$rollname, then I have not found that PARTLY AVAILABLE rollback segments in this performance view.

SQL> select * from V$rollname;
USN                     NAME
———- ——————————
0                          SYSTEM
11                        _SYSSMU11_3715213117$
12                       _SYSSMU12_3563522984$
13                       _SYSSMU13_1284218525$
14                       _SYSSMU14_1624181735$
15                       _SYSSMU15_1102482685$
16                       _SYSSMU16_1909532494$
17                       _SYSSMU17_3453924897$
18                       _SYSSMU18_4116382225$
19                       _SYSSMU19_606215510$
20                      _SYSSMU20_1963701883$
11 rows selected.

 Step9:

 I have created a pfile from my current spfile.
SQL> create pfile=’/home/oracle/Desktop/mypfile.ora’ from spfile;
File created.

 Step10:

I have added  “ORCL._offline_rollback_segments” parameter and assigning all PARTLY AVAILABLE rollback segments to it.
*.undo_tablespace=’UNDOTBSN01′
 ORCL._offline_rollback_segments=(‘_SYSSMU1_3780397527$’,’_SYSSMU6_2443381498$’,’_SYSSMU7_3286610060$’,’_SYSSMU9_1424341975$’)

 “OR”

ORCL.__corrupted_rollback_segments=(‘_SYSSMU1_3780397527$’,’_SYSSMU6_2443381498$’,’_SYSSMU7_3286610060$’,’_SYSSMU9_1424341975$’)
[oracle@localhost ~]$ cat /home/oracle/Desktop/mypfile.ora
ORCL.__db_cache_size=209715200
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=293601280
ORCL.__sga_target=549453824
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=297795584
ORCL.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/flash_recovery_area/orcl/control02.ctl’#Restore Controlfile
*.db_16k_cache_size=8388608
*.db_4k_cache_size=4096
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle/oradata’
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=6442450944
*.diagnostic_dest=’/u01/app/oracle’
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
*.fast_start_mttr_target=15
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=842006528
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.os_authent_prefix=’ ‘
*.processes=150
*.recyclebin=’ON’
*.remote_login_passwordfile=’EXCLUSIVE’
*.resource_manager_plan=”
ORCL.resource_manager_plan=”
orcl.resource_manager_plan=’FORCE:’
*.undo_tablespace=’UNDOTBS01′
ORCL._offline_rollback_segments=(‘_SYSSMU1_3780397527$’,’_SYSSMU6_2443381498$’,’_SYSSMU7_3286610060$’,’_SYSSMU9_1424341975$’)

 Step11:

Now I have started my database from this parameter file ‘/home/oracle/Desktop/mypfile.ora.
SQL> startup pfile=’/home/oracle/Desktop/mypfile.ora’;
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             603981816 bytes
Database Buffers     226492416 bytes
Redo Buffers             6590464 bytes
Database mounted.
Database opened.

 Step12:

Again checked dba_rollback_segs and found that status become of those those tablespace from PARTLY AVAILABLE  to NEEDS RECOVERY.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS                            TABLESPACE_NAME
——————– ——————– ———————————
SYSTEM                                            ONLINE                        SYSTEM
_SYSSMU1_3780397527$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE                       UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE                       UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE                       UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE                        UNDOTBS1
_SYSSMU6_2443381498$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU7_3286610060$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE                       UNDOTBS1
_SYSSMU9_1424341975$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE                      UNDOTBS1
_SYSSMU11_3715213117$             ONLINE                       UNDOTBSN01
_SYSSMU12_3563522984$          ONLINE                        UNDOTBSN01
_SYSSMU13_1284218525$           ONLINE                       UNDOTBSN01
_SYSSMU14_1624181735$           ONLINE                       UNDOTBSN01
_SYSSMU15_1102482685$          ONLINE                       UNDOTBSN01
_SYSSMU16_1909532494$          ONLINE                      UNDOTBSN01
_SYSSMU17_3453924897$          ONLINE                      UNDOTBSN01
_SYSSMU18_4116382225$          ONLINE                      UNDOTBSN01
_SYSSMU19_606215510$           ONLINE                       UNDOTBSN01
_SYSSMU20_1963701883$          ONLINE                     UNDOTBSN01
21 rows selected.

 Step13:

Now I have again tried to drop my old UNDO tablespace i.e UNDOTBS1 , and again I received same error “ORA-01548: active rollback segment ‘_SYSSMU1_3780397527$’ found, terminate dropping tablespace”
SQL> 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

 Step14:

So I have decided to drop these NEEDS RECOVERY type all rollback segments one by one.
SQL> drop rollback segment “_SYSSMU1_3780397527$”;
Rollback segment dropped.
SQL> drop rollback segment “_SYSSMU6_2443381498$”;
Rollback segment dropped.
SQL> drop rollback segment “_SYSSMU7_3286610060$”;
Rollback segment dropped.
SQL> drop rollback segment “_SYSSMU9_1424341975$”;
Rollback segment dropped.

 Step15:

 Now I have tried to drop old UNDO tablespace i.e. UNDOTBS1, and Hurry I have succeed.
 SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

 Step16:

I have shutdown my database and started again with database’s spfile.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             603981816 bytes
Database Buffers     226492416 bytes
Redo Buffers             6590464 bytes
Database mounted.
Database opened.

 Step17:

Checked again dba_rollback_segs data dictionary and found no active rollback segments related to my old UNDO tablespace i.e. UNDOTBS1.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS                TABLESPACE_NAME
——————– ————– —————————————-
SYSTEM                                         ONLINE           SYSTEM
_SYSSMU11_3715213117$          ONLINE           UNDOTBSN01
_SYSSMU12_3563522984$        ONLINE           UNDOTBSN01
_SYSSMU13_1284218525$         ONLINE           UNDOTBSN01
_SYSSMU14_1624181735$         ONLINE           UNDOTBSN01
_SYSSMU15_1102482685$        ONLINE           UNDOTBSN01
_SYSSMU16_1909532494$        ONLINE           UNDOTBSN01
_SYSSMU17_3453924897$        ONLINE           UNDOTBSN01
_SYSSMU18_4116382225$         ONLINE           UNDOTBSN01
_SYSSMU19_606215510$           ONLINE           UNDOTBSN01
_SYSSMU20_1963701883$        ONLINE           UNDOTBSN01
11 rows selected.

 Step18:

SQL> select a.TS# “TS_No”,a.file# “Datafile_No”,b.name “Tablespace_Name”,a.name “Datafile_Name” from v$datafile a,v$tablespace b where a.TS#=b.TS#  order by a.TS#;
TS_No          Datafile_No    Tablespace_Name   Datafile_Name
——– ———— —————- ————————————-
0                       7                            SYSTEM              /u01/app/oracle/oradata/orcl/system02.dbf
0                       1                            SYSTEM              /u01/app/oracle/oradata/orcl/system01.dbf
1                       6                            SYSAUX              /u01/app/oracle/oradata/orcl/sysaux02.dbf
1                       2                             SYSAUX            /u01/app/oracle/oradata/orcl/sysaux01.dbf
4                      4                               USERS             /u01/app/oracle/oradata/orcl/users01.dbf
6                      5                           EXAMPLE           /u01/app/oracle/oradata/orcl/example01.dbf
23                    9                  UNDOTBSN01          /u01/app/oracle/oradata/orcl/undotbsn1.dbf
7 rows selected.

Comments