Absolutely! Oracle GoldenGate is fully equipped to handle this exact kind of cross-table data consolidation and synchronization between Oracle and MySQL. Let me walk you through the practical steps and key considerations to make this work smoothly:

Core Strategy #

We’ll leverage GoldenGate’s data transformation and lookup capabilities to join the Service and Subscription tables from Oracle, then map the combined data to the single Subscription table in MySQL. You can handle the join either on the source side (via a pre-computed materialized view) or target side (via Replicat lookup logic)—I’ll cover both options to fit your needs.

1. Set Up Source Extract on Oracle #

First, create an Extract process to capture changes from both Oracle tables. Here’s a sample parameter file:

EXTRACT EORA_SUBS
USERIDALIAS ORA_ADMIN DOMAIN GG_DOMAIN
EXTTRAIL ./dirdat/es
TABLE YOUR_ORACLE_SCHEMA.SERVICE;
TABLE YOUR_ORACLE_SCHEMA.SUBSCRIPTION;

Replace YOUR_ORACLE_SCHEMA, ORA_ADMIN, and GG_DOMAIN with your actual Oracle schema name, GoldenGate user alias, and domain.

2. Add a Data Pump (For Reliability) #

It’s best practice to use a Data Pump process to replicate trail files to the target server (or intermediate node) instead of having Extract write directly to the target. This adds resilience if the target goes offline temporarily:

EXTRACT PORA_SUBS
USERIDALIAS ORA_ADMIN DOMAIN GG_DOMAIN
RMTHOST YOUR_MYSQL_SERVER, MGRPORT 7809
RMTTRAIL ./dirdat/rs
TABLE YOUR_ORACLE_SCHEMA.SERVICE;
TABLE YOUR_ORACLE_SCHEMA.SUBSCRIPTION;

3. Option 1: Target-Side Join with Replicat Lookup #

This is the most straightforward approach if you don’t want to modify the source Oracle schema. Configure the MySQL Replicat to use the @LOOKUP function to pull related fields from the Service table when applying Subscription changes:

REPLICAT RORA_SUBS
TARGETDB MYSQL, USERIDALIAS MYSQL_ADMIN DOMAIN GG_DOMAIN
ASSUMETARGETDEFS
MAP YOUR_ORACLE_SCHEMA.SUBSCRIPTION, TARGET YOUR_MYSQL_SCHEMA.SUBSCRIPTION,
COLMAP(
  sub_id = sub_id,
  service_id = service_id,
  service_name = @LOOKUP(YOUR_ORACLE_SCHEMA.SERVICE, service_id, service_name, service_id),
  op_id = @LOOKUP(YOUR_ORACLE_SCHEMA.SERVICE, service_id, op_id, service_id)
);

• The @LOOKUP function matches the service_id from Subscription to the Service table, pulling in the corresponding service_name and op_id.
• Ensure service_id is a primary or unique key on the Service table to guarantee accurate matches.

4. Option 2: Source-Side Materialized View (Better for Large Datasets) #

If you’re dealing with high data volumes, pre-joining the tables on the Oracle side will boost performance. Create a materialized view that combines the two tables:

CREATE MATERIALIZED VIEW YOUR_ORACLE_SCHEMA.COMBINED_SUBSCRIPTION
REFRESH FAST ON COMMIT
AS 
SELECT 
  s.sub_id, 
  s.service_id, 
  svc.service_name, 
  svc.op_id
FROM YOUR_ORACLE_SCHEMA.SUBSCRIPTION s
INNER JOIN YOUR_ORACLE_SCHEMA.SERVICE svc 
  ON s.service_id = svc.service_id;

Then adjust your Extract and Replicat to sync this materialized view directly to the MySQL Subscription table—no lookup logic needed in the Replicat.

5. Handle Initial Data Load #

Don’t forget to sync historical data before enabling incremental change capture:

• Use GoldenGate’s Initial Load Extract to pull data from the source (either the two tables or the materialized view) and load it into MySQL via Replicat.
• Alternatively, use Oracle’s EXPDP to export the combined data, then import it into MySQL, then start the GoldenGate processes to capture new changes.

Key Things to Keep in Mind #

• Data Consistency: Ensure that changes to the Service table are captured and applied to MySQL before corresponding Subscription changes. GoldenGate processes transactions in order, so this should hold if your application follows that order.
• Error Handling: Add REPERROR parameters to your Replicat to handle cases where a service_id in Subscription doesn’t exist in Service (e.g., log the error and skip the record, or halt the process to fix the data).
• Indexing: Make sure service_id is indexed on both Oracle tables to speed up lookups and change capture.

I’ve built several pipelines like this before, so once you get the configurations dialed in, it’ll run reliably for both initial loads and ongoing incremental syncs.

内容的提问来源于stack exchange，提问作者Chinthaka S