OCI - Monitoring Tables for Oracle Golden Gate Service Replicats ( works for On Prem Golden Gate as well )

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

Many a time as Oracle Golden Gate Engineers/Admins I see people are bit too focussed on the internals of Extracts and Replicats, it's good to work with Sys Admins and MOS to tune it better those lines , but it does not help working with the people owning the data.

For many of my business use cases people are just interested in a handful of tables on how they are replicating ,
i.e. I get to handle questions below

Is my Latest Sales Data being replicated ?
Are the data for my Quarterly Reports ready ?
Is my data in partitions being replicated for a specific table ?
I did a large insert do you see it ?
I had to archive large data set to anorther table , did that complete ?

These questions are more important to them , I cannot go about answering internals of Golden Gate (like lag /checkpoint) to people who are not interested in them. Each are focussed in what their job needs are.

Explaining the End users of apps like BI , Tableau , they have no understanding of the replications and rightly so , all they need to know is " DO I HAVE MY DATA " ?

To help my users I have come up with a small script , basically the least looked upon sys table called "dba_tab_modifications"

Below is the source code of the script in my GitHub Repo

https://github.com/abhilash-8/ora-tools/blob/master/gg_mon.sql

The below example shows the insert , updates , deletes of tables and partitions , these would help identify which set of tables and partitions are being replicated. I added a LAG column which essentially indicates the data was replicated last 8 minutes ago.

Such analysis would help an OGG Admin understand the business nature of the Apps and work with bussiness users better.

orcl> @gg_mon
  1  select * from
  2  (
  3  select
  4  table_owner,table_name,partition_name,inserts,updates,deletes,truncated TRUNC,
  5  timestamp,round((sysdate-timestamp)*1440) LAG_MINS
  6  from dba_tab_modifications where table_owner in
  7  (
  8  'BUSS_USER',
  9  'MOBI_USER'
 27  )
 28  and timestamp > sysdate-(1/24)
 29  order by TIMESTAMP
 30  );


TABLE_OWNER         |TABLE_NAME                            |PARTITION_NAME                     |    INSERTS|    UPDATES|    DELETES|TRU|TIMESTAMP           |  LAG_MINS
--------------------|--------------------------------------|-----------------------------------|-----------|-----------|-----------|---|--------------------|----------
BUSS_USER           |SAMPLING_DATA                         |                                   |    2501779|    1705168|       2282|NO |29-APR-2023 13:20:29|         8
BUSS_USER           |SEC_DATA                              |                                   |        112|          0|          0|NO |29-APR-2023 13:20:29|         8
MOBI_USER           |SALES_DATA                            |APR_2023_PART                      |    3088793|    2310528|      50940|NO |29-APR-2023 13:20:29|         8

NOTE : The data in timestamp column in dba_tab_modifications would be populated due to the auto stats functionality to help get the required objective.