Back to Blog Home

Seamless Data Replication from Oracle to YugabyteDB With GoldenGate

Nadeem Khan

January 23, 2025

A key requirement when migrating data between platforms is to have as little downtime as possible. Oracle GoldenGate is a tried and proven data replication tool that replicates data from one source to another in real-time.

In this blog, we demonstrate how GoldenGate can seamlessly perform data replication from Oracle Database to PostgreSQL-compatible YugabyteDB.

Integrating YugabyteDB and GoldenGate

YugabyteDB is an open source distributed SQL database that delivers global data distribution, resilience, scalability, and enterprise features.

The source and target environments detailed below use the following versions of YugabyteDB and GoldenGate software.

Source system
Oracle Database 19c on Linux
GoldenGate 21c for Oracle database

Target system
YugabyteDB v2024.2.0.0 on Linux
GoldenGate 21c for PostgreSQL

Integrating YugabyteDB and GoldenGate

For demonstration purposes, we installed GoldenGate on the database host machines only. You can deploy it on remote/dedicated machines for your production environments.

To demonstrate data replication here, we need to migrate a schema called “ggpdbuser” that resides under a PDB (pluggable database) named “ORCLPDB1” to the target YugabyteDB database named “yugadb1”.

Source Database Details
PDB Name – ORCLPDB1
Schema Name – ggpdbuser
Tables to be migrated: There are seven tables under the “ggpdbuser” schema, listed below:

  1. regions
  2. locations
  3. countries
  4. departments
  5. employees
  6. job_history
  7. jobs

Target Database Details
DB Name – yugadb1
Schema Name – public
User Name – yugabyte

Table structure migration: This is the first and most important step to take before setting up real-time data replication. Oracle GoldenGate doesn’t have a feature to migrate the table structure between heterogeneous databases, so you will need to carefully check and understand the source table definition and convert it into the required target database format.

You can use GoldenGate to perform the initial data load and continuous data replication once the corresponding table structure is ready in the target database. You must create a primary key for each table to better manage data synchronization and to avoid conflicts during replication.

Before getting started with the GoldenGate replication setup, we have created all seven tables in the target “yugadb1”.

How to Set Up Real-Time Replication

Follow the high-level steps below to set up real-time replication.

On the source side:

  1. Create GoldenGate user c##ggadmin (common user in case of Container DB) and grant required permission to goldengate extract user
  2. Set/enable required parameters in the database
  3. Create a defgen file for all seven tables and transfer them to target side
  4. Create Credentialstore and add credentials to GoldenGate
  5. Login to the source database (PDB) from the GoldenGate console using goldengate user credentials and start manager process
  6. To perform the initial load, create initial-load extract process
  7. Capture the database SCN number
  8. Create data-pump extract for real-time data capture and register it to PDB
  9. Start running initial-load extract
  10. Start real-time data extract process

On the target side:

  1. Setting up ODBC configuration for YugabyteDB
  2. Log-in to the source database from the GoldenGate console using goldengate user credentials and start manager process
  3. Create and run initial-load replicat process
  4. Configure and start the real-time data replicate process from the target SCN captured

Now, let’s get started with our configuration

Source Side Configuration

  1. Create goldengate replication user c##ggadmin (common user in case of Container DB) and grant the required permissions.
    SQL> create user C##GGADMIN identified by ggadmin;
SQL> grant resource to c##ggadmin; 
SQL> grant create session to c##ggadmin;
SQL> grant create view to c##ggadmin; 
SQL> grant create table to c##ggadmin;
SQL> grant connect to c##ggadmin CONTAINER=all;
SQL> grant alter system to c##ggadmin;
SQL> grant alter user to c##ggadmin;
SQL> alter user c##ggadmin set container_data=all container=current;
SQL> alter user c##ggadmin quota unlimited on gg_data;
SQL> grant select any dictionary to c##ggadmin;
SQL> grant select any transaction to c##ggadmin; 
SQL> exec dbms_goldengate_auth.grant_admin_privilege('c##ggadmin’,container=>'all');
SQL> grant dv_goldengate_admin; ——— for data vault user
SQL> grant dv_goldengate_redo_ACCESS; —— for data vault user
  2. Enable the required parameters in the database.
    SQL> alter database add supplemental log data;
Database altered.

SQL> alter system set enable_goldengate_replication=TRUE;
System altered.

  3. Create a defgen file for all seven tables.

    The defgen file is required when the source and target table structure are different, mostly when configuring data replication between heterogeneous databases.

    [oracle@ip-10-98-41-29 dirprm]$ cat defgen.prm 
DEFSFILE ./dirprm/all7tab.def
USERIDALIAS oracredcdb
TABLE ORCLPDB1.ggpdbuser.regions;
TABLE ORCLPDB1.ggpdbuser.locations;
TABLE ORCLPDB1.ggpdbuser.countries;
TABLE ORCLPDB1.ggpdbuser.departments;
TABLE ORCLPDB1.ggpdbuser.employees;
TABLE ORCLPDB1.ggpdbuser.job_history;
TABLE ORCLPDB1.ggpdbuser.jobs;
[oracle@ip-10-98-41-29 dirprm]$ 
[oracle@ip-10-98-41-29 gg_or_21c_home]$ ./defgen paramfile ./dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
      Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
 Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 28 2021 13:27:11
 
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
                    Starting at 2025-01-15 20:05:52
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Dec 11 11:42:44 PST 2024, Release 4.18.0-553.32.1.el8_10.x86_64
Node: ip-10-98-41-29.ap-south-1.compute.internal
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 11819
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirprm/all7tab.def
USERIDALIAS oracredcdb

2025-01-15 20:05:52  INFO    OGG-03541  Oracle Environment Variables: 
TNS_ADMIN = /opt/oracle/product/19c/dbhome_1/network/admin
LD_LIBRARY_PATH = /opt/oracle/product/19c/dbhome_1/lib.
TABLE ORCLPDB1.ggpdbuser.regions;
Retrieving definition for ORCLPDB1.GGPDBUSER.REGIONS.
TABLE ORCLPDB1.ggpdbuser.locations;
Retrieving definition for ORCLPDB1.GGPDBUSER.LOCATIONS.
TABLE ORCLPDB1.ggpdbuser.countries;
Retrieving definition for ORCLPDB1.GGPDBUSER.COUNTRIES.
TABLE ORCLPDB1.ggpdbuser.departments;
Retrieving definition for ORCLPDB1.GGPDBUSER.DEPARTMENTS.
TABLE ORCLPDB1.ggpdbuser.employees;
Retrieving definition for ORCLPDB1.GGPDBUSER.EMPLOYEES.
TABLE ORCLPDB1.ggpdbuser.job_history;
Retrieving definition for ORCLPDB1.GGPDBUSER.JOB_HISTORY.
TABLE ORCLPDB1.ggpdbuser.jobs;
Retrieving definition for ORCLPDB1.GGPDBUSER.JOBS.

Definitions generated for 7 tables in ./dirprm/all7tab.def.
[oracle@ip-10-98-41-29 gg_or_21c_home]$

    Create a defgen file 1

    Create a defgen file 2
    Transfer all7tab.def file to the target server.

  4. Add target container and pluggable database credentials to the GoldenGate CREDENTIALSTORE and log-in to the database via the GoldenGate console.
    Add the target database credential to CREDENTIALSTORE and log-in to the target database via the GoldenGate console.GoldenGate CREDENTIALSTORE
  5. Start manager process.
    GGSCI> dblogin useridalias oracredalias
Successfully logged into database ORCLPDB1.

GGSCI> view param mgr
PORT 7809

GGSCI> start mgr
Manager started.Start manager process
  6. To perform initial-load, create an initial-load extract process.
    GGSCI> view params expil7tb

EXTRACT expil7tb
USERIDALIAS oracredcdb
SETENV (LD_LIBRARY_PATH='opt/oracle/product/19c/dbhome_1/lib')
SETENV (TNS_ADMIN = '/opt/oracle/product/19c/dbhome_1/network/admin')
RMTHOST 10.98.41.158, MGRPORT 7811
RMTFILE ./dirdat/il, maxfiles 999999, megabytes 190, append
TABLE ORCLPDB1.ggpdbuser.regions;
TABLE ORCLPDB1.ggpdbuser.locations;
TABLE ORCLPDB1.ggpdbuser.countries;
TABLE ORCLPDB1.ggpdbuser.departments;
TABLE ORCLPDB1.ggpdbuser.employees;
TABLE ORCLPDB1.ggpdbuser.job_history;
TABLE ORCLPDB1.ggpdbuser.jobs;


GGSCI> add extract expil7tb, sourceistable
Extract added.

    The screenshot below shows the created initial-load extract parameter file “expil7tb.”created initial-load extract parameter file “expil7tb"

  7. Capture the database SCN number before starting the extract process.
    SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    3625429

    Capture the database SCN number

  8. Configure the Extract process named “ext7tab” for change data capture (CDC).
    GGSCI> view params ext7tab

EXTRACT ext7tab
USERIDALIAS oracredcdb
SETENV (LD_LIBRARY_PATH='opt/oracle/product/19c/dbhome_1/lib')
SETENV (TNS_ADMIN = '/opt/oracle/product/19c/dbhome_1/network/admin')
RMTHOST 10.98.41.158, MGRPORT 7811
RMTTRAIL ./dirdat/rt
DDL INCLUDE MAPPED
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE ORCLPDB1.ggpdbuser.regions;
TABLE ORCLPDB1.ggpdbuser.locations;
TABLE ORCLPDB1.ggpdbuser.countries;
TABLE ORCLPDB1.ggpdbuser.departments;
TABLE ORCLPDB1.ggpdbuser.employees;
TABLE ORCLPDB1.ggpdbuser.job_history;
TABLE ORCLPDB1.ggpdbuser.jobs;

GGSCI> add extract ext7tab, integrated tranlog, begin now
Integrated Extract added.

    Configure the Extract process named “ext7tab”

  9. Start running the initial-load extract, named “expil7tb” in our demo.
    GGSCI> start expil7tb

Sending START request to Manager ...
Extract group EXPIL7TB starting.

    Start running the initial-load extract

  10. Start the CDC extract process “ext7tab”
    GGSCI> dblogin useridalias oracredcdb
Successfully logged into database CDB$ROOT.

GGSCI> register extract ext7tab DATABASE container(ORCLPDB1)
2025-01-16 00:35:59 INFO OGG-02003 Extract group EXT7TAB successfully registered with database at SCN 3632430.

GGSCI> start ext7tab
Sending START request to Manager ...
Extract group EXT7TAB starting.

GGSCI> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT7TAB 00:00:00 02:59:12

    Start the CDC extract process “ext7tab”

Target Side Configuration

  1.  
  2.  
  3.  
  4.  
  5.  
  6.  
  7.  
  8.  
  9.  
  10.  
  11. Create the target database ODBC configuration. Oracle GoldenGate supports connectivity via an ODBC driver so we can use the PostgreSQL ODBC driver to connect to YugabyteDB.
    [yugabyte@ip-10-98-41-158 ~]$ cat /etc/odbc.ini
#Sample DSN entries
[ODBC Data Sources]
PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol

PG_src=DataDirect 7.1 PostgreSQL Wire Protocol
PG_tgt=DataDirect 7.1 PostgreSQL Wire Protocol
YB_tgt=DataDirect 7.1 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=4
#InstallDir=/home/postgres/gg_pg_21c/
InstallDir=/home/yugabyte/gg_yb_21c/

[YB_tgt]
Driver=/home/yugabyte/gg_yb_21c/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=yugadb1
HostName=10.98.41.158
PortNumber=5433
[yugabyte@ip-10-98-41-158 ~]$

    Create the target database ODBC configuration

  12. Connect to the target database via ODBC handle [YB_tgt] from the GoldenGate console and start the manager process.
    To log-in to the target YugabyteDB database (yugadb1), you must first add and generate credentials in the GoldenGate credential store.
    GGSCI> DBLOGIN SOURCEDB YB_tgt, useridalias yugacredalias
2025-01-15 22:13:09  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.UTF-8.
2025-01-15 22:13:09  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

GGSCI> 

GGSCI> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED                                           

GGSCI> start mgr
Manager started.

GGSCI> view param mgr
PORT 7811

    Connect to the target database via ODBC handle

  13. Configure initial-load replicat process “repil7tb”

    GGSCI> view params repil7tb
REPLICAT repip7tb
SOURCEDEFS dirdef/all7tab.def
SETENV (ODBCINI="/etc/odbc.ini")
SETENV (PGCLIENTENCODING="UTF8")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB YB_tgt USERIDALIAS yugacredalias
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP ORCLPDB1.ggpdbuser.regions, TARGET public.regions;
MAP ORCLPDB1.ggpdbuser.locations, TARGET public.locations;
MAP ORCLPDB1.ggpdbuser.countries, TARGET public.countries;
MAP ORCLPDB1.ggpdbuser.departments, TARGET public.departments;
MAP ORCLPDB1.ggpdbuser.employees, TARGET public.employees;
MAP ORCLPDB1.ggpdbuser.job_history, TARGET public.job_history;
MAP ORCLPDB1.ggpdbuser.jobs, TARGET public.jobs;


GGSCI> info CHECKPOINTTABLE
Error: Missing checkpoint table specification.

GGSCI> ADD CHECKPOINTTABLE public.ggcheckpoint
Successfully created checkpoint table public.ggcheckpoint.

GGSCI> add replicat repil7tb, exttrail ./dirdat/il checkpointtable public.ggcheckpoint
Replicat added.

    Configure initial-load replicat process “repil7tb” 1Configure initial-load replicat process “repil7tb” 2

  14. Configure the replicat process “rep7tab” for real-time data replication.
    GGSCI> view params rep7tab

REPLICAT rep7tab
SOURCEDEFS dirdef/all7tab.def
SETENV (ODBCINI="/etc/odbc.ini")
SETENV (PGCLIENTENCODING="UTF8")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB YB_tgt USERIDALIAS yugacredalias
BATCHSQL
GETTRUNCATES
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP ORCLPDB1.ggpdbuser.regions, TARGET public.regions;
MAP ORCLPDB1.ggpdbuser.locations, TARGET public.locations;
MAP ORCLPDB1.ggpdbuser.countries, TARGET public.countries;
MAP ORCLPDB1.ggpdbuser.departments, TARGET public.departments;
MAP ORCLPDB1.ggpdbuser.employees, TARGET public.employees;
MAP ORCLPDB1.ggpdbuser.job_history, TARGET public.history;
MAP ORCLPDB1.ggpdbuser.job, TARGET public.job;

GGSCI> add replicat rep7tab, exttrail ./dirdat/rt checkpointtable public.ggcheckpoint
Replicat added.

    Configure the replicat process “rep7tab”

Before starting the initial-load, let’s check the row counts of the source and target database tables.

Until 16-JAN-2025 12:14:22 AM, no data was present in the target side table as shown in the screenshots below.

We started the initial-load just after 12:14:22 AM, and checked the row count again at around 12:15:02 PM. You can see from the screenshot that this time the data was successfully imported to the target YugabyteDB tables.

GGSCI> start repil7tb

Sending START request to Manager ...
Replicat group REPIL7TB starting.

data was successfully imported to the target YugabyteDB tables

Success! As you can see, our initial data load is complete. Now, we’ll run the real-time replication from the SCN we captured from the source database before starting the real-time extract.

GGSCI> start REP7TAB, aftercsn 3625429

Sending START request to Manager ...
Replicat group REP7TAB starting.

This will allow continuous data replication to the target YugabyteDB database from the Oracle database.

Conclusion

This blog showcases how efficiently data can be replicated in real-time between Oracle and YugabyteDB using GoldenGate’s standard configuration and parameters. Customers should follow GoldenGate’s best practices for heterogeneous databases in their environments.

Want to know more about YugabyteDB and Oracle? Check out our recent blog, which details how you can deploy YugabyteDB on Oracle Cloud Infrastructure (OCI).

January 23, 2025
Distributed SQLGoldenGateOracleYugabyteDB
Nadeem Khan

January 23, 2025

Related Posts

Explore Distributed SQL and YugabyteDB in Depth

Discover the future of data management.
Learn at Yugabyte University
Get Started
Browse Yugabyte Docs
Explore docs
PostgreSQL For Cloud Native World
Read for Free