Skip to main content
Feedback

Oracle CDC DDL and flashback query

Oracle CDC Source connector supports native Data Definition Language (DDL) synchronization. The connector automatically detects the schema modificationssuch as adding or widening columns and refresh its metadata cache without requiring a manual restart.

How it works

To maintain data integrity, the connector uses Flashback Query to retrieve table metadata. Instead of quering the current database state, the connector views the schema as it existed at the specific Commit SCN (System Change Number) of the redo log record.

This approach eliminates the metadata gap that occurs when a DDL change is made in the database before the connector has finished processing older records in the redo logs.

Consistency and SCN logic

Commit SCN strategy: The connector performs flashback queries using the Commit SCN of the transaction. This ensures that the metadata retrieved is perfectly synchronized with the data processed at that specific time.

Multi-table consistency: A single connector instance (a river) maintains a unified position for all tables within its configuration. This ensures cross-table transactional consistency, even when DDL changes occur across multiple objects simultaneously.

DDL support boundaries

Supported MODIFY COLUMN operations

The connector handles in-place modifications where Oracle allows. Validated scenarios include:

  • Data type widening: Increasing length. For example, VARCHAR2(10) $\rightarrow$ VARCHAR2(20).
  • Numeric precision: Changing precision or scale. For example, NUMBER(10,2) $\rightarrow$ NUMBER(12,2).
  • Constraints: Switching between NULL and NOT NULL.
  • Defaults: UpdatingDEFAULT value expressions.
  • Character sets/collation: Supports changes to column-level character sets or collation settings.
  • Column renaming: The connector refreshes metadata and maps the new name to the existing ordinal position to preserve column order.

Metadata rules

  • Ordinal position: The connector strictly maintains the column order as defined in the database.
  • Exclusions: The connector automatically ignores hidden, virtual, and system-generated columns. For example, SYS_NC....

Unsupported operations

The following operations are logged but require manual configuration updates:

  • Table renames: Changing the name of a tracked table. For example, RENAME orders TO orders_old.
  • Management DDL: DROP TABLE or CREATE TABLE for tables not already included in the connector configuration.
  • Synonyms: Creating or dropping an alias for a table. For example, CREATE or DROP SYNONYM.

Flashback query configuration

Before configuring the connector, ensure you have administrative access to the Oracle Database. You must execute the following SQL commands as a user with SYSDBA or DBA privileges to prepare the environment for metadata synchronization.

Step 1: Grant flashback query privileges

To enable "As Of" SCN logic, the database user requires specific permissions to access historical metadata. Execute the following commands on the Oracle Database instance where the source data resides.

Choose one of the following options based on your organization's security policies:

The connector uses the DBMS_FLASHBACK package for precise metadata retrieval.

GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO <connector_user>;
note

In PDB (Pluggable Database) environments, apply these grants to the specific PDB user.

Option B: Object-level

If system-level access is restricted, grant FLASHBACK and SELECT privileges on every tracked table.

GRANT SELECT, FLASHBACK ON <schema>.<table_name> TO <connector_user>;

Step 2: Configure undo retention

Flashback success depends on Oracle's UNDO_RETENTION. If undo data is overwritten before the connector processes a record, the query fails with ORA-01555: snapshot too old.

ConfigurationRecommendation
Retention window24–72 hours (86,400 to 259,200 seconds) depending on expected replication lag.
Undo tablespaceEnsure the tablespace is large enough to support the retention window.

Update command

Run the following command to increase Oracle’s Undo retention period to ensure historical data remains available for the connector to query.

SQL
ALTER SYSTEM SET undo_retention = 259200 SCOPE=BOTH;

Sizing: Ensure the UNDO tablespace is large enough to support the retention window.

Verify tablespace size

Run the following query to verify the size of your current undo tablespace:

SQL
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS SIZE_MB 
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'undo_tablespace') 
GROUP BY TABLESPACE_NAME;

Error handling and observability

Failure protocol

When the connector detects a mismatch between the redo log structure and its cached metadata, it initiates an automated self-healing workflow:

  • Detection: The connector identifies a discrepancy between the incoming log record and the stored schema cache.
  • Retry (Automated): The system attempts to refresh the metadata using an exponential back-off strategy (defaulting to 3 retries). This provides time for temporary database locks or network latency to resolve.
  • Fallback: If a Flashback Query fails, for example, due to missing permissions or an expired undo retention period ORA-01555, the connector logs a warning and attempts to retrieve current metadata from the database.
  • Critical stop: If the schema remains incompatible, for example, a column was dropped or renamed, the connector enters a stopped state. This is a safety mechanism to prevent data corruption or misaligned field mapping in your downstream process.

Monitoring metrics

The connector provides detailed logging to help you triage schema synchronization issues. Monitor your logs for the following indicators:

IndicatorDescription
Error code: ORA-CDC-REFRESH-001A specific identifier for metadata synchronization events. It indicates a discrepancy between the incoming redo log data and the connector's local schema cache.
Log message: Critical schema mismatch...Provides technical context for a failure. It specifies the SCN of the conflict and compares the Expected (cached) column count against the Found (actual database) count.
Privilege pathIdentifies the internal method used to resolve metadata. This is used to verify if database permissions (DBMS_FLASHBACK vs. AS_OF_SCN) are functioning as intended.

Troubleshooting

Error:ORA-01555 (Snapshot too old)

This error occurs when the historical metadata required for a specific SCN has been purged from the Oracle undo tablespace. If increasing the undo retention does not resolve the gap, follow these steps to recover:

  1. Clear metadata cache: In the connector UI, locate the affected table and select the Reinitialize Sync option.
  2. Restart the River: Manually start the River process. This forces the connector to fetch the current metadata from the database as a new baseline and resume data capture.
note

Re-initializing the sync skips the historical metadata gap. Ensure no columns were dropped or renamed during the lag period to avoid mapping errors in your downstream process.

On this Page