当前位置:Gxlcms > mysql > 手工提交ClusterTable的事务

手工提交ClusterTable的事务

时间:2021-07-01 10:21:17 帮助过:26人阅读

本站文章除注明转载外,均为本站原创: 转载自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/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.
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 roger
SQL> 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        174
SQL> oradebug setmypid
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 6 block 172;
System altered.
SQL> oradebug close_trace
Statement 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=25165996
BH (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: 0x41a60601
frmt: 0x02 chkval: 0xcb52 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FADB771EA00 to 0x00007FADB7720A00
7FADB771EA00 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...........<..]
7FADB771EA50 0032019D 00002001 00B941A6 00000000  [..2.. ...A......]
7FADB771EA60 00000000 00020201 001AFFFF 1F5E1F78  [............x.^.]
7FADB771EA70 00001F5E 00010001 1F820001 00001F78  [^...........x...]
7FADB771EA80 00000000 00000000 00000000 00000000  [................]
 Repeat 500 times
7FADB77209D0 00000000 00000000 00000000 0001026C  [............l...]
7FADB77209E0 69616205 00AC7564 01000101 00800100  [.baidu..........]
7FADB77209F0 010000AC 00AC0080 02C10200 41A60601  [...............A]
Block header dump:  0x018000ac
 Object id on Block? Y
 seg/obj: 0x12ecf  csc: 0x00.b94199  itc: 2  flg: E  typ: 1 - DATA
 brn: 0  bdba: 0x18000a8 ver: 0x01 opc: 0
 inc: 0  exflg: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.008.00000515  0x00c00562.014e.2e  C---    0  scn 0x0000.00b94198
0x02   0x0007.01d.000004c9  0x00c03c08.019d.32  --U-    1  fsc 0x0000.00b941a6
bdba: 0x018000ac
data_block_dump,data header at 0x7fadb771ea64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7fadb771ea64
 76543210
flag=-------K
ntab=2                ---- > 大于2,说明这是一个cluster table
nrow=2
frre=-1
fsbo=0x1a
fseo=0x1f78
avsp=0x1f5e
tosp=0x1f5e
0xe:pti[0]      nrow=1  offs=0
0x12:pti[1]     nrow=1  offs=1
0x16:pri[0]     offs=0x1f82
0x18:pri[1]     offs=0x1f78
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 1 comc: 1 pk: 0x018000ac.0 nk: 0x018000ac.0
col  0: [ 2]  c1 02
tab 1, row 0, @0x1f78
tl: 10 fb: -CH-FL-- lb: 0x2  cc: 1 cki: 0
col  0: [ 5]  62 61 69 64 75
end_of_block_dump
End 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 user
USER is "SYS"
SQL> conn roger/roger
Connected.
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         20
SQL>

这里我们可以看到对于这个未提交的事务XID为:3.17.1212? 前面我们已经知道
测试表中的数据分布在3个block中,这里我模拟的情况是删除第3条数据,且不提交,通过bbed来实现手工
提交这个未提交事务,注意:第3条数据是在第3个block中,即174 block。

+++++首先修改表的itl等信息

BBED> set file 6 block 174
 FILE#           6
 BLOCK#          174
BBED> map
File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 174                                   Dba:0x018000ae
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes                      @0
struct ktbbh, 72 bytes                     @20
struct kdbh, 14 bytes                      @100
struct kdbt[2], 8 bytes                    @114
sb2 kdbr[2]                                @122
ub1 freespace[8030]                        @126
ub1 rowdata[32]                            @8156
ub4 tailchk                                @8188
BBED> p kdbr
sb2 kdbr[0]                                 @122      8066
sb2 kdbr[1]                                 @124      8056
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
 union ktbbhsid, 4 bytes                  @24
ub4 ktbbhsg1                          @24       0x00012ecf
 ub4 ktbbhod1                          @24       0x00012ecf
 struct ktbbhcsc, 8 bytes                 @28
ub4 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             @44
struct ktbitxid, 8 bytes              @44
ub2 kxidusn                        @44       0x0009
 ub2 kxidslt                        @46       0x0002
 ub4 kxidsqn                        @48       0x0000044c
 struct ktbituba, 8 bytes              @52
ub4 kubadba                        @52       0x00c000f9
 ub2 kubaseq                        @56       0x014a
 ub1 kubarec                        @58       0x1d
 ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
 union _ktbitun, 2 bytes               @62
sb2 _ktbitfsc                      @62       0
 ub2 _ktbitwrp                      @62       0x0000
 ub4 ktbitbas                          @64       0x00b941a4
 struct ktbbhitl[1], 24 bytes             @68
struct ktbitxid, 8 bytes              @68
ub2 kxidusn                        @68       0x0003
 ub2 kxidslt                        @70       0x0011
 ub4 kxidsqn                        @72       0x000004bc
 struct ktbituba, 8 bytes              @76
ub4 kubadba                        @76       0x00c0042a
 ub2 kubaseq                        @80       0x0185
 ub1 kubarec                        @82       0x13
 ub2 ktbitflg                          @84       0x0001 (NONE)
 union _ktbitun, 2 bytes               @86
sb2 _ktbitfsc                      @86       6
 ub2 _ktbitwrp                      @86       0x0006
 ub4 ktbitbas                          @88       0x00000000
BBED> modify /x 0180
Warning: 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
------------------------------------------------------------------------
 0000
BBED> sum apply
Check value for File 6, Block 174:
current = 0x055c, required = 0x055c
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 174
Block Checking: DBA = 25165998, Block Type = KTB-managed data block
data header at 0x7f88d49c1264
kdbchk: row locked by non-existent transaction
 table=1   slot=0
 lockid=2   ktbbhitc=2
Block 174 failed with check code 6101
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8156     0x7c
BBED> x /rnnnnccccccnnnnnnnnnnn
rowdata[0]                                  @8156
----------
flag@8156: 0x7c (KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC)
lock@8157: 0x02
cols@8158:    0
BBED> modify /x 6c offset 8156
 File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 174              Offsets: 8156 to 8159           Dba:0x018000ae
------------------------------------------------------------------------
 6c020100
BBED> sum apply
Check value for File 6, Block 174:
current = 0x054c, required = 0x054c
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 174
Block Checking: DBA = 25165998, Block Type = KTB-managed data block
data header at 0x1112864
kdbchk: row locked by non-existent transaction
 table=1   slot=0
 lockid=2   ktbbhitc=2
Block 174 failed with check code 6101
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes                       @100
ub1 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      8040
BBED> 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
------------------------------------------------------------------------
 0100ffff
BBED> sum apply
Check value for File 6, Block 174:
current = 0x054f, required = 0x054f
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 174
Block Checking: DBA = 25165998, Block Type = KTB-managed data block
data header at 0x1112864
kdbchk: fsbo(26) wrong, (hsz 24)
Block 174 failed with check code 6129
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> d /v offset 106
File: /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
------------------------------------------------------------------------
 1800781f
BBED> sum apply
Check value for File 6, Block 174:
current = 0x054d, required = 0x054d
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 174
Block Checking: DBA = 25165998, Block Type = KTB-managed data block
data header at 0x1112864
kdbchk: row count in table index incorrect
Block 174 failed with check code 6125
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p kdbt
struct kdbt[0], 4 bytes                     @114
sb2 kdbtoffs                             @114      0
 sb2 kdbtnrow                             @116      1
struct kdbt[1], 4 bytes                     @118
sb2 kdbtoffs                             @118      1
 sb2 kdbtnrow                             @120      1
BBED> p kdbt[1]
struct kdbt[1], 4 bytes                     @118
sb2 kdbtoffs                             @118      1
 sb2 kdbtnrow                             @120      1
BBED> 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
------------------------------------------------------------------------
 00000000
BBED> sum apply
Check value for File 6, Block 174:
current = 0x054d, required = 0x054d
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 174
Block Checking: DBA = 25165998, Block Type = KTB-managed data block
data header at 0x1112864
kdbchk: table index offset incorrect
 tab 1
Block 174 failed with check code 6124
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

我们可以看到,无论怎么修改,这个block通过bbed进行检测都报错,这里不是因为修改的不对,而是因为
还需要修改cluster 上的Index信息。通过treedump 我们可以确认index block为file 6 block 187.

+++++ dump Index block

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 6 block 187;
System altered.
SQL> oradebug close_trace
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/roger/roger/trace/roger_ora_17394.trc
SQL>

+++++ 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: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0009.002.0000044c  0x00c000f9.014a.1e  --U-    1  fsc 0x0000.00b941a4
Leaf block dump
===============
header address 140553058142820=0x7fd50f1a4a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7993=0x1f39
kdxcoavs 7951
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8032
row#0[8019] flag: ------, lock: 0, len=13, data:(8):  01 80 00 ac 00 00 01 00
col 0; len 2; (2):  c1 02
row#1[8006] flag: ------, lock: 0, len=13, data:(8):  01 80 00 ad 00 00 01 00
col 0; len 2; (2):  c1 03
row#2[7993] flag: ------, lock: 2, len=13, data:(8):  01 80 00 ae 00 00 01 00
col 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#          187
BBED> map
 File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 187                                   Dba:0x018000bb
------------------------------------------------------------
 KTB Data Block (Index Leaf)
struct kcbh, 20 bytes                      @0
struct ktbbh, 72 bytes                     @20
struct kdxle, 32 bytes                     @100
sb2 kd_off[3]                              @132
ub1 freespace[7951]                        @138
ub1 rowdata[39]                            @8089
ub4 tailchk                                @8188
BBED> set offset 8093
 OFFSET          8093
BBED> x /rn
rowdata[4]                                  @8093
----------
flag@8093:     0x00 (NONE)
lock@8094:     0x02
keydata[8]:    0x01  0x80  0x00  0xae  0x00  0x00  0x01  0x00
data key:
col    0[2] @8104: 8
BBED> map
 File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 187                                   Dba:0x018000bb
------------------------------------------------------------
 KTB Data Block (Index Leaf)
struct kcbh, 20 bytes                      @0
struct ktbbh, 72 bytes                     @20
struct kdxle, 32 bytes                     @100
sb2 kd_off[3]                              @132
ub1 freespace[7951]                        @138
ub1 rowdata[39]                            @8089
ub4 tailchk                                @8188
BBED> p kdxle
struct kdxle, 32 bytes                      @100
struct kdxlexco, 16 bytes                @100
ub1 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
------------------------------------------------------------------------
 0100
BBED> sum apply
Check value for File 6, Block 187:
current = 0x4faa, required = 0x4faa
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 187
Block Checking: DBA = 25166011, Block Type = KTB-managed data block
**** actual rows marked deleted = 0 != kdxlende = 1
---- end index block validation
Block 187 failed with check code 6401
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 1
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> set offset 8093
 OFFSET          8093
BBED> x /rn
rowdata[4]                                  @8093
----------
flag@8093:     0x00 (NONE)
lock@8094:     0x02
keydata[8]:    0x01  0x80  0x00  0xae  0x00  0x00  0x01  0x00
data key:
col    0[2] @8104: 8
BBED> modify /x 01 offset 8093
 File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 187              Offsets: 8093 to 8094           Dba:0x018000bb
------------------------------------------------------------------------
 0102
BBED> sum apply
Check value for File 6, Block 187:
current = 0x4eaa, required = 0x4eaa
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 187
Block Checking: DBA = 25166011, Block Type = KTB-managed data block
**** actual free space credit for itl 2 = 15 != # in trans. hdr = 0
---- end index block validation
Block 187 failed with check code 6401
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 1
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> map
 File: /oracle/oradata/roger/roger01.dbf (6)
 Block: 187                                   Dba:0x018000bb
------------------------------------------------------------
 KTB Data Block (Index Leaf)
struct kcbh, 20 bytes                      @0
struct ktbbh, 72 bytes                     @20
struct kdxle, 32 bytes                     @100
sb2 kd_off[3]                              @132
ub1 freespace[7951]                        @138
ub1 rowdata[39]                            @8089
ub4 tailchk                                @8188
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
ub1 ktbbhtyp                             @20       0x02 (KDDBTINDEX)
 union ktbbhsid, 4 bytes                  @24
ub4 ktbbhsg1                          @24       0x00012ed2
 ub4 ktbbhod1                          @24       0x00012ed2
 struct ktbbhcsc, 8 bytes                 @28
ub4 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             @44
struct ktbitxid, 8 bytes              @44
ub2 kxidusn                        @44       0x0000
 ub2 kxidslt                        @46       0x0000
 ub4 kxidsqn                        @48       0x00000000
 struct ktbituba, 8 bytes              @52
ub4 kubadba                        @52       0x00000000
 ub2 kubaseq                        @56       0x0000
 ub1 kubarec                        @58       0x00
 ub2 ktbitflg                          @60       0x0000 (NONE)
 union _ktbitun, 2 bytes               @62
sb2 _ktbitfsc                      @62       0
 ub2 _ktbitwrp                      @62       0x0000
 ub4 ktbitbas                          @64       0x00000000
 struct ktbbhitl[1], 24 bytes             @68
struct ktbitxid, 8 bytes              @68
ub2 kxidusn                        @68       0x0009
 ub2 kxidslt                        @70       0x0002
 ub4 kxidsqn                        @72       0x0000044c
 struct ktbituba, 8 bytes              @76
ub4 kubadba                        @76       0x00c000f9
 ub2 kubaseq                        @80       0x014a
 ub1 kubarec                        @82       0x1e
 ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
 union _ktbitun, 2 bytes               @86
sb2 _ktbitfsc                      @86       0
 ub2 _ktbitwrp                      @86       0x0000
 ub4 ktbitbas                          @88       0x00b941a4
BBED> 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
------------------------------------------------------------------------
 0f00
BBED> sum apply
Check value for File 6, Block 187:
current = 0x4ea5, required = 0x4ea5
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/oradata/roger/roger01.dbf
BLOCK = 187
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 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:

  1. 创建index之前如何确定其大小
  2. ora-00600 [kddummy_blkchk] solution
  3. 如何修复未格式化的坏块?
  4. Archivelog 模式下,datafile header损坏,如何恢复?
  5. 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. [...]

人气教程排行