重建UNDO表空间

Drop the undo tablespace. 

 

 Single instance

This error normally happens for a new transaction. The trace file actually shows an active transaction for the undo segment because this is the transaction created by the same process.If the undo segment happens to have an active transaction , then Oracle
may recover it later with no problems .

Normally if the header is dumped after the error, the active transactin is gone.

So a Simpler option to resolve this issue is.

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 ;

Please note  :-   You can delay the drop of the Old undo tablespace this is just to allow the block cleanout to happen for dead transaction.
                               So the below step can be issued after database has been  up and running with new undo tablespace for couple of hours.
                               Also note if your database has been forced open(datafiles are not in sync and archivelogs missing ) using any unsupported method then please donot drop the Old undo.

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

 

For Rac Instance(If one instance is down and other is up and running)

------------------------

If one node is up and running and other node is failing with ORA-00600[4194]/[4193] then

From the instance which is up and running create a new undo tablespace and make it the default one for the other instance which is down with the error.Startup the failing instance with
the new undo tablespace.

 

 

From Instance which is up and running 

Create undo tablespace undo_new datafile '' size <> m ;

Alter system set undo_tablespace= scope=spfile sid=;

Now Startup the Instance which is down

SQL>Startup mount 

SQL>Show parameter undo

Should show the new undo tablespace created above

SQL>Alter database open ;

SQL>Drop tablespace including contents and datafiles

 

If all the Instance is down in the Rac due to this error then following the instruction given for Single instance and  create new undo tablespace.

For 8i database and Below

 

SQL>Startup restrict

 

Drop the Manual rollback segments and recreate it

 

  Please note :-
  Option 1 would fail if the undo segment involved is System undo .
  Please open a Service request with Oracle to diagnose the issue further  
  if option 1 fails.

 

@ ---------------------------------------------------

@Arg [a] Maximum Undo record number in Undo block 
@Arg [b] Undo record number from Redo block 

@Search for UNDO BLK in the trace file

 

 

 

@Option 3(System undo segment erroring with Ora-00600[4194/4193]

@Option 1 would fail if the undo segment involved is System undo segment.

@Please refer the note given below for patching the same.

@Note.452620.1 :Int/Pub ORA-600 [4193] ORA-600 [4194] IN SYSTEM ROLLBACK SEGMENT. HOW TO @PATCH

 

 

 

 

 

References

NOTE:39283.1 - ORA-600 [4194] "Undo Record Number Mismatch While Adding Undo Record"

--end--