Counting the many rows of Oracle GoldenGate

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

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)
is
—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);
begin
 for vtable
 in (select table_name
     from all_tables
     where owner = v_tgtschema
     order by 1)

loop

v_sqlstmt0 := 'select count(*) from '||v_tgtschema||'.'||vtable.table_name;
 --dbms_output.put_line(v_sqlstmt0);
 execute immediate v_sqlstmt0 into v_tgtcount;

 v_sqlstmt1 := 'select count(*) from '||v_srcschema||'.'||vtable.table_name||'@'||v_dblink;
 --dbms_output.put_line(v_sqlstmt1);
 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';
 --dbms_output.put_line(v_sqlstmt2);
 execute immediate v_sqlstmt2;

 if (sql%notfound)
 then
     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;

 commit;
 end loop;
 exception
       when others
       then
              dbms_output.put_line(sqlerrm);
end;

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.

Enjoy!!

twitter: @dbasolved

blog: http://dbasolved.com

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

About these ads

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,457 other followers

%d bloggers like this: