Address DDL Changes in Oracle Database for Confluent Cloud

For releases before Oracle Database 19c, v1.3.0 and earlier of the Oracle CDC Source connector uses only the Online Catalog to understand a schema and parse records. It works in most cases, but if a DDL change (for example, ALTER TABLE CUSTOMERS ADD CITY VARCHAR(50);) happens before the connector processes all records based on the existing schema, it might not parse any more records due to the dictionary mismatch in LogMiner. In this case, you can either restart the connector with start.from=force_current, potentially losing these records, or create a new connector with a different name with start.from=snapshot. If you use v1.3.1 or later of Oracle CDC Source connector, the connector uses the Online Catalog and Archived Redo Logs to address a DDL change.

Confluent now provides an option that allows you to perform DDL operations to evolve table schemas while still providing low latency delivery of CDC records. This means DDL changes are now supported differently.

Configure the Dictionary Handling Mode

The Oracle CDC connector uses automated configuration switching to handle DDL changes using the oracle.dictionary.mode configuration property. This property allows you to toggle the dictionary mode used. The dictionary handling mode can be set to one of the following modes:

  • auto: The connector will default to this mode and use the dictionary from the online catalog until a DDL statement to evolve the table schema is encountered. At which point, the connector starts using the dictionary from archived redo logs. Once the DDL statement has been processed, the connector reverts back to using the online catalog. Use this mode if DDL statements are expected.

  • online: The connector always uses the online dictionary catalog. Use online mode if no DDL statements are expected as DDL statements aren’t supported in online mode.

  • redo_log: The connector always uses the dictionary catalog from archived redo logs. Use this mode if you can not access the online redo log.

    Note

    CDC events will be delayed until they are archived from online logs.

Make Changes in auto or redo_log Mode

To make a DDL change when in either auto or redo_log mode, a DBA should complete the following steps:

  1. Rebuild the dictionary in the database by running the following command:

    EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

  2. Perform a DDL change (for example, ALTER TABLE CUSTOMERS ADD CITY VARCHAR(50); ).

  3. Run the following command to rebuild the dictionary:

    EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);