Category: GoldenGate

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

If you are running GoldenGate in a RAC environment, there may be occasions where you have to relocate to a different node. An example of this may be an OS upgrade that is being rolled across the RAC nodes one at a time. The below MS Word Document contains the steps to relocate the GoldenGate process.

ORA Relocating GG

The below MS Word Document contains the steps to skip a GoldenGate transaction.
ORA Skipping transaction in GG

The GoldenGate data pump was abending on the source:

GGSCI (uiiompd17) 1> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                           
EXTRACT     ABENDED     DPUMPxx     00:00:00      04:00:29    
EXTRACT     RUNNING     EXAxxxx     00:00:00      00:00:02    

The GoldenGate error log contained the below message:

2016-12-17 07:22:13  ERROR   OGG-01031  Oracle GoldenGate Capture for Oracle, dpumprp.prm:  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "/ggs//dirdat/rp013263" (error 11, Resource temporarily unavailable)).

The file directory that was being reported was for the target of the GoldenGate process. On the target side, the file did exist and there were no issues with the file system, mount point etc. I moved the file as follows:

mv /ggs//dirdat/rp013263 /ggs//dirdat/rp013263.old

I then restarted the abended data pump and normal processing was resumed.

This is a short presentation on the operational commands used to support GoldenGate replication from DB2 on z/OS to Oracle on Linux. Click on the link below to open a MS Power Point presentation.

oracle-goldengate-on-zos

The application I support replicates a sub-set of the tables in the production environment via GoldenGate to a reporting database. Below are the steps to add an existing production table (with data) to the replication process without taking an outage. Continue reading “Adding an existing table to replication without taking an outage”