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 dictionary, I 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.
Airydba Blog Notes 