How to recover corrupted UNDO tablespace

Dear All,

Yesterday our development system went down because of CORRUPTED UNDO TABLESPACE.

the following error reported in the alert.log file

Sun Jul 31 09:27:35 2011

Flush retried for xcb 0x333b8e78, pmd 0x32898888

Doing block recovery for file 2 block 330

Block recovery from logseq 1462, block 70 to scn 42078052

Sun Jul 31 09:27:36 2011

Recovery of Online Redo Log: Thread 1 Group 3 Seq 1462 Reading mem 0

  Mem# 0 errs 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

Block recovery completed at rba 1462.408.16, scn 0.42078054

Sun Jul 31 09:27:38 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:41 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:45 2011

DEBUG: Replaying xcb 0x333ad298, pmd 0x33bc9e54 for failed op 8

Doing block recovery for file 2 block 227

Sun Jul 31 09:27:45 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

No block recovery was needed

Sun Jul 31 09:27:49 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

PMON: terminating instance due to error 472

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j009_1608.trc:

ORA-00472: PMON  process terminated with error

Kindly use the following solution if you encountered with the exact above situation.

Step 1

--------

SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;

SQL> Shutdown immediate;

Step 2

-------

Modify the corrupt.ora and set Undo_managment=Manual

SQL> Startup mount pfile='/tmp/corrupt.ora'

SQL> Show parameter undo

it should show manual

SQL> Alter database open ;

If it comes up

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles

Step 3

-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace= scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported

Live Example:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 31 10:52:25 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> create pfile = 'c:\corrupt.ora' from spfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount pfile='c:\corrupt.ora';

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> create rollback segment r01;

Rollback segment created.

SQL> alter rollback segment r01 online;

Rollback segment altered.

SQL> create undo tablespace undotbs01 datafile 'C:\oracle\product\10.2.0\oradata

\ORCL\undotbs001.dbf' size 100m;

Tablespace created.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> alter system set undo_tablespace=undotbs01 scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL>

ANY QUERIES ASKTAJ AT ASKDBATAJ