本站文章除注明转载外，均为本站原创： 转载自love wife love life —roger 的oracle技术博客 本文链接地址: 手工提交cluster table的事务 前几天培训班中有学生问到,对于cluster table如果去实现手工提交事务,来屏蔽一些错误.他在自己的 环境中遇到了ora-00
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —roger 的oracle技术博客
本文链接地址: 手工提交cluster table的事务
前几天培训班中有学生问到,对于cluster table如果去实现手工提交事务,来屏蔽一些错误.他在自己的
环境中遇到了ora-00600 4000错误,涉及的对象即为cluster table,下面我这里简单模拟了一下。供参考!
++++创建测试表
sql> conn roger/rogerconnected.sql> create cluster t_cluster(id number(2)) ;cluster created.sql> create table t_0610 2 (id number(2) primary key, 3 name varchar2(13)) 4 cluster t_cluster(id);table created.sql> create index t_cluster_idx on cluster t_cluster;index created.sql>sql> insert into t_0610 values(1,'baidu');1 row created.sql> insert into t_0610 values(2,'google');1 row created.sql> insert into t_0610 values(8,'roger');1 row created.sql> commit;commit complete.sql> select * from t_0610;id name---------- ------------- 1 baidu 2 google 8 rogersql> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# 2 from t_0610;file# blk#---------- ---------- 6 172 6 173 6 174sql> oradebug setmypidora-01031: insufficient privilegessql> conn /as sysdbaconnected.sql> oradebug setmypidstatement processed.sql> alter system flush buffer_cache;system altered.sql> alter system dump datafile 6 block 172;system altered.sql> oradebug close_tracestatement processed.sql> oradebug tracefile_name/oracle/diag/rdbms/roger/roger/trace/roger_ora_16777.trc
++++blockdump内容
dump of buffer cache at level 4 for tsn=7 rdba=25165996bh (0x71bf2f28) file#: 6 rdba: 0x018000ac (6/172) class: 1 ba: 0x71ad8000 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15 dbwrid: 0 obj: 77519 objn: 77520 tsn: 7 afn: 6 hint: f hash: [0x8abfa738,0x8abfa738] lru: [0x74ff33a0,0x7abf47d0] lru-flags: on_auxiliary_list ckptq: [null] fileq: [null] objq: [null] objaq: [null] st: free md: null fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33 flags:block dump from disk:buffer tsn: 7 rdba: 0x018000ac (6/172)scn: 0x0000.00b941a6 seq: 0x01 flg: 0x06 tail: 0x41a60601frmt: 0x02 chkval: 0xcb52 type: 0x06=trans datahex dump of block: st=0, typ_found=1dump of memory from 0x00007fadb771ea00 to 0x00007fadb7720a007fadb771ea00 0000a206 018000ac 00b941a6 06010000 [.........a......]7fadb771ea10 0000cb52 00000001 00012ecf 00b94199 [r............a..]7fadb771ea20 00000000 00320002 018000a8 00080006 [......2.........]7fadb771ea30 00000515 00c00562 002e014e 00008000 [....b...n.......]7fadb771ea40 00b94198 001d0007 000004c9 00c03c08 [.a........... 大于2，说明这是一个cluster tablenrow=2frre=-1fsbo=0x1afseo=0x1f78avsp=0x1f5etosp=0x1f5e0xe:pti[0] nrow=1 offs=00x12:pti[1] nrow=1 offs=10x16:pri[0] offs=0x1f820x18:pri[1] offs=0x1f78block_row_dump:tab 0, row 0, @0x1f82tl: 22 fb: k-h-fl-- lb: 0x0 cc: 1curc: 1 comc: 1 pk: 0x018000ac.0 nk: 0x018000ac.0col 0: [ 2] c1 02tab 1, row 0, @0x1f78tl: 10 fb: -ch-fl-- lb: 0x2 cc: 1 cki: 0col 0: [ 5] 62 61 69 64 75end_of_block_dumpend dump data blocks tsn: 7 file#: 6 minblk 172 maxblk 172
大家可以看到，这跟普通的data block的dump内容是有所差别的，因为这里涉及到cluster table。
下面来模拟下手工提交cluster table的事务。
+++++模拟事务不提交
sql> alter system checkpoint;system altered.sql> alter system flush buffer_cache;system altered.sql> show useruser is syssql> conn roger/rogerconnected.sql> delete from t_0610 where id=8; ++++++不提交1 row deleted.sql> alter system flush buffer_cache;system altered.sql> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;xidusn xidslot xidsqn ubablk ubafil ubarec---------- ---------- ---------- ---------- ---------- ---------- 3 17 1212 1066 3 20sql>
这里我们可以看到对于这个未提交的事务xid为：3.17.1212? 前面我们已经知道
测试表中的数据分布在3个block中，这里我模拟的情况是删除第3条数据，且不提交，通过bbed来实现手工
提交这个未提交事务，注意：第3条数据是在第3个block中，即174 block。
+++++首先修改表的itl等信息
bbed> set file 6 block 174 file# 6 block# 174bbed> mapfile: /oracle/oradata/roger/roger01.dbf (6) block: 174 dba:0x018000ae------------------------------------------------------------ ktb data block (table/cluster)struct kcbh, 20 bytes @0struct ktbbh, 72 bytes @20struct kdbh, 14 bytes @100struct kdbt[2], 8 bytes @114sb2 kdbr[2] @122ub1 freespace[8030] @126ub1 rowdata[32] @8156ub4 tailchk @8188bbed> p kdbrsb2 kdbr[0] @122 8066sb2 kdbr[1] @124 8056bbed> p ktbbhstruct ktbbh, 72 bytes @20ub1 ktbbhtyp @20 0x01 (kddbtdata) union ktbbhsid, 4 bytes @24ub4 ktbbhsg1 @24 0x00012ecf ub4 ktbbhod1 @24 0x00012ecf struct ktbbhcsc, 8 bytes @28ub4 kscnbas @28 0x00b94309 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (none) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x018000a8 struct ktbbhitl[0], 24 bytes @44struct ktbitxid, 8 bytes @44ub2 kxidusn @44 0x0009 ub2 kxidslt @46 0x0002 ub4 kxidsqn @48 0x0000044c struct ktbituba, 8 bytes @52ub4 kubadba @52 0x00c000f9 ub2 kubaseq @56 0x014a ub1 kubarec @58 0x1d ub2 ktbitflg @60 0x8000 (ktbfcom) union _ktbitun, 2 bytes @62sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00b941a4 struct ktbbhitl[1], 24 bytes @68struct ktbitxid, 8 bytes @68ub2 kxidusn @68 0x0003 ub2 kxidslt @70 0x0011 ub4 kxidsqn @72 0x000004bc struct ktbituba, 8 bytes @76ub4 kubadba @76 0x00c0042a ub2 kubaseq @80 0x0185 ub1 kubarec @82 0x13 ub2 ktbitflg @84 0x0001 (none) union _ktbitun, 2 bytes @86sb2 _ktbitfsc @86 6 ub2 _ktbitwrp @86 0x0006 ub4 ktbitbas @88 0x00000000bbed> modify /x 0180warning: contents of previous bifile will be lost. proceed? (y/n) y file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 84 to 87 dba:0x018000ae------------------------------------------------------------------------ 01800600 bbed> modify /x 00 offset 86 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 86 to 87 dba:0x018000ae------------------------------------------------------------------------ 0000bbed> sum applycheck value for file 6, block 174:current = 0x055c, required = 0x055cbbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 174block checking: dba = 25165998, block type = ktb-managed data blockdata header at 0x7f88d49c1264kdbchk: row locked by non-existent transaction table=1 slot=0 lockid=2 ktbbhitc=2block 174 failed with check code 6101bbed> p *kdbr[1]rowdata[0]----------ub1 rowdata[0] @8156 0x7cbbed> x /rnnnnccccccnnnnnnnnnnnrowdata[0] @8156----------flag@8156: 0x7c (kdrhfl, kdrhff, kdrhfd, kdrhfh, kdrhfc)lock@8157: 0x02cols@8158: 0bbed> modify /x 6c offset 8156 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 8156 to 8159 dba:0x018000ae------------------------------------------------------------------------ 6c020100bbed> sum applycheck value for file 6, block 174:current = 0x054c, required = 0x054cbbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 174block checking: dba = 25165998, block type = ktb-managed data blockdata header at 0x1112864kdbchk: row locked by non-existent transaction table=1 slot=0 lockid=2 ktbbhitc=2block 174 failed with check code 6101dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 1total blocks failing (data) : 1total blocks processed (index): 0total blocks failing (index): 0total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbedbbed> p kdbhstruct kdbh, 14 bytes @100ub1 kdbhflag @100 0x01 (kdbhffk) sb1 kdbhntab @101 2 sb2 kdbhnrow @102 2 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 26 sb2 kdbhfseo @108 8056 sb2 kdbhavsp @110 8030 sb2 kdbhtosp @112 8040bbed> d /v offset 102 count 4 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 102 to 105 dba:0x018000ae------------------------------------------------------- 0200ffff l ....bbed> modify /x 01 offset 102 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 102 to 105 dba:0x018000ae------------------------------------------------------------------------ 0100ffffbbed> sum applycheck value for file 6, block 174:current = 0x054f, required = 0x054fbbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 174block checking: dba = 25165998, block type = ktb-managed data blockdata header at 0x1112864kdbchk: fsbo(26) wrong, (hsz 24)block 174 failed with check code 6129dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 1total blocks failing (data) : 1total blocks processed (index): 0total blocks failing (index): 0total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbedbbed> d /v offset 106file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 106 to 109 dba:0x018000ae------------------------------------------------------- 1a00781f l ..x.bbed> modify /x 18 offset 106 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 106 to 109 dba:0x018000ae------------------------------------------------------------------------ 1800781fbbed> sum applycheck value for file 6, block 174:current = 0x054d, required = 0x054dbbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 174block checking: dba = 25165998, block type = ktb-managed data blockdata header at 0x1112864kdbchk: row count in table index incorrectblock 174 failed with check code 6125dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 1total blocks failing (data) : 1total blocks processed (index): 0total blocks failing (index): 0total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbedbbed> p kdbtstruct kdbt[0], 4 bytes @114sb2 kdbtoffs @114 0 sb2 kdbtnrow @116 1struct kdbt[1], 4 bytes @118sb2 kdbtoffs @118 1 sb2 kdbtnrow @120 1bbed> p kdbt[1]struct kdbt[1], 4 bytes @118sb2 kdbtoffs @118 1 sb2 kdbtnrow @120 1bbed> d /v offset 118 count 4 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 118 to 121 dba:0x018000ae------------------------------------------------------- 01000100 l ....bbed> modify /x 000000 offset 118 file: /oracle/oradata/roger/roger01.dbf (6) block: 174 offsets: 118 to 121 dba:0x018000ae------------------------------------------------------------------------ 00000000bbed> sum applycheck value for file 6, block 174:current = 0x054d, required = 0x054dbbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 174block checking: dba = 25165998, block type = ktb-managed data blockdata header at 0x1112864kdbchk: table index offset incorrect tab 1block 174 failed with check code 6124dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 1total blocks failing (data) : 1total blocks processed (index): 0total blocks failing (index): 0total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbed
我们可以看到，无论怎么修改，这个block通过bbed进行检测都报错，这里不是因为修改的不对,而是因为
还需要修改cluster 上的index信息。通过treedump 我们可以确认index block为file 6 block 187.
+++++ dump index block
sql> oradebug setmypidstatement processed.sql> alter system dump datafile 6 block 187;system altered.sql> oradebug close_tracestatement processed.sql> oradebug tracefile_name/oracle/diag/rdbms/roger/roger/trace/roger_ora_17394.trcsql>
+++++ dump 内容如下
block header dump: 0x018000bb object id on block? y seg/obj: 0x12ed2 csc: 0x00.b941a3 itc: 2 flg: e typ: 2 - index brn: 0 bdba: 0x18000b8 ver: 0x01 opc: 0 inc: 0 exflg: 0itl xid uba flag lck scn/fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0x0009.002.0000044c 0x00c000f9.014a.1e --u- 1 fsc 0x0000.00b941a4leaf block dump===============header address 140553058142820=0x7fd50f1a4a64kdxcolev 0kdxcolev flags = - - -kdxcolok 0kdxcoopc 0x80: opcode=0: iot flags=--- is converted=ykdxconco 1kdxcosdc 0kdxconro 3kdxcofbo 42=0x2akdxcofeo 7993=0x1f39kdxcoavs 7951kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 8kdxlebksz 8032row#0[8019] flag: ------, lock: 0, len=13, data:(8): 01 80 00 ac 00 00 01 00col 0; len 2; (2): c1 02row#1[8006] flag: ------, lock: 0, len=13, data:(8): 01 80 00 ad 00 00 01 00col 0; len 2; (2): c1 03row#2[7993] flag: ------, lock: 2, len=13, data:(8): 01 80 00 ae 00 00 01 00col 0; len 2; (2): c1 09----- end of leaf block dump -----
可以看到，该index block中还存在3个index entry信息，我们需要将第3条信息给删掉，首先计算一下offset：
sql> select 7993+76+24 from dual;7993+76+24---------- 8093
+++++利用bbed修改 index block
bbed> set file 6 block 187 file# 6 block# 187bbed> map file: /oracle/oradata/roger/roger01.dbf (6) block: 187 dba:0x018000bb------------------------------------------------------------ ktb data block (index leaf)struct kcbh, 20 bytes @0struct ktbbh, 72 bytes @20struct kdxle, 32 bytes @100sb2 kd_off[3] @132ub1 freespace[7951] @138ub1 rowdata[39] @8089ub4 tailchk @8188bbed> set offset 8093 offset 8093bbed> x /rnrowdata[4] @8093----------flag@8093: 0x00 (none)lock@8094: 0x02keydata[8]: 0x01 0x80 0x00 0xae 0x00 0x00 0x01 0x00data key:col 0[2] @8104: 8bbed> map file: /oracle/oradata/roger/roger01.dbf (6) block: 187 dba:0x018000bb------------------------------------------------------------ ktb data block (index leaf)struct kcbh, 20 bytes @0struct ktbbh, 72 bytes @20struct kdxle, 32 bytes @100sb2 kd_off[3] @132ub1 freespace[7951] @138ub1 rowdata[39] @8089ub4 tailchk @8188bbed> p kdxlestruct kdxle, 32 bytes @100struct kdxlexco, 16 bytes @100ub1 kdxcolev @100 0x00 ub1 kdxcolok @101 0x00 ub1 kdxcoopc @102 0x80 ub1 kdxconco @103 0x01 ub4 kdxcosdc @104 0x00000000 sb2 kdxconro @108 3 sb2 kdxcofbo @110 42 sb2 kdxcofeo @112 7993 sb2 kdxcoavs @114 7951 sb2 kdxlespl @116 0 sb2 kdxlende @118 0 ub4 kdxlenxt @120 0x00000000 ub4 kdxleprv @124 0x00000000 ub1 kdxledsz @128 0x08 ub1 kdxleflg @129 0x00 (none)bbed> d /v offset 118 count 2 file: /oracle/oradata/roger/roger01.dbf (6) block: 187 offsets: 118 to 119 dba:0x018000bb------------------------------------------------------- 0000 l ..bbed> modify /x 01 offset 118 file: /oracle/oradata/roger/roger01.dbf (6) block: 187 offsets: 118 to 119 dba:0x018000bb------------------------------------------------------------------------ 0100bbed> sum applycheck value for file 6, block 187:current = 0x4faa, required = 0x4faabbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 187block checking: dba = 25166011, block type = ktb-managed data block**** actual rows marked deleted = 0 != kdxlende = 1---- end index block validationblock 187 failed with check code 6401dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 0total blocks failing (data) : 0total blocks processed (index): 1total blocks failing (index): 1total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbedbbed> set offset 8093 offset 8093bbed> x /rnrowdata[4] @8093----------flag@8093: 0x00 (none)lock@8094: 0x02keydata[8]: 0x01 0x80 0x00 0xae 0x00 0x00 0x01 0x00data key:col 0[2] @8104: 8bbed> modify /x 01 offset 8093 file: /oracle/oradata/roger/roger01.dbf (6) block: 187 offsets: 8093 to 8094 dba:0x018000bb------------------------------------------------------------------------ 0102bbed> sum applycheck value for file 6, block 187:current = 0x4eaa, required = 0x4eaabbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 187block checking: dba = 25166011, block type = ktb-managed data block**** actual free space credit for itl 2 = 15 != # in trans. hdr = 0---- end index block validationblock 187 failed with check code 6401dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 0total blocks failing (data) : 0total blocks processed (index): 1total blocks failing (index): 1total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbedbbed> map file: /oracle/oradata/roger/roger01.dbf (6) block: 187 dba:0x018000bb------------------------------------------------------------ ktb data block (index leaf)struct kcbh, 20 bytes @0struct ktbbh, 72 bytes @20struct kdxle, 32 bytes @100sb2 kd_off[3] @132ub1 freespace[7951] @138ub1 rowdata[39] @8089ub4 tailchk @8188bbed> p ktbbhstruct ktbbh, 72 bytes @20ub1 ktbbhtyp @20 0x02 (kddbtindex) union ktbbhsid, 4 bytes @24ub4 ktbbhsg1 @24 0x00012ed2 ub4 ktbbhod1 @24 0x00012ed2 struct ktbbhcsc, 8 bytes @28ub4 kscnbas @28 0x00b941a3 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (none) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x018000b8 struct ktbbhitl[0], 24 bytes @44struct ktbitxid, 8 bytes @44ub2 kxidusn @44 0x0000 ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x00000000 struct ktbituba, 8 bytes @52ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x0000 (none) union _ktbitun, 2 bytes @62sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68struct ktbitxid, 8 bytes @68ub2 kxidusn @68 0x0009 ub2 kxidslt @70 0x0002 ub4 kxidsqn @72 0x0000044c struct ktbituba, 8 bytes @76ub4 kubadba @76 0x00c000f9 ub2 kubaseq @80 0x014a ub1 kubarec @82 0x1e ub2 ktbitflg @84 0x2001 (ktbfupb) union _ktbitun, 2 bytes @86sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00b941a4bbed> d /v offset 86 count 2 file: /oracle/oradata/roger/roger01.dbf (6) block: 187 offsets: 86 to 87 dba:0x018000bb------------------------------------------------------- 0000 l ..bbed> modify /x 0f offset 86 file: /oracle/oradata/roger/roger01.dbf (6) block: 187 offsets: 86 to 87 dba:0x018000bb------------------------------------------------------------------------ 0f00bbed> sum applycheck value for file 6, block 187:current = 0x4ea5, required = 0x4ea5bbed> verifydbverify - verification startingfile = /oracle/oradata/roger/roger01.dbfblock = 187dbverify - verification completetotal blocks examined : 1total blocks processed (data) : 0total blocks failing (data) : 0total blocks processed (index): 1total blocks failing (index): 0total blocks empty : 0total blocks marked corrupt : 0total blocks influx : 0message 531 not found; product=rdbms; facility=bbed
到这里，我们完成了index block的修改，最后我们再来校验一下前面的data block，你会发现已经ok了，如下：
bbed> set file 2 block 174
file# 2
block#? 174
bbed> verify
dbverify – verification starting
file = /oracle/oradata/roger/sysaux01.dbf
block = 174
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? : 1
total blocks marked corrupt : 0
total blocks influx : 0
message 531 not found;? product=rdbms; facility=bbed
bbed>
+++++ 最后来验证下数据
sql> alter system flush buffer_cache;system altered.sql> select * from roger.t_0610;id name---------- ------------- 1 baidu 2 google
至此，整个模拟测试结束，供参考！
related posts:
创建index之前如何确定其大小ora-00600 [kddummy_blkchk] solution如何修复未格式化的坏块？archivelog 模式下，datafile header损坏，如何恢复？datafile 也能跨resetlogs ？本站文章除注明转载外，均为本站原创： 转载自love wife & love life —roger 的oracle技术博客 本文链接地址: 手工提交cluster table的事务 前几天培训班中有学生问到,对于cluster table如果去实现手工提交事务,来屏蔽一些错误.他在自己的 环境中遇到了ora-00600 4000错误,涉及的对象即为cluster table,下面我这里简单模拟了一下。供参考! ++++创建测试表 sql> conn roger/roger connected. sql> create cluster t_cluster(id number(2)) ; cluster created. sql> create table t_0610 2 (id number(2) primary key, 3 name varchar2(13)) 4 cluster t_cluster(id); table created. sql> create index t_cluster_idx on cluster t_cluster; index created. [...]