OCI - Monitoring Tables for Oracle Golden Gate Service Replicats ( works for On Prem Golden Gate as well )
Published: 2023-04-29 10:36:15



[](
      <style>
        .libutton {
          display: flex;
          flex-direction: column;
          justify-content: center;
          padding: 7px;
          text-align: center;
          outline: none;
          text-decoration: none !important;
          color: #ffffff !important;
          width: 200px;
          height: 32px;
          border-radius: 16px;
          background-color: #0A66C2;
          font-family: "SF Pro Text", Helvetica, sans-serif;
        { Abhilash Kumar Bhattaram : </style> <a class="libutton" href="https://www.linkedin.com/comm/mynetwork/discovery-see-all?usecase=PEOPLE_FOLLOWS&followMember=abhilash-kumar-85b92918" target="_blank">Follow on LinkedIn</a>) }

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.


# #oracle #oci #oracledatabase #goldengate #oraclecloud #oracledba #nabhaas