ORA-01548: active rollback segment, Find out Oracle 10g 11g Active Undo Segments from system datafile

Error Description:

Drop UNDO tablespace command fails with following error.
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_2986795754$' found, terminate
dropping tablespace

Solution Description:
Pre-requisites need before dropping a undo tablespace.
  1. Create a new Undo tablespace

SQL> create undo tablespace undo02 datafile '/u01/app/oracle/oradata/testdb/undo0201.dbf' size 500m;

Tablespace created.

  1. Offline the tablespace to be dropped.

SQL> alter tablespace UNDOTBS1 offline;

Tablespace altered.

  1. Find out the active undo segments from the system datafile.
strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
              and substr(drs.segment_name,1,7) != '_SYSSMU'
_SYSSMU10_1912826470
_SYSSMU10_84651485
_SYSSMU11_2656753586
_SYSSMU12_2886358909
_SYSSMU1_2986795754
_SYSSMU13_718924942
_SYSSMU1_4289064447
_SYSSMU14_3114789169
_SYSSMU15_996261396
_SYSSMU16_1415326701
_SYSSMU17_2055763971
_SYSSMU18_3698054671
_SYSSMU19_3843065260
_SYSSMU20_4176760040
_SYSSMU2_346913448
_SYSSMU2_3741593945
_SYSSMU3_219912871
_SYSSMU3_2414309367
_SYSSMU4_1372950691
_SYSSMU4_2796668278
_SYSSMU5_1189263717
_SYSSMU5_3414239920
_SYSSMU6_2463593703
_SYSSMU6_3524683480
_SYSSMU7_1565166063
_SYSSMU7_2494227896
_SYSSMU8_2615489449
_SYSSMU8_625026625
_SYSSMU9_3163463872
_SYSSMU9_4094185854
YD'              and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
  1. Add the following parameters to the init<dbname>.ora file with active undo segments as follow.

undo_tablespace='UNDO02'
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_1912826470$,
_SYSSMU10_84651485$,_SYSSMU11_2656753586$,_SYSSMU12_2886358909$,
_SYSSMU1_2986795754$,_SYSSMU13_718924942$,_SYSSMU1_4289064447$,
_SYSSMU14_3114789169$,_SYSSMU15_996261396$,_SYSSMU16_1415326701$,
_SYSSMU17_2055763971$,_SYSSMU18_3698054671$,
_SYSSMU19_3843065260$,_SYSSMU20_4176760040$,_SYSSMU2_346913448$,
_SYSSMU2_3741593945$,_SYSSMU3_219912871$,_SYSSMU3_2414309367$,
_SYSSMU4_1372950691$,_SYSSMU4_2796668278$,_SYSSMU5_1189263717$,
_SYSSMU5_3414239920$,_SYSSMU6_2463593703$,_SYSSMU6_3524683480$,
_SYSSMU7_1565166063$,_SYSSMU7_2494227896$,_SYSSMU8_2615489449$,
_SYSSMU8_625026625$,_SYSSMU9_3163463872$,_SYSSMU9_4094185854)

Or

_offline_rollback_segments=(_SYSSMU10_1912826470$,
_SYSSMU10_84651485$,_SYSSMU11_2656753586$,_SYSSMU12_2886358909$,
_SYSSMU1_2986795754$,_SYSSMU13_718924942$,_SYSSMU1_4289064447$,
_SYSSMU14_3114789169$,_SYSSMU15_996261396$,_SYSSMU16_1415326701$,
_SYSSMU17_2055763971$,_SYSSMU18_3698054671$,
_SYSSMU19_3843065260$,_SYSSMU20_4176760040$,_SYSSMU2_346913448$,
_SYSSMU2_3741593945$,_SYSSMU3_219912871$,_SYSSMU3_2414309367$,
_SYSSMU4_1372950691$,_SYSSMU4_2796668278$,_SYSSMU5_1189263717$,
_SYSSMU5_3414239920$,_SYSSMU6_2463593703$,_SYSSMU6_3524683480$,
_SYSSMU7_1565166063$,_SYSSMU7_2494227896$,_SYSSMU8_2615489449$,
_SYSSMU8_625026625$,_SYSSMU9_3163463872$,_SYSSMU9_4094185854)

  1. Startup the database and drop the undo tablespace.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google