[20210223]bbed itl ktbitflg 2.txt
[20210223]bbed itl ktbitflg 2.txt
--//簡單探究bbed檢視ITL槽的ktbitflg含義.它包含2部分資訊佔32位,前4位表示提交狀態,後12位表示dml的記錄數.
1.環境:
SCOTT@book> @ 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
2.測試:
SCOTT@book> create table t as select rownum id from dual connect by level<=740;
Table created.
--//分析略.
SCOTT@book> select rowid,t.* from t where rownum=1;
ROWID ID
------------------ ----------
AAAWIjAAEAAAA2rAAA 1
SCOTT@book> @ rowid AAAWIjAAEAAAA2rAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90659 4 3499 0 0x1000DAB 4,3499 alter system dump datafile 4 block 3499
SCOTT@book> select max(id) from t where rowid <='AAAWIjAAEAAAA2rDDD';
MAX(ID)
------------
657
--//dba=4,3499佔657條記錄.
SCOTT@book> update t set id=id where id<=600;
600 rows updated.
SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.22.20803
SCOTT@book> alter system flush buffer_cache;
System altered.
--//保證該塊不再資料快取.
3.透過bbed觀察:
BBED> p dba 4,3499 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0016
ub4 kxidsqn @72 0x00005143
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c001b8
ub2 kubaseq @80 0x10d2
ub1 kubarec @82 0x06
ub2 ktbitflg @84 0x0258 (NONE)
~~~~~~~~~~~~~~~~~~~~~~ --//我沒有提交,而且當前該塊已經寫盤.
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
--//0x0258 = 600條.與操作事務記錄數一致,並且沒有提交.如果我提交不會修改該塊(延遲塊清除).而是下次select時修改寫上.
--//0x00005143 = 20803 與前面看到xid資訊也能對上.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select rowid,t.* from t where rownum=1;
ROWID ID
------------------ ----------
AAAWIjAAEAAAA2rAAA 1
SCOTT@book> alter system flush buffer_cache;
System altered.
--//再透過bbed觀察:
BBED> p dba 4,3499 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0016
ub4 kxidsqn @72 0x00005143
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c001b8
ub2 kubaseq @80 0x10d2
ub1 kubarec @82 0x06
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 3
ub2 _ktbitwrp @86 0x0003
ub4 ktbitbas @88 0x177275ce
--//下次讀出時修改該事務槽的ktbitflg,並且清除了lck資訊為0.
4.繼續:
SCOTT@book> update t set id=id-1 where id<=500;
500 rows updated.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.17.20632
--//bbed觀察:
BBED> p dba 4,3499 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0003
ub2 kxidslt @70 0x000b
ub4 kxidsqn @72 0x0000078f
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00754
ub2 kubaseq @80 0x03cc
ub1 kubarec @82 0x03
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 3
ub2 _ktbitwrp @86 0x0003
ub4 ktbitbas @88 0x17726fba
--//這個時候才修改該事務槽.
4.繼續:
SCOTT@book> update t set id=id where id<=500;
500 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system flush buffer_cache;
System altered.
--//bbed觀察:
BBED> p dba 4,3499 ktbbh.ktbbhitl[2]
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x000a
ub2 kxidslt @94 0x000f
ub4 kxidsqn @96 0x00005140
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00c001bb
ub2 kubaseq @104 0x10d2
ub1 kubarec @106 0x07
ub2 ktbitflg @108 0x21f4 (KTBFUPB)
~~~~~~~~~~~ --//我修改並提交,而且提交時該塊還在資料快取,這樣快速提交,修改ktbitflg=0x21f4.(0x1f4= 500)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x1772772f
SYS@book> alter system dump datafile 4 block 3499;
System altered.
--//轉儲資訊:
Block header dump: 0x01000dab
Object id on Block? Y
seg/obj: 0x16223 csc: 0x03.177275d8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000da8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.177274e5
0x02 0x000a.016.00005143 0x00c001b8.10d2.06 C--- 0 scn 0x0003.177275ce
0x03 0x000a.00f.00005140 0x00c001bb.10d2.07 --U- 500 fsc 0x0000.1772772f
bdba: 0x01000dab
data_block_dump,data header at 0x7f08e9727a7c
--//使用U表示 flag,對應就是0x2.很明顯0x8表示提交.
5.現在透過bbed修改觀察ktbitflg的情況.
BBED> assign dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg=0x11f4;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub2 ktbitflg @108 0x11f4 (KTBFTAC)
BBED> p dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x11f4 (KTBFTAC)
BBED> sum apply dba 4,3499
Check value for File 4, Block 3499:
current = 0xd412, required = 0xd412
SYS@book> alter system dump datafile 4 block 3499;
System altered.
--//轉儲資訊:
Block header dump: 0x01000dab
Object id on Block? Y
seg/obj: 0x16223 csc: 0x03.177275d8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000da8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.177274e5
0x02 0x000a.016.00005143 0x00c001b8.10d2.06 C--- 0 scn 0x0003.177275ce
0x03 0x000a.00f.00005140 0x00c001bb.10d2.07 ---T 500 fsc 0x0000.1772772f
bdba: 0x01000dab
data_block_dump,data header at 0x7f08e9727a7c
--//T 表示0x1,好像沒人看見過這樣的標識.
BBED> assign dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg=0x41f4;
ub2 ktbitflg @108 0x41f4 (KTBFIBI)
BBED> sum apply dba 4,3499
Check value for File 4, Block 3499:
current = 0x8412, required = 0x8412
SYS@book> alter system dump datafile 4 block 3499;
System altered.
--//轉儲資訊:
Block header dump: 0x01000dab
Object id on Block? Y
seg/obj: 0x16223 csc: 0x03.177275d8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000da8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.177274e5
0x02 0x000a.016.00005143 0x00c001b8.10d2.06 C--- 0 scn 0x0003.177275ce
0x03 0x000a.00f.00005140 0x00c001bb.10d2.07 -B-- 500 fsc 0x0000.1772772f
bdba: 0x01000dab
data_block_dump,data header at 0x7f08e9727a7c
--//B 表示0x4,好像也沒人看見過這樣的標識.
BBED> assign dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg=0xf1f4;
ub2 ktbitflg @108 0xf1f4 (KTBFTAC, KTBFUPB, KTBFIBI, KTBFCOM)
BBED> sum apply dba 4,3499
Check value for File 4, Block 3499:
current = 0x3412, required = 0x3412
BBED> p dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0xf1f4 (KTBFTAC, KTBFUPB, KTBFIBI, KTBFCOM)
SYS@book> alter system dump datafile 4 block 3499;
System altered.
--//轉儲資訊:
Block header dump: 0x01000dab
Object id on Block? Y
seg/obj: 0x16223 csc: 0x03.177275d8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000da8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.177274e5
0x02 0x000a.016.00005143 0x00c001b8.10d2.06 C--- 0 scn 0x0003.177275ce
0x03 0x000a.00f.00005140 0x00c001bb.10d2.07 CBUT 0 scn 0x0000.1772772f
bdba: 0x01000dab
data_block_dump,data header at 0x7f08e9727a7c
--//KTBFTAC, KTBFUPB, KTBFIBI, KTBFCOM 分別標識 0x1,0x2,0x4,0x8.
--//比較常見的組合前4位是0x8,0x2,0xa,0x0
--//收尾還原.
BBED> assign dba 4,3499 ktbbh.ktbbhitl[2].ktbitflg=0x21f4;
ub2 ktbitflg @108 0x21f4 (KTBFUPB)
BBED> sum apply dba 4,3499
Check value for File 4, Block 3499:
current = 0xe412, required = 0xe412
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 3499
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2758736/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210604]索引分裂與 itl ktbitflg.txt索引
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20230427]bbed sum apply問題2.txtAPP
- [20210318]bbed讀取資料塊2.txt
- [20220223]bbed讀取資料塊mssm與assm 2.txtSSM
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- 遭遇ITL死鎖
- 【BBED】BBED基礎知識
- [20210223]sys與Extended Data Types.txt
- 【BBED】Oracle bbed常用命令參考Oracle
- [20220223]Index ITL Limit.txtIndexMIT
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- [20210906]bbed讀取資料塊(bbed-wrap.sh).txt
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- BBED 的安裝
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- [20180327]行遷移與ITL浪費.txt
- [20181204]bbed修改問題.txt
- [20180619]bbed verify問題.txt
- [20231008]bbed探究lob段.txt
- oracle之 11.2.0.4 bbed安裝Oracle
- [20220223]bbed ktbbh.ktbbhict.txt
- [20210920]bbed的assign命令.txt
- bbed修改undo段狀態
- [20210304]bbed的assign命令.txt
- [20190125]MSSM表空間塊ITL的LCK.txtSSM
- [20180625]oradebug peek 2.txt
- [20191011]拆分rowid 2.txt
- [20190107]生成bbed執行指令碼:指令碼
- DBA手記-BBED 的說明
- 11_Oracle bbed工具介紹Oracle
- [20190104]bbed手工插入資料.txt
- [20181227]bbed的使用問題.txt
- [20210901]cygwin下使用bbed.txt
- [20230224]bbed設定偏移技巧.txt
- [20210303]bbed使用小問題.txt
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online