Hello Experts,
Customer Using on-premises Oracle Golden Gate and trying to capture data from Autonomous Oracle Database. At the same time, this autonomous database is a PaaS service, they have only PDB access. However, there is a constant 4-hour delay in the process:
GG SERVER TZ :
[oracle@host9]$ timedatectl
Local time: Wed 2024-09-25 11:17:00 +04
Universal time: Wed 2024-09-25 07:17:00 UTC
RTC time: Wed 2024-09-25 11:16:59
Time zone: Asia/Dubai (+04, +0400) ▶ From GG Server
EXTRACT RUNNING E_A1 03:59:47 00:00:04
There is a following warning in logs:
2024-09-20T12:40:52.467+0400 WARNING OGG-02920 Oracle GoldenGate Capture for Oracle, EXT_xxxx: Parameter TRANLOGOPTIONS SOURCE_OS_TIMEZONE must be specified, but it is not. Source database OS time zone offset: GMT, Extract OS time zone: Asia/Dubai.
Customer mentioned the time zone in the source database is Asia/Riyadh.
The problem is with seconds, 00:00:44 this is the least TZ settings which work without issue but 44 seconds Lag always.
For some reason, from GG perspective, the time difference between autonomous database and golden gate server is 3:59:44 (the last number is seconds).
Therefore:
If we put GMT-00:01, we get 00:00:44
If we put GMT-00:04, we get 00:03:44
We tried different setting and got below Results:
++++++++++++++++++++++++++++++++++++++++
when I put TRANLOGOPTIONS SOURCE_OS_TIMEZONE Asia/Dubai, the delay is 4 hours
when I put TRANLOGOPTIONS SOURCE_OS_TIMEZONE Asia/Riyadh, the delay is 3 hours
when I put TRANLOGOPTIONS SOURCE_OS_TIMEZONE Africa/Accra, the delay is unknown status
when I put TRANLOGOPTIONS SOURCE_OS_TIMEZONE UTC, the delay is unknown status
TRANLOGOPTIONS SOURCE_OS_TIMEZONE Atlantic/Madeira (00:59:44)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE America/New_York (unknown status)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE Africa/Bissau (unknown status)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE GMT+00:00 (unknown status)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE +00 (unknown status)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE +0 (unknown status)
setenv (TZ=GMT-2) (01:59:44)
setenv (TZ=GMT-1) (00:59:45)
setenv (TZ=GMT-0) (unknown status)
setenv (TZ=GMT+0) (unknown status)
setenv (TZ=GMT+1) (unknown status)
setenv (TZ=GMT-00:00) (unknown status)
setenv (TZ=GMT-00:01) (00:00:44)
Please pay attention to the last value, the lag there is 44 seconds only. As I mentioned before, we cannot use exactly 4 hours, because we go below zero. However, when we put the difference as 03:59, than it works. Now the question is why.
How can we resolve this issue with time difference? Let me know if there are any known issue.
Thanks,