[20231008]bbed探究lob段.txt

--//前幾天看提到corrupted block裡面的型別lob塊，出現ora-01578錯誤，
--//仔細看發現對方僅僅修改tailchk，問題就解決了。似乎寫塊時僅僅寫了一半資料庫掛起或者中斷或者寫磁碟時出現中斷或者已經寫
--//入磁碟快取但是並沒有真正的寫入磁碟。
--//我很少使用bbed看lob型別的資料塊，測試看看是否bbed能檢視lob段。
--//我主要目的是能否使用bbed檢視lob段的一些資料結構，如果不行，有什麼臨時解決的辦法，有點文不對題。

1.環境:
SCOTT@test01p> @ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

2.建立例子:
SCOTT@test01p> CREATE TABLE T ( ID NUMBER ,B BLOB) LOB ("B") STORE AS BASICFILE;
Table created.
--//注：現在12c以後版本預設建立的lob型別時securefile型別,要設定BASICFILE型別要明確指定，主要目的與作者的測試一致。
--//估計以後很少再用bascifile型別的lob段。

SCOTT@test01p> @ ddl t
C300
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
   (    "ID" NUMBER,
        "B" BLOB
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
LOB ("B") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING ) ;
--//注意LOB型別是BASICFILE.

CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION;

SCOTT@test01p> host ls -l /tmp/expdp/export.log
-rw-rw-rw-   1 user     group        5293 Oct 7 21:13 /tmp/expdp/export.log
--//export.log 檔案足夠大，保證儲存在資料段外。

SCOTT@test01p> insert into t values (1,to_blob(bfilename('TMP_EXPDP','export.log')));
1 row created.

SCOTT@test01p> commit ;
Commit complete.

--//確定lob段的位置。
SCOTT@test01p> alter system checkpoint;
System altered.

SCOTT@test01p> select rowid from t;
ROWID
------------------
AAAHZNAALAAABKfAAA

SCOTT@test01p> @ rowid AAAHZNAALAAABKfAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     30285         11       4767          0 0x2C0129F           11,4767              alter system dump datafile 11 block 4767

SCOTT@test01p> alter system dump datafile 11 block 4767;
System altered.

--//檢查跟蹤檔案,確定lob塊的塊地址。
Block header dump: 0x02c0129f
Object id on Block? Y
seg/obj: 0x764d csc: 0x0000000000f374bd itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x2c01298 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0008.01a.00000a88 0x018014c0.00e5.16 --U-    1 fsc 0x0000.00f374bf
0x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
bdba: 0x02c0129f
data_block_dump,data header at 0x1b3c3064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x1b3c3064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f69
avsp=0x1f55
tosp=0x1f55
0xe:pti[0]   nrow=1   offs=0
0x12:pri[0]   offs=0x1f69
block_row_dump:
tab 0, row 0, @0x1f69
tl: 47 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [40]
00 70 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 02 74 08 c4 00 14 05 00 00
00 00 00 14 ad 00 00 00 00 00 02 02 c0 12 a7
          ~~~~~
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 4767 maxblk 4767

--//檔案大小5293 = 0x14ad,佔1個chunk(8K的資料塊)。
--//LOB型別的一些細節可以參考文件。

col 1: [40]
00 70 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 02 74 08 c4 00 14 05 00 00
00 00 00 14 ad 00 00 00 00 00 02 02 c0 12 a7
          ~~~~~
--//最後的4個位元組:
--//02c012a7 = set dba 11,4775 = alter system dump datafile 11 block 4775 = 46142119
--//可以確定lob的塊地址在dba 11,4775.

SCOTT@test01p> @ti
New tracefile_identifier = D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_8060_0003.trc

SCOTT@test01p> alter system dump datafile 11 block 4775;
System altered.

--//按照站點介紹basic lob塊的格式如下:
Basic Files LOB Out-of-Line Storage
. Out-of-line LOB data is stored in a PAGETABLE MANAGED LOB BLOCK
, Each block has
, a 56 byte header
, a 4 byte trailer
, For example for a 8192 byte block
--//8192-56-4 = 8132 LOB Data

3.測試:
BBED> set dba 11,4776
        DBA             0x02c012a8 (46142120 11,4776)
--//注：我使用bbed for windows的版本，無法識別資料檔案的OS頭，設定塊偏移要+1.

BBED> map
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776                                  Dba:0x02c012a8
------------------------------------------------------------
BBED-00400: invalid blocktype (40)

BBED> dump /v offset 0 count 56
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776    Offsets:    0 to   55 Dba:0x02c012a8
-------------------------------------------------------
28a20000 a712c002 bc74f300 00000204 l (?.??紅?....
af0e0000 4e760000 00000001 00000274 l ?..Nv.........t
08c40000 01000000 00000000 00000000 l .?.............
a012c002 00000000                   l ??....

<16 bytes per line>

--//0x28 = 40,這樣型別的塊bbed根本無法讀取，只能使用最原始的dump命令。

BBED> dump /v offset 8 count 4
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776    Offsets:    8 to   11 Dba:0x02c012a8
-------------------------------------------------------
bc74f300                            l 紅?
<16 bytes per line>
--//塊scn。

BBED> dump /v offset 8188 count 4
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776    Offsets: 8188 to 8191 Dba:0x02c012a8
-------------------------------------------------------
0228bc74                            l .(紅
<16 bytes per line>
--//taichk的低位元組0xbc74與前面看到的scn的低位元組一致，intel CPU注意大小頭問題)。

--//嘗試修改第1位元組為0x06看看，注：正常的資料段開頭是0x06.
BBED> modify /x 0x06 offset 0
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776             Offsets:    0 to    3           Dba:0x02c012a8
------------------------------------------------------------------------
06a20000
<32 bytes per line>

BBED> map
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776                                  Dba:0x02c012a8
------------------------------------------------------------
BBED-00403: invalid DATA block subtype (78) - valid values are 1, 2
--//78 = 0x4e,估計按照上面的提示，bbed僅僅接受subtype=1,2的值。
--//檢視前面的dump很容易確定subtype的偏移在offset 20位置。

BBED> modify /x 0x01 offset 20
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776             Offsets:   20 to   75           Dba:0x02c012a8
------------------------------------------------------------------------
01760000 00000001 00000274 08c40000 01000000 00000000 00000000 a012c002
00000000 3b3b3b20 0d0a4578 706f7274 3a205265 6c656173
<32 bytes per line>

--//3b3b3b20 0d0a4578 706f7274 3a205265 6c656173 = ;;;
--//Export: Releas

BBED> map
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 4776                                  Dba:0x02c012a8
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes                      @0
struct ktbbh, 48 bytes                     @20
struct kdbh, 14 bytes                      @68
struct kdbt[32], 128 bytes                 @82
sb2 kdbr[25938]                            @210
ub1 freespace[4294946052]                  @52086
ub1 rowdata[4294967123]                    @30842
ub4 tailchk                                @8188
--//一些資訊完全不對，比如kdbr明顯不對。但是bbed的map命令可以正常顯示了.

BBED> p tailchk
ub4 tailchk                                 @8188     0x74bc2802
--//dump /v offset 8188 count 4看到的是0228bc74。

BBED> p kcbh.bas_kcbh
ub4 bas_kcbh                                @8        0x00f374bc

--//沒有別的意圖,主要想透過這樣的方式可以使用assign修改命令,操作更加簡單一些.
--//模擬ora-01578,結果我不再貼出，大家可以自行測試:
--//assign tailchk=0x00000001

--//還原執行如下:
modify /x 0x4e offset 20
modify /x 0x28 offset 0

BBED> sum
Check value for File 11, Block 4776:
current = 0x0eaf, required = 0x0eaf

SYS@test> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test01p> select rowid,t.id,substr(t.b,1,20) from t;
ROWID                      ID SUBSTR(T.B,1,20)
------------------ ---------- ----------------------------------------
AAAHZNAALAAABKfAAA          1 ;;;
                              Export: Releas

[20231008]bbed探究lob段.txt

相關文章