{"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":"replicacion-de-oracle-a-postgresql-debezium","status":"publish","type":"post","link":"https:\/\/rootfan.com\/es\/oracle-to-postgresql-replication-debezium\/","title":{"rendered":"C\u00f3mo configurar la replicaci\u00f3n en tiempo real de Oracle a PostgreSQL usando Debezium"},"content":{"rendered":"<p id=\"tl-dr\"><strong>En resumen<\/strong> Debezium lee los registros de rehacer de Oracle a trav\u00e9s de LogMiner, publica cada cambio como un evento de Kafka y un conector JDBC de destino aplica esos eventos a PostgreSQL en tiempo real.<br>El resultado es un pipeline de replicaci\u00f3n transparente y reproducible que puedes ejecutar durante semanas antes de la migraci\u00f3n, \u00fatil para cualquier migraci\u00f3n de Oracle a PostgreSQL que requiera tiempo de inactividad cero o cercano a cero.<br>Esta publicaci\u00f3n repasa una configuraci\u00f3n de extremo a extremo que funciona, en servidores Oracle y PostgreSQL reales, con un esquema bancario aut\u00f3nomo que puede copiar y reproducir.<\/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>\u00cdndice<\/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\">Preguntas frecuentes<\/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 resumen<\/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>clientes<\/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>Utilice <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\/es\/servicios\/\">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>No <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>N\u00daMERO<\/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> y <code>after<\/code> populated.<\/p>\n\n\n\n<p>A DELETE shows <code>\"op\":\"d\"<\/code> con <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>En <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> en lugar de <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>Ejecutar <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>N\u00daMERO<\/code> column lands in PostgreSQL as <code>texto<\/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>N\u00daMERO<\/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>ENTERO<\/code> o <code>NUM\u00c9RICO<\/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>Utilice <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\u00c9RICO<\/code> columnas.<\/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>SELECCIONE<\/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>CREAR TABLA<\/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>Utilice <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>En <code>schema.include.list<\/code> value in the connector config must match exactly \u2014 <code>BANKING<\/code>, no <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\">Preguntas frecuentes<\/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>S\u00ed.<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 resumen<\/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\/es\/servicios\/\">ponerse en contacto<\/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\/es\/oracle-to-postgresql-replication-debezium\/\" class=\"more-link\">Seguir leyendo<span class=\"screen-reader-text\"> &#8220;How to Set Up Real-Time Oracle to PostgreSQL Replication Using Debezium&#8221;<\/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\/es\/wp-json\/wp\/v2\/posts\/6832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/comments?post=6832"}],"version-history":[{"count":4,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts\/6832\/revisions"}],"predecessor-version":[{"id":6837,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/posts\/6832\/revisions\/6837"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/media\/6833"}],"wp:attachment":[{"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/media?parent=6832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/categories?post=6832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootfan.com\/es\/wp-json\/wp\/v2\/tags?post=6832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}