ORA-00600: internal error code, arguments: [4193], [46613], [47759], [], [], [], [], []

In this Document
 Goal
 Fix 
   Single instance
  For Rac Instance(If one instance is down and other is up and running)
  For 8i database and Below
 References

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

Applies to:
Oracle Server - Enterprise Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
***Checked for relevance on 03-Oct-2011***
Oracle Server Enterprise Edition

 

Goal
This note will give an Brief overview of the steps to resolve ORA-600 [4194]/[4193]:-

Fix
Short Description of ORA-00600[4194]

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

A mismatch has been detected between Redo records and rollback (Undo)
records.

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

 
Refer to Note:39283.1 for more details on the description of this error.

Basic Steps that Need to be Followed to Solve an ORA-00600[4194] Error

There are two Options to resolve this issue.

Options along with their solutions are given below.

 

Option 1:- Support Method(Drop the undo tablespace).

 

Option 1 :- Supported Method
======================

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 ;

Drop the Old undo tablespace

SQL> Drop tablespace <undo tablespace name> including contents and datafiles

 

Step 3
-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace=<new Undo tablespace created> 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 '<filename>' size <> m ;

Alter system set undo_tablespace=<New undo tablespace name> sid=<instance which has corrupt undo tablespace and is down>  ;

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 <Old undo tablespace of the failing instance> 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

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

Cause
When we try to apply redo to an undo block (forward changes are made by  the application of redo to a block) we check that the seq# in the undo  record matches the seq# in the redo record.

These seq# should be the  same because when we apply a redo record we must apply it to the
correct version of the block.

We can only apply a redo record to a  block that contains the same seq# as in the redo record.
If the seq# do not match then ORA-600[4193] [a].[b] is raised. .

Arg [a] Undo record seq number --> seq: 0xde0 = 3552
Arg [b] Redo record seq number --> seq: 0x0de2   = 3554

This implies some kind of block corruption in either the redo or the undo block.

 

Solution
A. If Database is opened:

1) Find out the rollback segment, based on the first part of the xid: 0x0002.045.00006c61

    usn=2 is the segment_id

    select segment_name,status from dba_rollback_segs where segment_id=2;

    RS_DATA1    ONLINE

2) Dump the transaction table of the rollback segment to see if all TX are commited:

    alter system dump undo header RS_DATA1;

3) check the trace file created under user_dump_dest

     In the trace file search for the Keyword "TRN TBL"

TRN TBL:: 

index state cflags wrap#   uel    scn             dba
-----------------------------------------------------------------------------
0x00   9     0x00  0x21eb1 0x0023 0x0000.d28c43e9 0x00000000 ......

state=9 means transaction is committed

4) offline the rollback segment:

     alter rollback segment rs_data1 offline;

      select status from dba_rollback_segs where segment_id=2;

5)   if STATUS=OFFLINE

      drop rollback segment RS_DATA1;

 

B. If Database doesn't open:

1.   a) If using rollback segments, remove the rollback_segments line from init.ora, and open database

      b) If using undo segments set undo_management = manual in init.ora/spfile, and try to open database.

2. If database opens means all transactions are committed, and you can drop the rollback segment or the undo tablespace

 

patchset

  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.