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 DECEMBER 30, 2019
Applies to:Oracle Database - Standard Edition - Version 188.8.131.52 and later
Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
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
This may happen:
- after an installation of a new server and new database.
- after a restart of the database / server.
- after an DST change in the region .
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
|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 .|