Orace block dump @ various stages of the transaction

Goal: See what happens during a Transaction (TX)... learn from block dump.

Step 1: Get to know your table - block map [thanks to Arup Nanda - http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html]

SQL> select
dbms_rowid.rowid_relative_fno(rowid) File#,
dbms_rowid.rowid_block_number(rowid) Block#,
count(1)
from T1_TEST1
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by
1,2  2    3    4    5    6    7    8    9   10  ;

     FILE#     BLOCK#   COUNT(1)
---------- ---------- ----------
      1012      55571         65
      1012      55572         59
      1012      55573         59
      1012      55574         59
      1012      55575         59
      1012      55576         59
      1012      55577         59
      1012      55578         60
      1012      55579         59
      1012      55580         59
      1012      55581         59
      1012      55582         59
      1012      55584         59
      1012      55585         59
      1012      55586         59
      1012      55587         59
      1012      55588         59
      1012      55589         59
      1012      55590         59
      1012      55591         59
      1012      55592         59
...
      1012      55750         59
      1012      55751         59
      1012      55752         59
      1012      55753         59
      1012      55754         59
      1012      55755         59
      1012      55756         59
      1012      55757         59
      1012      55758         59
      1012      55759         59
      1012      55760         59
      1012      55761         59
      1012      55762         59
      1012      55763         59
      1012      55764         59
      1012      55765         59
      1012      55766         59
      1012      55767         59
      1012      55768         40

189 rows selected.


Step 2: Pick the block we wanted to update and then find out its high/low value which will be used in the preference clause.

select min(OBJECT_ID), max(OBJECT_ID)
from T1_TEST1
where dbms_rowid.rowid_block_number(rowid) = 55572;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
        104344         149818


Step 3: For keeping this test simple, I will pick the second block and its min [104344]. value.

Step 4: Will dump the preimage of the block.


set lines 300 pages 3000
select a.sid,a.serial#,b.pid,b.spid,b.tracefile from v$session a,v$process b where a.paddr=b.addr and a.username='SCOTT';

alter system dump datafile 1012 block min 55572 block max 55572;

Step 5: Update the chosen record..

update T1_TEST1 set object_name='22SEP18_CHANGED' where object_id=104344;

Step 6: Will dump the postimage of the block.

alter system dump datafile 1012 block min 55572 block max 55572;

Step 7: Will perform commit and take a dump of the block.

commit;
alter system dump datafile 1012 block min 55572 block max 55572;

>>> done , the trace file is recovered @ all the three steps. I managed to remove the duplicate copies of the trace entries and kept only relevant parts in all 3 traces.
Let us do the examination.

Observations:
1) In the preimage dump of the block, we only had 1 buffer copy

Dump of buffer cache at level 4 for pdb=0 tsn=1013 rdba=-50276076
BH (0x9df6a158) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d104000
...
hash: [0xa95592e0,0xa95592e0] lru: [0x9df6a388,0x9df6a108]
...
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2

flags: only_sequential_access

Block dump from disk:
buffer tsn: 1013 rdba: 0xfd00d914 (1012/55572)

scn: 0x0.2cf023 seq: 0x02 flg: 0x04 tail: 0xf0230602

frmt: 0x02 chkval: 0xd066 type: 0x06=trans data

What we notice in this trace is, we had only the current version of the block available in memory. There were no active transaction in the block.

2) The same block dump on DML, had 2 versions of the block. 1 the pre-modification version which is the CR (consistent Read version) aka actually the step 1's current version and the actual current version which is the modified block version (this block actually is flagged as privte).

Current:
BH (0x9df61e18) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d032000
..
hash: [0x9df6a210,0xa95592e0] lru: [0x9df62048,0x9df61dc8]
..
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2 txn: 0xa530acb8   //// look here the block has a TXN associated
flags: private

Apart from this we had the past image of the block (CR)..

BH (0x9df6a158) file#: 1012 rdba: 0xfd00d914 (1012/55572) class: 1 ba: 0x9d104000
..
hash: [0xa95592e0,0x9df61ed0] lru: [0x9df627c8,0x9df6e348]

lru-flags: moved_to_tail
..
cr: [scn: 0x0.31ef05],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.31ef05],[sfl: 0x0],[lc: 0x0.2cf023]
flags: only_sequential_access

Rest all resembles.

3) Post the commit, I noticed the following sections in the block dump trace shows changes...

Pre commit#
ckptq: [NULL] fileq: [NULL]

objq: [0x9df6a3b0,0x9df69d70] objaq: [0x9df6a3c0,0x9df69d80]

use: [NULL] wait: [NULL]
                /// entry is found here alone
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2 txn: 0xa530acb8

flags: private

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]


Post Commit#
obj-flags: object_ckpt_list //// a new line post commit.From what I have read is the object checkpoint queue, the object post commit gets into the checkpoint queue.

ckptq: [0x9df941d8,0x9df6e398] fileq: [0xa9d30cb8,0xa9d30cb8]
        //// The block getting into checkpoint queue and also it gets a LRBA value (low redo block address)
objq: [0x7f0ea940,0x7f0ea940] objaq: [0x9df6a3c0,0x9df69d80]

st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.31ee4e tch: 2
 //// No Transaction entry, meaning the transaction is completed.
flags: buffer_dirty
                            //// Oracle announces now the buffer as dirty, earlier the buffer was private now it has become dirty   
LRBA: [0x7e.119ef.0] LSCN: [0x0.31ef32] HSCN: [0x0.31ef61] HSUB: [1]    One unique identification is the block dump didnt show use of ITL at any stage.
Since there were ckpt or events that can kick off DBW to write dirt list or cold buffers, the block isnt written to the disk yet.

Thanks