I recently received a call from one of my clients, regarding the fact that their brand new database was displaying the wrong time. He indicated that they had checked the time zone of the database and the time zone of the server and that everything was in order.
Fortunately I have seen this scenario a few times before and it is easy to resolve, so we got to work!
Symptoms
We started by checking that the applications showed time 5 hours ahead of the real time.
When we connect to the computer where the database operates, we verify that the time is correct, as well as the time zone (we are in Lima, Peru).
[root@node1 ~]# timedatectl
Local time: Sun 2024-08-25 18:07:48 -05
Universal time: Mon 2024-08-25 23:07:48 UTC
RTC time: Sun 2024-08-25 18:07:48
Time zone: America/Lima (-05, -0500)
NTP enabled: yes
NTP synchronized: yesCause
During the installation of Grid Infrastructure, Oracle saves the time zone information in a file, and the contents of this file is the only thing it pays attention to, hence the operating system can be configured with a new time zone, but the Oracle software continues to operate with the original time zone, causing the discrepancies in the applications.
We checked the contents of this file and voilà! We found that the time zone that Grid Infrastructure is using is actually: UTC (see line 19).
[root@node1 ~]# cd $ORACLE_HOME/crs/install
[root@node1 install]# cat s_crsconfig_node1_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=UTC
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
CRS_LIMIT_STACK=2048
CRS_LIMIT_OPENFILE=65536
CRS_LIMIT_NPROC=65536
TNS_ADMIN=Solution
The first step is to edit the file, registering the correct time zone, which for our case is: America/Lima.
[root@node1 install]# grep 'TZ=' s_crsconfig_node1_env.txt
TZ=UTC
[root@node1 install]# sed -i 's/TZ=UTC/TZ=America\/Lima/' s_crsconfig_node1_env.txt
[root@node1 install]# grep 'TZ=' s_crsconfig_node1_env.txt
TZ=America/LimaUnfortunately we have to restart the Grid Infrastructure services, but it is necessary because the file we have just modified is read only at startup.
[root@node1 ~]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'node1'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'node1'
CRS-2673: Attempting to stop 'ora.chad' on 'node1'
. . .
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@node1 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.Finally, we check that the application already shows the correct time, there are no more differences.
That was easy, wasn’t it? Of course, all this could have been avoided if the computer’s time zone had been verified to be correct before starting the installation and configuration of Grid Infrastructure.
If you want more information, read these notes, available at My Oracle Support:
| 1209444.1 | How To Change Timezone for Grid Infrastructure |
| 1390015.1 | Incorrect SYSDATE shown when connected via Listener in RAC |