Oracle CDC with synonym support

Data Integration Change Data Capture (CDC) supports Oracle synonym objects, including multi-level synonym chains and editioning views. You can configure rivers using any Oracle object name — a table, a synonym, or a synonym chain. When displayed in the UI, synonyms appear as tables. At startup, CDC resolves the full chain to the physical base table and tracks all intermediate chain objects (synonyms and editioning views) for DDL detection. Events always reference the user-configured name, providing a stable topic identity regardless of how the underlying chain changes.

This feature is particularly valuable for Oracle E-Business Suite (EBS) environments, where synonym chains and editioning views abstract physical tables across patches and upgrades.

note

A future release will introduce an additional UI column that identifies the object type: table, synonym, or editioning view.

Prerequisites

• An Oracle connection is configured in Data Integration.
• Oracle supplemental logging is enabled with ALL columns at the database level, or at the table level for each table you intend to capture.
• If the target physical table does not have a primary key or unique key, you must define composite KEYCOLS.
• The Oracle user associated with your connection has the permissions listed in Oracle required permissions.
• Review Limitations before configuring synonyms to confirm your environment is supported.

Key concepts

Synonym: An Oracle alias object that points to another synonym, editioning view, or table. Provides a layer of indirection so application code does not need to know the physical owner or table name.

Editioning view (EV): An edition-specific projection of a physical table, introduced by Oracle Edition-Based Redefinition (EBR). An EV always selects a subset of the physical table's columns, and column names always match those in the physical table.

Synonym chain: The full path from a user-configured synonym through intermediate synonyms and editioning views to the physical base table. For example: APPS.PO_HEADERS (synonym) → APPS.PO_HEADERS# (synonym) → PO.PO_HEADERS# (editioning view) → PO.PO_HEADERS (table).

Physical table: The actual Oracle table where data is stored and that LogMiner reads from. CDC captures changes at this level regardless of what was configured in Boomi.

Exposed name: The object name you configure in Boomi (for example, the synonym name). Events published downstream always use this name, not the physical base table name.

Reincarnation event: A database-level change that invalidates the existing CDC state, such as a database clone, a RESETLOGS operation, or a physical table drop and recreate. CDC stops immediately when a reincarnation event is detected. You must re-migrate the target before restarting.

SCN (System Change Number): Oracle's internal clock used to identify a point-in-time state of the database.

Flashback: Oracle capability to query the database dictionary as it existed at a historical SCN. Used by CDC during chain re-resolution on restart.

EBR (Edition-Based Redefinition): Oracle feature allowing online application upgrades by maintaining multiple editions of editioned objects.

EBS (Oracle E-Business Suite): Enterprise application suite that uses synonym chains and editioning views extensively to support online patching.

How synonym chains work in Oracle EBS

Why EBS uses synonym chains

Oracle E-Business Suite uses synonym chains and editioning views to insulate application code from schema-level changes introduced during patches and upgrades. This design allows Oracle to apply patches that rename, replace, or restructure tables without breaking application-level references.

The three main objects in a chain are:

• Synonyms: Oracle aliases pointing to another synonym, a view, or a table. They provide a layer of indirection so application code does not need to know the physical owner or table name.
• Editioning Views (EVs): Edition-specific projections of a physical table, introduced by Oracle EBR. An EV always selects a subset of the physical table's columns, and column names always match those in the physical table.
• Physical Table: The actual table in Oracle that stores and writes data. LogMiner operates exclusively at this level.

Typical EBS chain structure

A typical EBS synonym chain for the PO_HEADERS object looks like this:

APPS.PO_HEADERS  (synonym)
  → APPS.PO_HEADERS#  (synonym)
  → PO.PO_HEADERS#    (editioning view)
  → PO.PO_HEADERS     (physical table)

You configure the river using APPS.PO_HEADERS. Data Integration resolves the full chain and tracks LogMiner events at the PO.PO_HEADERS level while emitting events under the APPS.PO_HEADERS topic name.

Chain resolution algorithm

At startup, CDC resolves every user-configured object by walking the chain step by step:

1. Query ALL_OBJECTS_AE to determine the object type.
2. If TABLE: resolution is complete.
3. If SYNONYM: query ALL_SYNONYMS to get the target, then recurse (maximum 5 hops).
4. If VIEW (Oracle returns 'VIEW' for editioning views): query ALL_EDITIONING_VIEWS_AE. All editions must converge to the same physical base table.
5. Any other type raises an error and stops CDC for that chain.

Worked example

The following trace shows the full resolution for APPS.PO_HEADERS:

Input: APPS.PO_HEADERS

1. ALL_OBJECTS_AE(APPS, PO_HEADERS)    → SYNONYM
2. ALL_SYNONYMS(APPS, PO_HEADERS)      → target: APPS.PO_HEADERS#
3. ALL_OBJECTS_AE(APPS, PO_HEADERS#)   → SYNONYM
4. ALL_SYNONYMS(APPS, PO_HEADERS#)     → target: PO.PO_HEADERS#
5. ALL_OBJECTS_AE(PO, PO_HEADERS#)     → VIEW (editioning view)
6. ALL_EDITIONING_VIEWS_AE(PO, PO_HEADERS#)
      Edition V1: base table = PO.PO_HEADERS
      Edition V2: base table = PO.PO_HEADERS  ✓ same
7. ALL_OBJECTS_AE(PO, PO_HEADERS)      → TABLE ✓

Result:
  Physical table : PO.PO_HEADERS
  User name      : APPS.PO_HEADERS
  Event topic    : APPS.PO_HEADERS

Flashback strategy on restart

When CDC restarts after a DDL event or connector failure, chain resolution uses Flashback to read the Oracle dictionary as it existed at the resume SCN, not at the current moment. This ensures correct column metadata when an editioning view's column projection changed between the resume SCN and the present. The resolution strategy falls back in this order:

1. DBMS_FLASHBACK (preferred): session-level, pinned connection.
2. AS OF SCN (fallback): query-level clause.
3. Current state (last resort): used if ORA-41900, ORA-01031, or ORA-01555 errors are encountered.

Configure CDC with an Oracle synonym

Set up the Oracle connection

1. In Data Integration, open or create an Oracle connection.
2. Confirm that the Oracle user has the permissions listed in the Required permissions section.
3. Save and test the connection.

Configure the CDC pipeline

1. Create a new CDC pipeline and select your Oracle connection as the source.
2. In the Include Tables field, enter the object you want to track using OWNER.OBJECT_NAME format (for example, APPS.PO_HEADERS). You can enter a synonym name directly; Data Integration resolves the full synonym chain to the physical base table at startup.
3. Confirm that the object type is TABLE or SYNONYM. Editioning views and regular views cannot be specified as direct CDC sources. If you need to track an editioning view, use a synonym that points to it.
4. If the physical base table has no primary key or unique key, define composite KEYCOLS to identify rows uniquely.
5. Configure target connection, schema mapping, and any other pipeline settings.
6. Save the pipeline.

Start and validate CDC

1. Activate the pipeline.
2. At startup, Data Integration validates the full synonym chain and reports any configuration errors before CDC begins. Review the activation log to confirm successful chain resolution.
3. Verify that events arrive at the target using the exposed name (the synonym name you configured in Include Tables), not the physical base table name.

Re-migrate after a reincarnation event

If CDC stops with the error message, the database environment changed in a way that invalidates the existing CDC state (clone, resetlogs, or table rebuild). Do not attempt to resume automatically.

1. Run a full reload or migration of the target from the exposed object.
2. Restart the CDC pipeline with the new baseline.

DDL support

Supported DDL operations

CDC monitors DDL changes that affect the synonym chain structure. The following DDL operations on synonyms and editioning views in the tracked chain trigger a CDC restart:

DDL operation	Object type	Action
DROP SYNONYM	Synonym in chain	CDC restarts. If re-resolution fails, the connector stops.
CREATE OR REPLACE SYNONYM	Synonym in chain	CDC restarts and re-resolves chain from the DDL commit SCN.
CREATE SYNONYM	Synonym in chain	CDC restarts and re-resolves chain from the DDL commit SCN.
DROP VIEW (editioning view)	Editioning view in chain	CDC restarts. Re-resolution is attempted.
CREATE OR REPLACE VIEW (editioning view)	Editioning view in chain	CDC restarts and re-resolves chain from the DDL commit SCN.
ALTER DATABASE DEFAULT EDITION	Database-level	All chains are re-resolved at the new SCN.

Unsupported DDL operations

DDL operations at the physical table level do not trigger a synonym-chain restart. CDC tracks DDL only for synonym and editioning view objects in the chain, not for the underlying physical table.

The following DDL operations on the physical table are not handled as chain DDL:

• ALTER TABLE ... ADD COLUMN
• ALTER TABLE ... DROP COLUMN
• ALTER TABLE ... RENAME COLUMN
• ALTER TABLE ... RENAME TO
• DROP TABLE

If the physical table structure changes, the synonym chain resolution result remains unchanged. CDC continues processing without awareness of the structural change, which can lead to schema mismatch errors when new DML events arrive with the updated column layout. Stop the river, update the river configuration, and restart after any table-level DDL changes.

DDL restart flow

When a supported DDL event is detected, CDC follows this restart sequence:

1. Detect the DDL event and record the commit SCN.
2. Stop parsing; drain the pipeline to deliver all events that occurred before the commit SCN.
3. Restart LogMiner from the commit SCN.
4. Re-resolve all chains using Flashback at the commit SCN.
5. Resume CDC with the updated chain mapping.

DDL scenarios

Scenario A: Synonym re-pointed

Before: APPS.PO_HEADERS → ... → PO.PO_HEADERS
DDL:    CREATE OR REPLACE SYNONYM APPS.PO_HEADERS FOR PO.PO_HEADERS_V2
Action: CDC restarts → re-resolves → now tracks PO.PO_HEADERS_V2

warning

Target immutability applies here. If the synonym was previously resolved and persisted to PO.PO_HEADERS and the re-point yields a different physical table (PO.PO_HEADERS_V2), CDC raises an unrecoverable error. You must create a new river.

Scenario B: Edition cutover (EBS patch)

Before: Default edition = V1
DDL:    ALTER DATABASE DEFAULT EDITION = V2
Action: CDC restarts → re-resolves ALL chains at the new SCN

Scenario C: Synonym dropped

DDL:    DROP SYNONYM APPS.PO_HEADERS
Action: CDC restarts → re-resolution fails → connector stops.
        You must update the river configuration.

Target immutability

To protect target data integrity, Data Integration persists the initial synonym-to-physical-table resolution after the first successful chain resolution. This persisted mapping is checked on every subsequent restart.

State	Outcome
Restart resolves to the same physical table as persisted	Proceed normally.
Restart resolves to a different physical table	Unrecoverable error. CDC stops. Create a new river.
Full river re-init or Start from SCN (manual operation)	Persisted mapping is deleted; fresh resolution is allowed.

note

Direct tables (not accessed via a synonym) are not subject to the target immutability check. Only synonym-based configurations are persisted and checked.

Limitations

The following constraints apply when using Oracle synonyms with Data Integration. For CDC-specific error messages and resolutions, refer to Error reference.

Limitation	Detail
No DDL support at physical table level	ALTER TABLE, DROP TABLE, RENAME TABLE, and column-level DDL on the physical table do not trigger CDC chain restart or re-resolution. Stop the river and restart manually after any table-level DDL changes.
Staged values not applied for dropped or renamed columns	CDC processes DML normally, but if in-flight values for a column are staged and a DROP COLUMN or RENAME COLUMN is subsequently executed on that column, the staged values for the old column definition are not applied to the destination. Ensure all staged data is fully applied before running column-level DDL on the physical table.
Public synonyms	Public synonyms (OWNER = 'PUBLIC') are not supported and cannot be selected as river entry points. If encountered mid-chain, CDC raises a startup error.
Maximum chain depth	Synonym chains are supported up to 5 hops. Chains that exceed 5 hops are rejected at startup.
Circular synonym chains	Synonym chains that form a loop are detected and rejected at startup.
Remote synonyms	Synonyms that reference a remote database via a DB_LINK are not supported and are rejected at startup.
Materialized views	Materialized views cannot be specified as a CDC source, directly or via a synonym.
Editioning view as direct source	Editioning views cannot be specified directly in Include Tables for CDC. Use a synonym that resolves to the editioning view.
Regular views	Regular views cannot be specified directly as a CDC source.
Single physical table per editioning view	All editions of an editioning view in a synonym chain must resolve to the same physical base table. Chains where editions diverge to different tables are rejected.
Immutable synonym target	Once a synonym chain is resolved and CDC starts, the physical base table is persisted. If the synonym is later re-pointed to a different table, the river enters an unrecoverable error state. You must create a new river.
Flashback retention window	When CDC resumes from a saved SCN, Data Integration uses Oracle Flashback to resolve the chain at that point in time. If the undo data for the resume SCN has been purged, Oracle raises ORA-01555 and CDC falls back to current-state resolution. If flashback privileges are not granted, a misleading "no data to fetch" status may appear instead of a permission error. Refer to Oracle required permissions to confirm the correct grants are in place.
Column rename after update	If a column is renamed in the Oracle source after a DML update, the updated value may appear as NULL at the target. Avoid renaming columns while CDC is active on the affected table.
Column drop after update	If a column is dropped in the Oracle source, DML operations that occurred before the drop may not replicate correctly, resulting in data loss at the target.
Incremental synonym to editioning view column metadata	For incremental (non-CDC) rivers where a synonym resolves through an editioning view, column metadata is sourced from the physical table, not the editioning view's column projection.
Cross-PDB synonyms	Synonyms whose target is in a different PDB container are not supported.

Configuration reference

Parameter	Description	Required	Notes
Include Tables	The Oracle object to track. Accepts a table name or synonym name in OWNER.OBJECT_NAME format.	Yes	Synonym chains are resolved automatically at startup. Direct specification of editioning views or regular views is not supported.
KEYCOLS	Composite key columns to use when the physical table has no primary key or unique key.	Conditional	Required if the base table has no PK or UK.
Resume SCN	System Change Number from which CDC resumes on restart.	No	Used for Start from SCN operations. The synonym chain is re-resolved at the specified SCN using Oracle Flashback where available.

Supported object types

Object type	CDC support
TABLE	Supported. Captured directly from redo log.
SYNONYM → TABLE	Supported. Synonym resolved to physical table at startup.
SYNONYM → SYNONYM → ... → TABLE	Supported. Chains up to 5 synonym hops.
SYNONYM → EDITIONING VIEW → TABLE	Supported. Editioning view resolved to base table.
EDITIONING VIEW (specified directly)	Not supported. Use a synonym that points to the editioning view.
Regular VIEW (specified directly)	Not supported.
MATERIALIZED VIEW	Not supported.
Public SYNONYM (OWNER = 'PUBLIC')	Not supported.
Remote SYNONYM (DB_LINK)	Not supported.

Error reference

All errors below are reported at startup before CDC begins. Multiple errors are collected and reported together.

Error	Cause	Resolution
object not found or no permission: SCHEMA.NAME	The object does not exist in Oracle, or the CDC user lacks SELECT privilege.	Verify the object exists and grant SELECT to the CDC user.
editioning view SCHEMA.NAME cannot be specified directly; use a synonym that points to it	An editioning view was configured as the starting object.	Replace the editioning view with a synonym that points to it.
view SCHEMA.NAME cannot be specified directly; use a synonym that points to it	A regular view was configured as the starting object.	Replace the view with a synonym or use the underlying table.
unsupported object type: TYPE	The chain includes an object type CDC cannot handle.	Review the chain and remove unsupported objects.
remote synonym not supported: SCHEMA.SYN -> TARGET@DBLINK	The chain includes a DB_LINK synonym.	Remote synonyms are not supported. Reconfigure without DB_LINK.
synonym chain too deep for SCHEMA.SYN (max 5 synonym hops)	The synonym chain exceeds 5 hops.	Simplify the synonym chain or reconfigure using a closer alias.
editioning view has editions pointing to different tables: SCHEMA.EV: SCHEMA.TABLE_A vs SCHEMA.TABLE_B	Editions of the same editioning view resolve to different physical tables.	Verify the editioning view definition in Oracle. All editions must converge to a single base table.
table SCHEMA.TABLE requested multiple times via: SCHEMA.SYN1, SCHEMA.SYN2	Two configured synonyms resolve to the same physical table.	Configure only one synonym per physical table per river.
target table changed. CDC cannot proceed.	The synonym was re-pointed to a new physical table after initial resolution.	Create a new river for the new target table.
wrong container for SCHEMA.SYN: CON_ID X (expected Y)	The synonym target is in a different PDB container.	Configure the connection to point to the correct PDB, or use a synonym within the same container.
CDC failed – source table reincarnated or environment refreshed. Please re-migrate target and restart CDC.	The database was cloned, reset, or the physical table was dropped and recreated.	Re-migrate the target and restart the CDC pipeline. Refer to Re-migrate after a reincarnation event.

Oracle dictionary views reference

CDC runtime

The following views are used by CDC at runtime to resolve chains and capture changes.

View	Edition-aware	Purpose
ALL_OBJECTS_AE	Yes	Object type classification for all editions.
ALL_SYNONYMS	No	Synonym chain resolution.
ALL_EDITIONING_VIEWS_AE	Yes	Editioning view to base table for all editions.
ALL_TAB_COLUMNS	No	Physical table column metadata.
V$LOGMNR_CONTENTS	N/A	LogMiner CDC event stream.
V$DATABASE	N/A	Current SCN, supplemental logging check, archive log mode check.
V$LOGMNR_LOGS	N/A	Log files in active LogMiner session.
V$ARCHIVE_DEST	N/A	Filter archived logs by valid destination.
V$VERSION	N/A	Oracle version detection at startup.
V$ARCHIVED_LOG	N/A	Access archived logs for historical CDC.
V$LOGFILE	N/A	Identify active redo log files.
V$LOG	N/A	Current log group status.
V$INSTANCE	N/A	Database instance information.
SYS.PROPS$	N/A	DEFAULT_EDITION change detection (accessed via LogMiner; no direct SELECT required).

River configuration UI

Grant SELECT on the following views to enable table and synonym selection during river configuration in Data Integration.

View	Edition-aware	Purpose
ALL_OBJECTS	No	Table and synonym listing in river configuration UI.
ALL_VIEWS	No	Editioning view detection via EDITIONING_VIEW column.
ALL_EDITIONING_VIEWS	No	Editioning view to base table (current edition).
ALL_TAB_COLUMNS	No	Column metadata for field mapping.
DBA_SEGMENTS	N/A	Table size estimation (joined with DBA_TABLES).
DBA_TABLES	N/A	Table listing (joined with DBA_SEGMENTS for size data).
ALL_TAB_PARTITIONS	No	Partitioned table chunking during data load.

Related topics

• Oracle CDC configuration
• Oracle CDC overview
• Oracle CDC DDL and flashback query
• Oracle required permissions