Thinking Out Loud

February 11, 2018

Goldengate REPORTING

Filed under: GoldenGate — mdinh @ 2:52 pm

There were performance issues due to a new table being introduced to replication and I was asked to gather number of DMLs on table for 1 day.

Using DBA_TAB_MODIFICATIONS did not meet the requirements since statistics were gather about a week ago and over inflated.

Next thought process was why not use Goldengate since it captures all the changes and report on it.

This may or may not provide the required data if reporting is not properly configured.

Some will tell you to never rollover the report while others will rollover the report on a weekly basis.

From most recent experience, I would rollover report on daily basis because data can always be aggregated.

Here is an example of what I had to deal with.

$ grep -i report dirprm/e_hawk.prm

REPORTCOUNT EVERY 10 MINUTES, RATE

$ grep “activity since” dirtpt/E*.rpt|sort

E_HAWK0.rpt:Report at 2018-02-09 12:28:49 (activity since 2018-02-08 22:01:15)
--- How are you going to get daily activities accurately from aggregation over 2 months time frame?
E_HAWK1.rpt:Report at 2018-02-08 21:48:37 (activity since 2017-11-27 18:24:26)

Corresponding data from the report.
$ grep -A5 “From Table SCOTT.RATETEST” dirrpt/E_*rpt

E_HAWK0.rpt:Report at 2018-02-09 12:28:49 (activity since 2018-02-08 22:01:15)
dirrpt/E_HAWK0.rpt:From Table SCOTT.RATETEST:
dirrpt/E_HAWK0.rpt-       #                   inserts:       977
dirrpt/E_HAWK0.rpt-       #                   updates:  10439912
dirrpt/E_HAWK0.rpt-       #                   befores:  10439912
dirrpt/E_HAWK0.rpt-       #                   deletes:         0
dirrpt/E_HAWK0.rpt-       #                  discards:         0
--
E_HAWK1.rpt:Report at 2018-02-08 21:48:37 (activity since 2017-11-27 18:24:26)
dirrpt/E_HAWK1.rpt:From Table SCOTT.RATETEST:
dirrpt/E_HAWK1.rpt-       #                   inserts:     87063
dirrpt/E_HAWK1.rpt-       #                   updates: 821912582
dirrpt/E_HAWK1.rpt-       #                   befores: 821912582
dirrpt/E_HAWK1.rpt-       #                   deletes:         0
dirrpt/E_HAWK1.rpt-       #                  discards:         0

How did I end up getting the data?

From Goldengate report above (E_HAWK0.rpt) which is better as it provides the exact data processed by Goldengate vs trying to extract data from database.

I was lucky! Extract was restarted when table was added and removed.

Example of Better Configuration:

global_ggenv.inc

STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 60 MINUTES, RATE
DISCARDROLLOVER AT 00:01

e_hawk.prm
EXTRACT E_HAWK

EXTRACT e_hawk
EXTTRAIL ./dirdat/bb
-- CHECKPARAMS
INCLUDE ./dirprm/global_macro.inc
INCLUDE ./dirprm/global_dbenv.inc
INCLUDE ./dirprm/global_ggenv.inc

Reference:

REPORTROLLOVER

Use the REPORTROLLOVER parameter to force report files to age on a regular schedule, instead of when a process starts. 
For long or continuous runs, setting an aging schedule controls the size of the active report file and provides a more predictable set of archives that can be included in your archiving routine.
Report statistics are carried over from one report to the other. To reset the statistics in the new report, use the STATOPTIONS parameter with the RESETREPORTSTATS option.

DBA_TAB_MODIFICATIONS

INSERTS/UPDATES/DELETES - Approximate number since the last time statistics were gathered.
Advertisements

1 Comment »

  1. […] Previous post for Goldengate REPORTING […]

    Pingback by Goldengate REPORTING P2 | Thinking Out Loud — May 15, 2018 @ 2:36 am | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

%d bloggers like this: