[20231008]bbed探究lob段.txt

来源：这里教程网时间：2026-03-03 19:00:33 作者：

[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