Deleting rollback segments in recovery state

[Former Member]

Hi,

I am working on a crash and burn box with SAP 4.6D and Oracle 9.2.0.3. I had activated AUM (Auto undo Management) on it and someone tried to add another datafile. Once that was done for come reason its gone into recovery state for the undo segments. I have tried to delete them and tried to remove PSAPUNDO but its giving an error message:

ORA-01548: active rollback segment '_SYSSMU13$' found, terminate dropping

tablespace

A quick select statement on dba_rollback_segs table shows that the undo segments are in "NEEDS RECOVERY" status. Anyone knows why this is? I have currently put rollback segments back on so the systemn can continue running.

thanks,

Mani

[stefan_koehler · Accepted solution]

Hello Mani,

take a look at here:

http://www.fors.com/orasupp/rdbms/dba/28812_1.HTM

or here: http://www.my-whiteboard.com/oracle-dba/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data...

I think you are mixing two types of undo management.

• Rollback Segments (MANUAL)

• Undo Segments (AUTO)

Which type of segments needs recovery or in other words.. are the segments used in an automatic or manual undo management?

Regards

Stefan

[Former Member]

1) Take a Backup.

(2) Shutdown the DB

(2) Set UNDO_MANAGEMENT=MANUAL

(3) Set parameter OFFLINEROLLBACK_SEGMENT = (_SYSSMU1$, SYSSMU2$...) etc for all the segments that were in the original UNDO tablespace. I checked v$rollname for the name of those active ones in the replacement UNDO TBS (they were numbered 11 - 20) so I put 1 - 10 in the offlinerollback_segment parameter.

(4) Open the DB

(5) Drop each RBS 1 - 10 individually using 'DROP ROLLBACK SEGMENT "seg_name".

(6) Drop the UNDO tablespace.

(7) Shutdown the DB

(8) Comment out or remove the parameter OFFLINEROLLBACK_SEGMENT

(9) Set UNDO_MANAGEMENT back to AUTO

(10) Ensure the UNDO_TABLESPACE is set to your new UNDO TBS name

(11) Restart the DB

(12) Check the alert log for any errors - if all looks pukka then shutdown and take another backup. (You know it makes sense)

OFFLINEROLLBACK_SEGMENT is undocumented and support warn that its use should be restricted to those occasions when support ask you to use it. Be warned!

Regards,

Sam