5 Rules You Need to Know When Planning an Oracle GoldenGate Replication

When start working on an Oracle GoldenGate project, my first question is: "Is there any basic rules for implementing a good solution?" I think you might have the same question as I did.  Here are some of them which I summarized so far. 

1. Grouping Tables into Different Extract Groups

2. Using Oracle GoldenGate Macros

This rule talks about how to define your extract groups.  

• For tables with low activity rates, you should  group under one extract group.
  
• For tables with high activity rates, you should configure them  with their own extract group.

The purpose is to balance the work load across all the extract group processes. 

• For datatypes requires FETCHCOLS operations,  you should avoid mixing it with extract group where majority of datatypes don't need FETCHCOLS. 

This is because FETCHCOLS requires DB query connections, which can slow down the whole extract process. 

Oracle GoldenGate allows you to define macros in macro files such as follows:

teradata_instance_1.mac: 
MACRO  #odbc_connect
BEGIN 
  sourcedb TDINST1, useridalias ogguser 
END;

This allows you to externalize common parameters that might be used by multiple extract/replicat groups.  In production environment, you will have multiple and even hundreds of extract/replicat groups. Externalizing the common setup in macro file is a good practice. 

3. Using Oracle GoldenGate Pump

If Oracle GoldenGate on the source doesn't have the access to the target database, you need to create a data pump process with the extract group. This is to ensure a communication failure such as network connection issues with the target system doesn't not result in a data capture failure.  An example, a data pump setup is shown as follows: 

EXTRACT EXEPUMP
PASSTHRU
RMTHOST adc18739.us.oracle.com, MGRPORT 7809, COMPRESS 
RMTTRAIL ./dirdat/pe
TABLE *;
In general, you will always use the parameter PASSTHRU for the best performance. This setup allows data pump to move the data without accessing the database or interpreting the incoming data. 

4. Thinking of Using DEFGEN Files 

When replication cross-databases,  there are always differences between the source and the target, then the defgen file is needed to pass from the source to the target. This file contains information about the source data; table name, column names, data types, data length, offset, etc, which are needed by replicat to perform transformation. It's a very common ignorance. So, please always check if defgen is needed.

5. Checking Primary Keys

Oracle GoldenGate by default uses primary key to compare record with the target before apply the change. If there is no primary key then all the columns will be used for the comparison. It's a good practice to specify key columns using KEYCOLS in this case to void using all columns. 

Hope these rules are helpful and can save time for your next project.