Openflow Connector for Oracle: Maintenance¶

This topic describes maintenance tasks for the Openflow Connector for Oracle, such as reinstalling the connector or setting the starting redo log position.

These operations are often used in conjunction with Incremental replication without snapshots.

Check the replication status of a table¶

Replication state for table <database_name>.<schema_name>.<table_name> changed from <old_state> to <new_state>

If a permanent failure prevents table replication, remove the table from replication. After you address the problem that caused the failure, you can add the table back to replication. For more information, see Restart table replication.

Increase the oversized value limit¶

By default, the connector replicates individual values up to 16 MB and marks any table that contains a larger value as permanently failed. If your Snowflake account has the ENABLE_OPENFLOW_CDC_ORACLE_SSV2 parameter set to true, the per-value limit can be raised from 16 MB to 128 MB.

The increased limit doesn’t apply equally to all column types.

Check whether the 128 MB limit is available¶

You may not be able to verify the ENABLE_OPENFLOW_CDC_ORACLE_SSV2 parameter value by querying it. To check if it is enabled, see if the FlowFiles flow through Upload Rows via Snowpipe Streaming 2 processor (not through Upload Rows via Snowpipe Streaming).

Configure the processors¶

Update the Oversized Value Limit property to 128 MB on the following processors:

• Fetch Rows by ROWID Range (in the Snapshot Load group) — used when Snapshot Fetching Strategy is CONCURRENT_BY_ROWID
• Fetch Table Rows (in the Snapshot Load group) — used when Snapshot Fetching Strategy is SEQUENTIAL_BY_PRIMARY_KEY
• Read Oracle CDC Stream (in the Incremental Load group)

For each processor:

1. Locate the processor in the flow. On the connector canvas, you can use the search box in the top-right corner to find processors by name.
2. Right-click the processor and select Configure.
3. Open the Properties tab.
4. Set Oversized Value Limit to 128 MB.
5. Apply the change.

For tables that are already being replicated and have destination columns narrower than VARCHAR(134217728) or BINARY(67108864), see Migrate existing tables.

Migrate existing tables¶

The steps in Increase the oversized value limit raise the limit for newly created destination tables. If a table is already being replicated and its destination column type is not VARCHAR(134217728) or BINARY(67108864), but you now want to load values larger than the original 16 MB limit, you must manually widen the column type on both the journal and destination tables.

Before you migrate, check the current destination column type, because it can vary depending on when the snapshot replication was performed.

To migrate a table:

1. Stop replication for the affected table by stopping the topmost processors of the Snapshot Load and Incremental Load groups until all queues are empty. For the equivalent stop procedure, see the substeps in Reinstall the connector.
2. Widen the column on both the journal table and the destination table, according to the column type:
   1. For VARCHAR columns, run ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE VARCHAR(134217728) on the journal table and on the destination table (one statement per table).
   2. For BINARY columns, Snowflake doesn’t allow widening BINARY in place, so do the following on both the journal and destination tables:
      1. Add a new column of type BINARY(67108864).
      2. Copy data from the original column into the new column.
      3. Drop the original column and rename the new column to the original name.
3. Restart replication by re-enabling the processors.

Performance considerations¶

Raising the per-value limit increases the amount of data that the connector loads into memory and moves through the flow, which raises the load on both the runtime and the warehouse. Size the runtime and warehouse accordingly.

When Oversized Value Strategy is set to Set Null, the connector still loads each oversized value into memory before it can replace it with NULL. If your tables contain multi-gigabyte LOB columns, exclude those columns from replication.

During both snapshot and incremental replication, the queue in front of the Upload Rows via Snowpipe Streaming 2 processor can fill with FlowFiles and trigger back pressure, which consumes a large amount of runtime disk space. For larger tables, use a Large runtime to provide additional storage. For guidance on choosing a size, see Runtime sizing.

Snapshot replication¶

Large LOB values can substantially increase snapshot runtime memory and disk use, because Fetch Rows by ROWID Range reads full row payloads (including LOBs) into the runtime before emitting FlowFiles.

The Split Table into Chunks processor sizes ROWID ranges from table heap blocks. Because LOBs are stored in separate segments, their size is not included in that calculation. When non-LOB column data is small compared with LOB payloads, a single chunk can still require Fetch Rows by ROWID Range to read multi-gigabytes of data. Output FlowFiles for that chunk are not passed downstream until the JDBC fetch for the entire ROWID range completes, so later processors may not receive data for a long time.

Incremental replication¶

When the source produces frequent changes to rows that contain large values, you might need a Large warehouse. High-frequency merges of many moderately large rows (for example, many 8 MB values) can require a large single merge operation, and smaller warehouses can run out of memory. By contrast, fewer very large rows (for example, 128 MB values) are streamed file by file through the Upload Rows via Snowpipe Streaming 2 processor, and each file is merged incrementally, which typically completes without warehouse errors even on smaller warehouses.

During incremental CDC, Read Oracle CDC Stream materializes each changed row in memory before it is processed. This applies even when Oversized Value Strategy is set to Set Null: the connector must load the full row, including all LOB columns, before it can replace oversized values with NULL. If a single row contains multiple multi-gigabyte LOB columns, the total in-memory size can exceed the runtime heap and cause an out-of-memory error. Exclude such columns from replication before you rely on incremental CDC.

Reinstall the connector¶

This section provides instructions on how to reinstall the connector, and continue replicating data for the same tables without having to snapshot them again. It covers situations where the new connector is installed in the same runtime, as well as moved to a new runtime.

Prerequisites¶

Review and note connector parameter context values. If you’re reinstalling the connector in the same runtime, you can reuse the existing context. If the new instance is located in a different runtime, you must re-enter all parameters.

1. Finish processing all in-flight FlowFiles in the existing connector, then stop the connector.

   1. Sign in to Snowsight.

   2. In the navigation menu, select Ingestion » Openflow.

   3. Select Launch Openflow.

   4. In the Openflow pane select the Runtimes tab.

   5. Select the runtime containing the connector.

   6. Select the connector.

   7. Stop the topmost processor Set Tables for Replication in the Snapshot Load group.

   8. Stop the topmost processor Read Oracle CDC Stream in the Incremental Load group.

   9. If you changed the value of the Merge Task Schedule CRON parameter, return it to * * * * * ?, otherwise queues won’t be emptied until the next scheduled run.

      Wait until all FlowFiles in the connector have been processed, and all queues are empty. When all FlowFiles have been processed, the Queued value on the connector’s processor group becomes zero. If any items remain in the original connector’s queues, data gaps might occur when the new connector starts.

   10. Stop all processors and controller services in the connector.

   Caution

   The existing connector can remain in the runtime and doesn’t interfere with the new instance, as long as it remains stopped.

2. If you’re moving the connector to a new runtime, download the flow definition from the existing connector so that you can recreate the connector with its current state instead of configuring it from scratch. Downloading a flow definition requires Openflow Runtime Server version 2026.6.4.18 or later.

   1. Right-click the connector’s process group, then select Download flow definition.
   2. Select both of the following options, then download the flow definition:
      • Export with External Services: includes the controller services that the connector references from parent process groups.
      • Export with Components State: includes component state, such as redo log positions and incremental replication state, so that replication continues from where it left off.

3. Create the connector in the target runtime:

   • If you downloaded the flow definition, import it into the new runtime. Importing the flow definition preserves the component state captured during the export, so the connector resumes incremental replication from its previous positions.
   • Otherwise, create a new instance of the connector. If you’re using the same runtime as the original connector, you can choose to keep the existing parameter contexts and reuse the settings.

4. If you’re installing into a different runtime or you deleted the previous parameter contexts, enter the configuration settings into the new parameter contexts, including the table names and patterns as described in Install and configure the Openflow Connector for Oracle. A downloaded flow definition doesn’t include sensitive values (such as passwords) or uploaded files (such as the Oracle auto-login wallet file), so you must re-enter and re-upload them.

5. Navigate to the Oracle Ingestion Parameters context, and set the following parameters:

   • Set the Ingestion Type parameter to incremental. For more information on the concerns see Enable incremental replication without snapshots on an existing connector.
   • Set the Starting XStream Position parameter to Earliest. For more information and potential concerns see Alter XStream outbound server.

   Note

   If you imported the flow definition with Export with Components State selected, the connector retains its previous redo log positions. In this case, leave Starting XStream Position set to Latest to continue replication from where it stopped.

6. Start the new connector.

Usage notes¶

The new connector uses the existing destination tables that were created by the original connector, but the connector creates new journal tables.

Alter XStream outbound server¶

The connector regularly updates the XStream server with the latest SCN position it processed. If the connector is reinstalled and connects to the same XStream outbound server, it will resume reading from the SCN position where it left off. This SCN number can be checked with:

SELECT PROCESSED_LOW_SCN
FROM DBA_XSTREAM_OUTBOUND_PROGRESS
WHERE SERVER_NAME = 'XOUT1';

If you want to re-read data from an earlier position, you must first change the start SCN of the XStream server:

BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
        server_name => 'XOUT1',
        start_scn => <start_scn>
    );
END;
/

The value of <start_scn> must be a valid SCN within the range of available redo logs. The lowest SCN that the start position can be reset to can be checked with:

SELECT REQUIRED_CHECKPOINT_SCN
FROM DBA_CAPTURE
WHERE CLIENT_NAME = 'XOUT1';

This is the lowest SCN for which the capture process requires redo information.

Specify load from XStream position¶

The Openflow Connector for Oracle connector allows you to select the starting position where Oracle redo logs are read. By default the connector reads from the latest available position. Alternatively, you can choose the earliest position available on the source instance. Choosing to start from the earliest position is common when reinstalling the connector. This allows the new instance to catch up and continue replicating existing tables without having to snapshot each again.

The following parameters are available in the Ingestion Parameters context:

To determine whether the connector finished re-reading the redo logs:

1. Navigate to the Openflow canvas.
2. Open the Incremental Load process group.
3. Right-click the topmost processor named Read Oracle CDC Stream, then select View state.
4. Compare the state entries:
   • lcr.position.rewind: the latest position the processor read before re-reading of the redo logs started.
   • lcr.position.last: the current latest position read by the processor. As long as this value is lower than the rewind value above, the processor is still re-reading the redo logs.

Usage notes¶

• After a running connector is switched to read from the earliest position, and starts running, the process can’t be reconfigured or cancelled, and continues until the currently-read position reaches the position from before it started.
• Switching to the earliest position on a running connector will, for any tables being re-processed, finish their existing journals, and create new journal tables.
• If the redo log contains events from a previous table that was dropped and re-created in the source database, the re-reading the stream re-processes all events in the current destination. The connector can’t distinguish between a previous and current source table if they share the same name.