How to Diagnose Wrong Time -Wrong Time Zone- Oracle database

How to Diagnose Wrong Time

Issue Details:-

We had faced an issue of Wrong time zone in RAC environment after switchover done and application team informed that time was lagging behind by 5 hours as this application uses TZ=Europe/Berlin time zone but it was taking TZ=America/New_York

We started investigation:- 

ORCLDB:

SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

DBTIME  SESSIONTIMEZONE  

------  ------------------

+00:00   +01:00

sqlplus sys/******@ORCLDB as sysdba 

[oracle@bms04dbaora02t admin]$ sqlplus sys/******@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 10:49:12 2024

Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

---------------------------------------------------------------------------

07-MAR-24 10.49.30.206771 AM +01:00

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')  TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

--------------------------------------   ------------------------------------------

07/03/2024 04:50:42                      07/03/2024 04:50:42 -05:00 ------------------ >>>> Issue

We have below two nodes in RAC

Node 1 :- ora04dba05p

Node 2 :- ora04dba06p

I went to below location and checked s_crsconfig_ora04dba05p_env.txt file  and could see the TZ=America/New_York, this was the issue ..

cd /applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

Solution :-

1) Modify the files with TZ=Europe/Berlin time 

2) Restart the clusterware one by one

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

root#ora04dba05p] crsctl stop crs

root#ora04dba05p] crsctl start crs

ora04dba06p:-

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

root#ora04dba06p] crsctl stop crs

root#ora04dba06p] crsctl start crs

now we are getting correct value

[oracle@bms04dbaora02t admin]$ sqlplus sys/*****#1234@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 11:27:07 2024

Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')  TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

--------------------------------------   ------------------------------------------

07/03/2024 11:27:12                      07/03/2024 11:27:12 +01:00 -------------------->>>> Issue Resolved

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)

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

select current_timestamp from dual;

select systimestamp from dual;

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

SELECT DBTIMEZONE FROM DUAL

SELECT SESSIONTIMEZONE FROM DUAL

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;