Monitoring GoldenGate replicat performance using 'flashback query'

By using a checkpoint table in the OGG replicat database, you can retrospectively review the replicat's lag using flashback query (with some limitations if you do not utilise a heart beat table).

Picture the scenario. You receive observations that the Oracle GoldenGate (OGG) replicat system that you are responsible for had fallen behind overnight and all overnight processing was performed against incorrect data. What do you do? Perhaps the observation was erroneous. How can you demonstrate this?

set serveroutput on linesize 132 trimspool on

alter session set nls_date_format='yyyy-mm-dd hh24:mi';
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi';

declare
  cursor c_gg(ts timestamp) is
    select ts, group_name,
      last_update_ts, to_timestamp(audit_ts, 'yyyy-mm-dd hh24:mi:ss.ff') as audit_ts
    from ggs_admin.ggschkpt
    as of timestamp ts order by group_name;
  wts timestamp;
  lag interval day to second;
begin
  dbms_output.put_line('Time monitored   Replicat     Time updated     Time applied     Apply lag');
  dbms_output.put_line('----------------+------------+----------------+----------------+-------------------');
  wts := trunc(current_timestamp, 'hh24');
  for i in 1..24 loop
    for r_gg in c_gg(wts) loop
      lag := wts - r_gg.audit_ts;
      dbms_output.put_line(wts||' '||rpad(r_gg.group_name, 12)||' '||r_gg.last_update_ts||' '||r_gg.audit_ts||' '||lag);
    end loop;
    wts := wts - interval '1' hour;
  end loop;
end;
/

The output from the above PL/SQL can be easily converted into CSV format. From there it can be loaded into GNU Plot or a spreadsheet to produce a graphical representation of the lag for the replicats.

In the output sample below it can be seen that the RFDATA replicat has a lag of nearly twenty four hours. Running ggsci now does not reveal any lag for this replicat. This should be investigated further. The RBATCH replicat's lag is reported as being within expected tolerances.

...
2013-06-22 15:00 RFDATA       2013-06-22 15:15 2013-06-21 15:15 +00 23:44:56.573403
2013-06-22 15:00 RBATCH       2013-06-23 15:00 2013-06-22 14:59 +00 00:00:03.384317
2013-06-22 14:00 RFDATA       2013-06-22 15:15 2013-06-21 15:15 +00 22:44:56.573403
2013-06-22 14:00 RBATCH       2013-06-23 13:59 2013-06-22 13:59 +00 00:00:05.104662
...

It's up to you now, but at least you know if there really was a problem and you have somewhere to start the investigation.