Error: ORA-01555/ORA-22924 Snapshot Too Old

Issue

While using an Oracle connection with the Binary Log Reader incremental sync method, a table-level re-sync remains in the extract phase for several hours, then fails with the following errors:

• ORA-01555: snapshot too old
• ORA-22924: snapshot too old

This can happen even if the table scan completes quickly when run directly in Oracle. For example, SELECT COUNT(*) may finish in seconds, while the Fivetran table-level re-sync takes several hours before failing.

This issue commonly affects large tables that contain CLOB or LOB columns.

Environment

• Connector: Oracle
• Incremental sync method: Binary Log Reader (BLR)

Resolution

To resolve this issue, increase the UNDO retention window so that Oracle can preserve undo data for the full duration of the long-running snapshot:

1. Check the current UNDO retention value:

   SELECT name, value
   FROM v$parameter
   WHERE name = 'undo_retention';
   

   The value is returned in seconds. For example, 10800 represents 3 hours.

2. Compare the UNDO retention window with the table-level re-sync duration. If the re-sync runs longer than the UNDO retention value, Oracle may return an ORA-01555 error.

3. Work with your DBA to increase UNDO retention at the system level. For example:

   ALTER SYSTEM SET undo_retention = 172800 SCOPE=BOTH;
   

   In this example, 172800 seconds equals 48 hours.

4. Ensure the UNDO tablespace has sufficient capacity and autoextend is enabled to support the longer retention period.

5. Re-sync the affected table:

   i. In Fivetran, go to your Oracle connection page.

   ii. Select the Schema tab.

   iii. Find the affected table, then click Re-sync.

   iv. In the confirmation pop-up window, click Start re-sync.

Cause

This issue occurs when a table-level re-sync performs a long-running consistent snapshot that exceeds the configured UNDO retention window.

When you use the Binary Log Reader incremental sync method, we execute a full SELECT statement that retrieves all columns, including CLOB or LOB columns. Fetching LOB data can significantly increase the duration of the snapshot. If the snapshot runs longer than the UNDO_RETENTION setting, Oracle may reuse the required undo blocks and raise the above errors.

This behavior is expected when UNDO retention is insufficient for a large, LOB-heavy table snapshot.