Oracle ORA-600[4147]

Posted by PDSERVICE on Nov 15, 2015 In

ORA-600[4147][a][b] it’s a corruption in the undo segment.

Block not new enough (Undo) Sequences match but count wrong.

[a] Record number from the UBA we want to look at.

[b] Count on the Undo block header.

This error is raised when we are looking at a data block, and need to read the undo pointed at by the UBA(eg. for CR) but the record number from the UBA we want to look at doesn’t exist in the

undo block header.

The UNDO block we are looking at is dumped to the trace file before the ORA-600[4147] and call stack.

E.g. in 10.1.0.5

The undo record shows:

2007-07-10 10:48:48.467

*** SERVICE NAME:(SYS$USERS) 2007-07-10 10:48:48.466

*** SESSION ID:(281.26013) 2007-07-10 10:48:48.466

Dump of buffer cache at level 3

BH (0x0x72fcbfe0) file#: 2 rdba: 0x00802fdc (2/12252) class: 42 ba: 0x0x72618000

set: 6 blksize: 8192 bsi: 0 set_flg: 0 pwbcnt: 0

dbwrid: 0 obj: -1 objn: 0 tsn: 1 afn: 2

hash: [53faf870,ac20da50] lru: [53fd885c,57fb57ec]

ckptq: [60fe6408,53fd8830] fileq: [77fed448,68fe2cf0]

use: [NULL] wait: [NULL]

st: XCURRENT md: NULL tch: 2 txn: 0xac793324

flags 0x2002009: buffer_dirty private gotten_in_current_mode redo_since_read

LRBA: [0xba99.1bd30.0] HSCN: [0x0.132c41a1] HSUB: [1]

buffer tsn: 1 rdba: 0x00802fdc (2/12252)

scn: 0x0000.132c41a1 seq: 0x06 flg: 0x00 tail: 0x41a10206

frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK

********************************************************************************

UNDO BLK:

xid: 0x000d.024.00004a96 seq: 0x13ae cnt: 0xa irb: 0xa icl: 0x0 flg: 0x0000 --> cnt: 0xa = 10

The problem undo segment is usn#=13(d in hex)

--> cnt: 0xa = 10 means this TX has only 10 chained records

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [4147], [12], [10], [], [], [], [],

Current SQL statement for this session:

UPDATE POTENTIAL_VIOS ....

Block header dump: 0x09004664

Object id on Block? Y

seg/obj: 0x17adb csc: 0x00.132c4258 itc: 2 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x9004665 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000d.024.00004a96 0x00802fdc.13ae.0c ---- 1 fsc 0x0000.00000000

0x02 0x000d.020.00004a96 0x00802fdc.13ae.02 C--- 0 scn 0x0000.132c4193

We need to update a record that is part of an uncommitted TX

and the changes are in Uba: 0x00802fdc.13ae.0c.

Uba is the UndoBlockAddress = dba.seq#.rec#

The Uba shows rec#= 0c -> the change is in rec chain 12,

however the undo header shows this TX has only 10 chain records.

Because of this mismatch we raise the ora-600[4147[12][10]

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

TROUBLESHOOTING DETAILS

Check the statement being run, as this undo block may only have been needed for consistent read and may no longer be needed.

Also this error could appear if using IMU, see Bug 5381601/Bug 3383830.

If after bouncing database the error disappear means the cause was IMU, and customer should disable IMU.( alter system "_in_memory_undo" = false)

If it’s indeed an undo segment corruption the solutions are:

1) drop and recreate the object that hits the error, if it’s possible

2) restore the datafile that contains the bad undo segment from last good backup and recover

3) If none of the above is possible you will have to handle the issue as a corrupted rollback segment using _offline_rollback_segments or _corrupted_rollback_segments, see Note 292891.1.

You are here

Oracle ORA-600[4147]

Oracle ORA-600[4147]