.
\nIn RAC, blocks are copied across instances by the Global Cache Service. In single instance, we have only two status: CR for consistent read clones where undo is applied, and CUR for the current version that can be modified (then being a dirty block). I’ts a bit more complex in RAC. Here is a brief example to show the buffer status in Global Cache.<\/p>\n
I connect to one instance (I have a few singleton services. service ONE is on instance 3 and service TWO is on instance 1)<\/p>\n
SQL> connect demo\/demo@\/\/192.168.78.252\/ONE.racattack\nConnected.<\/code><\/pre>\nand I query a row by ROWID in order to read only one block<\/p>\n
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';\nold 1: select rowid,DEMO.* from DEMO where rowid='&rowid1'\nnew 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'\n\nROWID ID N\n------------------ ---------- ----------\nAAAXqxAALAAACUkAAD 10 10<\/code><\/pre>\nHere is the status of the buffer in the buffer cache:<\/p>\n
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id;\n\n INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N\n---------- ---------- ---------- ---------------- - - - - - -\n 3 1 scur 00000000B9FEA060 N N N N N N<\/code><\/pre>\nThe block has been read from disk by my instance. Without modification it is in SCUR status: it’s the current version of the block and can be shared.<\/p>\n
SCUR copies<\/h3>\n
Now connecting to another instance<\/p>\n
SQL> connect demo\/demo@\/\/192.168.78.252\/TWO.racattack\nConnected.<\/code><\/pre>\nand reading the same block<\/p>\n
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';\nold 1: select rowid,DEMO.* from DEMO where rowid='&rowid1'\nnew 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'\n\nROWID ID N\n------------------ ---------- ----------\nAAAXqxAALAAACUkAAD 10 10<\/code><\/pre>\nlet’s see what I have in my Global Cache:<\/p>\n
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;\n\n INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N\n---------- ---------- ---------- ---------------- - - - - - -\n 1 1 scur 00000000B0FAADC0 N N N N N N\n 3 1 scur 00000000B9FEA060 N N N N N N<\/code><\/pre>\nnon modified blocks can be shared: I have a copy on each instance.<\/p>\n
XCUR: exclusive current<\/h3>\n
I’ll start a new case, I flush the buffer cache<\/p>\n
connecting to the first instance<\/p>\n
SQL> connect demo\/demo@\/\/192.168.78.252\/ONE.racattack\nConnected.<\/code><\/pre>\nI’m now doing a modification with a select for update (which writes the lock in the block, so it’s a modification)<\/p>\n
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update;\nold 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update\nnew 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update\n\nROWID ID N\n------------------ ---------- ----------\nAAAXqxAALAAACUkAAD 10 10\nnow the status in buffer cache is different:\n\nSQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;\n\n INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N\n---------- ---------- ---------- ---------------- - - - - - -\n 3 1 cr 00 N N N N N N\n 3 1 xcur 00000000B9FEA060 Y N N N N N<\/code><\/pre>\nSo I have two buffers for the same block. The buffer that has been read and will not be current anymore because it has the rows before the modifications. It stays in consistent read (CR) status. The modified one is then the current one but cannot be shared: its the XCUR buffer where modifications will be done.<\/p>\n
CR consistent read<\/h3>\n
Now I’ll read it from the second instance<\/p>\n
SQL> connect demo\/demo@\/\/192.168.78.252\/TWO.racattack\nConnected.\nSQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';\nold 1: select rowid,DEMO.* from DEMO where rowid='&rowid1'\nnew 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'\n\nROWID ID N\n------------------ ---------- ----------\nAAAXqxAALAAACUkAAD 10 10<\/code><\/pre>\nthe block is read and I’ve another CR buffer:<\/p>\n
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;\n\n INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N\n---------- ---------- ---------- ---------------- - - - - - -\n 1 1 cr 00 N N N N N N\n 3 1 cr 00 N N N N N N\n 3 1 xcur 00000000B9FEA060 Y N N N N N<\/code><\/pre>\nthe CR buffer is at another SCN. A block can have several CR blocks (by default up to 6 per instance)<\/p>\n
PI: past image<\/h3>\n
Let’s do a modification from the other instance<\/p>\n
SQL> connect demo\/demo@\/\/192.168.78.252\/TWO.racattack\nConnected.\nSQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update;\nold 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update\nnew 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update\n\nROWID ID N\n------------------ ---------- ----------\nAAAXqxAALAAACUkAAD 10 10<\/code><\/pre>\nMy modification must be done on the current version, which must be shipped to my instance<\/p>\n
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;\n\n INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N\n---------- ---------- ---------- ---------------- - - - - - -\n 1 1 cr 00 N N N N N N\n 1 1 cr 00 N N N N N N\n 1 1 xcur 00000000B0FAADC0 Y N N N N N\n 3 1 cr 00 N N N N N N\n 3 1 pi 00000000B9FEA060 Y N N N N N<\/code><\/pre>\n