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 APRIL 09, 2024
Applies to:
Oracle Database - Standard Edition - Version 9.0.1.0 and laterOracle Database - Enterprise Edition - Version 9.0.1.0 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.
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:
- after an installation of a new server and new database.
- after a restart of the database / server.
- after an DST change in the region .
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 |