Real Time Replication - Oracle to Google AlloyDB (Heterogenous Database) - Part 1

This article i'll go over the technical aspects replication from an on-premises Oracle database to a heterogeneous databases like Google AlloyDB , Google BigQuery and Google GCS and Oracle Autonomous Database.

Disclaimer:

The information provided in this article is for general informational and educational purposes only. All content, including code snippets, configurations, and technical recommendations, is shared in good faith and reflects the author's personal understanding and experience at the time of writing.

  All views expressed are the author’s personal opinions and it is not affiliated with, endorsed by Oracle Corporation or any other entity. My current employer or previous employer nowhere responsible for this content. Readers are advised to perform their own research and testing before applying any of the concepts discussed.

Preface:

I had a golden opportunity to conduct a PoC to examine the challenges of replicating data from Oracle v19c On-premises to Oracle Autonomous Database and other heterogeneous databases such as Google AlloyDB, Google BigQuery, and Google GCS using Oracle GoldenGate Microservices v23ai.

Given that, it is a complex use case that involved multiple targets in multi cloud environment , there is no deficit for numerous challenges and unknown surprises .

Once again, Oracle GoldenGate prooved it has extream capability of

Architecture

Use-Case

Problem Statement

• Source Data from Oracle v19c (pdb and non-cdb) need to replicated to Google AlloyDB , Google BiqQuery, Google GCS and Oracle Autonomous Database 23ai using Oracle GoldenGate Microservices (OGG-MS)

• Evaluate the lag of real time replication from Oracle v19c (on-prem) for high volume of data around 8TB for all above four targets

• Study the performance glitches in all four targets for High Volume of Bulk Job ( 10M records) with single commit.

  
  

Success Criteria

• Replication latency should not be more than 30 secs

• Easy of setup and configuration

• Seamless business continuity during high peak load

Oracle Solution

Using GoldenGate v23ai, Oracle offered a solution for managing the operation of the replication of massive data sets.

By integrating GoldenGate for Oracle, GoldenGate for Non-Oracle, and GoldenGate for DAA, we are confident in our ability to meet customer success benchmarks.

• Oracle GoldenGate 23ai is an enhanced version of Oracle GoldenGate that streamlines data replication and integration for distributed environments.

• Oracle GolenGate for NON-Oracle ( for PostgreSQL) supports capture ( mapping, filtering, and transformation of source data) delivery of initial load and transactional data for supported PostgreSQL versions

  
  

• GoldenGate 23ai for Distributed Applications and Analytics (GGDAA) ensures data consistency, minimal or NO downtime, seamless business continuity, and plays a pivotal role in enabling real-time replication and ensuring transactional integrity

Environment Details

Source

• m/c - A Oracle v19c (onPrem)

OGG Extract & Distribution & Replicat Hub

• m/c - B OGG-MS v23ai Hub installed in Google Compute Engine

• m/c - C OGG-MS for Non-Oracle installed in Google Compute Engine

• m/c - D OGG-MS for DAA installed in Google Compute Engine

Targets

(1) Google AlloyDB

(2) Google BigQuery (BQ)

(3) Google Cloud Storage ( GCS)

(4) Oracle Autonomous Database @ OCI

The customer environment is a restricted zone, which means that all interconnects and data transmissions are highly secure. Snapshots are not possible since connections are only made through private interconnects.

Software Requirement :

The following GoldenGate binaries were used for this proof of concept:

• (a) GoldenGate Microservices for Oracle [OGGMS-ORA]

  • Oracle GoldenGate 23.4.1.24.05 on Linux x86-64 for Oracle for (Linux x86-64)

• (b) GoldenGate Microservices for Non-Oracle [OGGMS-NONORA]

  • Oracle GoldenGate 23.4.0.24.05 on Linux x86-64 for PostgreSQL for (Linux x86-64)

• (c) GoldenGate Microservices for Distributed Applications and Analytics [OGGMS-DAA]

  • GoldenGate for Distributed Applications and Analytics v23.4.0.24.06 on Linux x86-64

  
  

Link to download OGG binary

I will address the significant key factors and challenges of each target in a separate article in this series of four targets.

To begin, I will focus on Target —(1) Google AlloyDB.

Google - AlloyDB is a kind of wrapper that provides full compatibility with open source PostgreSQL, including popular extensions. Each cluster has a primary instance and an optional read pool with multiple read nodes, and we can replicate to secondary clusters in separate regions.

Considering that the primary objective of this PoC is to assess the latency time for real-time replication of a high volume of transactional data, we did not prioritize the creation of ddl objects, as this is not a migration engagement.

Method of Procedure at High Level

Task - 1 (Oracle GoldenGate installation) [m/c - B]

Binary of OGGMS-ORA is used to extract the data from source Oracle v19c. Since GUI is not enabled in client environment, I need to install OGGMS-ORA binary and deploy OGG Microservice through silent installation in m/c - B .

Task - 2 (Non-Oracle GoldenGate Installation) [m/c - C]

Binary of OGGMS-NONORA is used to replicat the data from trial file to Target Alloy DB

• Please refer my previous blogs for silent installation of Binary Installation and deployment of Service Manager

Task - 3 (Source Database Preparation)

Prepare the source database with appropriate users and privileges. Ref oracle-doc for reference.

For PDB database

• Connect as c##ggadmin to pdb database and ensure whether c##ggadmin has all privileges to select, insert, delete, update on source schema object.

For NON-CDB database

• Connect as ggadmin to non-cdb database and ensure whether ggadmin has all privileges to select,insert,delete,update on source schema object.

Task - 4 (Source Database Preparation)

At OGG Service manager console, Add DB connections with appropriate user with credentials

For pdb database -

• Login as c##ggadmin at CDB level with appropriate credentials

For non-cdb database -

• login as ggadmin / gguser with appropriate credentials

Once source database is connected,

o   Create checkpoint table 

o   Add Trandata 

Task - 5 ( Target Database Preparation )

Since we are going to perform REPLICAT to target AlloyDB, we need to provide necessary grants to appropriate users and prepare the TARGET database as per oracle doc for PostgreSQL to consume the data.

• Create tables with appropriate definitions.

• Disable FK constraints 

• Enable Client Authentication

Note:  Place request to Google AlloyDB Administrator to enable client authentication. 

• pg_hba.conf # Add client ip

Task - 6 ( OGG for Non-Oracle - Target Database Configuration )

OGGv23ai comes with default ODBC datadirect drivers. Hence, we no need to install other than these drivers.

Create odbc.ini (ref Ora document for more information).

it contains user-specified access information for the pgsqlODBC driver

• Ensure odbc.ini file has appropriate path to all default drivers

Example:

[oracle@pglora ogg23ai_deploy]$ cat odbc.ini

#[ODBC Data Sources]

[ODBC]

IANAAppCodePage=4

InstallDir=/u01/app/ogg23ai/ogg23ai_ma/datadirect

[PG_tgt]

Driver=/u01/app/ogg23ai/ogg23ai_ma/datadirect/lib/ggpsql25.so

Description=Oracle GoldenGate PostgreSQL Wire Protocol

Database=<target_dbname>

HostName=<target_hostIp>

PortNumber=5432      # default port 

Note:

odbcinst.ini file is created while installing OGG service manager and this file contains all appropriate driver information.

[oracle@pglora datadirect]$ pwd

/u01/app/ogg23ai/ogg23ai_ma/datadirect

[oracle@camsoggpglora datadirect]$ cat odbcinst.ini

[ODBC Drivers]

Oracle GoldenGate PostgreSQL Wire Protocol=Installed

[ODBC]

InstallDir=./

TraceDll=./lib/ggtrc25.so

[Oracle GoldenGate PostgreSQL Wire Protocol]

Driver=./lib/ggpsql25.so

Add above files in OGG service manager environment variables configuration.

From OGG Service manager console, Add (TARGET) DB connections with appropriate user (gguser) with credentials

connect to TARGET database

• Once TARGET is connected, Add CHECKPOINT table

7/ Configure Extract & Distribution service @ m/c - B

Connect Source Database (Oracle )

7.1 - Configure CDC Extract

Note: These snapshots are ONLY for reference. Values in OGG console fields and param file might vary due to security reasons.

CDC Extract Parameter

EXTRACT cdcext

USERIDALIAS ggcdb DOMAIN srcOracle

EXTTRAIL cdc/lt

sourcecatalog orclpdb

/* ddl include all      -- if oracle to oracle this parameter valid                                                                            --  other than oracle DDL replication is ignored  by default )

   ddloptions report    -- good to have for debuging                    

*/

table  <> .*;

7.2 - Configure Initial Load Extract

Initial Load Extract

Initial Load Parameter

EXTRACT inex4mig

USERIDALIAS ggcdb DOMAIN srcOracle

EXTFILE intld/il

sourcecatalog orclpdb

table <>.*;

Note: If source tables have partitions and if we required to load ONLY few partition data then use sqlpredicate

        table <>.sales, sqlpredicate "where year < 1992";

        table <>.sales, sqlpredicate "where year between 1992 and 1993";

7.3 - Configure Distribution Service 

                             - This service is required to transfer the trail files generated by the

Create Pump path to Target from Source

Create Distribution path

Select "Receiver Service" for Target Type and provide Target Host and Port details

File Format as "GGFormat"

Provide default for next 2 pages

Here is the sample list of distribution paths after creation.

Note: We need to create a separate distribution path for each TARGET based on extract process appropriately.

Task - 8 ( Configure Replicat Process ) @ [ m/c - C ]

From Oracle GoldenGate service Manager console, create a replicate process to replicate data to the (Google AlloyDB) target database.

Task - 8.1 ( Configure Initial Load Replicat )

Provide checkpoint table name. Once Replicate process started, OGG will create checkpoint table in TARGET database

Set Trial Position Sequence Number to 0

Set RBA Offset to 0

Initial Load Replicate Parameter

REPLICAT inldrep

USERIDALIAS tgtpgl DOMAIN tgtpgl

HANDLECOLLISIONS

map orclpdb.scott.DEPT, target crsk_schema.dept;

map orclpdb.scott.EMP, target crsk_schema.emp;

Challenge - 1:

Google Alloy DB does not support Oracle UNICODE chars.

Example, Oracle SPACE unicode character not accepted in Google AlloyDB

Below error triggered while Replicate process execution.

2025-01-23 19:44:07  WARNING OGG-01004  Canceled grouped transaction on table crsk_schema.testascii. Database error 3452617, (SQLState = S1000 SQLError = 3,452,617 SQLErrorHex = 0034aec9 SQLErrorText = [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; invalid byte sequence for encoding "UTF8": 0x00(Where portal "ST55A2B315CB00" parameter $2; File mbutils.c; Line 1669; Routine report_invalid_encoding; )).

To Handle above error

we need to write conversion script and that script should be included in paremeter file

Example:

REPLICAT inldrep

USERIDALIAS tgtpgl DOMAIN tgtpgl

HANDLECOLLISIONS

replacebadchar space forcecheck
charmap /u01/app/ogg23ai/ogg23ai_deploy/etc/conf/ogg/charmapdesc.txt

map orclpdb.scott.DEPT, target crsk_schema.dept;
map orclpdb.scott.EMP, target crsk_schema.emp;
map orclpdb.scott.SALES, target crsk_schema.sales;

Refer: Mos Note Doc ID 2561427.1

Challenge -2 [ Replicating Partition Tables ]

Tables with suitable partition definitions should be created for partition tables, and access to the tables should be granted with the corresponding privileges in TARGET.

Otherwise below error could be prevented

2025-01-26 08:14:05 WARNING OGG-01154 SQL error 3505720 mapping source table ORCLPDB.SCOTT.SALES to target table crsk_schema.sales. Database error: SQLState = 23000 SQLError = 3,505,720 SQLErrorHex = 00357e38 SQLErrorText = [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; new row for relation "sales" violates partition constraint(Detail Failing row contains (1, 100, 1000, 1991).; scrsk_schema; tsales; File execMain.c; Line 1806; Routine ExecPartitionCheckEmitError; ).

2025-01-26 09:13:12 WARNING OGG-01154 SQL error 3505720 mapping source table ORCLPDB.SCOTT.SALES to target table crsk_schema.sales. Database error: SQLState = 23000 SQLError = 3,505,720 SQLErrorHex = 00357e38 SQLErrorText = [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; no partition of relation "sales" found for row(Detail Partition key of the failing row contains (year) = (1992).; scrsk_schema; tsales; File execPartition.c; Line 327; Routine ExecFindPartition; ).

Example:

create table crsk_schema.sales (txn_id numeric not null,

prod_id numeric,

amt numeric,

year numeric not null) partition by range (year);

create table crsk_schema.sales_p1 PARTITION OF crsk_schema.sales FOR VALUES FROM (1900) TO (1992);

create table crsk_schema.sales_p2 PARTITION OF crsk_schema.sales FOR VALUES FROM (1992) TO (1993);

create table crsk_schema.sales_p3 PARTITION OF crsk_schema.sales FOR VALUES FROM (1993) TO (1994);

create table crsk_schema.sales_p4 PARTITION OF crsk_schema.sales FOR VALUES FROM (1994) TO (1995);

create table crsk_schema.sales_p5 PARTITION OF crsk_schema.sales FOR VALUES FROM (1996) TO (MAXVALUE);

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA crsk_schema TO gguser4tgt;

Major Challenges:

• Oracle UNICODE / Non-Printable Characters were not comprehensible to the Google AlloyDB engine. Example : null and space.

• Hence, we need to convert those UNICODE / Non-Printable chars in Goldengate Replicat Process through small convert mechanism .

  
  

• Replicate (Inserts) at Google AlloyDB is incredibly slow while comparing with ADB, Google BQ and Google GCS

  • Thus, it is necessary to implement a few performance-related parameters in the GoldenGate Replicate procedure, which has enabled the amazing performance boost from 1.2 to 2.5 M/hr to 127 million in 1 hour and 16 minutes for data set comprises 1k avg_row_len.

🔧 Key GoldenGate Parameters Optimized

To achieve this improvement, we fine-tuned a combination of Extract, Pump, and Replicat parameters:

• CHUNK_SIZE – Tuned for optimal memory use during trail file I/O

• SPLIT_TRANS_RECS – Enabled to break large transactions into smaller, parallelizable units

• MAP_PARALLELISM – Increased to distribute mapping load across multiple threads

• APPLY_PARALLELISM – Increased to scale parallel DML execution on the target

• BATCHSQL – Enabled for bulk DML processing, significantly reducing round trips

• BATCHESPERQUEUE – Tuned to ensure optimal queue length for consistent throughput

• BYTESPERQUEUE – Adjusted for memory-efficient queuing and reduced commit latency

⚙️ Outcome

• Previous Throughput: 1.2–2.5M rows/hour

• Achieved Throughput: 127M rows in 1h 16min (~100M/hour+ sustained)

• Improvement: ~50x performance gain

✅ Benefits

• Vastly reduced batch processing time

• Minimal replication lag

• Efficient CPU and memory utilization

• Seamless data integrity across environments

This optimization reaffirms Oracle GoldenGate's capacity to handle high-volume, low-latency replication at scale when properly tuned.

• The client's decision to select Oracle GoldenGate is reinforced by the remarkable lightning pace that we have achieved.

Achievement:

Oracle GoldenGate successfully overcame the aforementioned significant challenges to accomplish a latency of less than 15 to 20 seconds when replicating from on-premises to all four targets (ADB, Google AlloyDB, Google BiqQuery, and Google Cloud Storage).

Conclusion:

It is my conviction that the article mentioned above would have offered valuable insights and expected challenges regarding the process of replicating data from Oracle to AlloyDB.

In my upcoming post, I'll go through the details of replication from  Oracle to Google BigQuery setup and the challenges that I experienced.

Thanks for your time. Reach me if you need additional information.