oracle中处理一次ora600[4194]的例子
情景
数据库在启动时抛出如下错误
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [7], [7], [], [], [], [], []
从数据库启动报错可以看出在递归调用SQL出现错误,最终错误为ORA-600[4194]。
这里尝试屏蔽所有的undo segment 进行启动,得到结果还是报错.通过手工创建pfile增加参数,开启trace查看问题出现点
数据库启动mount状态下,recover database;
oradebug setmypid
oradebug unlimit
--dump回滚段回滚前后信息
ALTER SESSION SET events '10015 trace name context forever, level 10';
--错误errorstack
ALTER SESSION SET events '604 trace trace name errorstack level 10';
--sql执行顺序
ALTER SESSION SET events '10046 trace name context forever, level 12';
oradebug tracefile_name
分析
从10046的trace看到
----------------------------------------------
WAIT #2: nam='control file sequential read' ela= 11 file#=0 block#=1 blocks=1 obj#=-1 tim=3207213661467
WAIT #2: nam='control file sequential read' ela= 11 file#=1 block#=1 blocks=1 obj#=-1 tim=3207213661491
WAIT #2: nam='control file sequential read' ela= 10 file#=2 block#=1 blocks=1 obj#=-1 tim=3207213661512
WAIT #2: nam='control file sequential read' ela= 8 file#=0 block#=15 blocks=1 obj#=-1 tim=3207213661533
WAIT #2: nam='control file sequential read' ela= 8 file#=0 block#=17 blocks=1 obj#=-1 tim=3207213661553
*** 2015-08-25 18:18:56.341
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [7], [7], [], [], [], [], []
Current SQL statement for this session:
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
看到的SQL为Updata undo$表而出现问题.
Call stack
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 000000000 ? FFFFFFFFFFD868E ?
ksedmp+0290 bl ksedst 104A2C670 ?
ksfdmp+0018 bl 03F263A4
kgeriv+0108 bl _ptrgl
因为前面报错号中存在ORA-607,尝试文件中查找Error 607
Dump event group for SESSION
10046 trace name CONTEXT level 12, forever
10015 trace name CONTEXT level 10, forever
Dump event group for SYSTEM
Error 607 in redo application callback -->607的错误信息
–下面查看redo的信息
TYP:0 CLS:16 AFN:1 DBA:0x00400191 -->undo块地址 OBJ:4294967295 SCN:0x0001.2483756a SEQ: 1 OP:5.1 -->Undo block or undo segment header - KTURDB
ktudb redo: siz: 256 spc: 6602 flg: 0x0012 seq: 0x0040 rec: 0x07
xid: 0x0000.03e.0000003d
ktubl redo: slt: 62 rci: 0 opc: 11.1 objn: 15 objd: 15 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00400191.0040.06 -->uba地址
prev ctl max cmt scn: 0x0001.24833ccb prev tx cmt scn: 0x0001.24833d19
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 4194701 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0000.028.0000003d uba: 0x00400191.0040.06
flg: C--- lkc: 0 scn: 0x0001.248372a6
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040006a hdba: 0x00400069
-->更新块地址 -->更新块所在的区域头地址(可能是一级位图去)
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 9(0x9) flag: 0x2c lock: 0 ckix: 0
ncol: 17 nnew: 12 size: 0
col 1: [ 9] 5f 53 59 53 53 4d 55 39 24 -->_SYSSMU9$
col 2: [ 2] c1 02 -->2
col 3: [ 2] c1 03 -->
col 4: [ 3] c2 02 26 -->
col 5: [ 6] c5 07 0d 25 37 21
col 6: [ 2] c1 02
col 7: [ 4] c3 0d 11 58
col 8: [ 4] c3 05 63 43
col 9: [ 1] 80
col 10: [ 2] c1 06
col 11: [ 2] c1 02
col 16: [ 2] c1 02
Block after image is corrupt:
buffer tsn: 0 rdba: 0x00400191 (1/401)
scn: 0x0001.2483756a seq: 0x01 flg: 0x04 tail: 0x756a0201
frmt: 0x02 chkval: 0xc677 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
10046的绑定变量
PARSING IN CURSOR #2 len=148 dep=1 uid=0 oct=6 lid=0 tim=3207213633716 hv=3540833987 ad='6e989e58'
UPDATE undo$
SET name=:2,
file#=:3,
block#=:4,
STATUS$=:5,
USER#=:6,
undosqn=:7,
xactsqn=:8,
scnbas=:9,
scnwrp=:10,
inst#=:11,
ts#=:12,
spare1=:13
WHERE us#=:1
END OF STMT
PARSE #2:c=10000,e=18293,p=5,cr=44,cu=0,mis=1,r=0,dep=1,og=4,tim=3207213633715
BINDS #2:
kkscoacd
Bind#0
VALUE="_SYSSMU9$"
Bind#1
VALUE=2
Bind#2
VALUE=137
Bind#3
VALUE=6
Bind#4
VALUE=1
Bind#5
VALUE=49866
Bind#6
VALUE=121687
Bind#7
VALUE=612365432
Bind#8
VALUE=1
Bind#9
VALUE=0
Bind#10
VALUE=1
Bind#11
VALUE=1
Bind#12
VALUE=9
验证
col name for a40
select f_get_from_dump(replace('5f 53 59 53 53 4d 55 39 24',' ',','),'varchar2') name from dual;
从绑定变量信息来看和dump的信息一样.
上面的一些值已表示出大部分含义.下面我们关注几个点.
xid: 0x0000.028.0000003d uba: 0x00400191.0040.06
xid=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap
uba=Address.Of.Last.Undo.Block.Used+Sequence+Last.Entry.in.UNDO.Record.Map
从XID中可以看出此undo segment 的段号,为0.也就是system Undo段。
UBA为0x00400191.
现在知道了undoblock的地址,这样就可以去dump下块的信息
rdba 0x00400191
rdba: 0x400191(4194705) file: 1 ,block : 401
dump undo段头和undo block
alter system dump datafile 'xxxx/system01.dbf' block 9;
alter system dump datafile 'xxxx/system01.dbf' block 401;
原因
undo段头的信息
TRN CTL:: seq: 0x0040 chd: 0x003e ctl: 0x0028 inc: 0x00000000 nfb: 0x0001 -->一个空闲列表
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00400191.0040.06 scn: 0x0001.24833ccb
-->下次使用这个块和上面下信息一直
Version: 0x01
FREE BLOCK POOL:: -->freelist
uba: 0x00400191.0040.06 ext: 0x4 spc: 0x19ca --> 6602
uba: 0x00000000.0035.37 ext: 0x5 spc: 0x80c
uba: 0x00000000.0034.37 ext: 0x4 spc: 0x550
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
从uba里发现的信息和上面看的一直。然后查看undo block
dump undo 块
********************************************************************************
UNDO BLK:
xid: 0x0000.03e.0000003d seq: 0x40 cnt: 0x7 irb: 0x7 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1ee8 0x02 0x1de8 0x03 0x1ce8 0x04 0x1be8 0x05 0x1ae8
0x06 0x19e8 0x07 0x18e8 -->这里显示Last.Entry为0x07
我们都知道uba的最后一位代表Last.Entry.in.UNDO.Record.Map。这里出现了问题.段头里记录为6,Block里记录为7.这样我们就找到原因 undo segment 头 记录的信息和undo block 里不一致.
Fixed
下面为两种修复方式
1.改为undo segment block和undo block 信息一致
2.让undo 在使用时不使用此块/或者从头开始
undo_block.png
–方法一：
参考我以前的操作案例
http://www.traveldba.com/archives/650
系统undo block 计算方式
此类块的使用空间为block_size – block_header_size(kcbh) – tailchk (4)
大家都值oracle的块的使用方式为从下往上。如果告诉你我最后一条记录从offset为6000,这就是6000到blocksize -4 的空间全部被用了.所以这里计算使用空间为
使用的大小为: blocsize – tailchk – 最后一次记录的开始位置offset – block_header
转化成数据:
8192-4 - 0x18e8(6376) - 20 = 1792 --使用大小
计算剩余空间为:
block_size - block_header_size(kcbh) - tailchk (4)-14 (undo 一些基本信息) - 3*2 -使用的大小= free space
转化成数据:
8192 - 20 - 4 - 14 - 7*2 - 1792 = 6348
得到这些值,我们就可以修改
Version: 0x01
FREE BLOCK POOL:: -->freelist
uba: 0x00400191.0040.06 ext: 0x4 spc: 0x19ca --> 6602
把06改成07,0x19ca(6602)改成6348
uba: 0x00000000.0035.37 ext: 0x5 spc: 0x80c
uba: 0x00000000.0034.37 ext: 0x4 spc: 0x550
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
–方法二
$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production ON Wed Aug 26 09:03:26 2015
Copyright (c) 1982, 2007, Oracle. ALL rights reserved.
************* !!! FOR Oracle Internal USE ONLY !!! ***************
BBED> SET filename '/oradata/orcl/system01.dbf'
FILENAME /u02/PIC/PLHIS/system01.dbf
BBED> SHOW ALL
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME /u02/PIC/PLHIS/system01.dbf
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE DEC
OBASE DEC
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> SET mode edit
MODE Edit
BBED> SHOW ALL
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME /u02/PIC/PLHIS/system01.dbf
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE DEC
OBASE DEC
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
查看block信息
BBED> set block 9
BLOCK# 9
BBED> map /v
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Dba:0x00000000
------------------------------------------------------------
Unlimited Undo Segment Header
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktech, 72 bytes @20
ub4 spare1_ktech @20
word tsn_ktech @24
ub4 lastmap_ktech @28
ub4 mapcount_ktech @32
ub4 extents_ktech @36
ub4 blocks_ktech @40
ub2 mapend_ktech @44
struct hwmark_ktech, 32 bytes @48
struct locker_ktech, 8 bytes @80
ub4 flag_ktech @88
struct ktemh, 16 bytes @92
ub4 count_ktemh @92
ub4 next_ktemh @96
ub4 obj_ktemh @100
ub4 flag_ktemh @104
struct ktetb[6], 48 bytes @108
ub4 ktetbdba @108
ub4 ktetbnbk @112
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
struct ktuxcuba, 8 bytes @4156
sb2 ktuxcflg @4164
ub2 ktuxcseq @4166
sb2 ktuxcnfb @4168
ub4 ktuxcinc @4172
sb2 ktuxcchd @4176
sb2 ktuxcctl @4178
ub2 ktuxcmgc @4180
ub4 ktuxcopt @4188
struct ktuxcfbp[5], 60 bytes @4192
struct ktuxe[255], 10200 bytes @4252
ub4 ktuxexid @4252
ub4 ktuxebrb @4256
struct ktuxescn, 8 bytes @4260
sb4 ktuxesta @4268
ub1 ktuxecfl @4269
sb2 ktuxeuel @4270
ub4 tailchk @8188
设置空闲列表为空
TRN CTL:: seq: 0x0040 chd: 0x003e ctl: 0x0028 inc: 0x00000000 nfb: 0x0001 -->一个空闲列表
BBED> set offset 4168
OFFSET 4168
BBED> p
ktuxc.ktuxcnfb
--------------
sb2 ktuxcnfb @4168 1
BBED> dump count 1
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 4168 to 4168 Dba:0x00000000
------------------------------------------------------------------------
00
<32 bytes per line>
BBED> dump count 20
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 4168 to 4187 Dba:0x00000000
------------------------------------------------------------------------
00010000 00000000 003e0028 80020001 00680000
<32 bytes per line>
BBED> m 0x0000
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 4168 to 4187 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 003e0028 80020001 00680000
<32 bytes per line>
BBED> p
ktuxc.ktuxcnfb
--------------
sb2 ktuxcnfb @4168 0
清空undo block 信息
FREE BLOCK POOL:: -->freelist
uba: 0x00400191.0040.06 ext: 0x4 spc: 0x19ca --> 6602
BBED> p ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x24833ccb
ub2 kscnwrp @4152 0x0001
struct ktuxcuba, 8 bytes @4156
ub4 kubadba @4156 0x00400191
ub2 kubaseq @4160 0x0040
ub1 kubarec @4162 0x06
sb2 ktuxcflg @4164 1 (KTUXCFSK)
ub2 ktuxcseq @4166 0x0040
sb2 ktuxcnfb @4168 0
ub4 ktuxcinc @4172 0x00000000
sb2 ktuxcchd @4176 62
sb2 ktuxcctl @4178 40
ub2 ktuxcmgc @4180 0x8002
ub4 ktuxcopt @4188 0x7ffffffe
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00400191
ub2 kubaseq @4196 0x0040
ub1 kubarec @4198 0x06
sb2 ktufbext @4200 4
sb2 ktufbspc @4202 6602
struct ktuxcfbp[1], 12 bytes @4204
struct ktufbuba, 8 bytes @4204
ub4 kubadba @4204 0x00000000
ub2 kubaseq @4208 0x0035
ub1 kubarec @4210 0x37
sb2 ktufbext @4212 5
sb2 ktufbspc @4214 2060
struct ktuxcfbp[2], 12 bytes @4216
struct ktufbuba, 8 bytes @4216
ub4 kubadba @4216 0x00000000
ub2 kubaseq @4220 0x0034
ub1 kubarec @4222 0x37
sb2 ktufbext @4224 4
sb2 ktufbspc @4226 1360
struct ktuxcfbp[3], 12 bytes @4228
struct ktufbuba, 8 bytes @4228
ub4 kubadba @4228 0x00000000
ub2 kubaseq @4232 0x0000
ub1 kubarec @4234 0x00
sb2 ktufbext @4236 0
sb2 ktufbspc @4238 0
struct ktuxcfbp[4], 12 bytes @4240
struct ktufbuba, 8 bytes @4240
ub4 kubadba @4240 0x00000000
ub2 kubaseq @4244 0x0000
ub1 kubarec @4246 0x00
sb2 ktufbext @4248 0
sb2 ktufbspc @4250 0
BBED> p ktuxc.ktuxcfbp[0]
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00400191
ub2 kubaseq @4196 0x0040
ub1 kubarec @4198 0x06
sb2 ktufbext @4200 4
sb2 ktufbspc @4202 6602
BBED> set offset 4192
OFFSET 4192
BBED> dump
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 4192 to 4211 Dba:0x00000000
------------------------------------------------------------------------
00400191 00400600 000419ca 00000000 00353700
<32 bytes per line>
BBED> m /x 00000000
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 4192 to 4211 Dba:0x00000000
------------------------------------------------------------------------
00000000 00400600 000419ca 00000000 00353700
<32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x0e
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00400009
ub4 bas_kcbh @8 0x248372a6
ub2 wrp_kcbh @12 0x0001
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xbe32
ub2 spare3_kcbh @18 0x0000
清空flg_kcbh
BBED> set offset 15
OFFSET 15
BBED> m /x 00
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 15 to 34 Dba:0x00000000
------------------------------------------------------------------------
00be3200 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 16
OFFSET 16
清空chkval
BBED> m /x 0000
File: /u02/PIC/PLHIS/system01.dbf (0)
Block: 9 Offsets: 16 to 35 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x0e
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x00400009
ub4 bas_kcbh @8 0x248372a6
ub2 wrp_kcbh @12 0x0001
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x00 (NONE)
ub2 chkval_kcbh @16 0x0000
ub2 spare3_kcbh @18 0x0000
BBED> p ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x24833ccb
ub2 kscnwrp @4152 0x0001
struct ktuxcuba, 8 bytes @4156
ub4 kubadba @4156 0x00400191
ub2 kubaseq @4160 0x0040
ub1 kubarec @4162 0x06
sb2 ktuxcflg @4164 1 (KTUXCFSK)
ub2 ktuxcseq @4166 0x0040
sb2 ktuxcnfb @4168 0
ub4 ktuxcinc @4172 0x00000000
sb2 ktuxcchd @4176 62
sb2 ktuxcctl @4178 40
ub2 ktuxcmgc @4180 0x8002
ub4 ktuxcopt @4188 0x7ffffffe
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00000000
ub2 kubaseq @4196 0x0040
ub1 kubarec @4198 0x06
sb2 ktufbext @4200 4
sb2 ktufbspc @4202 6602
struct ktuxcfbp[1], 12 bytes @4204
struct ktufbuba, 8 bytes @4204
ub4 kubadba @4204 0x00000000
ub2 kubaseq @4208 0x0035
ub1 kubarec @4210 0x37
sb2 ktufbext @4212 5
sb2 ktufbspc @4214 2060
struct ktuxcfbp[2], 12 bytes @4216
struct ktufbuba, 8 bytes @4216
ub4 kubadba @4216 0x00000000
ub2 kubaseq @4220 0x0034
ub1 kubarec @4222 0x37
sb2 ktufbext @4224 4
sb2 ktufbspc @4226 1360
struct ktuxcfbp[3], 12 bytes @4228
struct ktufbuba, 8 bytes @4228
ub4 kubadba @4228 0x00000000
ub2 kubaseq @4232 0x0000
ub1 kubarec @4234 0x00
sb2 ktufbext @4236 0
sb2 ktufbspc @4238 0
struct ktuxcfbp[4], 12 bytes @4240
struct ktufbuba, 8 bytes @4240
ub4 kubadba @4240 0x00000000
ub2 kubaseq @4244 0x0000
ub1 kubarec @4246 0x00
sb2 ktufbext @4248 0
sb2 ktufbspc @4250 0
BBED>
BBED> sum apply
Check value for File 0, Block 9:
current = 0x0000, required = 0x0000
BBED> v
DBVERIFY - Verification starting
FILE = /u02/PIC/PLHIS/system01.dbf
BLOCK = 9
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED>
然后open reset logs 成功启动数据库。
