This document describes how to convert a primary key update operation into a delete and insert operation based on the customer’s needs. This will be required where data is filtered and sent to different target locations based on the value of the primary key. This document is intended for Oracle Database Administrators (DBAs), Oracle Developers with some basic knowledge of Oracle GoldenGate software and Oracle GoldenGate Administrators. The document is intended to be a supplement to the existing series of documentation available from Oracle.
Consider a hub-and-spoke architecture, as shown, where Oracle GoldenGate is used to move real time data from the hub (“Server A”) to the spokes (“Target B”, “Target C”...), where the spokes are a subset of the Hub. A record created in A is sent to location B, C, D or E based on the value of the primary key. Assume a record with a key value of B is created which gets inserted in Target B. The primary key of this record is updated in A and the new value points to Target C. With normal processing this update will be send to location C, where the update operation will fail (with Oracle error ORA-1403) as the original record is not there. This record should be sent to locations B and C. On target B, the update operation should be changed into a delete operation of the old record. On target C, the update operation should be changed into an insert operation for the new record. How to achieve this using Oracle GoldenGate is described below.
The complete document can be found on the Oracle Support site under the document ID:1632428.1