Undo tablespace recovery

Follow the below steps, If the database is up and running

You can see this error in your alert log file:

ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
SMON: mark undo segment 29 as needs recovery

ORACLE Instance test2 (pid = 11) - Error 600 encountered while recovering
transaction (29, 42) on object 36.

Step 1 : Create a new undo tablespace

create undo tablespace undotbs2 datafile
‘/opt/oracle/oradata/prod003/undotbs2.dbf’ size 300m;

Step 2: Find the problematic undo segment using the below command

sql>select SEGMENT_NAME, STATUS from dba_rollback_segs;

Step 3: Alter the spfile to use the currently created undotbs2.

sql>alter system set undo_tablespace=undotbs2 scope=spfile;

Step 4 : create a pfile from the spfile

sql> create pfile from spfile;

Step 5 : Append the problematic rollback segements in the pfile.

_offline_rollback_segments=” segment_name from dba_rollback_segs"
_corrupt_rollback_segments=”segment_name from dba_rollback_segs”


Step 6 : shutdown the database using immediate or abort.

Step 7 : startup using pfile.

Step 8 : bring the tablespace offline and drop the corrupted undo tablespace .

sql> alter tablespace undotbs1 offline;
sql> drop tablespace undotbs1 including contents and datafile;


More Info Refer :

http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm