LogMiner

Set up Oracle LogMiner capture, which mines redo with DBMS_LOGMNR over the agent's ordinary database connection.

LogMiner captures Oracle changes through Oracle's own DBMS_LOGMNR package. Use it when you can't install a log broker on the database host, or when redo is stored in ASM.

How it works

The agent runs LogMiner sessions over its ordinary database connection, in CDB$ROOT for a PDB source, and reads the committed changes from V$LOGMNR_CONTENTS. On Oracle RAC it captures the changes of every redo thread. LOB values that LogMiner doesn't return in full are read back from the table as of the commit.

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 and supplemental logging in the root container:

   SELECT log_mode, supplemental_log_data_min FROM v$database;

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

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

3. Enable minimal supplemental logging:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

4. In CDB$ROOT, create the capture user and grant the mining privileges:

   CREATE USER C##SQLFLOW IDENTIFIED BY "<password>" CONTAINER=ALL;
   GRANT CREATE SESSION, SET CONTAINER TO C##SQLFLOW CONTAINER=ALL;
   GRANT LOGMINING TO C##SQLFLOW CONTAINER=ALL;
   GRANT SELECT ANY DICTIONARY TO C##SQLFLOW CONTAINER=ALL;
   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##SQLFLOW CONTAINER=ALL;
   
   ALTER USER C##SQLFLOW SET CONTAINER_DATA = (CDB$ROOT, <pdb_name>) CONTAINER = CURRENT;

   Grant these from the root with CONTAINER=ALL: the mining session runs in CDB$ROOT, so a grant made only inside the PDB is invisible to it. The CONTAINER_DATA statement lets the root connection see the PDB's open transactions.

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

   ALTER SESSION SET CONTAINER = <pdb_name>;
   GRANT SELECT, FLASHBACK ON <owner>.<table> TO C##SQLFLOW;
   ALTER TABLE <owner>.<table> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

   FLASHBACK lets SQL Flow read a LOB or LONG value as of its commit when LogMiner returns it incomplete. The (ALL) COLUMNS group makes every update carry the complete row, which Collapse row versions needs; without it, SQL Flow applies changes row by row.

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

7. In New pipeline, choose Oracle and LogMiner. 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.

8. Choose Test connection & list tables, then select your tables. The privilege and supplemental logging checks run when the pipeline starts, before the initial load, and list every gap they find.

Encrypted data

• TDE (tablespace or column encryption): supported, because the database decrypts the data for the mining and the LOB reads. Keep the TDE keystore open while the pipeline runs: with the keystore closed, capture stops by name rather than write a value it couldn't read.
• Encrypted SecureFile LOBs: supported. The database decrypts them.
• Encrypted connections: see Connection encryption.

Pros and cons

Pros

• Nothing to install on the database host.
• Works when redo and archives are stored in ASM, and on Oracle RAC.
• Enforces only database-level supplemental logging.

Cons

• The mining runs inside the database and uses its CPU, memory and temporary space.
• Lag is seconds rather than milliseconds.
• Needs LOGMINING and broad dictionary access granted from the root.
• Refuses tables with native BOOLEAN, JSON or VECTOR columns, or with extended-width VARCHAR2, NVARCHAR2 or RAW columns.

Limitations

• Column types refused: a table with one of these columns is refused by name at start:
  • Native BOOLEAN, VECTOR or JSON, because LogMiner returns no usable change records for the table. Use DirectLog for these tables, or store JSON in a CLOB or BLOB with an IS JSON constraint.
  • Extended-width VARCHAR2 or NVARCHAR2 wider than 4,000 bytes, or RAW wider than 2,000 bytes (MAX_STRING_SIZE=EXTENDED), because LogMiner can report a placeholder instead of the value.
  • BFILE, REF, nested-table, SYS.ANYDATA, SYS.ANYTYPE, SYS.ANYDATASET and URI-type columns (SYS.URITYPE, HTTPURITYPE, DBURITYPE, XDBURITYPE). DirectLog refuses them too.
  • XMLTYPE not stored as CLOB (binary or object-relational XML).
• Unreadable values: when LogMiner can't show a column's value, for example for an encrypted column while the TDE keystore is closed, capture stops by name rather than write a placeholder to the target.
• LOB values: a DELETE doesn't carry LOB values. LOB re-reads need undo for the commit; if the historical row can't be read, capture stops rather than substituting the current value.
• LONG values: a LONG or LONG RAW value that LogMiner doesn't return is read from the table as of the commit, like a LOB. The initial load and these re-reads take values up to 16 MiB; a larger value stops capture by name.
• Keys: relational targets need a key. SQL Flow takes an Oracle table's key from its primary key constraint.
• Purged archives: archived logs that are deleted before LogMiner reads them stop the pipeline.

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

Troubleshooting

Missing privileges or settings at start

Oracle CDC reader is missing privileges / settings

Cause: the start-time check found missing grants or settings. It lists each one with its fix, for example LOGMINING or SELECT ANY DICTIONARY (GRANT LOGMINING TO <user> CONTAINER=ALL; GRANT SELECT ANY DICTIONARY TO <user> CONTAINER=ALL), ARCHIVELOG mode, or Database-level supplemental logging. For a PDB source the check runs in CDB$ROOT, so a grant made only inside the PDB is reported as missing.

Fix: run the statements the message lists from CDB$ROOT with CONTAINER=ALL, then restart the pipeline.

Cannot mine a PDB from inside the PDB

LogMiner cannot mine a PDB source from inside the PDB (ORA-65040)

Cause: the source is a PDB and Root service for capture names the PDB rather than the CDB root.

Fix: set Root service for capture to the CDB root service, for example ORCLCDB. Keep Service name (PDB) on the PDB. The capture user must be a common user that can log in to the root.

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;

Redo or archive logs not found

No redo/archive log files found for SCN range

Cause: the archived logs that cover the capture position were deleted or moved.

Fix: restore the archived logs, or reload the pipeline. Keep archived logs long enough to cover agent outages.

Unsupported native BOOLEAN column

Table ... has a 23ai native BOOLEAN column

Cause: the table has a column type that LogMiner can't capture. Similar messages name VECTOR and native JSON columns.

Fix: remove the table from the pipeline, change the column type, or capture the table with DirectLog.

Extended-width column

Table <schema>.<table> has an extended-width <type>(<length>) column "<column>" (MAX_STRING_SIZE=EXTENDED). Oracle LogMiner can report a placeholder instead of values wider than the standard limit, so SqlFlow refuses LogMiner capture of this table; exclude it from LogMiner capture.

Cause: the table has a VARCHAR2 or NVARCHAR2 column wider than 4,000 bytes, or a RAW column wider than 2,000 bytes.

Fix: remove the table from the pipeline, or narrow the column to the standard limit.

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.

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

XMLTYPE column not stored as CLOB

Oracle XMLTYPE text projection requires catalog-proven CLOB storage; binary and object-relational XML representations are refused.

Cause: an XMLTYPE column uses binary or object-relational storage.

Fix: remove the table from the pipeline, or store the column as CLOB (XMLTYPE COLUMN <column> STORE AS CLOB).

LONG value larger than 16 MiB

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

Cause: the initial load, or a read of a LONG or LONG RAW value that LogMiner didn't return, met a value larger than 16 MiB.

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

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.

See also

• Oracle overview
• DirectLog
• Large values and LOBs
• Lag, spool and log retention