LATEST TOPICS

Oracle Database: Timezone mismatch between Local and Remote Connections

Today, I have come across an interesting issue. One of the application that I support, was complaining about timezone mismatch in the database between the local and remote connections.

Basically, when they were directly (BEQ connections/ Local connections) connecting to the database, it was showing the correct timezone for the database. However, when they were connecting remotely (through Listener), it was showing an incorrect timezone for the database.

I have simulated the scenario on my lab machine. Here is the scenario that the application was facing.

My lab machine is using IST (Asia/Kolkata – UTC +05:30) timezone. Therefore, I am expecting the database to return the same timezone; whether I connect locally or remotely to the database.

Problem Simulation

Lets check the timezone configuration of the database server.

[oracle@labserver etc]$ hostname
labserver.home.com
[oracle@labserver etc]$ date
Fri Dec 5 23:30:36 IST 2014
[oracle@labserver etc]$ cat /etc/sysconfig/clock
# The ZONE parameter is only evaluated by system-config-date.
# The timezone of the system is defined by the contents of /etc/localtime.
ZONE="Asia/Kolkata"
UTC=true
ARC=false

As we can see, the database server is using IST (Asia/Kolkata: UTC + 05:30) timezone.

Now, lets query the database locally to check the timezone.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 5 23:36:29 2014

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

sys@LABDB> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
labdb            labserver.home.com

sys@LABDB> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-DEC-14 11.36.45.469523 PM +05:30

As expected, database is showing IST (UTC+05:30) timezone.

Now, lets query the database remotely to check the timezone.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 5 23:40:18 2014

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

Enter user-name: abbas/abbas@labdb
Last Successful login time: Sun Nov 30 2014 11:49:50 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

abbas@LABDB> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
labdb            labserver.home.com

abbas@LABDB> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-DEC-14 01.10.44.840608 PM -05:00

As we can see, when we queried the database remotely, it is showing a different timezone (in our case EST UTC-05:00 which is US/Eastern timezone)

Possible Root Cause Identification

Now, lets find out, the root cause of this issue.

The first thing that needs to be checked is, whether the database listener is using a different timezone than that of the database; since I am getting the mismatch only when I connect remotely through the listener.

We can check the timezone environment settings of the LISTENER by using any of the following methods

[oracle@labserver ~]$ srvctl getenv listener -l LISTENER -envs "TZ"
LISTENER:
TZ=Asia/Kolkata

If the above method is not showing the TZ information, we can use the following alternative method.

Get the PID of the LISTENER process.

[oracle@labserver ~]$ ps -ef | grep tns
oracle    9728     1  0 00:09 ?        00:00:00 /app/oracle/grid/12.1.0.2/bin/tnslsnr LISTENER -inherit
oracle    9768  5992  0 00:10 pts/2    00:00:00 grep tns

Now, check the environment settings related to the Listener process PID using ‘cat /proc/[process pid]/environ’. Look for TZ environment variable for timezone setup.

[oracle@labserver ~]$ cat /proc/9728/environ
HOSTNAME=labserver.home.com
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
OLDPWD=/etc
USER=oracle
LD_LIBRARY_PATH=N #line added by Agent :/app/oracle/grid/12.1.0.2/lib
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
ORACLE_SID=+ASM
MAIL=/var/spool/mail/oracle
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/app/oracle/db/12.1.0.1/bin:N           # line added by Agent: /app/oracle/grid/12.1.0.2/bin:N         # line added by Agent:/app/oracle/db/12.1.0.1/bin:N              # line added by Agent: /app/oracle/db/12.1.0.1/bin:N             # line added by Agent:/app/oracle/grid/12.1.0.2/bin:N          # line added by Agent: /app/oracle/db/12.1.0.1/bin:N             # line added by Agent:/app/oracle/grid/12.1.0.2/bin:N           # line added by Agent
INPUTRC=/etc/inputrc
PWD=/home/oracleLANG=en_US.UTF-8
TZ=Asia/Kolkata
GG_HOME=N              # line added by Agent
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=1
HOME=/home/oracle
LOGNAME=oracle
CVS_RSH=ssh
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=localhost:11.0
ORACLE_HOME=/app/oracle/grid/12.1.0.2
G_BROKEN_FILENAMES=1
_=/app/oracle/db/12.1.0.1/bin/lsnrctlORA_NET2_DESC=7,10

In my case, TZ=Asia/Kolkata for Listener process. Hence, I can conclude that Listener is not playing around with the timezone while connecting to the database.
However, if we see the TZ for Listener is different from that of the database/server, we need to restart the Listener by setting the appropriate value for TZ environment variable.

We can use any of the following methods to set the TZ for Listener.

$ srvctl setenv listener -l [LISTENER_NAME] -env "TZ=Asia/Kolkata"
$ srvctl stop listener -l [LISTENER_NAME]
$ srvctl start listener -l [LISTENER_NAME]

OR

$ export TZ='Asia/Kolkata'
$ lsnrctl stop [LISTENER_NAME]
$ lsnrctl start [LISTENER_NAME]

Since we have verified that Listener is using correct timezone; we can rule out Listener from the list of probable root cause. Lets, check if my database is using a correct timezone (in our case Asia/Kolkata: UTC+05:30) or not.

Get the PID of the PMON database process.

[oracle@labserver ~]$ ps -ef | grep pmon | grep labdb
oracle    7160     1  0 Dec05 ?        00:00:01 ora_pmon_labdb

Now, check the environment settings related to the Database PMON process PID using ‘cat /proc/[process pid]/environ’. Look for TZ environment variable for timezone setup.

[oracle@labserver ~]$ cat /proc/7160/environ
__CRS_ACTIVE_VERSION=12.1.0.2.0
__CLSAGFW_TYPE_NAME=ora.asm.type
ORA_CRS_HOME=/app/oracle/grid/12.1.0.2
HOSTNAME=labserver.home.com
TERM=xterm
SHELL=/bin/bash
__CRSD_CONNECT_STR=(ADDRESS=(PROTOCOL=IPC)(KEY=OHASD_IPC_SOCKET_11))
HISTSIZE=1000
NLS_LANG=AMERICAN_AMERICA.US7ASCII
CRF_HOME=/app/oracle/grid/12.1.0.2
GIPCD_PASSTHROUGH=false
__CRSD_AGENT_NAME=/app/oracle/grid/12.1.0.2/bin/oraagent_oracle
__CRSD_MSG_FRAME_VERSION=2
USER=oracle
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
ORACLE_SID=labdb
__CLSAGENT_INCARNATION=1
CRS_LIMIT_MEMLOCK=unlimited
ORACLE_BASE=
ORASYM=/app/oracle/grid/12.1.0.2/bin/oraagent.bin
PATH=MAIL=/var/spool/mail/oracle
PWD=/
INPUTRC=/etc/inputrc
ENV_FILE=/app/oracle/grid/12.1.0.2/crs/install/s_crsconfig_labserver_env.txt
CRS_LIMIT_OPENFILE=65536
LANG=en_US.UTF-8
CRS_LIMIT_NPROC=16384
TZ=US/Eastern
__IS_HASD_AGENT=TRUE
__CLSAGENT_LOG_NAME=ora.asm.type_oracle
GG_HOME=N           # line added by Agent
__CLSAGENT_INCARNATIONTS=1012484
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
HOME=/home/oracleSHLVL=1
__CLSAGENT_LOGDIR_NAME=ohasd
LD_ASSUME_KERNEL=
CRS_LIMIT_CORE=unlimited
__CLSAGENT_USER_NAME=oracle
CRS_LSNR_STACK=10240
LOGNAME=oracle
CVS_RSH=sshLESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=localhost:11.0
ORACLE_HOME=/app/oracle/db/12.1.0.1
G_BROKEN_FILENAMES=1
CRS_LIMIT_STACK=2048
ORA_NET2_DESC=103,109
ORACLE_SPAWNED_PROCESS=1
SKGP_HIDDEN_ARGS=0
SKGP_SPAWN_DIAG_PRE_EXEC_TS=1417328257SKGP_SPAWN_DIAG_PRE_FORK_TS=1417328257SKGP_SPAWN_DIAG_POST_FORK_TS=1417328257

When I look for the TZ environment variable, I could see PMON is using TZ=US/Eastern rather than TZ=Asia/Kolkata. This means, the database itself is running with a different timezone (US/Eastern) setup rather the server timezone setup.

There are lot of probabilities, which could start a database with a different timezone than the server timezone.

1. There is a possibility that, when the database was started the timezone environment variable TZ was set to a different timezone (in our case US/Eastern) than the server timezone (in our case Asia/Kolkata)
2. There is a possibility that, the server timezone was intentionally changed to different timezone since the last database startup.
3. There is a possibility that, the timezone is set to a different value than the server timezone in the cluster configuration file ($GRID_HOME/crs/install/s_crsconfig_[hostname]_env.txt) file which controls the environment settings for the Clusterware components. If TZ has a different value than the server timezone for clusterware components, then using SRVCTL/CRSCTL would start the database with the clusterware TZ settings rather than the server TZ settings

Skipping first two options based on probable assumptions, Lets take a look at the 3rd options related to the clusterware environment settings.

[oracle@labserver ~]$ cat /app/oracle/grid/12.1.0.2/crs/install/s_crsconfig_labserver_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=US/Eastern
NLS_LANG=AMERICAN_AMERICA.US7ASCII
CRS_LIMIT_STACK=2048
CRS_LIMIT_OPENFILE=65536
CRS_LIMIT_NPROC=16384
TNS_ADMIN=

As we can see, the clusterware environment configuration is pointing to US/Eastern timezone, which is why the database was started with a different timezone than the server timezone.

Then the obvious question,why are we getting correct timezone while querying the database locally?

This is because, when we connect to the database locally, we actually bypass the Listener and the BEQ (Bequeath) protocol spawns a dedicated server with the environment settings taken from the server. That is why, when a system call is performed to get the SYSTIMESTAMP; the dedicated server process directly performs a Operating system call to get the current system timestamp which is evaluated based on the server timezone settings as the server process was spawned with environment settings from the database server.

However, when we connect to the database remotely, PMON registers the service name as well as the environment variables for the database associated with that service name to the Listener and in turn spawns a dedicated server with environment settings passed by the PMON process. In this case, when a system call is performed to get the current system timestamp, it is evaluated based on the environment settings passed by the PMON process to the server process.

Fixing the Problem

Based on the component (Listener/Database) where the problem lies, we can choose different solutions to fix the problem.

1. If the problem is at Listener level, we need to restart the Listener by setting the TZ environment variable correctly.
2. If the problem is at Database level, we need to restart the database by setting the TZ environment variable correctly.
3. If the problem is at clusterware environment settings, We need correct the configuration setting in $GRID_HOME/crs/install/s_crsconfig_[hostname]_env.txt file as well as restart the clusterware stack in order to reflect the correct TZ settings.

All of the above steps requires an outage either for the Listener, database or for the entire clusterware stack.

However, we can also implement a quick workaround with a negligible connection outage by configuring a static listener service for the database. In this way, the remote connections will bypass PMON process even after coming through the Listener.

For example, lets create a static entry for the labdb database Listener and connect to the database trough the static service.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
DEDICATED_THROUGH_BROKER_LISTENER=ON

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
  (SID_NAME=labdb)
  (GLOBAL_DBNAME=labdb)
  (ORACLE_HOME=/app/oracle/db/12.1.0.1)
 )
)


[oracle@labserver ~]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-DEC-2014 01:51:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

[oracle@labserver ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-DEC-2014 01:51:25

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                06-DEC-2014 00:09:26
Uptime                    0 days 1 hr. 41 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/grid/12.1.0.2/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/labserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=labserver.home.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "labdb" has 1 instance(s).
  Instance "labdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now, lets connect to the database remotely through the static listener entry.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Dec 6 01:52:19 2014

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

Enter user-name: abbas/abbas@labdb
Last Successful login time: Sat Dec 06 2014 01:39:19 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options



abbas@LABDB> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
labdb            labserver.home.com

abbas@LABDB> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-DEC-14 01.52.38.322174 AM +05:30

As we can see, while connecting remotely through static listener service, we are getting the correct server timezone, even if the database was started with a different timezone.

Conclusion

Whenever, there is a timezone mismatch between local and remote connections, it is the TZ environment variable that decides on which timezone to be displayed. We need to check Listener Process, Database Process and the Clusterware environment configuration file to see if the TZ environment variable is matching with the server timezone.

We can optionally set the TZ for each of the CLusterware components (Listener, Database, etc.) to have explicit values using the SRVCTL SETENV command.

Reference

Why does sysdate have the Wrong Time Stamp when Connecting via the Listener (Doc ID 301420.1)

5 Comments
  1. saurabh.jec@gmail.com
    • Abu Fazal Abbas
      • Saurabh sharma
  2. Ankur Varshney
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed