How to resolve the ora-600 with argument 4194

Problem:

The following error is occurring in the alert.log right before the database crashes.

 ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

Cause:

This issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.

 ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

 This error indicates that 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

 Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

Solution:

1. Create pfile from spfile to edit

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

2. Shutdown the instance

3. set the following parameters in the pfile /tmp/initsid.ora
 
    undo_management = manual
     event = ‘10513 trace name context forever, level 2’

4. SQL>>startup restrict pfile=’/tmp/initsid.ora’

5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != ‘OFFLINE’;

 If tablespace is completely offline then continue to the next step or any other status contact oracle support.

6. Create new undo tablespace – example

SQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;

7. Drop old undo tablespace

SQL>drop tablespace <old undo tablespace> including contents and datafiles;

8. SQL>shutdown immediate;

9 SQL>startup nomount;  –> Using your Original spfile

10. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = ‘<new tablespace created in step 6>’ scope=spfile;

11. SQL>shutdown immediate;

12. SQL>startup;  –> Using spfile
 

Note:

The reason we create a new undo tablespace first is to use new undo segment numbers that are higher then the current segments being used.  This way when a transaction goes to do block clean-out the reference to that undo segment does not exist and continues with the block clean-out.

  • September 23, 2018 | 15 views
  • Comments