Oracle → PostgreSQL

Replicate an Oracle table to PostgreSQL with DirectLog capture, from preparing the CDB, the PDB and PostgreSQL to verifying an insert, an update and a delete.

This walkthrough builds a pipeline that replicates an Oracle table to PostgreSQL with Oracle DirectLog, the default Oracle capture mode.

What you'll build

The table CUSTOMERS lives in a schema of a pluggable database (PDB). A log broker on the Oracle host serves the online redo and archived logs to one agent, which applies every committed change to customers in PostgreSQL. The agent connects to both the PDB service and the CDB root service.

Arrows point from the side that opens the connection. The agent and the broker also connect out to the control plane over HTTPS.

Before you begin

• An Oracle multitenant database with a PDB. SQL Flow is tested on Oracle 12.2, 19.3, 21.3 and 23.26 (26ai); other releases are expected to work. A non-CDB database works too: run the statements without the CONTAINER clauses.
• One redo thread, and online redo logs and archived logs on a file system, not in ASM.
• A TLS listener on the database, with a server certificate issued by a certificate authority the agent machine trusts, and the distinguished name (DN) of that certificate. With Encryption at Require (default), the agent validates the certificate and checks its DN. See Oracle: Connection encryption.
• SYSDBA access for the setup, and administrator rights on the Oracle host to install the log broker. On Linux, the broker needs glibc 2.28 or later, as on Oracle Linux 8.
• An existing schema in the PDB where you can create a table. This walkthrough calls it <owner>.
• A PostgreSQL 14 to 18 server, and a role on it that can create roles and databases. SQL Flow stops on other versions when it creates target tables for an Oracle source. The server should offer TLS with a certificate the agent machine trusts, because the connection validates it by default.
• A 64-bit Windows or Linux machine for the agent. It reaches the Oracle TLS listener, the Oracle host on port 7003, and PostgreSQL on port 5432.
• A SQL Flow user with the Account admin or User role. See Getting started.

1. Check the database

Connect to CDB$ROOT as SYSDBA, and check the archive mode, supplemental logging, block checksums and redo threads:

SELECT log_mode, supplemental_log_data_min FROM v$database;
SELECT value FROM v$parameter WHERE name = 'db_block_checksum';
SELECT COUNT(*) AS enabled_threads FROM v$thread WHERE enabled <> 'DISABLED';

You need ARCHIVELOG, db_block_checksum set to TYPICAL or FULL, and one enabled thread. DirectLog tells a half-written redo block from a complete one by its checksum.

1. If LOG_MODE is NOARCHIVELOG, enable archive mode. This restarts the database:

   SHUTDOWN IMMEDIATE
   STARTUP MOUNT
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;

2. If SUPPLEMENTAL_LOG_DATA_MIN is NO, enable minimal supplemental logging:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2. Create the capture user

In CDB$ROOT, create a common user and grant it the redo catalog views DirectLog reads. Replace <capture_password>, and <pdb_name> with the PDB's container name.

CREATE USER C##SQLFLOW IDENTIFIED BY "<capture_password>" CONTAINER=ALL;
GRANT CREATE SESSION, SET CONTAINER TO C##SQLFLOW CONTAINER=ALL;

GRANT SELECT ON SYS.V_$DATABASE TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$DATABASE_INCARNATION TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOG TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$LOGFILE TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$THREAD TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$TRANSACTION TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$PARAMETER TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$SGASTAT TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO C##SQLFLOW CONTAINER=ALL;
GRANT SELECT ON SYS.V_$CONTAINERS TO C##SQLFLOW CONTAINER=ALL;

ALTER USER C##SQLFLOW SET CONTAINER_DATA = (CDB$ROOT, <pdb_name>) CONTAINER = CURRENT;

The CONTAINER_DATA statement lets the root connection see the PDB's open transactions.

To use table resync or key repair on this pipeline later, also run GRANT ALTER SYSTEM TO C##SQLFLOW CONTAINER=ALL;, because those operations force a log switch. See Oracle DirectLog: Setup.

3. Create the table and its grants

Switch to the PDB, create the demo table with three rows, grant the dictionary views and the table to the capture user, and add an (ALL) COLUMNS supplemental log group. Without it, an update logs only the changed columns, and DirectLog can't produce the complete row.

ALTER SESSION SET CONTAINER = <pdb_name>;

CREATE TABLE <owner>.CUSTOMERS (
  CUSTOMER_ID NUMBER(10)    PRIMARY KEY,
  NAME        VARCHAR2(100) NOT NULL,
  EMAIL       VARCHAR2(200)
);
INSERT INTO <owner>.CUSTOMERS VALUES (1, 'Ana Silva', 'ana@example.com');
INSERT INTO <owner>.CUSTOMERS VALUES (2, 'Ben Okafor', 'ben@example.com');
INSERT INTO <owner>.CUSTOMERS VALUES (3, 'Chen Wei', 'chen@example.com');
COMMIT;

GRANT SELECT ON SYS.DBA_SEGMENTS TO C##SQLFLOW;
GRANT SELECT ON SYS.DBA_EXTENTS TO C##SQLFLOW;
GRANT SELECT ON SYS.DBA_TABLESPACES TO C##SQLFLOW;
GRANT SELECT ON SYS.DBA_OBJECTS TO C##SQLFLOW;

GRANT SELECT, FLASHBACK ON <owner>.CUSTOMERS TO C##SQLFLOW;
ALTER TABLE <owner>.CUSTOMERS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The table has no LOB columns, so it needs no LOB or undo retention changes. For tables with BLOB, CLOB, NCLOB or XMLTYPE columns, see Oracle DirectLog: Setup.

4. Find the redo folders

In CDB$ROOT, list the online redo members and recent archived logs:

SELECT member FROM v$logfile;
SELECT name FROM v$archived_log WHERE name IS NOT NULL AND completion_time > SYSDATE - 1;

If the second query returns no rows, run ALTER SYSTEM ARCHIVE LOG CURRENT; and query again. Note the folders that hold these files: the log broker must serve every one of them. A path that starts with +, such as +DATA/..., is in ASM, which the broker can't read; use LogMiner for that database.

5. Prepare PostgreSQL

Connect with psql and create the apply role and a database it owns. Owning the database lets the role create the target schema and table, and SQL Flow's sqlflow bookkeeping schema. Replace <apply_password>.

CREATE ROLE sqlflow_apply WITH LOGIN PASSWORD '<apply_password>';
CREATE DATABASE sqlflow_demo OWNER sqlflow_apply;

If pg_hba.conf restricts connections by address, allow the agent machine, then reload the configuration.

6. Install the agent

1. In SQL Flow, open Agents and select Add agent.
2. Choose the agent machine's platform, download the agent, and copy it to the agent machine.
3. Keep Install as a service, select Copy command, and run the command in the folder that holds the download:

.\SqlFlow.Agent.exe install --account <join_code>

4. On Agents, check the new agent's name and Agent id, then select Approve agent.

If the TLS listener requires a client certificate, put the wallet that holds it on the agent machine, in a folder the agent's service account can read: NT SERVICE\SqlFlow.Agent on Windows, or the sqlflow-agent user on Linux.

7. Install the log broker on the Oracle host

1. On Agents, select Add broker, choose the Oracle host's platform, and download the broker.
2. Move the executable to a permanent folder on the Oracle host, such as /opt/sqlflow/logbroker or C:\Program Files\SqlFlow\LogBroker.
3. In Log folders, enter the online redo folder and the archived log folder from step 4, separated by a comma.
4. In Agent addresses, check that the IP address of the agent machine is listed. The panel fills in your known agents, and only listed agents can connect. Select Copy command, and run the command in the permanent folder:

.\SqlFlow.LogBroker.exe install --account <join_code> --allow-paths '<online_redo_folder>,<archive_folder>' --allow-ips <agent_addresses>

5. Note the fingerprint on the output line that starts Created broker identity.
6. On Agents, under Brokers, check the broker's Certificate SHA-256 under Details and its Serves row, then select Approve.

On Linux the broker runs as the sqlflow-logbroker user. If a parent folder of the redo folders is closed to other users, give that user traverse (execute) permission on it. See Log brokers.

8. Create the pipeline

Open Pipelines and select New pipeline.

Source

1. Select the Oracle tile, and keep DirectLog under Capture mode.

2. Check that Runs on names your agent.

3. Fill in the connection:

   Field	Value
   Host	<oracle_host>, the Oracle host's machine name
   Port	<tls_port>, the TLS listener's port
   Service name (PDB)	<pdb_service>, the service of the PDB
   Username	C##SQLFLOW
   Password source	Password
   Password	<capture_password>
   Server certificate DN	<certificate_dn>, the DN of the listener's certificate, such as CN=oracle.example.com. Required under Require: Next stays disabled while it's empty.
   Wallet path on agent	Blank, unless the listener requires a client certificate. Then the wallet folder from step 6.
   Encryption	Require (default)

4. Open Advanced capture settings and enter <root_service>, the CDB root service, in Root service for capture. Oracle runs redo discovery only from the root, so a PDB source needs it.

5. The Log broker panel reads Log broker active on and the Oracle host. If it reports no broker for your host, select Use an existing broker and choose the broker you installed.

6. Select Test connection & list tables. The test also checks that the agent reaches the broker. Select Next.

Target

1. Under Relational databases, select the PostgreSQL tile.

2. Under Where this target is applied, keep This pipeline’s agent (local, no relay). With one agent online, the step states this as a fact.

3. Fill in the connection:

   Field	Value
   Host	<postgres_host>
   Port	Blank, for 5432
   Database	sqlflow_demo
   Username	sqlflow_apply
   Password source	Password
   Password	<apply_password>
   Encryption	Require (default), which uses TLS and validates the server certificate for <postgres_host>. On a test server without TLS, choose Prefer, which connects unencrypted when the server offers no TLS.

4. Keep Collapse row versions (faster) selected. It needs the (ALL) COLUMNS supplemental log group you added in step 3.

5. Select Test connection, then Next. If the test fails on the certificate under Require, see PostgreSQL target: Options.

Tables

The wizard selects every table it can replicate. Select Deselect all, then select <OWNER>.CUSTOMERS, and select Next.

Prepare

The card marked target schema says the table must exist in the PostgreSQL target. Select Show SQL to read the statements. They create the schema <owner> in lowercase and the table customers, with a primary key on customer_id. Select Prepare target schema and confirm with Prepare schema. Then select Next.

Review

1. Check the Pipeline name the wizard generated, and change it if you like.
2. Select Create and start.

The pipeline checks the privileges and the supplemental logging when it starts. If a grant is missing, it stops with a message that names each missing grant and the statement that fixes it. See Oracle DirectLog: Troubleshooting.

9. Watch the initial load

The pipeline's page shows Loading and an Initial load card, then Healthy. Check the target in psql, connected to sqlflow_demo. Replace <owner> with the schema name in lowercase: Oracle's uppercase names become lowercase on PostgreSQL.

SELECT customer_id, name, email FROM <owner>.customers ORDER BY customer_id;

The query returns the three rows from step 3.

10. Verify changes

1. Connect to the PDB service and change the source table:

   INSERT INTO <owner>.CUSTOMERS VALUES (4, 'Dana Lee', 'dana@example.com');
   UPDATE <owner>.CUSTOMERS SET EMAIL = 'ben.okafor@example.com' WHERE CUSTOMER_ID = 2;
   DELETE FROM <owner>.CUSTOMERS WHERE CUSTOMER_ID = 3;
   COMMIT;

2. Run the PostgreSQL query again. Customer 4 is new, customer 2 has the new email, and customer 3 is gone:

    customer_id |    name    |         email
   -------------+------------+------------------------
              1 | Ana Silva  | ana@example.com
              2 | Ben Okafor | ben.okafor@example.com
              4 | Dana Lee   | dana@example.com

3. On the pipeline's Overview tab, the Changes card counts one insert, one update and one delete under Observed and Applied.

DirectLog emits only committed changes, so nothing reaches PostgreSQL until the COMMIT.

11. Clean up

1. On the pipeline's page, select Pause and wait for Paused.

2. Select Actions > Archive and confirm, then Actions > Delete and confirm.

3. Drop the demo table in the PDB, then the capture user in CDB$ROOT:

   ALTER SESSION SET CONTAINER = <pdb_name>;
   DROP TABLE <owner>.CUSTOMERS PURGE;
   ALTER SESSION SET CONTAINER = CDB$ROOT;
   DROP USER C##SQLFLOW CASCADE;

   Archive mode and minimal supplemental logging stay as they are. Turn them off only if nothing else needs them.

4. In psql, connect to another database, such as postgres, and drop the demo database and role:

   DROP DATABASE sqlflow_demo;
   DROP ROLE sqlflow_apply;

5. To remove the agent or the broker as well, see Remove an agent and Remove a broker.

Next steps

• Oracle DirectLog, for every requirement and limitation
• Oracle LogMiner, for ASM and RAC
• Large values and LOBs, before you add tables with LOB columns
• Naming conventions per target engine
• Compare