Automatic Timezone Detection for Source Oracle DB (LGWR/SESSIONTIMEZONE) During Capture/Refresh
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
0 comments