Memperbaiki undotbs yang membengkak atau corrupt

Ternyata Oracle cukup rumit. Pertanyaan selanjutnya adalah bagaimana memanage file-file control dalam oracle.
Untuk masalah undotbs1.dbf yang membengkak, dapat disolusikan sbb:

Create a new system management undo tablespace:
SQL> connect / as sysdba
SQL> create undo tablespace undotbs2
datafile'/u/oracle/oradata/test/undotbs2.dbf’ size 500m;

Determine the problem undo segment:
SQL> select SEGMENT_NAME, STATUS from dba_rollback_segs;

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

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)

If you are using an spfile, create a pfile from it:
SQL> connect / as sysdba
SQL> create pfile=’/u01/oracle/admin/test/pfile/inittest.ora’ from spfile;

Edit the inittest.ora pfile and add (using the undo segment from our example error):
*._offline_rollback_segments=” _SYSSMU29$”
*._corrupt_rollback_segments=”_SYSSMU29$”

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

Startup using the manual startup command:
SQL> startup pfile=’/u01/oacle/admin/test/pfile=inittest.ora’

Alter the old undo tablespace offline:
SQL> alter tablespace undotbs1 offline;

Drop the offending tablespace:
SQL> drop tablespace undotbs1 including contents and datafiles;

Shut down immediate.
Edit the inittest.ora file to eliminate the underscore parameters.
Restart the instance using the pfile option.

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


Sumber :
http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm
http://www.dba-oracle.com/t_fix_undo_log_corruption.htm
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5669213349582
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1354906470493