Technical Service Bulletin 2022-02-28 (TSB) - Oracle CDC origin potential data loss when Daylight Saving Time enabled in Oracle Database

StreamSets Support Team would like to inform you that we discovered an issue where StreamSets Data Collector pipelines with the Oracle CDC origin may experience data loss during the time change back to standard time (in fall/winter) for Daylight Saving Time.

Products Affected: StreamSets Data Collector

Releases Affected: StreamSets Data Collector 4.3.0 and lower

Users Affected: StreamSets Data Collector using Oracle CDC Client stage 

Severity: High/Medium

Description: 

StreamSets Data Collector pipelines using the Oracle CDC origin may experience data loss during the time change back to standard time (in fall/winter) for  Daylight Saving Time (DST).

This issue occurs when the Oracle CDC Origin is processing LogMiner records of operations occurring during a time period impacted by DST. This happens regardless of the current time; the main issue happens when processing the affected time period, even if it was far in the past.

The issue occurs if processing of this time period is done when DST is moving time backward. The root cause is that, as some date/times will happen more than once in a day, we are unable to reposition our LogMiner cursor correctly to ingest records incrementally.

Even though we do not expect this issue to happen when DST moves time forward, we recommend treating moving time forward and backward due to DST as the same issue as Oracle might raise unexpected exceptions when queried for non-existing time windows. The main problem would happen when Oracle is requested for LogMiner records for a time period with no actual operations.

This issue does not happen in all scenarios. This issue happens only if condition 1, along with condition 2 or condition 3, takes place:

1. Having Oracle configured to use a Daylight Saving Time (DST) time zone. This implies having its host server also using DST, as Oracle takes its date/time from its host server
   • To determine whether your Oracle server uses the DST time zone, users can follow these steps:
     • Execute the following SQL command to confirm which time zone the Oracle server uses:
           select DBTIMEZONE from DUAL;
     • Per Oracle’s document, there are two ways a time zone can be defined: by an OFFSET, which is a numeric value like -08:00, or by a NAMED time zone like 'Iran' or 'America/Vancouver' or 'CET'. DST can affect only a NAMED time zone.  Numeric time zones like ‘-08:00’ are not impacted.
     • Users can verify if the specific NAMED time zone uses DST at http://www.iana.org/time-zones as Oracle uses IANA as the official authority to support and implement DST.
     • Another method to determine if the NAMED time zone uses DST is to use zdump.  Linux uses the same IANA standard for date/time.  You can use the Linux zdump utility to determine if a timezone uses DST by checking the output of ”zdump -v <time zone>” for lines describing a time change in the current year.
       
       For instance “zdump -v Europe/Berlin | grep 2022” returns the following
       This indicates there are 4 times during the year 2022 that the time jumps forward or backward and thus are impacted by DST.
     • The following query shows Oracle databases supported NAMED time zones:
           select * from V$TIMEZONE_NAMES;
     • The following queries can help to verify if the Oracle database uses the most recent available DST specification:
           select * from V$TIMEZONE_FILE;
           select DBMS_DST.GET_LATEST_TIMEZONE_VERSION from DUAL;
   • If it is still unclear if the Oracle server uses the DST time zone, we recommend contacting Oracle support for further clarification.
2. Processing Redo Log for the DST-impacted period. To be more precise: if a DST-impacted time period is processed in several steps (please, check LogMiner Session Window in our documentation)
3. Processing Redo Log during a DST-impacted period (using as wall time Oracle’s host server clock time). Date/time configuration in SDC servers is irrelevant for this issue, as this connector always uses Oracle’s system time

Please note that, if there is no actual operation in the database during impacted periods, this issue does not happen.

Immediate action required:  Yes

Workaround: Yes

There are a few workarounds for this issue:

1. If you are in a position to change your database configuration so that it does not use Daylight Saving Time (DST), changing it is one of the workarounds.
2. You can manually control this issue by applying the following procedure:
   • Assume DST will move time from T1 to T2.
   • Say Pmin is the smallest date/time, and Pmax is the largest.
   • Stop your pipeline when Oracle’s system time is Pmax - 1hour.
   • Look at the stored offset and pick its timestamp.
   • Say configuration parameter Transaction Length is TL.
   • Then calculate the LogMiner Session Window (LSW) so that Pmin - TL + LSW >= Pmax + TL + 1hour.
   • Configure your pipeline with this new LSW value.
   • When Oracle’s system time is at least Pmax + TL + 1hour, restart your pipeline.
   • When your offset is well ahead of Pmax + TL + 1hour, you can stop your pipeline, place back your original LSW value, and restart your pipeline again.
   • Basically, this workaround is forcing the pipeline not to run during the DST critical period, and to mine the critical period in one single query.     

For Long-Running Transactions

• Starting from SDC version 4.2.0, a Data Collector pipeline uses the start date/time of the oldest identified transaction not yet sent to batch as a starting date/time when a pipeline is restarted (not the date/time of the last transaction sent to batch). For long-running transactions, this starting point can be long back in time, and its final commit could be long ahead in the future. Special attention should be put in these cases; the general rule is that you need to start your pipeline well before the DST affected period, and you need to let it run until there are no pending transactions started before Pmax + TL + 1hour.
• When in doubt, let the pipeline run (after restarting) for several hours or days to make sure you follow this requirement.

Fix: A fix for this issue is available in SDC 4.4.0. From the SDC version 4.4.0, if the Oracle server uses the DST time zone, pipelines will pause from 1.5 to 2.5 hours during the time change, depending on whether the daylight saving time change is forward or backward. After the time change completes, the pipeline resumes processing at the last-saved offset.

Resolution: If you are using Data Collector pipelines with the Oracle CDC origin, your Oracle database is configured to use a Daylight Saving Time time zone, and the pipeline processes Redo Log for DST impacted period or the pipeline processes Redo Log during a DST impacted period, please follow one of our workarounds and upgrade your Data Collector instance to the version 4.4.0 with a fix at your earliest convenience.

Addendum: 

In addition to the workaround or the fix in SDC 4.4.0, for users who use redo logs as the dictionary source in the Oracle CDC pipeline, we recommend extracting the Log Miner dictionary to the redo logs before and after the time change to avoid any performance issues in case a pipeline needs to be restarted due to some issues around the time change. We recommend extracting the Log Miner dictionary 1 hour before the time change and 1 hour after the time change. However, this task may be done even several hours before and several hours after the time change.
This additional step does not affect users who use the online catalog as the dictionary source in the Oracle CDC pipelines.

For your questions, please contact our support team at support@streamsets.com.