If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.
Parnassusdata Software Database Recovery Team
Service Hotline: +86 13764045638 E-mail: [email protected]
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
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 number of undo records in the undo block +1 matches the record number in the redo record.
Because we are adding a new undo record, we know that the record number in that undo block must be one greater than the maximum number in that block.
ORA-600 [4194] and ORA-600 [4193] happens when Oracle is trying to add a new undo block for an existent transaction.
In order to do that the UBA is taken from the FREE POOL in the undo segment compared with the undo block.
If there is a mismatch, the error is produced.
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 2: – Drop the Problematic undo segment
@Option 3:- If System undo segment is involved
There is no need to use Unsupported parameter like _offline_rollback_segments and @_corrupted_rollback_segments to resolve ora-00600[4193]/[4194]
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 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 This note is supported method however If your database has been forced open using unsupported method and then you are encountering this ora-00600[4194]/[4193] during database open tablespace. Just create the New undo tablespace and make it the default and do the full database export.
when your database has been forced open using _allow_resetlogs_corruption and _corrupted_rollback_segments the undo$ is not populated with the correct version information undo tablespace you would get errors like ora-00600[4097] or ORA-1555 during the export if the undo segment number gets reused by new undo tablespace So its advisable tablespace as default undo tablespace without dropping the old one. This is very important. This is applicable only if you have used unsupported parameters _allow_resetlogs__corrupted_rollback_segments
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 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
@Go to option 2 to identify which undo segment has issue
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.
Option 2 (Drop the Rollback segment)
—————————————————
From the ora-00600[4194] trace file identify the undo segment
For example
ORA-00600: internal error code, arguments: [4194], [19], [33], [], [], [],
In the above example
ARGUMENTS:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block
a—-> 19
Search for UNDO BLK in the trace file
********************************************************************************
UNDO BLK:
xid: 0x0002.014.0004d316 seq: 0x7817 cnt: 0x13 irb: 0x13 icl: 0x0 flg: 0x0000
cnt—>0x13 –> Decimal –> 19
First argument of xid is Undo segment number in Hex.
So in the above example its confirmed its undo segment 0x0002 –> @Decimal=2 which has the issue .
Once the database is up after following step1 and step 2 of Option 1
Identify the rollback segment
SQL>Select name,us# from undo$ where us# =<value found in the trace>
Identify the current value of _smu_debug_mode
SELECT a.ksppinm “Parameter”,a.ksppdesc “Description”, b.ksppstvl “Session Value”,c.ksppstvl “Instance Value” FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = @ Alter system set “_smu_debug_mode”=4;
Alter rollback segment “_SYSSMUX$” offline;
drop rollback segment “_SYSSMUX$”;
alter system set “_smu_debug_mode”=<original value>;
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
Leave a Reply