Friday, November 13, 2009

Using x$logbuf_readhist to check for Dataguard Transport Lag

From the Oracle 11g Dataguard Handbook :-

Extract here :- http://www.mhprofessional.com/downloads/products/0071621113/CarpenterCh1.pdf

They say that if the LNS process cannot keep up with the LGWR process, then it switches from log buffer to online log, which introduces a transport lag. In 11g this can be detected by selecting from the x$logbuf_readhist;

>desc x$logbuf_readhist
Name Null? Type
----------------------------------------- --------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
BUFSIZE VARCHAR2(20)
RDMEMBLKS VARCHAR2(24)
RDDISKBLKS VARCHAR2(24)
HITRATE NUMBER
BUFINFO VARCHAR2(20)

select bufsize, rdmemblks, rddiskblks, hitrate from x$logbuf_readhist;

BUFSIZE RDMEMBLKS RDDISKBLKS HITRATE
-------------------- ------------------------ ------------------------ ----------
2665K 484903388 42044064 92
3197K 506308671 20638781 96
3730K 508354202 18593250 96
4263K 512373070 14574382 97
4796K 518101523 8845929 98
5329K 523136015 3811437 99
5862K 523347898 3599554 99
6395K 523556441 3391011 99
6928K 523634012 3313440 99
7461K 523704211 3243241 99
7994K 523734931 3212521 99
8526K 523771795 3175657 99
9059K 523792275 3155177 99
9592K 523808659 3138793 99
10125K 523818899 3128553 99
10658K 523822995 3124457 99