{"id":6832,"date":"2026-05-03T11:43:47","date_gmt":"2026-05-03T09:43:47","guid":{"rendered":"https:\/\/rootfan.com\/?p=6832"},"modified":"2026-05-03T11:54:56","modified_gmt":"2026-05-03T09:54:56","slug":"replication-doracle-vers-postgresql-avec-debezium","status":"publish","type":"post","link":"https:\/\/rootfan.com\/fr\/oracle-to-postgresql-replication-debezium\/","title":{"rendered":"Comment configurer la r\u00e9plication Oracle en temps r\u00e9el vers PostgreSQL avec Debezium"},"content":{"rendered":"<p id=\"tl-dr\"><strong>TL;DR :<\/strong> Debezium lit les journaux de transactions Oracle via LogMiner, publie chaque modification sous forme d'\u00e9v\u00e9nement Kafka, et un connecteur JDBC sink applique ces \u00e9v\u00e9nements \u00e0 PostgreSQL en temps r\u00e9el.<br>Le r\u00e9sultat est un pipeline de r\u00e9plication transparent et rejouable que vous pouvez ex\u00e9cuter pendant des semaines avant la mise en production \u2014 utile pour toute migration d'Oracle vers PostgreSQL qui n\u00e9cessite un temps d'arr\u00eat nul ou quasi nul.<br>Ce post pr\u00e9sente une configuration fonctionnelle de bout en bout, sur de vrais serveurs Oracle et PostgreSQL, avec un sch\u00e9ma bancaire autonome que vous pouvez copier et reproduire.<\/p>\n\n\n\n<!--more-->\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>Most Oracle to PostgreSQL migration projects fail at the cutover, not at the schema conversion.<\/p>\n\n\n\n<p>The schema work is hard but bounded \u2014 you spend a few weeks on it, you fix what the conversion tool cannot translate, and you move on.<\/p>\n\n\n\n<p>The cutover is open-ended.<\/p>\n\n\n\n<p>You have a production Oracle database that processes thousands of transactions per second.<\/p>\n\n\n\n<p>You need to switch the application to PostgreSQL without losing a single row, and ideally without a multi-hour downtime window.<\/p>\n\n\n\n<p>A snapshot-based migration alone \u2014 dump, restore, repoint \u2014 does not solve this.<\/p>\n\n\n\n<p>The moment you take the snapshot, every new INSERT, UPDATE, and DELETE on Oracle is invisible to PostgreSQL.<\/p>\n\n\n\n<p>You need a tool that captures Oracle's ongoing changes and applies them to PostgreSQL continuously.<\/p>\n\n\n\n<p>Debezium is the open-source standard for that job.<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table des mati\u00e8res<\/h2><nav><ul><li><a href=\"#what-debezium-does\">What Debezium Does<\/a><\/li><li><a href=\"#the-lab-environment\">The Environment<\/a><\/li><li><a href=\"#step-1-prepare-the-oracle-source\">Step 1 \u2014 Prepare the Oracle Source<\/a><\/li><li><a href=\"#step-2-create-the-banking-schema\">Step 2 \u2014 Create the BANKING Schema<\/a><\/li><li><a href=\"#step-3-start-kafka-and-kafka-connect\">Step 3 \u2014 Start Kafka and Kafka Connect<\/a><\/li><li><a href=\"#step-4-register-the-oracle-source-connector\">Step 4 \u2014 Register the Oracle Source Connector<\/a><\/li><li><a href=\"#step-5-watch-cdc-events-live\">Step 5 \u2014 Watch CDC Events Live<\/a><\/li><li><a href=\"#step-6-set-up-the-postgre-sql-sink\">Step 6 \u2014 Set Up the PostgreSQL Sink<\/a><\/li><li><a href=\"#step-7-add-a-new-table-mid-stream\">Step 7 \u2014 Add a New Table Mid-Stream<\/a><\/li><li><a href=\"#the-schema-evolution-tradeoff\">The schema.evolution Tradeoff<\/a><\/li><li><a href=\"#the-new-table-discovery-delay\">The New-Table Discovery Delay<\/a><\/li><li><a href=\"#production-gotchas\">Production Gotchas<\/a><\/li><li><a href=\"#frequently-asked-questions\">Foire aux questions<\/a><ul><li><a href=\"#faq-question-1777800041851\">Can Debezium migrate the Oracle schema, or only the data?<\/a><\/li><li><a href=\"#faq-question-1777800042851\">What is the latency between Oracle and PostgreSQL?<\/a><\/li><li><a href=\"#faq-question-1777800043851\">Is LogMiner deprecated in Oracle 19c or 21c?<\/a><\/li><li><a href=\"#faq-question-1777800044851\">Can this run against Oracle Standard Edition?<\/a><\/li><li><a href=\"#faq-question-1777800045851\">Can I use Debezium for one-shot migration without keeping it running?<\/a><\/li><\/ul><\/li><li><a href=\"#in-summary\">En r\u00e9sum\u00e9<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-debezium-does\">What Debezium Does<\/h2>\n\n\n\n<p>Debezium is a change data capture (CDC) framework built on top of Kafka Connect.<\/p>\n\n\n\n<p>For Oracle, it uses the LogMiner adapter \u2014 a built-in Oracle feature that reads the redo logs and exposes every committed change as a structured stream.<\/p>\n\n\n\n<p>Debezium polls LogMiner, converts each row change into a Kafka event, and publishes the event to a Kafka topic.<\/p>\n\n\n\n<p>A second connector \u2014 the Debezium JDBC sink \u2014 subscribes to those topics and applies the changes to a target database.<\/p>\n\n\n\n<p>For Oracle to PostgreSQL replication, the pipeline looks like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Oracle (LogMiner) \u2192 Debezium source \u2192 Kafka \u2192 Debezium JDBC sink \u2192 PostgreSQL<\/code><\/pre>\n\n\n\n<p>Both connectors run inside a single Debezium Connect process.<\/p>\n\n\n\n<p>Kafka holds events on topics named after each Oracle table.<\/p>\n\n\n\n<p>You can stop the sink and let events accumulate in Kafka.<\/p>\n\n\n\n<p>You can replay events from a specific offset.<\/p>\n\n\n\n<p>You can run two sinks in parallel \u2014 one to PostgreSQL, one to a staging environment for testing.<\/p>\n\n\n\n<p>This is what makes Debezium useful for migration work: you can see exactly what is happening at every stage, and you can intervene at any of them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-lab-environment\">The Environment<\/h2>\n\n\n\n<p>Three VMs on the same network.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>srv1<\/code> (192.168.0.180) runs Oracle 19c Enterprise Edition with a CDB called <code>ORADB<\/code> and a pluggable database <code>pdb1<\/code>.<\/li>\n\n\n\n<li><code>srvdebezium<\/code> (192.168.0.230) runs Ubuntu 24.04 with Docker, hosting the Kafka broker and the Debezium Connect container.<\/li>\n\n\n\n<li><code>srv2<\/code> (192.168.0.181) runs PostgreSQL 18 from the official PGDG repository, hosting the target database <code>bankingdb<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>The pipeline replicates a small banking schema from Oracle to PostgreSQL: five tables (<code>branches<\/code>, <code>employees<\/code>, <code>clients<\/code>, <code>accounts<\/code>, <code>transactions<\/code>) with foreign-key relationships, mixed data types, and 18 sample rows.<\/p>\n\n\n\n<p>The full DDL and sample data are below \u2014 copy-paste reproducible from a clean Oracle install.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-1-prepare-the-oracle-source\">Step 1 \u2014 Prepare the Oracle Source<\/h2>\n\n\n\n<p>LogMiner needs three things turned on before Debezium can read it.<\/p>\n\n\n\n<p><strong>Archivelog mode.<\/strong><br>Without it, Oracle overwrites old redo logs once they are no longer needed for instance recovery.<br>LogMiner needs them on disk to read.<br>Switching to archivelog mode requires an instance restart.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- On srv1 as SYSDBA\nSHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE ARCHIVELOG;\nALTER DATABASE OPEN;\nALTER PLUGGABLE DATABASE pdb1 OPEN;\n<\/pre><\/div>\n\n\n<p><strong>Supplemental logging.<\/strong><br>By default, Oracle writes only the changed columns to the redo log.<br>Debezium needs full row images to populate the <code>before<\/code> field in update events.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nALTER DATABASE ADD SUPPLEMENTAL LOG DATA;\nALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;\n<\/pre><\/div>\n\n\n<p><strong>A capture user.<\/strong><br>Debezium connects as a common user (the <code>C##<\/code> prefix is mandatory in a multitenant database) with read access to the data dictionary and LogMiner views.<br>Apply every grant \u2014 Debezium will fail to start if any one of them is missing.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- On srv1, as SYSDBA, connected to the CDB root\n\nCREATE USER C##DBZUSER IDENTIFIED BY dbz\n  DEFAULT TABLESPACE users\n  QUOTA UNLIMITED ON users\n  CONTAINER=ALL;\n\n-- Session grants\nGRANT CREATE SESSION TO C##DBZUSER CONTAINER=ALL;\nGRANT SET CONTAINER  TO C##DBZUSER CONTAINER=ALL;\n\n-- LogMiner privilege \u2014 required to start and use LogMiner sessions\nGRANT LOGMINING TO C##DBZUSER CONTAINER=ALL;\n\n-- Transaction and dictionary access\nGRANT SELECT ANY TRANSACTION TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ANY DICTIONARY  TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT_CATALOG_ROLE    TO C##DBZUSER CONTAINER=ALL;\nGRANT EXECUTE_CATALOG_ROLE   TO C##DBZUSER CONTAINER=ALL;\n\n-- Flashback and table access \u2014 needed for the initial snapshot\nGRANT FLASHBACK ANY TABLE TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ANY TABLE    TO C##DBZUSER CONTAINER=ALL;\n\n-- Object creation \u2014 Debezium creates internal tracking objects in its own schema\nGRANT CREATE TABLE    TO C##DBZUSER CONTAINER=ALL;\nGRANT LOCK ANY TABLE  TO C##DBZUSER CONTAINER=ALL;\nGRANT CREATE SEQUENCE TO C##DBZUSER CONTAINER=ALL;\n\n-- LogMiner package execution\nGRANT EXECUTE ON DBMS_LOGMNR   TO C##DBZUSER CONTAINER=ALL;\nGRANT EXECUTE ON DBMS_LOGMNR_D TO C##DBZUSER CONTAINER=ALL;\n\n-- V$ views \u2014 Debezium queries these to track log position, archive status, and transactions\nGRANT SELECT ON V_$DATABASE             TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOG                  TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOG_HISTORY          TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOGFILE              TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$ARCHIVED_LOG         TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$ARCHIVE_DEST_STATUS  TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOGMNR_LOGS          TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOGMNR_CONTENTS      TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$LOGMNR_PARAMETERS    TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$TRANSACTION          TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$MYSTAT               TO C##DBZUSER CONTAINER=ALL;\nGRANT SELECT ON V_$STATNAME             TO C##DBZUSER CONTAINER=ALL;\n<\/pre><\/div>\n\n\n<p>Do not shortcut this with <code>GRANT DBA<\/code> \u2014 the explicit grants are auditable, reversible, and match Debezium's documentation exactly.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-2-create-the-banking-schema\">Step 2 \u2014 Create the BANKING Schema<\/h2>\n\n\n\n<p>The schema below is small enough to paste in 30 seconds and varied enough to exercise the data-type behaviour Debezium gets right and wrong.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- On srv1, connected to pdb1 as a privileged user\nCREATE USER banking IDENTIFIED BY banking\n  DEFAULT TABLESPACE users\n  TEMPORARY TABLESPACE temp;\nGRANT CONNECT, RESOURCE, CREATE VIEW TO banking;\nALTER USER banking QUOTA UNLIMITED ON users;\n<\/pre><\/div>\n\n\n<p>Connect as <code>banking<\/code> and create the five tables:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TABLE branches (\n  branch_id    NUMBER(6) PRIMARY KEY,\n  branch_name  VARCHAR2(100) NOT NULL,\n  city         VARCHAR2(50),\n  country      VARCHAR2(50),\n  opened_date  DATE\n);\n\nCREATE TABLE employees (\n  employee_id  NUMBER(6) PRIMARY KEY,\n  branch_id    NUMBER(6) NOT NULL,\n  full_name    VARCHAR2(100) NOT NULL,\n  role         VARCHAR2(50),\n  hire_date    DATE,\n  CONSTRAINT fk_emp_branch FOREIGN KEY (branch_id) REFERENCES branches(branch_id)\n);\n\nCREATE TABLE customers (\n  customer_id  NUMBER(8) PRIMARY KEY,\n  full_name    VARCHAR2(100) NOT NULL,\n  email        VARCHAR2(100),\n  country      VARCHAR2(50),\n  created_at   TIMESTAMP DEFAULT SYSTIMESTAMP\n);\n\nCREATE TABLE accounts (\n  account_id    NUMBER(10) PRIMARY KEY,\n  customer_id   NUMBER(8) NOT NULL,\n  branch_id     NUMBER(6) NOT NULL,\n  account_type  VARCHAR2(20),\n  balance       NUMBER(15,2) DEFAULT 0,\n  opened_at     TIMESTAMP DEFAULT SYSTIMESTAMP,\n  CONSTRAINT fk_acc_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),\n  CONSTRAINT fk_acc_branch   FOREIGN KEY (branch_id)   REFERENCES branches(branch_id)\n);\n\nCREATE TABLE transactions (\n  txn_id       NUMBER(12) PRIMARY KEY,\n  account_id   NUMBER(10) NOT NULL,\n  employee_id  NUMBER(6),\n  txn_type     VARCHAR2(20),\n  amount       NUMBER(15,2) NOT NULL,\n  txn_date     TIMESTAMP DEFAULT SYSTIMESTAMP,\n  description  VARCHAR2(200),\n  CONSTRAINT fk_txn_account  FOREIGN KEY (account_id)  REFERENCES accounts(account_id),\n  CONSTRAINT fk_txn_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id)\n);\n<\/pre><\/div>\n\n\n<p>Insert a small representative dataset \u2014 three branches, four employees, three customers, four accounts, four transactions:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO branches VALUES (1, &#039;Madrid Centro&#039;,      &#039;Madrid&#039;,    &#039;Spain&#039;,    DATE &#039;2010-03-15&#039;);\nINSERT INTO branches VALUES (2, &#039;Barcelona Diagonal&#039;, &#039;Barcelona&#039;, &#039;Spain&#039;,    DATE &#039;2012-06-01&#039;);\nINSERT INTO branches VALUES (3, &#039;Lisbon Avenida&#039;,     &#039;Lisbon&#039;,    &#039;Portugal&#039;, DATE &#039;2015-09-10&#039;);\n\nINSERT INTO accounts VALUES (10001, 1001, 1, &#039;CHECKING&#039;, 5000.00,  SYSTIMESTAMP);\nINSERT INTO accounts VALUES (10002, 1001, 1, &#039;SAVINGS&#039;,  25000.00, SYSTIMESTAMP);\n-- (additional INSERTs in the lab repository)\n\nCOMMIT;\n<\/pre><\/div>\n\n\n<p>Eighteen rows in total, all the foreign keys satisfied.<\/p>\n\n\n\n<p>This is the source state Debezium will snapshot.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-3-start-kafka-and-kafka-connect\">Step 3 \u2014 Start Kafka and Kafka Connect<\/h2>\n\n\n\n<p>On <code>srvdebezium<\/code>, create a Docker network and start Kafka in detached mode so the broker survives terminal closure.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\ndocker network create debezium-net\n\ndocker run -d --name kafka --hostname kafka --network debezium-net \\\n  -p 9092:9092 \\\n  -e CLUSTER_ID=$(docker run --rm quay.io\/debezium\/kafka:3.5 \/kafka\/bin\/kafka-storage.sh random-uuid) \\\n  -e NODE_ID=1 -e NODE_ROLE=combined \\\n  -e KAFKA_CONTROLLER_QUORUM_VOTERS=1@kafka:9093 \\\n  -e KAFKA_LISTENERS=PLAINTEXT:\/\/0.0.0.0:9092,CONTROLLER:\/\/0.0.0.0:9093 \\\n  -e KAFKA_ADVERTISED_LISTENERS=PLAINTEXT:\/\/kafka:9092 \\\n  quay.io\/debezium\/kafka:3.5\n<\/pre><\/div>\n\n\n<p>The Debezium Oracle connector ships without the Oracle JDBC driver for licensing reasons.<\/p>\n\n\n\n<p>Download <code>ojdbc11.jar<\/code> from Maven Central and mount it into Connect at startup:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nmkdir -p \/home\/fernando\/oracle-jdbc\ncurl -L \\\n  https:\/\/repo1.maven.org\/maven2\/com\/oracle\/database\/jdbc\/ojdbc11\/21.9.0.0\/ojdbc11-21.9.0.0.jar \\\n  -o \/home\/fernando\/oracle-jdbc\/ojdbc11.jar\n\ndocker run -d --name connect --network debezium-net \\\n  -p 8083:8083 \\\n  -e BOOTSTRAP_SERVERS=kafka:9092 \\\n  -e GROUP_ID=1 \\\n  -e CONFIG_STORAGE_TOPIC=my_connect_configs \\\n  -e OFFSET_STORAGE_TOPIC=my_connect_offsets \\\n  -e STATUS_STORAGE_TOPIC=my_connect_statuses \\\n  -v \/home\/fernando\/oracle-jdbc\/ojdbc11.jar:\/kafka\/connect\/debezium-connector-oracle\/ojdbc11.jar \\\n  quay.io\/debezium\/connect:3.5\n<\/pre><\/div>\n\n\n<p>Utilisation <code>-d<\/code> (detached).<br>Without it, closing the terminal stops the container \u2014 and stops your replication pipeline.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>If you are scoping an Oracle to PostgreSQL migration and want a second opinion on cutover design, <a href=\"https:\/\/rootfan.com\/fr\/services\/\">I offer a fixed-fee migration assessment \u2192<\/a><\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-4-register-the-oracle-source-connector\">Step 4 \u2014 Register the Oracle Source Connector<\/h2>\n\n\n\n<p>The connector configuration tells Debezium which database to read, which schema to capture, and where to store its internal schema history.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\ncurl -i -X POST -H &quot;Content-Type:application\/json&quot; \\\n  http:\/\/localhost:8083\/connectors\/ -d &#039;{\n    &quot;name&quot;: &quot;oracle-connector&quot;,\n    &quot;config&quot;: {\n      &quot;connector.class&quot;: &quot;io.debezium.connector.oracle.OracleConnector&quot;,\n      &quot;tasks.max&quot;: &quot;1&quot;,\n      &quot;database.hostname&quot;: &quot;192.168.0.180&quot;,\n      &quot;database.port&quot;: &quot;1521&quot;,\n      &quot;database.user&quot;: &quot;C##DBZUSER&quot;,\n      &quot;database.password&quot;: &quot;dbz&quot;,\n      &quot;database.dbname&quot;: &quot;ORADB&quot;,\n      &quot;database.pdb.name&quot;: &quot;pdb1&quot;,\n      &quot;topic.prefix&quot;: &quot;oracle&quot;,\n      &quot;schema.include.list&quot;: &quot;BANKING&quot;,\n      &quot;schema.history.internal.kafka.bootstrap.servers&quot;: &quot;kafka:9092&quot;,\n      &quot;schema.history.internal.kafka.topic&quot;: &quot;schemahistory.oracle&quot;,\n      &quot;decimal.handling.mode&quot;: &quot;string&quot;\n    }\n  }&#039;\n<\/pre><\/div>\n\n\n<p>Three settings worth understanding:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>schema.include.list: \"BANKING\"<\/code> captures every table in the BANKING schema.<br>Non <code>table.include.list<\/code> is set, so when a new table appears in BANKING, Debezium picks it up automatically \u2014 you'll see this in Step 6.<\/li>\n\n\n\n<li><code>topic.prefix: \"oracle\"<\/code> controls the Kafka topic naming.<br>Events for <code>BANKING.TRANSACTIONS<\/code> land on <code>oracle.BANKING.TRANSACTIONS<\/code>.<br>The PDB name does not appear in the topic name, even though <code>database.pdb.name<\/code> is set \u2014 it is used for filtering only.<\/li>\n\n\n\n<li><code>decimal.handling.mode: \"string\"<\/code> makes Oracle <code>NOMBRE<\/code> values arrive as readable strings instead of base64-encoded <code>BigDecimal<\/code> bytes.<br>Convenient for debugging events.<br>As we will see, this has a downstream consequence on the PostgreSQL side.<\/li>\n<\/ul>\n\n\n\n<p>The connector immediately starts a snapshot phase: it scans every captured table, publishes every existing row as an event with operation type <code>r<\/code> (read), and then switches to streaming mode where it tails the redo log.<\/p>\n\n\n\n<p>You can watch progress with <code>docker logs -f connect<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-5-watch-cdc-events-live\">Step 5 \u2014 Watch CDC Events Live<\/h2>\n\n\n\n<p>Open a second terminal on <code>srvdebezium<\/code> and start a Kafka console consumer to watch events as they arrive.<\/p>\n\n\n\n<p>Pipe through <code>grep<\/code> to extract just the meaningful fields \u2014 the full JSON envelope is dominated by the schema definition, which is fine for processing but unreadable on screen.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\ndocker run -it --rm --name watcher --network debezium-net \\\n  --entrypoint \/kafka\/bin\/kafka-console-consumer.sh \\\n  quay.io\/debezium\/kafka:3.5 \\\n  --bootstrap-server kafka:9092 \\\n  --topic oracle.BANKING.TRANSACTIONS \\\n  --from-beginning 2&gt;\/dev\/null \\\n  | grep -oE &#039;&quot;(op|TXN_ID|TXN_TYPE|AMOUNT|DESCRIPTION)&quot;:&quot;&#x5B;^&quot;]*&quot;&#039;\n<\/pre><\/div>\n\n\n<p>Four snapshot events appear immediately, one per existing transaction, each with <code>\"op\":\"r\"<\/code>.<\/p>\n\n\n\n<p>Now generate a live change on Oracle:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO transactions VALUES (100005, 10002, 102, &#039;DEPOSIT&#039;, 5000.00, SYSTIMESTAMP, &#039;Bonus deposit&#039;);\nCOMMIT;\n<\/pre><\/div>\n\n\n<p>A new event appears with <code>\"op\":\"c\"<\/code> and the full row in <code>after<\/code>.<\/p>\n\n\n\n<p>An UPDATE shows <code>\"op\":\"u\"<\/code> with both <code>before<\/code> et <code>after<\/code> populated.<\/p>\n\n\n\n<p>A DELETE shows <code>\"op\":\"d\"<\/code> avec <code>before<\/code> populated and <code>after<\/code> null.<\/p>\n\n\n\n<p>Sub-second latency in steady state.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-6-set-up-the-postgre-sql-sink\">Step 6 \u2014 Set Up the PostgreSQL Sink<\/h2>\n\n\n\n<p>On <code>srv2<\/code>, create the target user, database, and schema, and allow remote connections from <code>srvdebezium<\/code>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nsudo -u postgres psql -c &quot;CREATE USER banking WITH PASSWORD &#039;banking&#039;;&quot;\nsudo -u postgres psql -c &quot;CREATE DATABASE bankingdb OWNER banking;&quot;\nsudo -u postgres psql -d bankingdb -c &quot;CREATE SCHEMA banking AUTHORIZATION banking;&quot;\n<\/pre><\/div>\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\">\n# \/etc\/postgresql\/18\/main\/postgresql.conf\nlisten_addresses = &#8216;*'\n\n# \/etc\/postgresql\/18\/main\/pg_hba.conf\nhost    all    all    192.168.0.0\/24    scram-sha-256\n<\/div>\n\n\n<p>Then register the JDBC sink connector.<\/p>\n\n\n\n<p>Le <code>RegexRouter<\/code> SMT strips the <code>oracle.BANKING.<\/code> prefix from each topic before it becomes the target table name, so <code>oracle.BANKING.TRANSACTIONS<\/code> maps to <code>banking.transactions<\/code> plut\u00f4t que <code>banking.oracle_banking_transactions<\/code>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\ncurl -i -X POST -H &quot;Content-Type:application\/json&quot; \\\n  http:\/\/localhost:8083\/connectors\/ -d &#039;{\n    &quot;name&quot;: &quot;jdbc-sink-connector&quot;,\n    &quot;config&quot;: {\n      &quot;connector.class&quot;: &quot;io.debezium.connector.jdbc.JdbcSinkConnector&quot;,\n      &quot;tasks.max&quot;: &quot;1&quot;,\n      &quot;connection.url&quot;: &quot;jdbc:postgresql:\/\/192.168.0.181:5432\/bankingdb&quot;,\n      &quot;connection.username&quot;: &quot;banking&quot;,\n      &quot;connection.password&quot;: &quot;banking&quot;,\n      &quot;insert.mode&quot;: &quot;upsert&quot;,\n      &quot;delete.enabled&quot;: &quot;true&quot;,\n      &quot;primary.key.mode&quot;: &quot;record_key&quot;,\n      &quot;schema.evolution&quot;: &quot;basic&quot;,\n      &quot;topics.regex&quot;: &quot;oracle\\\\.BANKING\\\\..+&quot;,\n      &quot;table.name.format&quot;: &quot;banking.${topic}&quot;,\n      &quot;transforms&quot;: &quot;route&quot;,\n      &quot;transforms.route.type&quot;: &quot;org.apache.kafka.connect.transforms.RegexRouter&quot;,\n      &quot;transforms.route.regex&quot;: &quot;oracle\\\\.BANKING\\\\.(.*)&quot;,\n      &quot;transforms.route.replacement&quot;: &quot;$1&quot;\n    }\n  }&#039;\n<\/pre><\/div>\n\n\n<p>Within seconds of registration, the snapshot rows appear in PostgreSQL.<\/p>\n\n\n\n<p>The 18 BANKING rows replicate in under a minute.<\/p>\n\n\n\n<p>Any subsequent change on Oracle propagates to PostgreSQL in seconds.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"step-7-add-a-new-table-mid-stream\">Step 7 \u2014 Add a New Table Mid-Stream<\/h2>\n\n\n\n<p>This is where the pipeline proves itself.<\/p>\n\n\n\n<p>Add a <code>LOANS<\/code> table to BANKING while replication is running.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TABLE loans (\n  loan_id        NUMBER(10) PRIMARY KEY,\n  customer_id    NUMBER(8) NOT NULL,\n  loan_type      VARCHAR2(30),\n  principal      NUMBER(15,2) NOT NULL,\n  interest_rate  NUMBER(5,2),\n  start_date     DATE,\n  status         VARCHAR2(20),\n  CONSTRAINT fk_loan_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)\n);\n\nINSERT INTO loans VALUES (1, 1001, &#039;MORTGAGE&#039;, 250000.00, 3.25, DATE &#039;2023-01-15&#039;, &#039;ACTIVE&#039;);\nINSERT INTO loans VALUES (2, 1002, &#039;AUTO&#039;,     18000.00,  4.50, DATE &#039;2024-06-01&#039;, &#039;ACTIVE&#039;);\nINSERT INTO loans VALUES (3, 1003, &#039;PERSONAL&#039;, 5000.00,   6.75, DATE &#039;2025-02-10&#039;, &#039;ACTIVE&#039;);\nCOMMIT;\n<\/pre><\/div>\n\n\n<p>The Oracle connector picks up the new table because it matches <code>BANKING\\..*<\/code>.<\/p>\n\n\n\n<p>A new topic <code>oracle.BANKING.LOANS<\/code> appears in Kafka.<\/p>\n\n\n\n<p>The JDBC sink picks up that topic because it matches <code>oracle\\.BANKING\\..+<\/code>.<\/p>\n\n\n\n<p>A new table <code>banking.loans<\/code> is created in PostgreSQL with the three rows.<\/p>\n\n\n\n<p>No connector reconfiguration.<\/p>\n\n\n\n<p>No restart.<\/p>\n\n\n\n<p>No redeploy.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-schema-evolution-tradeoff\">The schema.evolution Tradeoff<\/h2>\n\n\n\n<p><code>schema.evolution: basic<\/code> is what made the auto-creation of <code>banking.loans<\/code> work without manual intervention.<\/p>\n\n\n\n<p>It is also what disqualifies this configuration for production.<\/p>\n\n\n\n<p>Ex\u00e9cuter <code>\\d banking.accounts<\/code> on PostgreSQL after replication and the table looks like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>                  Table \"banking.accounts\"\n    Column    |              Type              |   Default\n--------------+--------------------------------+----------------\n account_id   | text                           | not null\n customer_id  | text                           | not null\n branch_id    | text                           | not null\n account_type | text                           |\n balance      | text                           | '0.00'::text\n opened_at    | timestamp(6) without time zone |<\/code><\/pre>\n\n\n\n<p>Every Oracle <code>NOMBRE<\/code> column lands in PostgreSQL as <code>texte<\/code>.<\/p>\n\n\n\n<p>Including <code>balance<\/code>.<\/p>\n\n\n\n<p>With a default of <code>'0.00'::text<\/code>.<\/p>\n\n\n\n<p>This is the combined effect of two settings:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>schema.evolution: basic<\/code> \u2014 Debezium auto-creates target tables using the lowest-common-denominator JDBC types from the Kafka event schema.<\/li>\n\n\n\n<li><code>decimal.handling.mode: \"string\"<\/code> \u2014 Oracle <code>NOMBRE<\/code> values arrive as strings in Kafka events, so the auto-created PostgreSQL columns are typed accordingly.<\/li>\n<\/ul>\n\n\n\n<p>The practical impact appears as soon as you write a query: <code>WHERE customer_id = 1001<\/code> fails with <code>operator does not exist: text = integer<\/code>.<\/p>\n\n\n\n<p>You have to use a string literal: <code>WHERE customer_id = '1001'<\/code>.<\/p>\n\n\n\n<p>Every existing application query breaks.<\/p>\n\n\n\n<p>The production fix is straightforward but requires preparation:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Build the PostgreSQL schema beforehand using ora2pg or another conversion tool.<br>The schema gets the right types: <code>NUMERIC(15,2)<\/code> for amounts, <code>ENTIER<\/code> ou <code>NUM\u00c9RIQUE<\/code> for IDs, proper <code>VARCHAR(n)<\/code> lengths.<\/li>\n\n\n\n<li>Disable <code>schema.evolution<\/code> in the sink connector.<br>Debezium streams data into the existing schema without modifying it.<\/li>\n\n\n\n<li>Utilisation <code>decimal.handling.mode: \"precise\"<\/code> (the default).<br>Values arrive as Java <code>BigDecimal<\/code> and the JDBC sink correctly maps them to the PostgreSQL <code>NUM\u00c9RIQUE<\/code> colonnes.<\/li>\n<\/ol>\n\n\n\n<p>This is the pattern for any production migration.<\/p>\n\n\n\n<p>The schema evolution feature is for labs and prototypes \u2014 useful for proving the pipeline works, dangerous to ship.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-new-table-discovery-delay\">The New-Table Discovery Delay<\/h2>\n\n\n\n<p>When a new table appears in Oracle while replication is running, there is a small but real delay before it appears in PostgreSQL.<\/p>\n\n\n\n<p>The first <code>SELECT<\/code> on the new PostgreSQL table can return <code>relation does not exist<\/code> even though the Kafka topic is already populated.<\/p>\n\n\n\n<p>The sequence is:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Oracle commits the DDL \u2014 the new table appears in the redo log.<\/li>\n\n\n\n<li>Debezium captures the DDL and creates the Kafka topic.<br>At this point the topic exists, but the sink connector has not seen it yet.<\/li>\n\n\n\n<li>The INSERTs publish to the new topic.<\/li>\n\n\n\n<li>The sink connector eventually refreshes its topic metadata.<br>The default <code>metadata.max.age.ms<\/code> is 5 minutes, though Connect typically refreshes more often.<br>When it does, the sink discovers the new topic, reads the schema from the first event, runs <code>CR\u00c9ER UNE TABLE<\/code> on PostgreSQL via <code>schema.evolution: basic<\/code>, and then applies the events.<\/li>\n\n\n\n<li>The table now exists on PostgreSQL.<\/li>\n<\/ol>\n\n\n\n<p>Typical end-to-end discovery delay: 5\u201330 seconds.<\/p>\n\n\n\n<p>In a real migration, this matters if an application is expected to write to a brand-new Oracle table and read it from PostgreSQL immediately.<\/p>\n\n\n\n<p>Two ways to handle it:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lower <code>consumer.override.metadata.max.age.ms<\/code> in the sink connector config (e.g. to 5 seconds) for faster discovery.<\/li>\n\n\n\n<li>Pre-create tables in PostgreSQL with the correct types and disable <code>schema.evolution<\/code> \u2014 the production pattern recommended above.<br>Tables exist before any data flows.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"production-gotchas\">Production Gotchas<\/h2>\n\n\n\n<p>A few things to watch for in production that the lab does not surface.<\/p>\n\n\n\n<p><strong>Tables without primary keys.<\/strong><br>Debezium's UPDATE and DELETE events are keyed by the primary key.<br>Tables without a PK can be snapshotted and INSERTs replicated, but UPDATEs and DELETEs cannot be applied to PostgreSQL.<br>Audit the source schema for PK-less tables before scoping the migration.<\/p>\n\n\n\n<p><strong>LogMiner archive log retention.<\/strong><br>LogMiner can only mine archive logs that still exist on disk.<br>If your retention policy deletes archive logs after 24 hours and Debezium falls behind by more than that, the pipeline cannot resume from where it stopped.<br>Size archive log retention to your worst-case Debezium downtime \u2014 multiply your usual retention by 3 for safety.<\/p>\n\n\n\n<p><strong>Container persistence.<\/strong><br>Running <code>docker run -it<\/code> ties the container to the terminal.<br>Closing the terminal stops the container and breaks the pipeline.<br>Utilisation <code>-d<\/code> (detached) for everything that runs longer than a manual test.<\/p>\n\n\n\n<p><strong>Consumer group offsets.<\/strong><br>Deleting a sink connector and recreating it with the same name does not reset its position in Kafka.<br>The new connector picks up at the previous committed offset, skipping any events queued in between.<br>Reset offsets explicitly with <code>kafka-consumer-groups.sh --reset-offsets --to-earliest<\/code> before re-registering.<\/p>\n\n\n\n<p><strong>Schema name case.<\/strong><br>Oracle schema names are uppercase by default.<br>Le <code>schema.include.list<\/code> value in the connector config must match exactly \u2014 <code>BANKING<\/code>, pas <code>banking<\/code>.<br>The sink-side topic names follow the Oracle case, which is why <code>RegexRouter<\/code> strips an uppercase prefix.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"frequently-asked-questions\">Foire aux questions<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<div class=\"rank-math-list\">\n<div id=\"faq-question-1777800041851\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Can Debezium migrate the Oracle schema, or only the data?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Only the data.<br \/>Debezium captures DML events \u2014 INSERT, UPDATE, DELETE \u2014 from the Oracle redo log.<br \/>Schema definitions, stored procedures, triggers, views, and sequences must be migrated separately, typically with ora2pg.<br \/>The schema.evolution: basic feature can auto-create target tables but with simplified types and no constraints \u2014 fine for labs, wrong for production.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1777800042851\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>What is the latency between Oracle and PostgreSQL?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>A few seconds in steady state on a healthy LogMiner setup.<br \/>Latency grows under heavy write load or if archive log generation outpaces LogMiner's read rate.<br \/>Plan for sub-minute lag and monitor it explicitly with kafka-consumer-groups.sh &#8211;describe.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1777800043851\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Is LogMiner deprecated in Oracle 19c or 21c?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>LogMiner is the supported CDC mechanism in Oracle 19c.<br \/>In Oracle 21c and later, Oracle introduced XStream as a paid alternative.<br \/>Debezium continues to support both \u2014 the LogMiner adapter is the default and works without an additional Oracle license.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1777800044851\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Can this run against Oracle Standard Edition?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Yes, with caveats.<br \/>LogMiner is available in Standard Edition 2, but supplemental logging and archivelog mode are required, and some optimisations available in Enterprise Edition are not.<br \/>Test against the exact edition you have.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1777800045851\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Can I use Debezium for one-shot migration without keeping it running?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Oui.<br \/>You can run the snapshot phase, wait for it to complete, then stop the connector.<br \/>But this is rarely the right call \u2014 if you are going to set up Debezium, you may as well let it stream until cutover so you have a tested, lag-free pipeline ready when the application repoints.<\/p>\n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"in-summary\">En r\u00e9sum\u00e9<\/h2>\n\n\n\n<p>Debezium turns an Oracle to PostgreSQL migration from a single-shot dump-and-restore into a controllable, observable pipeline that you can run for weeks before cutover.<br>LogMiner reads Oracle's redo log, Kafka holds the event stream, and the JDBC sink applies changes to PostgreSQL with sub-minute latency.<\/p>\n\n\n\n<p>The result is a real-time replication channel you can validate, replay, and cancel cleanly \u2014 the foundation of any zero or near-zero downtime migration.<\/p>\n\n\n\n<p>If you are planning an Oracle to PostgreSQL migration and want help designing the cutover phase or reviewing your CDC architecture, <a href=\"https:\/\/rootfan.com\/fr\/services\/\">prendre contact \u2192<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>TL;DR: Debezium reads Oracle&#8217;s redo logs through LogMiner, publishes every change as a Kafka event, and a JDBC sink connector applies those events to PostgreSQL in real time.The result is a transparent, replayable replication pipeline you can run for weeks before cutover \u2014 useful for any Oracle to PostgreSQL migration that needs zero or near-zero &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/rootfan.com\/fr\/oracle-to-postgresql-replication-debezium\/\" class=\"more-link\">Continuer la lecture<span class=\"screen-reader-text\"> de &laquo;&nbsp;How to Set Up Real-Time Oracle to PostgreSQL Replication Using Debezium&nbsp;&raquo;<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":6833,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_focus_keyword":"oracle to postgresql replication","rank_math_title":"How to Set Up Real-Time Oracle to PostgreSQL Replication Using Debezium","rank_math_description":"Step-by-step guide to setting up real-time Oracle to PostgreSQL replication using Debezium and Kafka. Covers LogMiner setup, capture user grants, sink connector config, schema.evolution gotchas, and production tips.","rank_math_robots":"","rank_math_og_title":"","rank_math_og_description":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[31,126],"tags":[143,137,81],"class_list":["post-6832","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","category-postgresql","tag-data-migration","tag-migration","tag-step-by-step"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/rootfan.com\/wp-content\/uploads\/pexels-photo-33593134.jpeg?fit=1880%2C1253&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/comments?post=6832"}],"version-history":[{"count":4,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6832\/revisions"}],"predecessor-version":[{"id":6837,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6832\/revisions\/6837"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media\/6833"}],"wp:attachment":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media?parent=6832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/categories?post=6832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/tags?post=6832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}