RAC: How data blocks are loaded into the buffer cache

In this scenario, we will demonstrate how buffer cache is populated by data blocks in a RAC environment.
The following information was copied by Oracle9i Real Application Clusters Concepts Guide and describes the states of a block, when loaded into the buffer cache. It's strange, this information was removed from 10g/11g documentation:

Block Access Modes and Buffer States
An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance.
The buffer state of a block relates to the access mode of the block.
For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.
To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view.
The V$BH view provides information about the block access mode and their buffer state names as follows:

* With a block access mode of NULL the buffer state name is CR:
An instance can perform a consistent read of the block.
That is, if the instance holds an older version of the data.
* With a block access mode of S the buffer state name is SCUR:
An instance has shared access to the block and can only perform reads.
* With a block access mode of X the buffer state name is XCUR:
An instance has exclusive access to the block and can modify it.
* With a block access mode of NULL the buffer state name is PI:
An instance has made changes to the block but retains copies of it as past images to record its state before changes.

Only the SCUR and PI buffer states are Real Application Clusters-specific.
There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time.
To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.
For example, if another instance requests read access to the most current version of the same block,
then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance
and keeps a PI buffer if the buffer contained a dirty block.
At this point, the first instance has the current block and the requesting instance also has the current block in shared mode.
Therefore, the role of the resource becomes global.
There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.

First, we create a new LMT/ASSM tablespace:

SQL> CREATE TABLESPACE HELPDESK2 DATAFILE 
  '/oradb/helpdesk2_01.dbf' SIZE 5M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Tablespace created.

SQL> ALTER USER HELPDESK QUOTA UNLIMITED ON HELPDESK2;
User altered.

We connect to Instance 1 and create a new table:

1> CREATE TABLE HELPDESK.TESTTBL (ID NUMBER, TEXT VARCHAR2(100)) TABLESPACE HELPDESK2;
Table created.

We query DBA_EXTENTS:

SELECT *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME='TESTTBL'
AND OWNER='HELPDESK';

   

OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BYTES	BLOCKS	RELATIVE_FNO
HELPDESK	TESTTBL		TABLE	HELPDESK2	0	2483	9	65536	8	437

LMT/ASSM tablespaces have a minimum extent size of 64K, that's why table's initial size is 64K.
Since tablespace's block size is 8K, table's initial extent occupies 8 blocks and since the starting block's ID is 9, it occupies blocks 9 to 16.
Are all 8 blocks free?
Let's find out:

SQL> SET SERVEROUTPUT ON

DECLARE
        V_OWNER  DBA_SEGMENTS.OWNER%TYPE;
        V_NAME   DBA_SEGMENTS.SEGMENT_NAME%TYPE;
        V_TYPE   DBA_SEGMENTS.SEGMENT_TYPE%TYPE;
        V_TBL    NUMBER;
        V_TBB    NUMBER;
        V_UBL    NUMBER;
        V_UBB    NUMBER;
        V_LUFID  NUMBER;
        V_LUBID  NUMBER;
        V_LUB    NUMBER;
BEGIN
        V_OWNER := 'HELPDESK';
        V_NAME := 'TESTTBL';
        V_TYPE := 'TABLE';
        DBMS_SPACE.UNUSED_SPACE (V_OWNER, V_NAME, V_TYPE, V_TBL, V_TBB, V_UBL, V_UBB, V_LUFID, V_LUBID, V_LUB);
        DBMS_OUTPUT.PUT_LINE ('OWNER: ' || V_OWNER);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT NAME: ' || V_NAME);
        DBMS_OUTPUT.PUT_LINE ('SEGMENT TYPE: ' || V_TYPE);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BLOCKS: ' || V_TBL);
        DBMS_OUTPUT.PUT_LINE ('TOTAL BYTES: ' || V_TBB);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BLOCKS: ' || V_UBL);
        DBMS_OUTPUT.PUT_LINE ('UNUSED BYTES: ' || V_UBB);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT FILE ID: ' || V_LUFID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT BLOCK ID: ' || V_LUBID);
        DBMS_OUTPUT.PUT_LINE ('LAST USED BLOCK: ' || V_LUB);
END;
/
 
OWNER: HELPDESK
SEGMENT NAME: TESTTBL
SEGMENT TYPE: TABLE
TOTAL BLOCKS: 8
TOTAL BYTES: 65536
UNUSED BLOCKS: 5
UNUSED BYTES: 40960
LAST USED EXTENT FILE ID: 2483
LAST USED EXTENT BLOCK ID: 9
LAST USED BLOCK: 3

PL/SQL procedure successfully completed.

No, only 5 blocks are unused.
Now, let's see what was loaded into the buffer caches, when we created the table:

SELECT A.INST_ID,
         C.TABLESPACE_NAME,
         C.FILE_NAME,
         A.BLOCK#,
         B.OWNER,
         B.OBJECT_NAME,
         B.SUBOBJECT_NAME,
         B.OBJECT_TYPE,
         DECODE (A.CLASS#,
                    1, 'data block',
                    2, 'sort block',
                    3, 'save undo block',
                    4, 'segment header',
                    5, 'save undo header',
                    6, 'free list',
                    7, 'extent map',
                    8, '1st level bmb',
                    9, '2nd level bmb',
                    10, '3rd level bmb',
                    11, 'bitmap block',
                    12, 'bitmap index block',
                    13, 'file header block',
                    14, 'unused',
                    15, 'system undo header',
                    16, 'system undo block',
                    17, 'undo header',
                    18, 'undo block',
                    A.CLASS#)
             CLASS,
         DECODE (A.STATUS,
                    'free', 'Not currently in use',
                    'xcur', 'Exclusive',
                    'scur', 'Shared current',
                    'cr', 'Consistent read',
                    'read', 'Being read from disk',
                    'mrec', 'In media recovery mode',
                    'irec', 'In instance recovery mode',
                    A.STATUS)
             STATUS
  FROM GV$BH A, DBA_OBJECTS B, DBA_DATA_FILES C
 WHERE A.OBJD = B.DATA_OBJECT_ID
AND A.FILE# = C.FILE_ID
AND B.OWNER='HELPDESK'
AND B.OBJECT_NAME='TESTTBL'
ORDER BY 4, 1;

   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Exclusive

So, TESTTBL's blocks 9 (1st level bitmap block), 10 (2nd level bitmap block) and 11(segment header) were loaded into Instance 1's buffer cache.
These are the 3 used blocks. Let's take a segment header's (11) dump to verify it:

SQL> EXEC DBMS_SPACE_ADMIN.SEGMENT_DUMP('HELPDESK2','437','11');

PL/SQL procedure successfully completed.

We read in the generated trace file:

Segment Dump: segment dba = 644 : 0x6d40000b <--Block 11
Segment Type - Pagetable Segment
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x6d40000c <-- Highwater Mark at block 12   ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x6d400009
  Level 1 BMB for Low HWM block: 0x6d400009
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001438
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x6d40000a
  Last Level 1 BMB:  0x6d400009 <-- Block 9
  Last Level II BMB:  0x6d40000a <-- Block 10
  Last Level III BMB:  0x00000000 <-- There is no 3rd level bitmap block
     Map Header:: next  0x00000000  #extents: 1    obj#: 1667683 flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x6d400009  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x6d400009 Data dba:  0x6d40000c
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x6d40000a

Dump of Second Level Bitmap Block
   number: 1       nfree: 1       ffree: 0      pdba:     0x6d40000b
   Inc #: 0 Objd: 1667683 <-- TESTTBL's data_object_id
  opcode:0
 xid:
  L1 Ranges :
  --------------------------------------------------------
   0x6d400009  Free: 5 Inst: 1

  --------------------------------------------------------
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x6d40000a   poffset: 0
   unformatted: 5       total: 8         first useful block: 3 <-- 8 blocks total, 5 blocks unused
   owning instance : 1 <-- Instance 1 has the ownership
   instance ownership changed at
   Last successful Search
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 3
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 1667683
  HWM Flag: HWM Set
      Highwater::  0x6d40000c  ext#: 0      blk#: 3      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x6d400009  Length: 8      Offset: 0

   0:Metadata   1:Metadata   2:Metadata   3:unformatted
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
  --------------------------------------------------------

We insert 1 row from Instance 1:

1> INSERT INTO HELPDESK.TESTTBL VALUES(1, 'TEXT1');

1 row created.

All TESTTBL's blocks were loaded into Instance 1's buffer cache:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	13	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	14	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	15	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We commit:

1> COMMIT;
Commit complete.

Let's find out the block in which this row was stored:

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(A.ROWID) BLOCK#, A.*
FROM HELPDESK.TESTTBL A;

   

BLOCK#	ID	TEXT
16	1	TEXT1

The block where the row was stored (16) remained in exclusive (xcur) state and all other data blocks changed their state to shared current (scur):   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	13	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	14	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	15	HELPDESK	TESTTBL		TABLE	data block	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We insert one more row from Intance 2, this time, and we commit:

2> INSERT INTO HELPDESK.TESTTBL VALUES(2, 'TEXT2');
1 row created.

2> commit;
Commit complete.

This row was placed in block 12:

BLOCK#	ID	TEXT
12	2	TEXT2
16	1	TEXT1

Instance 1 kept an image of 1st level bmb (pi), Instance 2 got exclusive access (xcur) to 1st level bmb and 2nd level bmb, read (cr) the segment header and got exclusive access (xcur) to block 12, where the new row was stored:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive

We, now, delete the 2nd row from Instance 1:

1> DELETE FROM HELPDESK.TESTTBL WHERE ID=2;
1 row deleted.

1> COMMIT;
Commit complete.

Instance 1 got block 12 in exclusive (xcur) state:   

INST_ID	TABLESPACE_NAME	FILE_NAME	BLOCK#	OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	CLASS	STATUS
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	Exclusive
2	HELPDESK2	/oradb/helpdesk2_01.dbf	9	HELPDESK	TESTTBL		TABLE	1st level bmb	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	pi
2	HELPDESK2	/oradb/helpdesk2_01.dbf	10	HELPDESK	TESTTBL		TABLE	2nd level bmb	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
1	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Shared current
2	HELPDESK2	/oradb/helpdesk2_01.dbf	11	HELPDESK	TESTTBL		TABLE	segment header	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Exclusive
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Consistent read
1	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	Consistent read
2	HELPDESK2	/oradb/helpdesk2_01.dbf	12	HELPDESK	TESTTBL		TABLE	data block	pi
1	HELPDESK2	/oradb/helpdesk2_01.dbf	16	HELPDESK	TESTTBL		TABLE	data block	Exclusive