Counting the many rows of Oracle GoldenGate

!The code contained in this post is meant to be  used at your own risk!

With any Oracle GoldenGate replication configuration, it is always good to monitor what is going on.  One aspect that many people want to know is how can I validate that every record is being transferred to the target system.  Once such way is to use Oracle Veridata; however, some times there is not enough time to setup and run Oracle Veridata.  Also in smaller shops, Oracle Veridata may be a bit costly to initially start with. How can someone get the “warm-and-fuzzy” feeling when replicating data with Oracle GoldenGate?
Oracle GoldenGate has a lot of good command that can be used from the GGSCI prompt to check and see what type of transactions have been processed and the totals of those transactions.  What I was recently tasked with was a way to quickly do a validation of the rows between source and target within a Oracle GoldenGate configuration.  One way to do this is to quickly get a count of the rows per table between the source and target systems.  In discussions with a co-worker, it came out that they had a small utility, primarily scripts, that could do the counts between source and target systems.  In reviewing these scripts I saw where I could possibly improve on the process and make it a bit more streamlined and integrated with the target database.
In streamlining the process I decided to take the main portions of the scripts and rewrite it into a stored procedure that could be use from the Oracle GoldenGate user inside the target database of the replication environment.  The initial stored procedure I came up with can be seen in Code 1 below.
Code 1: Stored procedure for counts

create or replace procedure rowcounts(v_tgtschema in varchar2, v_srcschema in varchar2, v_dblink in varchar2)
—Author: Bobby Curtis, Oracle ACE
—Copyright: 20014
—Company: Accenture Enkitec Group
v_tgtcount number(16) := 0;
v_srccount number(16) := 0;
v_sqlstmt0 varchar2(1000);
v_sqlstmt1 varchar2(1000);
v_sqlstmt2 varchar2(1000);
 for vtable
 in (select table_name
     from all_tables
     where owner = v_tgtschema
     order by 1)
v_sqlstmt0 := 'select count(*) from '||v_tgtschema||'.'||vtable.table_name;
 execute immediate v_sqlstmt0 into v_tgtcount;
 v_sqlstmt1 := 'select count(*) from '||v_srcschema||'.'||vtable.table_name||'@'||v_dblink;
 execute immediate v_sqlstmt1 into v_srccount;
v_sqlstmt2 := 'update onetstats set row_cnt_source='|| v_srccount ||', row_cnt_target=' || v_tgtcount || ', end_time=sysdate where schemaname='''||v_tgtschema||''' and tablename='''||vtable.table_name||''' and dataset=null';
 execute immediate v_sqlstmt2;
 if (sql%notfound)
     v_sqlstmt2 := 'insert into onetstats (schemaname,tablename,start_time,end_time,row_cnt_source,row_cnt_target,dataset) values ('''||v_tgtschema||''','''||vtable.table_name||''',sysdate,sysdate,' || v_srccount || ',' || v_tgtcount || ', null)';
    --dbms_output.put_line (v_sqlstmt2);
     execute immediate v_sqlstmt2;
 end if;
 end loop;
       when others

As you can tell from looking at the stored procedure it uses a table to store the counts for each table in the schema being replicated.  Also notice that a database link is used to access the source server.  The table that stores the count information is just a really simple table with columns that maps to the update/insert statement in the stored procedure.  The database link needs to be configured in the local TNSNames.ora on the target server.  Code 2 and code 3 show an example of these objects.
Code 2: Table for counts

create table &ggate_user..onetstats (
 schemaname varchar2(30),
 tablename varchar2(30),
 start_time date,
 end_time date,
 row_cnt_source number,
 row_cnt_target number,
 dataset number

Code 3: Database Link to source

create database link ggcounts connect to &&ggate_user identified by &ggate_user_pwd using 'ggcounts';

The last thing that needed to be done is granting SELECT ON <TABLE> to the Oracle GoldenGate user on the source and target systems.  Once this is done, the stored procedure can be ran from SQL*Plus or SQL Developer at anytime on the target system to get a rough estimate count of the rows between the source and target databases.
twitter: @dbasolved

!The code contained in this post is meant to be  used at your own risk!

Please follow and like:

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.