Index Block Dump: Block Header Part I (Editions Of You) July 20, 2010

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.

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:

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.

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.

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 …