A colleague had a requirement to replicate rows from a table only when a column was updated to a specific value and came up with this solution. The table in question was a configuration table which contained parameters and values that was loaded in advance of code deployment. After the code was deployed, the parameter was activated by updating the column REPLICATE_FLAG to a value of “y”.
On the source side, the table is defined in the .prm file as follows:
TABLE TABLE_OWNER.CONFIG_TABLE, WHERE (REPLICATE_FLAG = 'y' OR REPLICATE ='Y');
This causes GoldenGate to replicate only after the row is updated to a “y”. On the target side, the row can either exist in which case GoldenGate performs an update or the row does not exist and GoldenGate performs an insert. In order to handle both conditions, the *.prm file has the parameter insertmissingupdates.
insertmissingupdates MAP TABLE_OWNER_PROD.CONFIG_TABLE , TARGET TABLE_OWNER_TARGET.CONFIG_TABLE; noinsertupdates MAP TABLE_OWNER_PROD.OTHER_TABLE , TARGET TABLE_OWNER_TARGET.OTHER_TABLE
Since you do not want this parameter (i.e. missing updates converted to inserts behavior) to be applied to other tables being replicated, you can either turn it off with the noinsertupdates or put the table that needs the special handling at the end.
This was tested on Oracle GoldenGate Version 19.1.0.0.4 and Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
