Samstag, 2. September 2017

SecureFile LOB - the 1st update

This blog post continues with all the objects and settings from SecureFile LOB - the empty table and SecureFile LOB - the 1st insert



the statement is quite easy:
update berx.TEST_BLOBS
set (comments, file_content) = 
(select comments, file_content
from berx.source_blobs
where id=4)
;

The new CLOB is about 11.1 kb in size - so it will require more than 1 block.

Fortunately the number of extents did not change.

New information needs to be gathered again:

ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_PRE_BLOB";  
alter system dump datafile 4 block min 255360 block max 255375;


ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_PRE_EXT2";  
alter system dump datafile 4 block min 256512 block max 257023;

@LOBMAP.sql (row 1)

System altered.

SQL> ROWID = AABu7gAAEAAA+N7AAA
ROWNUM  = 1
LOBID   = 00000001000099BF531D
EXTENT# = 0
HOLE?   = n
Superchunk cont? = n
Overallocation  = n
rdba   = 17032588 - 0x  103E58C
File   = 4
Block  = 255372
nblks  = 2
offset = 0
length = 11389
---

The LOBID changed. So the lob (for the same row) is in another location now.

In the 2nd extent, as far as I can judge, did not change.

In the 1st extend, some things changed (obviously, somewhere the LOB needs to be stored).

NGLOB: Segment Header stays the same.
all the NGLOB: Hash Bucket blocks stays the same. (that confuses me as I expected at least one of them to change)
Also block 0x0103e58b (trans data) - the one which holds the previous LOB is exactly the same (!)


The new trans data blocks


1st block:
buffer tsn: 4 rdba: 0x0103e58c (4/255372)
scn: 0xa9c.9062fe3c seq: 0x02 flg: 0x04 tail: 0xfe3c0602
frmt: 0x02 chkval: 0xba2b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F98ED985E00 to 0x00007F98ED987E00
7F98ED985E00 0000A206 0103E58C 9062FE3C 04020A9C  [........<.b.....]
7F98ED985E10 0000BA2B 00000005 0006EF63 9062FE3C  [+.......c...<.b.]
7F98ED985E20 00000A9C 00220001 FFFFFFFF 001901B0  [......".........]
7F98ED985E30 00020F33 00000000 00000000 00004000  [3............@..]
7F98ED985E40 00000000 00000000 00000000 9062FE3C  [............<.b.]
7F98ED985E50 00200A9C 01000000 BF990000 00001D53  [.. .........S...]
7F98ED985E60 00000000 00000000 00000000 00000000  [................]
7F98ED985E70 00000000 00000000 00000000 00001F7C  [............|...]
7F98ED985E80 70747468 2F2F3A73 616E6F6A 6E616874  [https://jonathan]
7F98ED985E90 6977656C 6F772E73 72706472 2E737365  [lewis.wordpress.]
7F98ED985EA0 2F6D6F63 35313032 2F31302F 6C2F3132  [com/2015/01/21/l]
7F98ED985EB0 732D626F 65636170 0D0A0D2F 6572630A  [ob-space/....cre]
...
Block header dump:  0x0103e58c
 Object id on Block? Y
 seg/obj: 0x6ef63  csc: 0xa9c.9062fe3c  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x01b0.019.00020f33  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x0103e58c]
kdlich  [0x7f98ed985e4c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0a9c.9062fe3c
  lid   00000001000099bf531d
  rid   0x00000000.0000
kdlidh  [0x7f98ed985e64 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   8060
  spr   0
  data  [0x7f98ed985e80 52 8060]
68 74 74 70 73 3a 2f 2f 6a 6f 6e 61 74 68 61 6e 6c 65 77 69 73 2e 77 6f 72 64
70 72 65 73 73 2e 63 6f 6d 2f 32 30 31 35 2f 30 31 2f 32 31 2f 6c 6f 62 2d 73
70 61 63 65 2f 0d 0a 0d 0a 63 72 65 61 74 65 20 74 61 62 6c 65 20 74 65 73 74
...

It is the rdba as described in the lob extent map above and references the lid == LOBID. Also the content is correct (this file is an early copy of my scratchpad to this topic). The hwm is 8060 which is smaller as the length of the LOB (11389).
Also nblks is 2, the next block is of some interest as well.

2nd block:

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58d (4/255373)
scn: 0xa9c.9062fe3c seq: 0x02 flg: 0x04 tail: 0xfe3c0602
frmt: 0x02 chkval: 0xb95a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F98ED985E00 to 0x00007F98ED987E00
...
Block header dump:  0x0103e58d
 Object id on Block? Y
 seg/obj: 0x6ef63  csc: 0xa9c.9062fe3c  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x01b0.019.00020f33  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x0103e58d]
kdlich  [0x7f98ed985e4c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0a9c.9062fe3c
  lid   00000001000099bf531d
  rid   0x00000000.0000
kdlidh  [0x7f98ed985e64 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   3329
  spr   0
  data  [0x7f98ed985e80 52 8060]

This block contains the remaining part of the LOB. hwm shows it contains the remaining bytes.


The new LOB is present in the LOB segment, the old one is still present - exactly as I'd expect as UNDO is handled as copy on write in LOBs. It's only confusing to see no indication (visible to me) that block 0x0103e58b is old and not used anymore.



After some time, (but no activity on the DB) block dumps are created again:

ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_COM_LONGTIME_BLOB";  
alter system dump datafile 4 block min 255360 block max 255375;

ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_COM_LONGTIME_EXT2";  
alter system dump datafile 4 block min 256512 block max 257023;

Now there is an interesting change:

2nd NGLOB: Hash Bucket

(part of UFS)
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e583 (4/255363)
scn: 0xa9c.916207e5 seq: 0x01 flg: 0x04 tail: 0x07e53d01
frmt: 0x02 chkval: 0xa4e3 type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F9AFDEAE800 to 0x00007F9AFDEB0800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Inst:1 Flag:6 Total:0 FSG_COUNT:0 OBJD:454499 Inc:0
Self Descriptive
 fsg_count:0
Head:0x 0x00000000  Tail:0x 0x00000000
 Opcdode:3 Locker Xid: 0x01b0.019.00020f35
Fsbdba: 0x0    Fbrdba: 0x0
Head Cache Entries
-------------------
-------------------
Tail Cache Entries
-------------------
-------------------
Free Space Chunk Summary
Inst:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:1
UFS List
--------------
Deleted - Chunk:0 DBA: 0x0103e58b Len:140299401691137 Xid: 0x01b0.019.00020f35 Ctime:1504343585
Scn: 0x0a9c.9062fe3c
  --------------------------------------------------------

No clue why this block changed in between.
Ctime: is epoc for GMT: Saturday, September 2, 2017 9:13:05 AM (when the update was done) and Scn: is the exact SCN when the new LOB was written.

Now it makes some sense, but it's unclear what changed the block between the 2 dumps.


Kommentar veröffentlichen