Index Block Dump: Block Header Part I (Editions Of You)

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail.

As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the covers and over the years I’ve found them to be an invaluable aid in helping me understand Oracle behaviour, troubleshoot issues, investigate the contents of corrupted blocks, etc. The focus in this series will be Oracle block dumps from the perspective of indexes, although the contents any Oracle block can be dumped and investigated.
 
To setup the demo, I’m going to initially create a simple little table and associated index that has only 3 rows to begin with. As the index is so tiny, all the contents can fit within the one index leaf block resulting in an index with a blevel of 0 (height of 1). Note I’m using a 11.1.0.6.0 database running on Windows for this specific demo. The actual format and content of a block dump differs between releases and continually changes. However, much of the useful content which I’ll focus on remains relatively consistent.
 

SQL> create table bowie (id number, name varchar2(20));
 
Table created.
 
SQL> insert into bowie values (1, 'BOWIE');
 
1 row created.
 
SQL> insert into bowie values (2, 'ZIGGY');
 
1 row created.
 
SQL> insert into bowie values (3, 'MAJOR TOM');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index bowie_name_i on bowie(name);
 
Index created.
 
SQL> select index_name, blevel, leaf_blocks from dba_indexes where index_name = 'BOWIE_NAME_I';
 
INDEX_NAME          BLEVEL LEAF_BLOCKS
--------------- ---------- -----------
BOWIE_NAME_I             0           1

 

OK, as this is a simple blevel 0 index, all the contents of the index can be found in the block immediately following the index segment header.
 

SQL> select header_file, header_block from dba_segments where segment_name='BOWIE_NAME_I';
 
HEADER_FILE HEADER_BLOCK
----------- ------------
          8        84233

 

So the block we want to dump is the block in datafile 8, block 84234 (1 more than the segment header block). Before we dump the block though, let’s just note a few other points about this index. Firstly, given we know the datafile and block of interest, we can determine its relative data block address (rdba) by plugging in these details into the dbms_utility.make_data_block_address function:

SQL> select dbms_utility.make_data_block_address(8, 84234) from dual;
 
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(8,84234)
---------------------------------------------
                                     33638666

  
We’ll note the rdba of 33638666 for future reference. The other thing we’ll just record is the Object ID of the index:
 

SQL> select object_id, data_object_id from dba_objects where object_name = 'BOWIE_NAME_I';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    115205         115205

 

Again, we’ll note an Object_Id and Data_Object_Id of 115205 for future reference. Finally, I’m just going to flush the buffer cache to ensure the current contents of the block is written to disk. This is useful in 11g when the block dump differentiates between the block contents in the buffer cache and on disk.

SQL> alter system flush buffer_cache;
 
System altered.

 

OK, let’s now dump the block associated with this index leaf block:
 

SQL> alter system dump datafile 8 block 84234;
 
System altered.

 

Below is just the block header portion of the resultant block dump:
 

Start dump data blocks tsn: 8 file#:8 minblk 84234 maxblk 84234
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=33638666
BH (0x17BF3D8C) file#: 8 rdba: 0x0201490a (8/84234) class: 1 ba: 0x17A70000
  set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 115205 objn: 115205 tsn: 8 afn: 8
  hash: [0x292CCE8C,0x15BFB42C] lru: [0x167EA1EC,0x15BFB48C]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
  Buffer contents not dumped
Block dump from disk:
buffer tsn: 8 rdba: 0x0201490a (8/84234)
scn: 0x0000.02d11215 seq: 0x01 flg: 0x04 tail: 0x12150601
frmt: 0x02 chkval: 0x9ae6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0BF39A00 to 0x0BF3BA00
BF39A00 0000A206 0201490A 02D11215 04010000  […..I……….]
BF39A10 00009AE6 001A0002 0001C205 02D11214  […………….]
BF39A20 1FE80000 00021F02 00000000 00000000  […………….]
BF39A30 00000000 00000000 00000000 00000000  […………….]
BF39A40 00000000 0000FFFF 00000000 00000000  […………….]
 
… Snip rest of memory dump …
 

As I mentioned, the actual format and content of a block dump differs between releases and continually changes so your formatted block dump may differ somewhat. However, the main points which I’ll discuss should be found in most currently supported versions of Oracle. This post should only be considered as a basic introduction on the subject. More depth and details to come.

As this is an 11g block dump, the block header consists of three distinct sections:

1) Dump of the buffer cache details associated with the index block

2) Dump of index block from disk

3) Full raw hex dump of the associated block
 

The first thing to check is that one is looking at the correct block dump and that the correct segment block was indeed dumped. The start of the formated block dump states the details of the dumped block(s):
 
file#:8 minblk 84234 maxblk 84234
 
so indeed, we’re looking at the correct dump file. These details are also listed along with the hex representation of relative block address (rdba) information:
 
file#: 8 rdba: 0x0201490a (8/84234)
 
and we can also confirm that the rdba is also correct and consistent with the block we have dumped:
 
rdba=33638666
 
as this matches the results of the rdba from the rdba as previously displayed via the MAKE_DATA_BLOCK_ADDRESS function.
 
We can also confirm that the correct index segment was dumped as the object id of the index:
 
obj: 115205 objn: 115205
 
matches the data_object_id and object_id from dba_objects as listed previously.
 

Other details of interest found in block dumps in most Oracle versions include:
 
scn: 0x0000.02d11215 – The System Change Number (SCN) of the block when it was last modified (I’ll show you how this gets populated in a later post). This is the effective “timestamp” of the block and is used by Oracle to determine crucial information such as effectively “when” the block was last modified and by “what” transaction.
 
type: 0x06=trans data – denotes the type of block. 06 represents a transactional block (table/index/cluster)
 
seq: 0x01 – number of the block changes within the the current scn (we’ll see how this increments in a later post)
 
tail: 0x12150601 – which consists of the last 2 bytes of the SCN, type and seq
 
frmt: 0x02 – denotes the block format with 02 representing a post Oracle8 block format. An A2 block (as shown in the raw block dump) denotes a post 10g block format.
 
chkval: 0x9ae6 – checksum value of block as used by Oracle in part to check the consistency and validity of the block 
 

In later versions of Oracle (10g and beyond), the block dump includes a full hex dump of the associated memory buffer. I’ve snipped most of this in the above dump extract. However, one can see where the details of the block I’ve listed above can be found within the memory dump (hopefully, the colour code will help to highlight where each distinct piece of information can be found). Note also in 11g and beyond, more details of the buffer cache are listed and detailed as defined in the buffer cache section.
 
I’ll look at the following portion of the index block header, the Interested Transaction Slots, in the next post. We’ve only just begun …