My Oracle Support Banner

How to Diagnose Wrong Time ( SYSDATE and SYSTIMESTAMP) After DST Change , Server Reboot , Database Restart or Installation When Connecting to a Database on an Unix Server (Doc ID 1627439.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Standard Edition - Version 9.0.1.0 and later
Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.

Symptoms



Possible reasons to follow this note:

* The result of SYSDATE and SYSTIMESTAMP is not what is expected and is one (or several) hours "off".
* The result of SYSDATE and SYSTIMESTAMP of a "local" oracle_sid connection using sqlplus on the server shows the correct time but when using sqlplus with an remote (listener) connection SYSDATE and SYSTIMESTAMP are not correct / one or more hours "off".

 For changing the result of SYSDATE and SYSTIMESTAMP to a NEW timezone please see <note 1988586.1> How to Change the "Database Time" ( SYSDATE and SYSTIMESTAMP) To An Other Time / Timezone

Changes

 This may happen:

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
 A) Is there any "Oracle database" setting or init.ora parameter that impacts or defines the timezone / result of SYSDATE and SYSTIMESTAMP ?
 B) The FIRST question to ask is what is the correct / required Unix Operating System setting to get a correct time:
 B.1) Check if the current time in UTC is correct on OS level .
 B.2) Check that the timezone chosen is correct / gives the time you require .
 B.3) Check that the timezone chosen gives the correct OFFSET .
 B.4) Note that on Unix setting the TZ setting in a Unix prompt does NOT change the timezone for any other Unix session or already running program (even if they are using the same OS user) .
 B.5) Note that on most Unix systems setting the TZ parameter to an invalid value it will NOT report an error, the time shown is then typically in UTC .
 B.6) Note that the Etc/GMT<offset>, GMT<offset> syntax for TZ is inverse of what is often assumed .
 C) Why SYSDATE and SYSTIMESTAMP may be different when connecting "locally" (= ORACLE_SID connection) in sqlplus on the server and trough a listener connection ?
 C.1) How to see if automatic listener registration is used ?
 C.2) How to see the OS timezone used when the database was started / current used OS timezone for background processes ?
 D) Once the correct/required OS TZ setting is known then check and change (if needed) the Unix env used by Oracle:
 D.1) When using Grid Infrastructure  (if Grid Infrastructure is NOT used go to step D.2) .
 D.2) When using RAC (if RAC is NOT used go to step D.3) .
 D.3) When using ASM (if ASM is NOT used go to step D.4) .
 D.4) Check the default setting for the Unix user (typically the Unix "oracle" user) used to stop and start the oracle environment .
 E) Once everything is checked restart the oracle stack.
 E.1) If Grid control is used and s_crsconfig_<nodename>_env.txt was changed on any of the nodes (or all) then use crsctl to restart the cluster .
 E.2) If RAC is used and the TZ value seen in srvctl was changed then use srvctl to restart the databases and listeners .
 E.3) When not using Grid control or RAC then restart the database using sqlplus and the listener using lsnrctl .
 E.4) Make a remote client connection using sqlplus and check the result of "sysdate" and "systimestamp" .
 F) Check also the OEM agent configuration ( this TZ is used when DB is started trough the Agent ).
 G) I did all the checks in point D) and found no wrong configuration but sysdate is wrong when connecting trough the listener .
 H) Other often misunderstood settings (who have NO impact on "sysdate" but are often confused with or looked at) or posed questions .
 I) I restart the listener and then sysdate is the first few minutes correct for a remote connection using this listener but after a minute or two connections have again a wrong sysdate .
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.