Today I came across a standby copy of a library to be built as a test library!

After rebuilding the control file and starting the library with resetlogs, the database went down directly.

Reported a bunch of ora-600 [4194] problems out

Fri May 09 11:38:24 2014
opidrv aborting process M000 ospid (20091) as a result of ORA-603
Fri May 09 11:38:27 2014
Flush retried for xcb 0x44053b268, pmd 0x43f7e9628
Errors in file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_pmon_16902.trc (incident=17):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/incident/incdir_17/t2fls_pmon_16902_i17.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri May 09 11:38:29 2014
Dumping diagnostic data in directory=[cdmp_20140509113829], requested by (instance=1, osid=16902 (PMON)), summary=[incident=17].
Errors in file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_pmon_16902.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 16902): terminating the instance due to error 472
System state dump requested by (instance=1, osid=16902 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /paic/t2fls/rdbms/oracle/diag/rdbms/t2fls/t2fls/trace/t2fls_diag_16973.trc
Dumping diagnostic data in directory=[cdmp_20140509113834], requested by (instance=1, osid=16902 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 16902

This issue looks familiar and ora-600 [4193] is the same as the undo issue.

The information on the metlink is as follows.

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

Solution
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_management=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 <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> scope=spfile ;

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