Introduction
In many organizations, Oracle databases power mission-critical systems — order management, billing, customer profiles — and remain the backbone of daily operations.
Yet, modern architectures often call for data to be shared with other platforms, such as PostgreSQL, for specialized workloads or integration with certain applications.
Bridging these environments often means moving not just data, but live transactional changes, with high precision and minimal downtime.
That’s where Oracle GoldenGate shines. Replication between Oracle and other platforms starts with instantiation — capturing a consistent snapshot of Oracle data to initialize the target system, while also setting up for real-time synchronization.
Whether you’re replicating sales transactions to an analytics platform, syncing customer data across systems, or performing phased migrations, instantiation is the first critical step.
This post walks through how GoldenGate enables seamless instantiation from Oracle to other environments — in this case, PostgreSQL — including the key steps, expert tips, and challenges to anticipate along the way.
Prerequisites for Oracle to PostgreSQL Replication using GoldenGate
Before kicking off the instantiation process, both the source (Oracle) and target (PostgreSQL) environments need to be prepared. Skipping these steps often leads to setup failures, inconsistent data, or unsupported configuration errors.
Here’s what must be in place:
- GoldenGate Installation :
Install the latest patch release of Oracle GoldenGate 23ai (Microservices Architecture) for both Oracle and PostgreSQL platforms.
See the Oracle GoldenGate documentation for details regarding software installation and patching.
- Create Deployments for Oracle and PostgreSQL GoldenGate :
Next create deployments for Oracle and PostgreSQL. If you are installing as a hub, then can use a single Service Manager. See the Oracle GoldenGate documentation for more details.
- Database Configuration
- Prepare Oracle Source DB for Oracle GoldenGate. See the following Oracle documentation
Oracle GoldenGate documentation
- Prepare PostgreSQL Server DB for Oracle GoldenGate. See the following Oracle Documentation
Oracle GoldenGate documentation
Once the prerequisites are in place, the system is ready for the snapshot + replication setup — which begins with instantiating the initial data from Oracle to PostgreSQL Server.
GoldenGate Pre-Instantiation Steps for Oracle to PostgreSQL
When setting up Oracle GoldenGate for replication, taking time to understand your table metadata isn’t just good practice, it’s essential for performance and reliability.
GoldenGate’s Initial Load Extract process is single-threaded and performs a basic SELECT * on each table (processed in alphabetical order). While this approach is straightforward, it can be inefficient for environments with large tables or high table counts. All rows are selected and streamed downstream to the target, which can quickly become time-consuming without the right preparation.
To optimize this, it’s important to:
Capture row counts per table: Knowing how much data needs to move helps you estimate the time and resources required for instantiation.
Review key constraints: Primary keys, unique constraints, and foreign keys impact GoldenGate’s ability to replicate changes accurately and efficiently. Tables without keys may require special handling.
Understanding this metadata upfront allows you to make informed decisions, identify potential bottlenecks, and tailor your instantiation strategy to your environment’s scale and complexity.
For this demonstration, we’re leveraging the Swingbench Order Entry schema, which simulates a realistic transactional workload. This schema includes 11 interrelated tables, designed to reflect a typical e-commerce environment.
Image 1 provides a snapshot of these tables, showcasing both their sizes and the number of rows currently stored in each. To generate this overview, we used the SQL query shown in Query 1, which retrieves table metadata in real time.
-- Gather fresh statistics for the SOE schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SOE');
-- Now print table size and row counts
SELECT
t.table_name,
s.num_rows,
ROUND(SUM(d.bytes) / 1024 / 1024 / 1024, 2) AS size_gb
FROM
all_tables t
JOIN
all_tab_statistics s ON t.owner = s.owner AND t.table_name = s.table_name
JOIN
dba_segments d ON t.owner = d.owner AND t.table_name = d.segment_name
WHERE
t.owner = UPPER('SOE')
AND d.segment_type = 'TABLE'
GROUP BY
t.table_name, s.num_rows
ORDER BY
size_gb DESC;
Query 1 : To retrieve Table record count and size
Also to find the constraints use the following query,
WITH cons AS (
SELECT ac.owner, ac.table_name, ac.constraint_name, ac.constraint_type,
acc.column_name, ac.status, ac.validated
FROM all_constraints ac
JOIN all_cons_columns acc
ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name
WHERE ac.owner = UPPER('&schema')
AND ac.constraint_type IN ('P', 'U', 'R')
),
cols AS (
SELECT owner, table_name, column_name, nullable
FROM all_tab_columns
WHERE owner = UPPER('&schema')
)
SELECT c.owner, c.table_name, c.constraint_type,
DECODE(c.constraint_type, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY', 'R', 'FOREIGN KEY') AS constraint_kind,
c.constraint_name, c.column_name, t.nullable,
c.status AS constraint_enabled, c.validated AS constraint_validated
FROM cons c
JOIN cols t ON c.owner = t.owner AND c.table_name = t.table_name AND c.column_name = t.column_name
ORDER BY c.table_name, c.constraint_type, c.constraint_name, c.column_name;
Query 2 : To retrieve Table Constraints
Source Side Instantiation Steps of GoldenGate for Oracle
In this section, we will complete all the required steps on the source side of the replication setup.
Add Trandata
After creating the source Oracle database connection, the first step is to enable supplemental logging using ADD TRANDATA (per table) or ADD SCHEMATRANDATA (for all tables in a schema). It is recommended to enable TRANDATA for all columns, as this ensures that Oracle GoldenGate can capture all column-level changes, even for updates that do not modify primary or unique key columns. This improves replication accuracy and conflict detection, especially when replicating to heterogeneous targets or performing initial loads.
Create Primary CDC Extract
Create the Integrated Extract and record the registration SCN, as this SCN is crucial for ensuring a precise and consistent instantiation point during replication setup.
EXTRACT CDCSOE USERIDALIAS ashburn_dbcs_cdb DOMAIN OracleGoldenGate EXTTRAIL so -- Every 5 minutes, report the count of records processed since the Extract start REPORTCOUNT EVERY 5 MINUTES, RATE -- Every Sunday midnight new report file is opened and forced existing report files to age REPORTROLLOVER AT 00:01 ON SUNDAY -- Extract generates a warning message to report file for long running transactions -- In this case any transaction which is open more than 1 hour -- CHECKINTERVAL is the frequency at which Oracle GoldenGate checks for long transactions WARNLONGTRANS 1H, CHECKINTERVAL 10M -- Capture changes for all tables in the SOE schema and PDB DBCSASH_PDB1 SOURCECATALOG DBCSASH_PDB1 TABLE SOE.*;
Parameter File 1 : CDC Extract Parameter File
To find the start or registered scn run the query on source oracle database and note it down.
SQL> SELECT capture_name, start_scn FROM dba_capture WHERE capture_name = 'OGG$CAP_CDCSOE'; CAPTURE_NAME START_SCN ------------------------- ---------- OGG$CAP_CDCSOE 36626406
Query 3 : To retrieve start scn of the CDC Extract from Source Oracle Database
To achieve a consistent and reliable initial load using Oracle GoldenGate, it’s crucial to identify a valid SCN (System Change Number) from which the extract can begin. This SCN must be recorded after ensuring no open transactions exist in the source Oracle database.
Why? Because the CDC (Change Data Capture) extract only tracks transactions that start after its registration. Any open transactions initiated before registration won’t be captured—even if they commit later—leading to potential data loss.
Use the following query to list all open transactions on the source database before registering the extract:
-- Query for active transactions
SELECT
T.START_SCN,
T.STATUS AS TSTATUS,
TO_CHAR(T.START_DATE, 'MM/DD/YY HH24:MI:SS') AS START_TIME,
S.SID,
S.SERIAL#,
S.INST_ID,
S.USERNAME,
S.OSUSER,
S.STATUS AS SSTATUS,
TO_CHAR(S.LOGON_TIME, 'MM/DD/YY HH24:MI:SS') AS LOGON_TIME,
S.SQL_ID,
Q.SQL_TEXT
FROM
gv$transaction T
JOIN
gv$session S ON T.ses_addr = S.saddr AND T.inst_id = S.inst_id
LEFT JOIN
gv$sql Q ON S.SQL_ID = Q.SQL_ID AND S.inst_id = Q.inst_id
UNION ALL
-- Fallback row when there are no open transactions
SELECT
D.CURRENT_SCN,
'CURRENT',
TO_CHAR(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS') AS START_TIME,
NULL, NULL, NULL,
'SYS', NULL, NULL,
TO_CHAR(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS') AS LOGON_TIME,
NULL, NULL
FROM
v$database D
ORDER BY 1;
Query 4 : To retrieve open transactions
START_SCN TSTATUS START_TIME SID SERIAL# INST_ID USERNAME OSUSER SSTATUS LOGON_TIME SQL_ID ---------- --------- ---------------- --- ------ ------- -------- ------- -------- ------------ ----------- 38935678 ACTIVE 07/08/25 10:58:41 68 34756 1 SOE opc ACTIVE 07/08/25 10:58:19 g81cbrq5yamf5 38935679 CURRENT 07/08/25 16:28:41 SYS 07/08/25 16:28:41
Query 4 Result : All Open Transactions
After running the query to check for open transactions, compare each transaction’s START_SCN with the registration SCN of your CDC extract. In our case, the extract was registered at: 36626406
As all active transactions have START_SCN > 36626406, it is safe to pick any of these START_SCN values as the starting SCN for your initial load extract. This ensures there’s no overlap or data loss between the initial load and CDC trail capture.
If your output shows any open transaction with START_SCN < 36626406, you have two options:
- Wait until those transactions complete (and disappear from the query).
- Identify and terminate unwanted or long-running sessions.
Create Initial Load Extract
Create the Initial Load Extract with the note scn from above step.
EXTRACT ILSOE USERIDALIAS ashburn_dbcs_cdb DOMAIN OracleGoldenGate -- Create 2Gb initial load trail files. -- Purge if the file exists EXTFILE IL Megabytes 2000 Purge -- Every 60 seconds, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 SECONDS, RATE -- Capture changes for all tables in the SOE schema and PDB DBCSASH_PDB1 SOURCECATALOG DBCSASH_PDB1 TABLE SOE.*, SQLPREDICATE "As Of SCN 38935678";
Parameter File 2 : Initial Load Extract Parameter File
For large tables with high row counts, it’s recommended to parallelize the initial load extract to improve performance and reduce overall load time. This can be achieved by assigning different tables or schemas to separate initial load extracts.
When dealing with a single large table, you can further enhance efficiency by splitting the table across multiple extracts using the @RANGE function, enabling parallel processing of different segments of the table.
Example:
TABLE SOE.ORDER_ITEMS , FILTER (@RANGE (1, 2)), SQLPREDICATE "As Of SCN 33888252";
For more information on @range function Goldengate 23ai Parameters and Functions Reference Guide
Create Distribution Path
Create and start two Distribution Paths from the source Oracle GoldenGate server to the target Oracle GoldenGate server, as specified in the Oracle GoldenGate documentation, one for the instantiation trail data and another for the CDC trail data.
Target Side Instantiation Steps of GoldenGate for PostgreSQL
If you are unfamiliar with how to create Replicats, please refer to the Oracle GoldenGate documentation.
Create Initial Load Replicat
Before proceeding, it’s important to understand how Oracle GoldenGate handles Initial Load Trail data and how it impacts downstream processing:
- The Initial Load Extract reads data directly from the source tables, meaning no transactional metadata (like BEGIN/COMMIT records) is written to the Initial Load Trail files.
- Oracle GoldenGate does not use a dedicated Initial Load Replicat. Instead, the CDC Replicats are responsible for applying the data captured during the Initial Load.
Since CDC Replicats rely on transaction boundaries in the trail files to group operations into transactions, Oracle GoldenGate generates a synthetic (fake) transaction ID for the Initial Load data. This ID is derived from: The trail file sequence number, The relative byte address (RBA), The timestamp of the first record in the trail
A synthetic transaction is considered complete under one of the following conditions:
- End-of-file (EOF) is reached in each trail being processed, or
- The number of records read by Replicat reaches the value set by the MAXTRANSOPS parameter (default: 10,000,000 records)
Because the entire Initial Load data may be treated as a single large transaction, it’s critical to adjust the Replicat configuration, especially when using Parallel Replicat. If not properly tuned:
- Replicat may appear to be hung or stalled,
- No statistics will be reported, and
- No checkpoints will be recorded until the synthetic transaction is committed.
To avoid these issues, review and adjust settings such as MAXTRANSOPS and BATCHSQL.
REPLICAT ILSOE USERIDALIAS PG_Ashburn_SOE DOMAIN OracleGoldenGate -- Set minimum number of Appliers to 1 and spawn -- up to 6 to improve initial load performance. -- When everything has been applied, only 1 Applier -- will be running, signalling initial load is complete. MIN_APPLY_PARALLELISM 1 MAX_APPLY_PARALLELISM 6 -- Every 60 seconds, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 SECONDS, RATE -- Organize similar SQL statements into arrays and apply -- them at an accelerated rate. BATCHSQL -- Large transactions should be broken into pieces of -- specified size and applied concurrently. SPLIT_TRANS_RECS 10000 MAP DBCSASH_PDB1.SOE.*, TARGET dbo.*;
Parameter File 3 : Initial Load Replicat Parameter File
Start the Replicat. Note, the Replicat will not stop when it has completed applying the Initial Load Data. There are a couple of ways to determine if instantiation is complete.
- Check Replicat Checkpoint vs. Trail File Size
On the Goldengate Hub Server, list the trail files used for initial load and compare the last trail sequence number and size with Replicat’s checkpoint sequence number and RBA (from the Web UI).
]$ ls -lrt -rw-r-----. 1 opc opc 2041669555 Jul 8 11:09 IL000000 -rw-r-----. 1 opc opc 1455812218 Jul 8 11:09 IL000001
- Compare Extract and Replicat Statistics
Review the Initial Load Extract report to see how many rows were fetched per table. Then, check Replicat statistics to verify the same row counts. Matching numbers indicate that the initial load completed successfully.
Output to IL:
From table DBCSASH_PDB1.SOE.ADDRESSES:
# inserts: 1518190
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.CARD_DETAILS:
# inserts: 1516534
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.CUSTOMERS:
# inserts: 1016534
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.INVENTORIES:
# inserts: 900376
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.LOGON:
# inserts: 2405462
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.ORDERENTRY_METADATA:
# inserts: 4
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.ORDERS:
# inserts: 1474183
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.ORDER_ITEMS:
# inserts: 7260999
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.PRODUCT_DESCRIPTIONS:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.PRODUCT_INFORMATION:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table DBCSASH_PDB1.SOE.WAREHOUSES:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
Image 6 : Initial Load Extract Report
If the record counts reported by Initial Load Extract and Replicat match, instantiation is complete.
Create CDC Replicat
Once instantiation is complete, create a CDC (Change Data Capture) Replicat to apply both queued and real-time changes. Start it using the AFTERCSN option with the same SCN used during the Initial Load Extract which is 38935678 to ensure continuity.
REPLICAT RSOE USERIDALIAS PG_Ashburn_SOE DOMAIN OracleGoldenGate -- Every 60 seconds, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 Seconds, RATE -- Large transactions should be broken into pieces of -- specified size and applied concurrently. BATCHSQL SPLIT_TRANS_RECS 5000 MAP DBCSASH_PDB1.SOE.*, TARGET PUBLIC.*;
Parameter File 4 : CDC Replicat Parameter File
Post Instantiation Cleanup
We recommend the following to clean up the Oracle GoldenGate installations post instantiation:
- Stop and delete the Replicat used to apply instantiation data.
- Stop and delete the Distribution Path used for transmitting instantiation data.
- Delete the Initial Load Extract.
- Create a Trail Purge Task in the Administration Service WebUI for each Oracle GoldenGate Instance to ensure disk space is freed when Oracle GoldenGate Trails are consumed.
Conclusion
In this article we presented detailed steps to perform precise instantiation of a PostgreSQL target database from a source Oracle database using Oracle GoldenGate. To learn more about Oracle GoldenGate functionality, you may refer to:
Resources :