Fixing undo segment corruption

Recently i faced a typical situation where user performed a delete on
a huge table. All of a sudden he terminated that delete statement which
leaded to one undo segment corruption because of this, when other transactions hitting this segment, lands
up with an error. so I used below procedure to recover that undo segment

Note : The procedure i followed may differ from other solutions

Initially I tried to drop undo tablespace (after creating new one), but its saying i cannot drop it. so i followed below steps

1) created pfile from spfile (if your database is already using
pfile, then take a backup of that file and this step is not necessary)

2) shutdown database

3) edit pfile and include the following line

*._corrupt_rollback_segments=”segment_name which is giving problem”

we can know the corrput segment name using dba_rollback_segs

4) startup pfile=’pfile name’

5) drop the old undo tablespace (remember i created new one already and made that as default )

6) shutdown the database

7) startup using spfile (if no spfile used, edit your pfile and remove the parameter which was included in step 3

Now it should start normally and your work will be smoother

  • July 15, 2017 | 38 views
  • Comments