(1) Create a new system management undo tablespace:

SQL> connect / as sysdba

SQL> create undo tablespace undotbs2 datafile ‘/mnt/MX_ORA_5/oradata/PROD/undotbs2.dbf’ size 8192m;

(2) Determine the problem undo segment:

SQL> select SEGMENT_NAME, STATUS from dba_rollback_segs;

The problem segment will show a “Needs Recovery” status.

(3) Alter the system to use the new undo tablespace:

SQL> alter system set undo_tablespace=undotbs2 scope=both; (Note if you are not using an spfile, omit the scope command)

(4) If you are using an spfile, create a pfile from it:

SQL> connect / as sysdba

SQL> create pfile=’/mnt/MX_ORA_5/oradata/PROD/initPROD.ora’ from spfile;

(5) Edit the inittest.ora pfile and add (using the undo segment from our example error):

*._offline_rollback_segments=” _SYSSMU13$”

*._corrupt_rollback_segments=”_SYSSMU13$”

(6) Now shutdown your instance, this may require a shutdown abort, but try a shutdown immediate first.

(7) Startup using the manual startup command:

SQL> startup pfile=’/u01/oacle/admin/test/pfile=inittest.ora’

(8) Alter the old undo tablespace offline:

SQL> alter tablespace undotbs1 offline;

(9) Drop the offending tablespace:

SQL> drop tablespace undotbs1 including contents and datafiles;

(10) Shut down immediate.

(11) Edit the inittest.ora file to eliminate the underscore parameters.

(12) Restart the instance using the pfile option.

(13) Create an spfile from the pfile:

SQL> create spfile from pfile=’/u01/oracle/admin/test/pfile/inittest.ora’;

Once step 13 is accomplished the database should be up normally. However, it might be wise to do a full backup and then rebuild using an export and import. Since we had to drop a undo segment that had some possibly active transactions un-applied the database may not be fully consistent.

sql> alter session set "_smu_debug_mode"=4;
sql> alter rollback segment "_SYSSMU13$" online;