1. Create a table gg_latency in source and target databases:
create table gg_latency ( extr varchar2(10), pump varchar2(10), repl varchar2(10), update_time date ); alter table gg_latency add constraint gg_latency_pk primary key(extr, pump, repl) using index;2. Create a procedure that is used to update the latency table:
create or replace procedure proc_update_gg_latency
is
begin
for rec in ( select * from gg_latency)
loop
update gg_latency set update_time=sysdate where extr=rec.extr and pump=rec.pump and repl = rec.repl;
commit;
end loop;
end;
/
3. Populate the table with every possible combination of the processing group names:For example, in my replication enviroment, at source I have three Extract groups, three Pump groups, at target I have 15 Replicat groups :
insert into gg_latency(extr, pump, repl) values('ECRUDR1', 'PCRUDR1', 'CRURDR1');
insert into gg_latency(extr, pump, repl) values('ECRUDR1', 'PCRUDR1', 'CRURDR1A');
insert into gg_latency(extr, pump, repl) values('ECRUDR1', 'PCRUDR1', 'CRURDR1B');
insert into gg_latency(extr, pump, repl) values('ECRUDR1', 'PCRUDR1', 'CRURDR1C');
insert into gg_latency(extr, pump, repl) values('ECRUDR1', 'PCRUDR1', 'CRURDR1D');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2A');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2B');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2C');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2D');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2F');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2G');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2H');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2M');
insert into gg_latency(extr, pump, repl) values('ECRUDR2', 'PCRUDR2', 'CRURDR2N');
insert into gg_latency(extr, pump, repl) values('ECRUDR3', 'PCRUDR3', 'CRURDR3');
4. For each EXTRACT group parameter file at source, add the TABLE clause with WHERE option for the GG_LATENCY table , e.g.TABLE DB_ADMIN.GG_LATENCY WHERE ( EXTR="ECRUDR1");Note: do this for all the EXTRACT groups
5. For each PUMP group parameter file at source, add the TABLE clause with WHERE option for the GG_LATENCY table , e.g.
TABLE DB_ADMIN.GG_LATENCY, WHERE (PUMP="PCRUDR1");Note: add the line before the PASSTHRU if exists, do this for all the PUMP groups
6. For each REPLICAT group parameter file at target, add MAP clause with WHERE option for the GG_LATENCY table , e.g.
MAP DB_ADMIN.GG_LATENCY, TARGET DB_ADMIN.GG_LATENCY, WHERE (REPL='CRURDR1');
Note: do this for all the REPLICAT groups. In 12c OGG, single quotation mark should be used for literal string.
7. Bounce all processes as parameter files are modified
8. Create a scheduler job to update the latency table every minute
begin
DBMS_SCHEDULER.create_job (
job_name => 'UPDATE_GG_LATENCY_TABLE',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN db_admin.proc_update_gg_latency; END;',
start_date => trunc(sysdate, 'HH24'),
repeat_interval => 'freq=minutely',
end_date => NULL,
enabled => TRUE
);
end;
/
9. Check latency by the following query at target:
SQL> select extr, pump, repl, update_time, round((sysdate - update_time) *24*60) latency_mins from gg_latency; EXTR PUMP REPL UPDATE_TIME LATENCY_MINS ---------- ---------- ---------- -------------------- ------------ ECRUDR1 PCRUDR1 CRURDR1D 14-Apr-2015 12:46:00 1 ECRUDR1 PCRUDR1 CRURDR1B 14-Apr-2015 12:46:00 1 ECRUDR1 PCRUDR1 CRURDR1A 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2D 14-Apr-2015 12:46:00 1 ECRUDR1 PCRUDR1 CRURDR1C 14-Apr-2015 12:46:00 1 ECRUDR1 PCRUDR1 CRURDR1 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2H 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2C 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2N 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2B 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2A 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2M 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2G 14-Apr-2015 12:46:00 1 ECRUDR2 PCRUDR2 CRURDR2F 14-Apr-2015 12:46:00 1 ECRUDR3 PCRUDR3 CRURDR3 14-Apr-2015 12:46:00 1 15 rows selected.Note: As we update every minute, the smallest unit for latency is a minute.
