欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

手工提交Cluster Table的事务

程序员文章站 2024-02-03 19:58:46
...

本站文章除注明转载外,均为本站原创: 转载自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...........<.. ...a...... repeat times block header dump: object id on y seg csc: itc: flg: e typ: data brn: bdba: ver: opc: inc: exflg: itl xid uba flag lck scn c--- fsc data_block_dump at tsiz: hsiz: pbl: 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 ?
手工提交Cluster Table的事务 本站文章除注明转载外,均为本站原创: 转载自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. [...]手工提交Cluster Table的事务