Day Two of the GoldenGate Proof of Concept delved into the mechanics of the technology and looked at some of the pitfalls of using an advanced replication system in the real world.
The second day of Oracle GoldenGate got under way with an interesting discussion about how ODI and GoldenGate can work together for data warehouses with real-time data requirements, something that may be of use for an upcoming data integration project. The actual work of the day started off by looking at how to handle automated maintenance asks within the database, particularly those introduced by an application vendor. Those housekeeping jobs can modify data and so a strategy for ensuring changes only get applied on one side of the replication has to be implemented (one suggestion was to allow the maintenance to happen on the source system and push the changes over to the target as opposed to allowing the jobs to run on both sides and thus hit problems acting on data on the target that has already been modified).
A similar problem, but one with potentially far-reaching consequences, is how to deal with triggers. The trouble with GoldenGate and triggers is that an insert or update can fire a trigger which in turn performs an insert or update. GoldenGate comes along and replicates the first insert or update to the target as well as the second insert or update (the one performed by the trigger). However, the first replicated insert or update causes the same trigger to fire on the target database, trying to modify a row already replicated by GoldenGate. The system is wise to this problem though and includes the SUPPRESS TRIGGER option to avoid such complications.
With the restore to the target database complete we looked into activating GoldenGate against our test environment (using copies of the production database in order to keep things as close to the real world as possible). With GoldenGate running, we started up one of our applications and watched what happened.
What happened was it errored out almost immediately and the afternoon was spent hunting down the cause, identifying how best to handle the problem, and resetting the system for another go.
The problem we encountered was relating to the database activity that occurs when the application starts up. The application performs a series of truncate table commands and then inserts data into those tables. GoldenGate is not configured to handle DDL statements by default so those truncates weren’t captured so the insert on the target tried to write into tables that already contained those rows. Once this was figured out it was obvious, but it took some time to wrap our heads around how data could possibly already exist on the target side seeing as how it had only just been generated on the source!
As we worked through the problem we got into some of the parameters that can be used to deal with this type of issue including the HANDLE COLLISIONS parameter (though you need to be careful with this parameter as it could result in data loss if you don’t deal with duplicated data correctly).
Another neat function of GoldenGate that interested everyone was the ability to set a point from which the delivery process reads from within a specified Trail File. Using the RBA and Trail sequence number, it is possible to begin the delivering data into the target database from a certain point within a certain trail file forward, enabling unnecessary or troublesome data to be circumvented if required.
Tomorrow is the last day of the P.O.C. but already ideas are flowing as to how best we can utilise GoldenGate. For me, a surprisingly welcome side effect of the P.O.C. is the level of insight I’m getting into our application layer on top of the chance to learn something as powerful as GoldenGate!
Martin Minnock 