Step-by-Step Tutorial: Setting Up Your First GoldenGate Replication Pipeline
Data replication is vital for modern data architecture. It enables real-time analytics, high availability, and zero-downtime migrations. Oracle GoldenGate is a premier tool for this job, offering low-impact, log-based data capture.
This guide walks you through setting up a basic, unidirectional replication pipeline between two databases using GoldenGate Microservices Architecture. Prerequisites and Architecture Overview
Before configuration begins, ensure your environment meets these structural requirements. The Component Framework
Source Database: The primary database where transactions occur.
Target Database: The destination database receiving the replicated data.
Extract Process: Captures data changes from the source database logs and writes them to trail files.
Distribution Service: Pumps the trail files over the network from the source to the target environment.
Replicat Process: Reads the trail files on the target side and applies the changes to the destination database. System Requirements
Two running database instances (e.g., Oracle, MySQL, or PostgreSQL).
Oracle GoldenGate software installed on both source and target servers.
Network connectivity between the source and target servers on GoldenGate ports (typically 443 for HTTPS microservices). Administrative privileges on both databases. Step 1: Prepare the Source Database
GoldenGate relies on database transaction logs. You must configure the source database to log enough detail for GoldenGate to reconstruct transactions. Enable Logging
For an Oracle source database, you must enable ARCHIVELOG mode and supplemental logging. Run these commands as a privileged user:
– Check logging status SELECT log_mode, supplemental_log_data_min FROM v$database; – Enable supplemental logging (requires DBA privileges) ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE FORCE LOGGING; Use code with caution. Create the GoldenGate User
The GoldenGate process needs a dedicated database user with specific privileges to read transaction logs.
CREATE USER ogg_user IDENTIFIED BY YourSecurePassword; GRANT CONNECT, RESOURCE TO ogg_user; GRANT SELECT ANY DICTIONARY TO ogg_user; GRANT SELECT ANY TABLE TO ogg_user; ALTER USER ogg_user QUOTA UNLIMITED ON users; Use code with caution. Step 2: Prepare the Target Database
The target database needs a schema to receive the data and a GoldenGate user to apply the transactions. Create the Target Schema and User
Log into your target database and create the replication user:
CREATE USER ogg_target IDENTIFIED BY TargetSecurePassword; GRANT CONNECT, RESOURCE TO ogg_target; GRANT CREATE TABLE, INSERT, UPDATE, DELETE ANY TABLE TO ogg_target; Use code with caution. Step 3: Configure the Extract Process (Source Side)
The Extract process captures data from the database redo logs. Using the GoldenGate Service Manager web interface, navigate to the Administration Service on your source deployment. Create a Credentials Alias Navigate to Configuration > Database. Click the + icon to add database credentials.
Enter a User ID (ogg_user), the database connection string, and the password. Assign it an alias like SourceDB. Add the Extract Go to the Overview tab of the Administration Service. Click the + icon next to Extracts.
Choose Integrated Extract (recommended for Oracle databases). Enter a Process Name (e.g., EXT_SRC). Select the SourceDB credential alias. Specify the Trail Name (two characters, e.g., e1). Edit the Extract Parameter File
In the parameter configuration window, define which tables to capture:
EXTRACT ext_src USERIDALIAS SourceDB EXTTRAIL e1 – Specify the schema and tables to replicate TABLE source_schema.; Use code with caution.
Click Save and Close, then select Start from the Action menu next to your new Extract. Step 4: Configure the Distribution Service
The Distribution Service sends the trail files created by the Extract process across the network to the target machine.
Open the Distribution Service web console on the source server. Click the + icon to create a path. Name the path (e.g., PATH_TO_TARGET). Set the Source to your local trail (e.g., e1).
Set the Target protocol to wss (secure websockets) or ogg and enter the target trailing host IP and port. Specify a target trail name (two characters, e.g., t1). Save and Start the path. Step 5: Configure the Replicat Process (Target Side)
The Replicat process runs on the target server, reads the incoming trail files, and writes the changes to the target database. Open the Administration Service on your target deployment. Create a Target Credentials Alias Navigate to Configuration > Database.
Click + and add the credentials for ogg_target. Assign it the alias TargetDB. Add the Replicat
Go to the Overview tab of the target Administration Service. Click the + icon next to Replicats.
Select Non-Integrated Replicat or Integrated Replicat based on your performance needs. Name the process (e.g., REP_TGT). Select the TargetDB credential alias.
Input the Target Trail name matching the distribution service (t1). Edit the Replicat Parameter File
Map the source tables to your target tables in the parameter file:
REPLICAT rep_tgt USERIDALIAS TargetDB – Map source schema tables to target schema tables MAP source_schema., TARGET target_schema.*; Use code with caution. Click Save and Close, then click Start. Step 6: Test the Replication Pipeline
With all components running, verify that data flows correctly from source to target. Insert Test Data
Create a sample table and insert a row on the source database:
– Run on Source CREATE TABLE source_schema.test_colors (id NUMBER PRIMARY KEY, color VARCHAR2(20)); INSERT INTO source_schema.test_colors VALUES (1, ‘Blue’); COMMIT; Use code with caution. Verify on Target Log into the target database and check if the row arrived: – Run on Target SELECTFROM target_schema.test_colors; Use code with caution.
If the row appears in your target table, your GoldenGate pipeline is successfully configured and running in real-time. Monitoring and Troubleshooting To keep your pipeline healthy, monitor these key areas:
Lag Time: Check the Administration Service dashboard. High lag indicates network bottlenecks or target database performance issues.
Discard Files: If a Replicat fails due to a data mismatch (e.g., trying to insert a duplicate key), check the .dsc report file in the deployment console to see the exact SQL statement that failed.
Process Status: Ensure Extract, Path, and Replicat all display a green “Running” status. If a process stops, review its report file (.rpt) for error codes.
To help fine-tune this setup for your environment, let me know:
What source and target databases (and versions) are you using?
Leave a Reply