Monday, November 9, 2015

Goldengate replicat waiting for commit


Scenario: Replicat is struck on database and have around 17 hour lag, it is outage on our environment :) & data is out of sync. 

REPLICAT    RUNNING     RWTEMP        0:42:30      17:34:02

On Initial investigation 
Check for blocking sessions on  oracle database 
Check for long running transaction on oracle database
Check for Replicat is moving on the trail using "info processname" command 
Check Trail is healthy using log dump utility
check for abended process ( extract or pump ) associated with the replicat 

performed all steps during initial investigation, I found pump which is writing to target server is abended and after when i started this pump, lag is cleared on the replicat 

Finally concluded, if there is any opened transaction on the replicat, replicat is waiting for commit, which created lag on the replicat 

How to check open transaction on oracle database? 

SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1;

----Nikhil Tatineni---
----Oracle in memory----

Querys to monitor RAC

following few  Query's will help to find out culprits-  Query to check long running transaction from last 8 hours  Col Sid Fo...