DirectLog

Set up Oracle DirectLog capture, which reads redo and archived log files through a log broker on the Oracle host.

DirectLog is the default Oracle capture mode. It reads Oracle's redo and archived log files directly, so the database does no mining work and changes reach the agent within milliseconds of being written to the online redo log.

How it works

A log broker on the Oracle host serves the online redo log members and the archived log files to the agent over TLS, and the agent decodes the committed changes. When the online redo log can't be read, capture continues from the archived logs, with more lag. Table metadata, and LOB values that the redo doesn't carry in full, are read over the ordinary database connection.

Requirements

Setup

Run the SQL steps as an Oracle DBA. The examples use the common user C##SQLFLOW. Replace <pdb_name> with the PDB that holds your tables, <owner>.<table> with each table you replicate, and <password> with the capture user's password. On a non-CDB database, run the same statements without the CONTAINER clauses and skip the CONTAINER_DATA statement.

1. Check archive mode, supplemental logging, Flashback Database and the database character set in the root container:

   SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container FROM dual;
   SELECT log_mode, supplemental_log_data_min, flashback_on FROM v$database;
   SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

   If the character set isn't one of those in Requirements, use LogMiner.

2. If LOG_MODE is NOARCHIVELOG, enable archive mode. This restarts the database, so schedule it:

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

   Keep archived logs on disk long enough to cover the initial load and the longest agent outage you plan for. DirectLog can't skip a missing archived log.

3. Enable minimal supplemental logging:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

4. In CDB$ROOT, create the capture user and grant read access to the redo catalog views:

   CREATE USER C##SQLFLOW IDENTIFIED BY "<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.

   If you'll use table resync, key repair or in-flight processing rules with this source, also grant ALTER SYSTEM in the root. Those operations may force a log switch, and each forced switch archives one online log.

   GRANT ALTER SYSTEM TO C##SQLFLOW CONTAINER=ALL;

5. In the PDB, grant the dictionary views, grant access to each table, and add an (ALL) COLUMNS supplemental log group to each table:

   ALTER SESSION SET CONTAINER = <pdb_name>;
   
   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>.<table> TO C##SQLFLOW;
   ALTER TABLE <owner>.<table> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

   Repeat the last two statements for every table, including tables you add to the pipeline later. Check the result:

   SELECT owner, table_name, log_group_type
   FROM all_log_groups
   WHERE owner = '<OWNER>' AND log_group_type = 'ALL COLUMN LOGGING';

6. If a selected table has BLOB, CLOB, NCLOB or XMLTYPE columns, guarantee one hour of LOB and undo history, because SQL Flow reads some LOB values from the table as of the commit.

   With Flashback Database on (FLASHBACK_ON is YES in step 1), give each LOB column RETENTION MIN 3600 with the MODIFY LOB statement below. For an XMLTYPE column, name the LOB "<COLUMN>".XMLDATA.

   With Flashback Database off, RETENTION MIN doesn't keep old LOB versions, so DirectLog refuses it. Give each LOB column RETENTION MAX with a MAXSIZE sized for its LOB changes over one hour, in its SecureFile storage clause, for example STORE AS SECUREFILE (RETENTION MAX STORAGE (MAXSIZE <size>)).

   Find the active undo tablespace first, in the container that owns it, then set the retention:

   SELECT name, value FROM v$parameter WHERE name IN ('undo_tablespace', 'undo_retention');
   
   ALTER TABLE "<OWNER>"."<TABLE>" MODIFY LOB ("<COLUMN>") (RETENTION MIN 3600);
   ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;
   ALTER TABLESPACE <undo_tablespace> RETENTION GUARANTEE;

   With RETENTION GUARANTEE, Oracle rejects writes when the undo tablespace fills, rather than discarding history. Size the undo tablespace for your write rate before you enable it.

   The wizard's Prepare step generates the MODIFY LOB statements for the exact columns that need them. With Flashback Database off, it marks those columns MANUAL instead, because only you can size MAXSIZE.

7. Configure a TLS listener on the database if it only has a TCP listener, and note the DN of its server certificate.

8. Install a log broker on the Oracle host. In the wizard's source step, choose Add broker (or Use an existing broker when the connection uses an alias for the host), allow both the online redo folders and the archive folders, and run the install command the panel gives you on the host. Check that Agent addresses lists the agents that read through the broker. When the database runs in a container, the folders must be visible to the broker at the same paths on the host.

9. In New pipeline, choose Oracle and DirectLog. Enter the TLS listener port, the PDB service in Service name (PDB), the capture user, and the Server certificate DN. Fill in Wallet path on agent only if the listener requires a client certificate. For a PDB, open Advanced capture settings and enter the CDB root service in Root service for capture.

10. Choose Test connection & list tables. The test connects, lists the tables, checks that the agent can reach the broker, and finds the online redo and archive folders, which the broker must be allowed to read.

11. Select your tables and continue to Prepare. For DirectLog, Prepare checks the LOB retention of the selected tables and shows Copy DBA SQL when a column needs changes. The remaining checks (privileges, supplemental logging, undo retention) run when the pipeline starts, before the initial load.

Encrypted data

• TDE (tablespace or column encryption): refused. DirectLog has no decryption step, so it refuses a selected table in a TDE-encrypted tablespace or with encrypted columns by name, before the initial load. Capture those tables with LogMiner.
• Encrypted SecureFile LOBs: refused by name before the initial load. Capture these tables with LogMiner.
• Encrypted connections: see Connection encryption. The broker serves log files to the agent over TLS.

Pros and cons

Pros

• Lowest lag of the two Oracle modes: changes are read from the online redo log as they're written.
• No mining work inside the database; the broker reads files and the agent parses them.
• No LOGMINING privilege; the capture user needs read access to catalog views and tables.
• Captures native BOOLEAN, JSON and VECTOR columns, which LogMiner refuses.

Cons

• Needs a log broker on the Oracle host.
• Can't read redo stored in ASM, and reads only one redo thread, so Oracle RAC isn't supported.
• Every table needs an (ALL) COLUMNS supplemental log group, which increases redo volume.
• Tables with LOB columns need SecureFile retention and guaranteed undo.
• Refuses tables in TDE-encrypted tablespaces, and tables with encrypted columns or encrypted SecureFile LOBs.

Limitations

• Redo layout: online redo block sizes of 512, 1024 and 4096 bytes. One enabled redo thread.
• Table shapes refused:
  • Virtual (generated) columns.
  • XMLTYPE columns not stored as CLOB (binary, transportable binary and object-relational XML).
  • BFILE, REF, nested-table, SYS.ANYDATA, SYS.ANYTYPE and SYS.ANYDATASET columns, and URI-type columns (SYS.URITYPE, HTTPURITYPE, DBURITYPE, XDBURITYPE). LogMiner refuses them too.
  • Object and VARRAY columns whose attributes or elements aren't supported scalar types.
  • Index-organized tables with an overflow segment.
  • BLOB, CLOB and NCLOB columns stored as BasicFile, or as partitioned LOBs.
• LONG and LONG RAW: an update that re-chains a row's LONG or LONG RAW value across blocks, for example by growing it, stops capture by name. Capture such tables with LogMiner. The initial load reads values up to 16 MiB; a larger value stops the load.
• Character data: a stored value with bytes that the database character set doesn't define stops capture rather than being written with replacement characters.
• Keys: relational targets need a key. SQL Flow takes an Oracle table's key from its primary key constraint.
• Archived log retention: an archived log deleted before DirectLog reads it stops the pipeline. Restore the archive, or reload the pipeline.

See Data type support by engine for type mapping, and Schema changes for DDL handling.

Troubleshooting

Server certificate DN missing for TLS

The Oracle connection requires TLS: enter the Server certificate DN of the listener's certificate.

Cause: the connection has no Server certificate DN, and either Encryption is Require (default) or a Wallet path on agent is set. The connection form requires the DN under Require, so this message usually comes from a connection created with the CLI.

Fix: enter the listener's TLS port and the Server certificate DN, then test again.

Log paths not discovered

Could not discover the DirectLog log-path candidates for this source

Cause: the source test couldn't read the redo catalog views, or found no filesystem path. A message containing V$LOGFILE and the archive destination views report no filesystem redo path means the redo and archives are in ASM, which the broker can't read.

Fix: grant the V_$LOGFILE, V_$ARCHIVE_DEST and V_$ARCHIVED_LOG views (step 4 of Setup). If the logs are in ASM, use LogMiner.

Prerequisite check fails before capture

Oracle DirectLog prerequisite check failed before capture

Cause: the capture user can't read a catalog view, or the database isn't in ARCHIVELOG mode. The message names each gap and the grant that fixes it, for example root/admin cannot read V$LOG; GRANT SELECT ON SYS.V_$LOG TO <user> CONTAINER=ALL. Views marked source dictionary/PDB are granted in the PDB.

Fix: run the grants the message lists, then restart the pipeline.

Root login can't see the source container

The CDB root connection's login ... cannot see the source container

Cause: the common user's CONTAINER_DATA attribute covers only the root.

Fix: run the statement the message ends with, in CDB$ROOT: ALTER USER <user> SET CONTAINER_DATA = (CDB$ROOT, <pdb_name>) CONTAINER = CURRENT;

Supplemental logging not ready

Oracle DirectLog supplemental-logging readiness failed before capture

Cause: either V$DATABASE.SUPPLEMENTAL_LOG_DATA_MIN is NO, or the message lists tables that lack an (ALL) COLUMN supplemental log group. SQL Flow never enables supplemental logging itself.

Fix: run ALTER DATABASE ADD SUPPLEMENTAL LOG DATA and ALTER TABLE <owner>.<table> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS for each listed table, then restart.

Exact-LOB history not ready

Oracle DirectLog exact-LOB history is not ready for the required 3600-second horizon.

Cause: a selected table's LOB column isn't SecureFile, is partitioned, or its segment hasn't been created yet, or its retention isn't RETENTION MIN of at least 3,600 seconds with Flashback Database on, or RETENTION MAX. RETENTION MIN with Flashback Database off is refused. The Prepare step shows this; at pipeline start it appears as Oracle DirectLog exact-LOB history readiness failed before capture.

Fix: use Copy DBA SQL on the Prepare step, have a DBA run it in the source PDB, then choose Check again. BasicFile and partitioned LOBs, and columns in a database with Flashback Database off, need a manual change, which the generated SQL marks as MANUAL: enable Flashback Database, or move the column to RETENTION MAX with a MAXSIZE.

Undo retention not ready

Oracle DirectLog undo-retention readiness failed before capture

Cause: a selected table has LOB columns, and UNDO_RETENTION is under 3,600 seconds or the undo tablespace isn't RETENTION GUARANTEE. If the message says it could not verify undo retention, the capture user can't read V$PARAMETER or DBA_TABLESPACES.

Fix: run ALTER SYSTEM SET undo_retention=3600; and ALTER TABLESPACE <undo_tablespace> RETENTION GUARANTEE;, or grant the missing views, then restart.

Column type no capture mode supports

Table <schema>.<table> has a <type> column "<column>", which Oracle lists as unsupported by DBMS_LOGMNR: the table would silently produce no usable DML records. No SqlFlow capture mode supports this column type (DirectLog refuses it too); exclude this table from capture.

Cause: the table has a BFILE, REF, nested-table, SYS.ANYDATA, SYS.ANYTYPE, SYS.ANYDATASET or URI-type column, which neither capture mode can capture. DirectLog can also report it as <schema>.<table>: composite/unmapped column(s) <column> (<type>) flatten into hidden physical columns the bootstrap can't expand.

Fix: remove the table from the pipeline, or change the column to a supported type.

LONG update can't be reconstructed

DirectLog does not yet reconstruct a re-chained LONG/LONG RAW row update (its last-piece marker is carried by an OP 11.8 forwarding change); capture this table with LogMiner.

Cause: an update left a row's LONG or LONG RAW value spread over more than one block, typically because the value grew.

Fix: capture the table in a LogMiner pipeline, and remove it from this one.

LONG value larger than 16 MiB

oracle_long_prefetch_value_exceeds_allowance: complete bounded original-result LONG acquisition is required.

Cause: the initial load read a LONG or LONG RAW value larger than 16 MiB.

Fix: remove the table from the pipeline, or convert the column to CLOB or BLOB.

Database character set not supported

Oracle database character set '<name>' is not supported for raw value decoding.

Cause: the database's NLS_CHARACTERSET isn't one that DirectLog decodes. See Requirements.

Fix: use LogMiner, which reads text through the database.

Online redo tail unavailable

Oracle DirectLog online tail is unavailable ... online redo tail supports exactly one enabled thread

Cause: the database runs more than one redo thread (Oracle RAC).

Fix: use LogMiner.

Missing ALTER SYSTEM for log switch

oracle_archive_fence_privilege_required: The configured Oracle root connection requires ALTER SYSTEM for ALTER SYSTEM ARCHIVE LOG CURRENT.

Cause: a table resync, a key repair, or the first load of a pipeline with in-flight processing rules needed to force a log switch, and the capture user lacks ALTER SYSTEM in the root.

Fix: run GRANT ALTER SYSTEM TO <user> CONTAINER=ALL; in CDB$ROOT, then retry the operation.

Unrecoverable redo gap

unrecoverable redo gap for Oracle DirectLog group

Cause: an archived log that DirectLog still needed was deleted, for example by an RMAN retention policy, before the agent read it.

Fix: restore the missing archived log to its archive folder, or reload the pipeline. Keep archived logs long enough to cover agent outages.

See also

• Oracle overview
• LogMiner
• Log brokers
• Prepare
• Oracle → PostgreSQL walkthrough