Understanding the RAC - Cache Fusion, GES, GRD and GCS Practically !!!

,One of the best document on  RAC Concepts that I came across is DemystifiyingCacheFusion  by Arup nanda .
As this document makes the concept more clear and provides script to see actually what happens in the Database.
Script Location: racsig_scripts.zip


The document didn't provide the order of execution of the scripts,I  figured out the order after few executions and here it is for all,



From Here on , Instance1 =I1   &  Instance2 =I2

1) Created a User sri/sri from I1 .  Check the same from I2 and Confirm.
2) Create a Table and populate it using "setup.sql" from I1 .
3) Get the object id of the table created using "dobjid.sql" .
4) run "sel.sql" in I1 .
5) Check the statistics using  "segstat.sql" in I1. (Use Data object get from step3),
STATISTIC_NAME                VALUE
-------------------------   --------
logical reads                    80
physical writes                   8
physical write requests           8
space allocated               65536
6)Now run "sel.sql" in I2" 
7) Check the statistics using  "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME                     VALUE
-----------------------------    --------
logical reads                         32
gc cr blocks received                 12
gc current blocks received             6
Note the difference between the two statistics, this shows that the I2 got the block from I1 through Cache fusion and no Physical reads.


8) Run "flush.sql" in I1. and run "sel.sql" in I2.
9)  Check the statistics using  "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME                   VALUE
------------------------------      -------
logical reads                        64
physical reads                        6
physical read requests                2
gc cr blocks received                12
gc current blocks received            6
Now we can see , since the cache was cleared the I2 read the blocks from the Disk.
10) Run the "block.sql" to find the block associated with the table :
 COL1      FILE#     BLOCK#
---------- ---------- ----------
1          4        179
2          4        179
3          4        179
4          4        179
5          4        179
6          4        179
11) To see the CR and SCUR :
Flush the cache and run a checkpoint in both the nodes .
alter system flush buffer_cache;
alter system checkpoint;

update the table from I1 using "upd.sql".
Check the lock status of the block using "vbh.sql" (edit the object Id if needed).
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         free       00
4        179 data block         xcur       3D7E3FE4
4        179 data block         cr         00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00


This shows that the cr and Xcur. cr -> Consistent read , xcur --> exclusive Current.


And check the lock status from I2 -> "vbh.sql"
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
12) Run select * from racsig :   from I2, and check the block status "vbh.sql"
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         cr    00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
4        179 data block         free       00
13) The master node of a block can be obtained using "check_master.sql "
DBABLK MASTER_NODE
---------- -----------
  178           1
  183           1
  180           1
  182           1
  179           1  ================(racsig table)
  181           1
14) Do an update of a column in I2 and select the same row in I1. Now check the block status (vbh.sql)in I2,

FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         scur       433EA63C
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
15) do a commit on both Instances. and in I1 run
update racsig set col2='C';

on I2 check the block status,
FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         pi         433EA63C
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
Since we issued a commit (not a checkpoint) ,and I1 requested the same so I2 created a PI of the block and sent it to I1.

I1 --> vbh.sql


FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELE
---------- ---------- ------------------ ---------- --------
4        179 data block         cr         00
4        179 data block         xcur       3D7E3FE4
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00
4        179 data block         cr         00


16) Run : alter system checkpoint and see block status in I2 (after a checkpoint is issued the PI will be destroyed).

Note: All the .sql are in the racsig_scripts.zip .

Special thanks to Arup for this Document.

Check errors on Alert log based on Date.

This script is for the DBA whose alertlog is huge , may be a year old history in it.This script will report the errors that occurred yesterday.

Script :


Today=`date +"%a %b %d"`
Yesterday=`date +"%a %b %d" -d "YESTERDAY"`
sed -ne "/$Yesterday /,/$Today/p" alert_envrtl.log >alert_temp
cat alert_temp|egrep -i "ORA-|SP2-"

rm alert_temp

Also you can add other errors that you like to grep apart from ORA- & SP2-

Note : The alert log should not be older than one year.