Openflow Connector for Oracle: Set up incremental replication without snapshots

Note

This connector is subject to the Snowflake Connector Terms.

Note

The Openflow Connector for Oracle is also subject to additional terms of service beyond the standard connector terms of service. For more information, see the Openflow Connector for Oracle Addendum.

This topic describes how to configure the Openflow Connector for Oracle connector to start replicating incremental changes for newly added tables immediately, bypassing snapshots. This configuration is useful when you reinstall the connector over previously replicated data and want to continue replication without snapshotting every table again.

You can enable incremental replication on either a new or an existing connector instance.

Enable incremental replication without snapshots on a new connector

To enable incremental replication on a new connector instance:

  1. Set up the connector as described in Install and configure the Openflow Connector for Oracle.
  2. In the Oracle Ingestion Parameters context, set the Ingestion Type parameter to incremental.

Enable incremental replication without snapshots on an existing connector

To enable incremental replication on an existing connector:

  1. sign in to Snowsight.
  2. in the navigation menu, select Ingestion » Openflow.
  3. In the Openflow pane select the Runtimes tab.
  4. Select the runtime containing the connector.
  5. Select the connector.
  6. In the Ingestion Parameters context, specify Ingestion Type = incremental.
  7. Add new replication tables. These tables immediately switch to their incremental load.

Note

To return to replicating tables with the snapshot load, change Ingestion Type from incremental to full.

Usage notes

  • Changing the value of Ingestion Type does not impact any tables that have begun replicating data. Tables currently in the snapshot phase continue until the snapshot load is complete.

  • While Ingestion Type is set to incremental, new tables added to the list of replicated tables bypass the snapshot phase. This includes new tables added to the source database that match the Included Table Regex parameter. Ensure that the ingestion type is set to incremental to bypass the snapshot phase.

    Note

    Connectors should only remain in incremental mode as long as required as it bypasses snapshots. Once customer needs for incremental updates have been satisfied the connector should be returned to full mode.

  • For tables that bypass snapshot load, the connector creates a destination table in Snowflake, by executing CREATE TABLE IF NOT EXISTS, only if no destination table already exists. Tables going through the snapshot require that no destination table exist.

Recover a table using incremental-only mode

If a table’s snapshot completed successfully but incremental replication later failed, you don’t need to remove the table and snapshot it again. Instead, you can recover the table by replaying the changes that are still available in the source redo logs through XStream and merging them onto the existing destination table.

Incremental replication can fail for several reasons, for example:

  • A record in the source database can’t be read because it has an incorrect or unsupported format.
  • A row exceeds the maximum supported size.
  • A merge operation can’t complete.
  • A transient error persists through so many retries that the table enters the FAILED state.

To recover the table without a new snapshot, remove it from replication, switch the connector to incremental-only mode reading from the earliest available position, and add the table back. The connector reads all available changes from the oldest available XStream position, then replays and reapplies them to the destination table.

Important

Before you recover the table, address the underlying cause of the failure. Otherwise, the connector encounters the same error again when it replays the changes. For example, raise the per-value limit (see Increase the oversized value limit) or fix the problematic record in the source database.

To recover the table:

  1. Remove the table from replication. In the Ingestion Parameters context, remove the table from Included Table Names, or modify Included Table Regex so the table is no longer matched. Wait until the table’s state is fully removed from the Table State Store controller service before you continue.

    Important

    Don’t drop the destination table. This procedure reuses the existing destination table and replays incremental changes onto it.

  2. Stop the connector’s process group so that you can change its configuration. On the connector canvas, right-click the connector’s process group and select Stop.

  3. In the Ingestion Parameters context, set the Ingestion Type parameter to incremental.

  4. Set the Starting XStream Position parameter to Earliest. The connector reads all available changes again from the oldest available XStream position, then replays and reapplies them to the destination table. For more information, see the “Specify load from XStream position” section in Openflow Connector for Oracle: Maintenance.

    Leave Re-read Tables in State at its default value, New, so that only the table you add back reads from the earliest position. Tables already in replication continue from their last positions.

  5. Add the table back to replication by reversing the change you made in step 1.

  6. Start the connector’s process group. Right-click the connector’s process group and select Start.

  7. Wait until the table returns to incremental replication. In the Table State Store controller service state, the table transitions to INCREMENTAL_REPLICATION when recovery completes.

  8. Revert the changes you made in steps 3 and 4: set Ingestion Type and Starting XStream Position back to their previous values.

Warning

This procedure recovers only the changes still available in the source redo logs. If the required redo information is no longer retained on the source, the recovered table can have gaps. In that case, you must take a new snapshot to fully resynchronize the table.