Golden Gate activity auditing with SQL?

Question

In a scenario where Oracle Golden Gate is used to replicate a primary site with an Oracle RAC database to a secondary site (and active/active back) we suspect unexpected changes from the unused secondary site.

The issue is a bit hard to debug as we do not have direct DBA access. I wonder is there an easy way with unprivileged SQL access on the primary side to see if any changes are received from the other database?

Can I see counters or timestamps of OGG activity which helps me to track down DML made?

As I understand it I could see changes from the OGG user when setting up triggers or auditing - however both is not available in this situation.

Answer 1

Short answer is 'No', you can't reliably prove this without lower level details. Auditing and Triggers will work from the database layer. If you have access to the GoldenGate Trail files (files with all the extracted changes), you can see exactly what GoldenGate will replicate by using logdump (another binary that comes with OGG).

It sounds like you have bi-directional replication (active-active back). If so, your Extract process should be configured to ignore your OGG user, since you don't want to replicate changes back to the source.

Another thing to considers whether or not triggers are enabled on your unused/secondary site. I have seen weird things happen when triggers are not disabled on the receiving side (duplicated history records, unique key violations, etc).

Also, if your secondary site is unused, maybe DataGuard is a better solution. It all depends on your software and infrastructure architecture. It won't be a bi-directional solution, but arguably a simpler solution than OGG.