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

advertisements

_____________________________________________________________________________________________________________________

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.

2. Offline the tablespace to be dropped.

SQL> alter tablespace UNDOTBS1 offline;

Tablespace altered.

3. 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'' ' );

4. 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)

5. Startup the database and drop the undo tablespace.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

_____________________________________________________________________________________________________________________

Website Stats