{ 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.
Top comments (0)