Wednesday, January 25, 2012

ORA-600 [4194] or ORA-600 [4193]

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE

Problem Description
ORA-600[4194] or ORA-600[4193] occurs when there is mismatch detected between Redo records and rollback (Undo) records. ORA-600[4194] / ORA-600[4193] error comes with two more arguments within bracket. 
Arg [a] indicates Maximum Undo record number in Undo block
Arg [b] indicates Undo record number from Redo block
Sample error is below:
ORA-00600: internal error code, arguments: [4194], [19], [33], [], [], []

Solution of the Problem - Drop the undo tablespace
This solution does not deal with Oracle unsupported parameter like _offline_rollback_segments and @_corrupted_rollback_segments to resolve ora-00600[4193]/[4194]. 

Step 01: 
Startup the database in nomount stage using spfile.
SQL> Startup nomount; 

In this case ensure you have started your database using spfile. "show parameter spfile" will display the location of spfile.

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

Shutdown the database.
SQL> Shutdown immediate; 

Step 02: 
Modify the pfile.ora and set Undo_managment=Manual
$vi /tmp/pfile.ora

Start the database in mount mode using modified pfile.
SQL> Startup mount pfile='/tmp/pfile.ora'

Ensure that you have undo_managment initialization parameter set to manual.
SQL> Show parameter undo

SQL> Alter database open ;

Once it open, create a rollback segment.

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile 'datafile_name.dbf' size 100M ;

Drop the old undo tablespace

SQL> Drop tablespace undotbs_old including contents and datafiles

Step 03:

Shutdown the database.
SQL> Shutdown immediate;

Start the database using spfile where it had undo_managment initialization parameter set to AUTO.

SQL> Startup nomount ; 

Change the undo_tablespace parameter to use new tablespace.

SQL>Alter system set undo_tablespace=undotbs_new scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported.

In case of RAC database instances from Instance which is up and running do the following:

SQL> Create undo tablespace undo_new datafile 'undotbs_new' size 100m ;

SQL> Alter system set undo_tablespace=undo_new 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 undotbs_old 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.
Get Oracle Certifications for all Exams
Free Online

No comments: