Oracle LogMiner, LOB Columns, and CDC: What You Need to Know

 

The Problem

When replicating Oracle tables with LOB (CLOB/BLOB) columns using Change Data Capture (CDC) tools, a common challenge arises: if your application only updates non-LOB columns (e.g., a timestamp), the LOB data is not written to the redo log, and CDC tools cannot capture the full row.

A well-known workaround is to use a BEFORE UPDATE trigger to force the LOB into the redo stream. But how this works — and whether it works at all — depends heavily on the LOB storage type and which CDC reader you use.

This post documents the behavior we observed through hands-on testing on Oracle 19c.

---

Setup

-- Table with a CLOB column
CREATE TABLE test_logminer (
    id NUMBER PRIMARY KEY,
    content CLOB,
    last_update TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Enable supplemental logging
ALTER TABLE test_logminer ADD SUPPLEMENTAL LOG GROUP sg_pk (id) ALWAYS;

-- Row 1: 1KB CLOB (stored in-row / inline)
INSERT INTO test_logminer (id, content, last_update)
VALUES (1, RPAD('A', 1000, 'A'), SYSTIMESTAMP);

-- Row 2: 32KB CLOB (stored out-of-row / out-of-line)
DECLARE
    v_clob CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    FOR i IN 1..32 LOOP
        DBMS_LOB.WRITEAPPEND(v_clob, 1000, RPAD('B', 1000, 'B'));
    END LOOP;
    INSERT INTO test_logminer (id, content, last_update) VALUES (2, v_clob, SYSTIMESTAMP);
    DBMS_LOB.FREETEMPORARY(v_clob);
    COMMIT;
END;
/

Key concept — In-row vs Out-of-row LOB storage:

• In-row (inline): LOB data <= ~4000 bytes is stored within the table row itself
• Out-of-row (out-of-line): LOB data > ~4000 bytes is stored in a separate LOB segment; only a locator (pointer) resides in the row

---

Test Case 1: Without Trigger — Update Non-LOB Column Only

UPDATE test_logminer SET last_update = SYSTIMESTAMP WHERE id = 1;
COMMIT;

LogMiner output:

SCN     OPERATION  SQL_REDO
620194  UPDATE     update "ADMIN"."TEST_LOGMINER" set "LAST_UPDATE" = TO_TIMESTAMP('...')
                   where "ID" = '1' and "LAST_UPDATE" = TO_TIMESTAMP('...') and ROWID = '...';

Result: Only the changed column appears in redo. No LOB data is logged. CDC tools will replicate the update without the CLOB value.

---

Test Case 2: With Trigger — In-row LOB (1KB)

CREATE OR REPLACE TRIGGER trg_before_update_test_logminer
BEFORE UPDATE ON test_logminer
FOR EACH ROW
BEGIN
    IF NOT UPDATING('CONTENT') THEN
      :NEW.CONTENT := :OLD.CONTENT;
    END IF;
END;
/

UPDATE test_logminer SET last_update = SYSTIMESTAMP WHERE id = 1;
COMMIT;

LogMiner output:

SCN     OPERATION  SQL_REDO
620486  UPDATE     set "LAST_UPDATE" = TO_TIMESTAMP('...')
                   where "ID" = '1' and "LAST_UPDATE" = TO_TIMESTAMP('...');
620487  UPDATE     set "CONTENT" = 'AAAA...AAA'
                   where "ID" = '1' and "LAST_UPDATE" = TO_TIMESTAMP('...') and ROWID = '...';

Result: The trigger forces the LOB into redo. Oracle generates two separate redo entries — one for the scalar column change, one for the LOB. The in-row LOB appears as a standard UPDATE with the value in the SET clause.

---

Test Case 3: With Trigger — Out-of-row LOB (32KB)

UPDATE test_logminer SET last_update = SYSTIMESTAMP WHERE id = 2;
COMMIT;

LogMiner output:

OPERATION       NUM_ENTRIES  TOTAL_REDO_BYTES
UPDATE                    1               178
SEL_LOB_LOCATOR           1               280
LOB_TRIM                  1                38
LOB_WRITE                32            33,931

Result: The out-of-row LOB is logged completely differently:

1. UPDATE — scalar column change only
2. SEL_LOB_LOCATOR — select the LOB pointer
3. LOB_TRIM — truncate the existing LOB
4. LOB_WRITE x 32 — rewrite the full LOB in ~1KB chunks

Oracle does not embed out-of-row LOB values in a SQL UPDATE statement. Instead, it uses piecewise LOB operations.

---

Testing Summary:

Scenario	Row 1 (1KB in-row LOB)	Row 2 (32KB out-of-row LOB)
LogMiner redo (without trigger)	No LOB in redo	No LOB in redo
LogMiner redo (with trigger)	2 UPDATEs (value in SET clause)	1 UPDATE + LOB_TRIM + 32 LOB_WRITEs