Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Automatic Timezone Detection for Source Oracle DB (LGWR/SESSIONTIMEZONE) During Capture/Refresh

Answered
Chirag Shah User

Which database system?: Source DB - Oracle DB with different SESSIONTIMEZONE/LGWR's TZ than HVR HU Server Timezone.

Additional details: We've observed that transactions get skipped/missed after a Refresh when the source Oracle DB's LGWR (Log Writer) process / SESSIONTIMEZONE differs from the HVR Hub Server's (Capture Job) timezone.

Example:
Our source Oracle DB server has DBTIMEZONE set to GMT+00:00. However, because the 'oracle' OS user's .bash_profile sets TZ=America/New_York, all Oracle background processes — including LGWR — start up using GMT-04:00. The HVR Hub Server, meanwhile, runs in GMT+00:00.

When we perform a 'Refresh' using the default option ("Changes before refresh are skipped by both capture and integrate jobs"), any DMLs executed afterward are ignored/skipped. This happens because the control file treats those transactions as "old," due to the -04:00 offset on the LGWR process's timezone.

As a workaround, we had to explicitly set the environment variable TZ=America/New_York in the Channel Action for that specific source location.

Suggested enhancement:
HVR's Capture/Refresh process should automatically detect the source database's timezone and manage it transparently during capture — without requiring an additional TZ environment parameter to be manually configured for each source location.

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Edwin Fivetranner

    Hi Chirag,

    Thank you for this feature request and we want to prevent missing DMLs. We will investigate the issue further before I can comment on whether and how we plan to solve it. Second, I would like to reword some requirements:

    • “HVR should not skip valid post-refresh Oracle changes when redo timestamps are written in a timezone different from the hub/agent timezone.”
    • “HVR should automatically normalize or otherwise handle Oracle redo timestamp timezone differences without per-location manual TZ configuration.”

    This is because internal notes say SESSIONTIMEZONE, DBTIMEZONE, and ORA_SDTZ can be misleading or irrelevant to this bug. The real issue is the timezone used by LGWR / redo timestamps, which may come from the Oracle process environment.

    Best regards,

    Edwin