Back to Main

PDF VERSION

Oracle GoldenGate Row Comparison

Process and Alert

Author: Vladimir Grigorian

Review Date: June 20, 2019

Last Updated: June 21, 2019

Version: 2.0

Table of Contents

Document Control ………………………………………………………………………. 3

Introduction ……………………………………………………………………………… 4

Overview of GoldenGate Row Count Utility ……………………………….. 5

APPENDIX ……………………………………………………………………………….. 16

Document Control

Change Record

Date	Author	Version	Change Reference
March 10, 2019	Vladimir Grigorian	1.0	First Draft
March 20, 2019	Vladimir Grigorian	2.0	Changed scripts from 5 mins checks from cron to on-demand run.

Reviewers

Name

Position

Distribution

Copy No.	Name	Location
1
2
3

This publication may not be reproduced, stored in a retrieval system, or transmitted in whole or in part, in any form, or by any means, electronic, mechanical, photocopying, recording or otherwise, without the prior written permission of Cognizant/ Xerox Company.

Introduction

Purpose

The purpose of this document is document is to outline a procedure for comparing row counts in source and target in a GoldenGate replication.

Audience

This document is intended for:

· Oracle DBAs and GoldenGate Administrators.

Assumptions

None.

Overview of GoldenGate Configuration

The following outlines the intended setup for this configuration.

Table 1. Server Information (Due to Hardware Constraint this POC was executed on a Single Server)

	Source	Destination
Hostname	Oralinux6	Oralinux6
OS	3.8.13-55.1.2.el7uek.x86_64	3.8.13-55.1.2.el7uek.x86_64
GoldenGate Home	/u02/gg/source	/u02/gg/target
Oracle Instance	PBMP	STBMP
Oracle Version	12.1.0.2.0	12.1.0.2.0
GoldenGate version	11.2.1.0.27	11.2.1.0.27

Table 2. GoldenGate Processes Information

	Primary	Standby
Manager Port	7809	7810
DataPump	ESNDRT01	ESNDRT01
Failover Scripts in	/u02/gg/source/scripts	/u02/gg/source/scripts
Trails	/u02/gg/source/dirdat/s1	/u02/gg/source/dirdat/s1
GoldenGate Admin	GGATE	GGATE
User schemas	SENDER	SENDER

Fig 1. Regular DataGuard and GoldenGate Operation

1. GoldenGate Row Count Comparison Between Source and Target

A GoldenGate DBA / Admin sometimes needs an insight into the difference between the source and target, in an effort to determine if the replication is running smoothly. There are tools for this purpose, including Oracle’s own Veridata. But most of environments without unlimited licensing don’t have access to such an expensive software.

The following process runs a nightly job of comparing each table on the source with the target and at the end of the process it shoots out an email with a row comparison report.

The following shows the steps for creating the process.

Table 1. GoldenGate Row Comparison Process Creation Steps

Fig 2. GoldenGate Row Counts Process Actual Steps

2. Implementation

1. Stored Procedure in GGATE

create or replace procedure rowcounts(v_tgtschema in varchar2, v_srcschema in varchar2, v_dblink in varchar2)

is

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;

2. Create table in GGATE

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

)

;

3. Create dblink

create database link GGATECOUNTS connect to GGATE identified by password using 'SOURCE';

connect ggate/password@target

select * from ONETSTATS;

4. Create SQL script

masu2:/home/oracle/scripts/DBA> vi ggate_rowcounts.sql

truncate table ggate.onetstats;

exec ggate.rowcounts ('PDPROD', 'PDPROD', 'GGATECOUNTS');

set timing off time off veri off space 1 flush on pause off termout on numwidth 10

set echo off feedback off heading off pages 999 lines 200 newpage 1

set trimspool on trimout on

TTITLE CENTER -

"MA UAT GoldenGate MASU => MADWU PDPROD Table Row Count Difference"

SKIP 1 -

CENTER ==================== SKIP 1

spool $HOME/scripts/DBA/log/ggate_rowcounts.log

select SCHEMANAME || '.' || TABLENAME , (ROW_CNT_SOURCE - ROW_CNT_TARGET ) from ggate.onetstats;

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Report Created" FROM DUAL;

spool off

5. Create the KSH shell script, the wrapper

target:/home/oracle/scripts/DBA> more $HOME/scripts/DBA/ggate_rowcounts.ksh

#!/bin/ksh

#

# Compare GG rowcounts PRPROD user MASU MADWU

# written by: Vlad

#

process_name=MA_UAT_GOLDENGATE_ROWCOUNT

grpmail=grigorianvlad@yahoo.com

. $HOME/masu.env

rm $HOME/scripts/DBA/log/ggate_rowcounts.log

sqlplus -s / << EOF

set serveroutput on size unlimited

@/home/oracle/scripts/DBA/ggate_rowcounts.sql;

exit;

EOF

mailx -s "UAT MA GoldenGate Row Count Difference Source => Target on $BOX at $(date)" grigorianvlad@yahoo.com < $HOME/scripts/DBA/log/ggate_rowcounts.log

TTITLE CENTER 'MA UAT GoldenGate MASU => MADWU PDPROD Table Row Count Difference' SKIP 1 -

CENTER ==================== SKIP 1 LEFT 'Table' -

RIGHT 'Difference' SKIP 2

6. Implementing the Row Counts GoldenGate Emails Alerts

To test this functionality simply schedule the ksh wrapper to run at a desired time. Keep in mind for large tables and/or a large number of tables the process may take hours or longer. It actually selects count of actual rows, so it is also resource intensive, so schedule it after business hours. A simple email alert shown below.

END OF DOCUMENT