Applies to:                                                                                                                                                            

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 10.2.0.1 - Release: 9.0.1 to 10.2 Information in this document applies to any platform.

Purpose                                                                                                                                                  

This note will cover the basic undo corruption questions and answers.

Questions  and Answers                                                                                                                                                 

1.  What  is Undo (Rollback  Segments)?

Rollback Segments(Undo ) are central to the RDBMS, being used for both rollback (transaction) recovery and for building consistent read database snapshots.

2. What is the  difference  between MANUAL and AUTOMATIC  undo?

Manual rollback segments are created and maintained by the DBA manually. The operations like creating, offline,online and drop rollback segments needs to be done manually by the DBA.You need specify the rollback_segments parameter in the init.ora to specify the rollback segments needs to be used.The "Note.62005.1 " will explain the details on handling manual rollback

From oracle 9i onwards oracle introduced the new method of automatic undo management.Automatic undo segments are created and maintained by ORACLE automatically. By creating undo tablespace and specifying the parameters undo_management=auto and undo_tablespace= <tablespace name> will allow oracle to manage undo automatically.The undo segments are created automatically with segment names as

_SYSSMU1$,_SYSSMU2$...etc .The "Note.135090.1" will explain details on automatic undo management.

3.  How to  Identify Undo Corruption?

Undo Corruption is automatically detected by oracle and reported in the form of ORA-600[4xxx] errors. Following are the most common undo corruption errors.

a) ORA-600 [4193] [a] [b]

Arg [a] Undo record seq number Arg [b] Redo record seq number

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

We are validating the Undo block sequence number in the undo block against the Redo block sequence number relating to the change being applied. This error is reported when this validation fails.

@Note 409214.1 How to resolve ORA-600 [4194] errors. b) ORA-600 [4194] [a] [b]

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

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

We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails.

@Note 409214.1 How to resolve ORA-600 [4194] errors. c) ORA-600 [4146] [a] [b]

Arg [a] Sequence# expected

Arg [b] Sequence# in block header.

The rollback segment does not contain sufficient undo to rollback a transaction

The ORA-600 [4146] basically indicates some kind of corruption with the UNDO (rollback segment) block. Note 28226.1 ORA-600 [4146] Undo Block not new enough.

d) ORA-600 [4000] [a]

Arg [a] Undo segment number

"It means that Oracle has tried to find an undo segment number in the dictionary cache and failed."

@Note 741227.1 ORA-600[4000].

e) ORA-600 [4137] "XID in Undo and Redo Does Not Match"

While backing out an undo record (i.e. at the time of rollback) we found a transaction id mis-match indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.

Note 43914.1 ORA-600 [4137] XID in Undo and Redo Does Not Match.

f)   Alert log shows " Error 600 encountered while recovering transaction " messages The two arguments in the recovering transaction are (usn,slot)

Where usn ---> Undo segment number

slot ---> The slot within the undo which contains the active transaction.

4.  How to  identify which  rollback segment (Undo  Segment) is  corrupted?

Usually all the above errors will dump a trace file under udump directory. In most of the trace files you will see the following lines.

UNDO BLK:

xid: 0x0008.01c.00021ef1 seq: 0x4ad4 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000

xid: 0x0008.01c.00021ef1 =Undo segment no + Slot no + Sequence no Undo segment no =0x0008 =8

In the above example the undo segment number 8 is bad.

You can get the name of the undo segment from dba_rollback_segs view: select segment_name from dba_rollback_segs where segment_id=8;

5.  How to find if there are  any active  transactions in the  undo  segments?

If the undo segment number and name are known from the trace file then you can use the following command to dump the undo segment header.

alter system dump undo header "<undo segment name>"; eg: alter system dump undo header "_SYSSMU1$";

If the undo segment is unknown then set the following event in the init.ora file and bounce the instance. This event will dump all the undo segment headers.

event="10015 trace name context forever, level 10"

The trace file will be created under udump directory in both the cases. In the trace file search for the Keyword "TRN TBL"

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num

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

0x00 9 0x00 0x21eb1 0x0023 0x0000.d28c43e9 0x00000000 0x0000.000.00000000 0x00000000 0x00000000

.

.

0x14 10 0x90 0x21e6d 0x0000 0x0000.d28c4437 0x0080007e 0x0000.000.00000000 0x00000001 0x00000000

0x15 9 0x00 0x21e30 0x0020 0x0000.d28c4064 0x00800484 0x0000.000.00000000 0x00000003 0x00000000

Note the second column "state" specifies the status of the rollback segment . If 10 then there is an active transaction.

If  9 then the transaction is committed.

6.  How to  resolve  Undo Corruption?

The severity of the undo corruption depends on transaction activity. Case 1: There are no active transactions in the undo segments.

There are few options to resolve case1.

a)    Restore from backup and recover.

b)     Drop and recreate the undo tablespace.

Create new undo tablespace and drop the old one.

Make sure to change the undo_tablespace to the new tablespace created.

Case 2: There are active transactions in the undo segments.

There are few options to resolve case2

a)    Best option to resolve this is to restore from backup and recover.

b)   Use the _offline/_corrupted rollback_segment parameters. This is NOT SUPPORTED solution and should only be used if advised by an oracle support person.

Follow steps 3 to 9 from Note 292891.1 to identify the active transactions and dropping undo segments using