分享程序员开发的那些事...
更新时间：2022-09-06 23:40:44
[20160527]快速提交的一个疑问.txt
--这个是我前几天恢复update没有加where条件的恢复,记录不多,但是我发现一个"奇怪"的问题,或者讲我以前没有注意的问题,
--我在itpub上问了,没人解答.链接http://www.itpub.net/thread-2060064-1-2.html
Block header dump: 0x0180239c
Object id on Block? Y
seg/obj: 0x1da20 csc: 0x03.8fc12309 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1802399 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00001ac8 0x00800860.2398.1c --U- 1 fsc 0x0027.8fc1230f
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x02 0x0001.005.00001873 0x0080000f.1e28.0d C--- 0 scn 0x0003.690c955a
--问一下~,fsc 0x0027.8fc1230f 前面的0x0027 表示什么?
--因为恢复update的数据要将这个fsc的前面部分0x0027设置为0.而我发现有一些这个原来就是0,而这个表示什么呢?,自己以前一直没有
--注意,有空做一些简单探究:
--说明一下,我没有metalink帐号,这些探究可能不对,完全是基于我的猜测.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table DEMO (id number, update_scn number, commit_scn number,text varchar2(200));
insert into DEMO values (1,dbms_flashback.get_system_change_number,userenv('commitscn'),lpad('a',50,'a'));
commit;
insert into DEMO values (2,dbms_flashback.get_system_change_number,userenv('commitscn'),lpad('b',50,'b'));
commit;
insert into DEMO values (3,dbms_flashback.get_system_change_number,userenv('commitscn'),lpad('c',50,'c'));
commit;
column text noprint
select row_orascn,rowid,demo.* from demo;
SCOTT@book> select ora_rowscn,rowid,demo.* from demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237906989 AAAW74AAEAAAAIjAAA 1 13237906963 13237906975
13237906989 AAAW74AAEAAAAIjAAB 2 13237906981 13237906982
13237906989 AAAW74AAEAAAAIjAAC 3 13237906988 13237906988
SCOTT@book> @ &r/rowid AAAW74AAEAAAAIjAAA
OBJECT FILE BLOCK ROW DBA
------------ ------------ ------------ ------------ --------------------
93944 4 547 0 4,547
2.使用bbed探究:
BBED> map
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[3] @118
ub1 freespace[7772] @124
ub1 rowdata[292] @7896
ub4 tailchk @8188
BBED> p kdbh
struct kdbh, 14 bytes @100 <-- Data Header Structure
ub1 kdbhflag @100 0x00 (NONE) <-- N=pctfree hit(clusters)；F=do not put on freelist;K=flushable cluster keys
sb1 kdbhntab @101 1 <-- Number of tables (>1 in clusters)
sb2 kdbhnrow @102 3 <-- Number of rows
sb2 kdbhfrre @104 -1 <-- First free row entry index: -1= 你必须添加一个
sb2 kdbhfsbo @106 24 <-- Freespace begin offset
sb2 kdbhfseo @108 7796 <-- Freespace end offset
sb2 kdbhavsp @110 7845 <-- Available space in the block
sb2 kdbhtosp @112 7845 <-- Toatal available space when all TXs commit
BBED> p kdbr
sb2 kdbr[0] @118 8015
sb2 kdbr[1] @120 7869
sb2 kdbr[2] @122 7796
BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0] @7896 0x2c <-- 这里与上面的偏移相差100,我认为上面记录的相对偏移,从kdbh算起,如果你使用ctas建表
<-- ITL槽的数量是3,这样kdbh就不是100,而是124.这样我一直有一个疑问,如果大量事务导致
<-- ITL槽增加, 行目录记录的信息不是要重新计算吗?
<-- 还有如果表空间不是assm,好像也不同,大家可以自行测试.
--//后面的解析来自:http://www.housong.net/oracle-bbed-app-2.html
3.修改kdbhavsp减少1看看:
BBED> assign kdbhavsp=7844
sb2 kdbhavsp @110 7844
BBED> sum apply
Check value for File 4, Block 547:
current = 0x8357, required = 0x8357
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x2278a64
kdbchk: the amount of space used is not equal to block size
used=243 fsc=0 avsp=7844 dtl=8088
Block 547 failed with check code 6110
--//这样破坏空间计算的一致性.
-- dtl 猜测一下 最后tailchk 占用4个字节. 8192-4=8188,再减去100就是就是8088.也就真正能写数据的空间8088.
-- dtl - used + fsc = avsp 8088-243+0=7845 , 这里也说明现在avsp=7844.
--还原回来:
BBED> assign kdbhavsp=kdbhtosp
sb2 kdbhavsp @110 7845
4.继续探究:
--//首先说明如果update前后字段长度不变,oracle是原地替换,而不是在上面"插入"新信息,修改对应行目录的信息指向当前的位置.
SCOTT@book> update DEMO set commit_scn=userenv('commitscn') ,text=lpad('C',40,'C') where id=1;
1 row updated.
--长度比原来少10个字节.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select ora_rowscn,rowid,demo.* from demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237909275 AAAW74AAEAAAAIjAAA 1 13237906963 13237909274
13237909275 AAAW74AAEAAAAIjAAB 2 13237906981 13237906982
13237909275 AAAW74AAEAAAAIjAAC 3 13237906988 13237906988
SCOTT@book> alter system flush buffer_cache;
System altered.
--注意要退出bbed在进入,不然看到还是旧的信息.
BBED> set dba 4,547
DBA 0x01000223 (16777763 4,547)
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 24
sb2 kdbhfseo @108 7733
sb2 kdbhavsp @110 7845
sb2 kdbhtosp @112 7855
--可以对比上面的变化.
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @7833 0x2c
BBED> x /rnnnc
rowdata[0] @7833
----------
flag@7833: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7834: 0x02
cols@7835: 4
col 0[2] @7836: 1
col 1[7] @7839: 13237906963
col 2[7] @7847: 13237909274
col 3[40] @7855: CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
--lock =0x02 ,说明使用ITL槽=0x02.
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0009
ub4 kxidsqn @72 0x00009c8d
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c002aa
ub2 kubaseq @80 0x1e47
ub1 kubarec @82 0x09
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub2 _ktbitwrp @86 0x000a
ub4 ktbitbas @88 0x150a771b
select 13237909275,trunc(13237909275/power(2,32)) scn_wrap,mod(13237909275,power(2,32)) scn_base from dual
13237909275 SCN_WRAP SCN_BASE
------------ ------------ ------------
13237909275 3 353007387
SCOTT@book> @ &r/10to16 353007387
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00000150a771b 0x1b770a15-00000000
--ktbbhitl[1].ktbitbas=0x150a771b ,与ORA_ROWSCN=13237909275的BASE部分正好对上.
--另外注意~,正好是10,与记录减少的长度10正好对应(50-40=10)
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 24
sb2 kdbhfseo @108 7733
sb2 kdbhavsp @110 7845
sb2 kdbhtosp @112 7855
--将kdbhavsp减少1看看:
BBED> assign kdbhavsp=7844
sb2 kdbhavsp @110 7844
BBED> sum apply
Check value for File 4, Block 547:
current = 0x5933, required = 0x5933
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7f6f85ae1264
kdbchk: the amount of space used is not equal to block size
used=233 fsc=10 avsp=7844 dtl=8088
Block 547 failed with check code 6110
-- dtl - used + fsc = avsp
-- 8088 - 233 +10 =7865 ,说明猜测的计算结果正确.
-- 从这里也可以在猜测出来当快速提交时,itl中的ktbbhitl[1]._ktbitun._ktbitfsc=记录减少的长度.
5.如果有dml操作这里的信息会清除的.我门看看清除后情况如何:
--先还原看看:
BBED> assign kdbhavsp=7845
sb2 kdbhavsp @110 7845
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 24
sb2 kdbhfseo @108 7733
sb2 kdbhavsp @110 7845
sb2 kdbhtosp @112 7855
SCOTT@book> update DEMO set id=2 where id=2;
1 row updated.
--这样原地覆盖.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system flush buffer_cache;
System altered.
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0009
ub4 kxidsqn @72 0x00009c8d
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c002aa
ub2 kubaseq @80 0x1e47
ub1 kubarec @82 0x09
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 10
ub2 _ktbitwrp @86 0x000a
ub4 ktbitbas @88 0x150a771b
--还没有覆盖.继续:
SCOTT@book> update DEMO set id=2 where id=2;
1 row updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system flush buffer_cache;
System altered.
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0008
ub4 kxidsqn @72 0x00009cde
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00185
ub2 kubaseq @80 0x1e4e
ub1 kubarec @82 0x01
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 3
ub2 _ktbitwrp @86 0x0003
ub4 ktbitbas @88 0x150aa20f
--可以发现这个槽被覆盖.
BBED> x /rnnnc
rowdata[0] @7833
----------
flag@7833: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7834: 0x00
~~~~~~~~~~~~~~~
cols@7835: 4
col 0[2] @7836: 1
col 1[7] @7839: 13237906963
col 2[7] @7847: 13237909274
col 3[40] @7855: CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
--对应lock的信息也被清除.
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 24
sb2 kdbhfseo @108 7733
sb2 kdbhavsp @110 7855
sb2 kdbhtosp @112 7855
-- kdbhavsp 有 7854=> 7855. 也就是在覆盖时将fsc+kdbhavsp = kdbhavsp.从以上可以看出当dml曹组时记录长度减少时提交时对应itl
--槽的_ktbitfsc等于减少的记录程度.
6.继续做一个验证,假设做一个事务
--删除id=2 并且修改id=3的text=lpad('C',60,'C')
BBED> p *kdbr[1]
rowdata[136]
------------
ub1 rowdata[136] @7969 0x2c
BBED> x /rnnnc
rowdata[136] @7969
------------
flag@7969: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7970: 0x00
cols@7971: 4
col 0[2] @7972: 2
col 1[7] @7975: 13237906981
col 2[7] @7983: 13237906982
col 3[50] @7991: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
-- id =2 记录长度等于 1+1+2+1+7+1+7+1+50=71,,也就是长度减少71.(注意计算不包括flag,lock的长度)
-- 也可以看这个例子链接 [20160526]bbed修复删除记录.txt http://blog.itpub.net/267265/viewspace-2107125/
-- id =3 记录长度增加10,
-- 这样相当于两者合并 减少61 ,看看自己的判断是否正确.
delete from demo where id=2;
update demo set text=lpad('C',60,'C') where id=3;
commit ;
alter system flush buffer_cache;
--bbed要退出在进入,看到改变.
BBED> p ktbbhitl[0]
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x0016
ub4 kxidsqn @48 0x00009ce7
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00186
ub2 kubaseq @56 0x1e4e
ub1 kubarec @58 0x14
ub2 ktbitflg @60 0x2002 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 61
ub2 _ktbitwrp @62 0x003d
ub4 ktbitbas @64 0x150aa393
--看到自己的判断正确61.
-- 从定义也可以看出_ktbitun是一个union:
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 61
ub2 _ktbitwrp @62 61
--当快速提交时,并不清除lock信息,而_ktbitfsc保存减少的记录长度.而快速提交对应自然scn的wrap.
--以上完全是基于我的猜测,不知道是否正确.这样就很好理解为什么恢复原来的update时要将它设置为0.