[20231008]bbed探究lob段.txt

--//前几天看https://www.xifenfei.com/2023/09/bbed-ora-1578.html提到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类型的一些细节可以参考http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals文档。

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