How to diagnose Sysdate return error time issues in a RAC environment

Recently, there have been issues with access to sysdate return error times in some RAC environments, which often occur when database links are created through listener, and in most cases are related to time zone settings. In this article we will explain how to diagnose this problem. This article applies to version 11.2.0.2 and above.

First, introduce the knowledge involved in the problem.
1. Starting from version 11.2.0.2 Oracle cluster (GI) has its own time zone and some other configurations that are saved in the profile <gi_home>/crs/install/s_crsconfig_< node name The >_env.txt.
For example:
Tz=asia/shanghai
Nls_lang=american_america. Al32utf8
tns_admin=
oracle_base=
We can see that the variable TZ is used to define the time zone of the cluster. Of course, the time zone of this cluster is obtained from the operating system when GI is installed. Since the cluster has a time zone, we need to ensure that the GI time zone and operating system settings are consistent, and when the operating system's time zone changes, the GI time zone also needs to be changed. The basic steps to modify the cluster time zone are (modify the <gi_home>/crs/install/s_crsconfig_< node name >_env.txt file and restart the nodes).
2. When the database or Listner uses the Srvctl command or when the GI boot is started, the environment variable inherits the GI's time zone. You can also manually set the environment variables for the database and listener resources by using the following command.
Srvctl setenv database-d <dbname>-T ' tz=< time zone > '
srvctl setenv listener-l <listenername>-T ' tz=& lt; time zone > '
3. The value returned by Sysdate does not depend on the time zone setting of the database, but Oracle simply fetches the system time back from the operating system (for example, calling the OS function Gettimeofday). Therefore, modifying the time zone of the database is not helpful for this issue. The environment variable TZ used by the corresponding server process will affect the system time returned.

Next, let's briefly describe the process through which clients connect to the database through listener. We will explain it through a concrete example. In this example, we use Sqlplus to create a database link and collect truss information for the listener process
1. Client Connection Database
Sqlplus Scott/[email protected]
The 2.listner process received the corresponding link and generated the corresponding server process.
524732:psargs:/u01/app/11.2.0/grid/bin/tnslsnr Listener-inherit
......
524732:kfork () = 496094
496094:kfork () (returning as child ...) = 0
......
496094:kfork () = 483742
483742:kfork () (returning as child ...) = 0
3. Specify the environment variables for the server process.
483742:execve (0x0fffffffffff2660, 0x0000000110773730, 0x000000011077b670) argc:2
483742:argv:oracle<sid name> (local=no) <<<<<<<< server process environment variable is specified
483742:ENVP: _=/u01/app/11.2.0/grid/bin/oraagent.bin Lang=en_us login=root
483742: __clsagent_incarnation=2 _ora_agent_action=true path=
483742:nls_lang=american_america. WE8ISO8859P1 __clsagent_user_name=oracle
......
483742:env_file=/u01/app/11.2.0/grid/crs/install/s_crsconfig_<node Name>_env.txt
......
483742: __CLSAGENT_LOGDIR_NAME=CRSD pwd=/Tz=asia/shanghai <<<< time zone is specified.

We can see that the value of the environment variable TZ is bound to the server process when it is created. Of course, if you do not collect truss output for Lisetner. You can also obtain environment variables for the corresponding process through the operating system commands, for example: PS eauwww <pid from above>, you can get commands for different platforms from the content in MOS note 373303.1. In addition, the above database is started by the GI agent, if the database is started manually (for example: Startup command), then the output will be different. Of course, Pmon also registers its environment variables with the corresponding service when registering the database service to listener.

Therefore, we need to check the following information when diagnosing the problem of Sysdate return error time in a RAC environment.
1. The operating system-level time zone setting and ensure that the operating system command date returns the correct time. For information on how to view the time zone settings for different platforms, please refer to note 1209444.1
2. Confirm that the variable tz in the GI profile <gi_home>/crs/install/s_crsconfig_< node name >_env.txt file is consistent with the operating system TZ setting.
3. Verify that the TZ variable is set at the database or listener resource level. If set, whether the settings are consistent with the os,gi.
4. In addition, the environment variables Libpath or Ld_library_path of the server process also have an impact on Oracle access to operating system functions. And the GI Agent process (for versions 11.2.0.3 and above) automatically empties the following environment variables of the process when the resource is started (for example, database resource)
Ld_library_path, Shlib_path (HP-UX), ld_libpath_path_64 (Solaris), LIBPATH (AIX)
So, if your database is started with the SRVCTL command, you need to make sure that the environment variables above are set correctly.
Example: Srvctl setenv database-d <db_name>-t ' libpath=<gi_home/lib> '
Note: The above commands may be different for different UNIX platforms.
So, we also go to confirm whether the LIBPATH or ld_library_path variable of database resource is set.
Example: Srvctl getenv database-d <db_name>
In addition, the following information needs to be collected when diagnosing this problem.
1. <gi_home>/crs/install/s_crsconfig_< Node name >_env.txt file
2. Settings for operating system time zone settings (cat/etc/sysconfig/clock) and environment variable TZ. and the environment variables of the Pmon process.
3. Environment variables for database and listener resources
Example: Srvctl getenv database-d <db_name>
Srvctl getenv listener-l <listener name>
4. If the above information is not a problem, then you need to collect the truss (or strace) output of the listener process to find the problematic environment variable settings.
5. If the information in 1-4 is still unable to find the cause of the problem, gather the client and server-side sqlnet trace to confirm that there are any ' alter session set ... ' commands that modify the session's time zone or related variables.
Client sqlnet Trace: Set the following parameters in the client's Sqlnet.ora file.
Trace_level_client=16
Trace_directory_client=c:\tmp ==> Ensure that the path exists
Trace_file_client=client
Trace_unique_client=on
Trace_timestamp_client=on
Server-side sqlnet trace: Set the following parameters to the server-side Sqlnet.ora file
Trace_level_server=16
Trace_file_server=server
Trace_directory_server=/tmp ==> Ensure that the path exists
Trace_timestamp_server=on

Another 11.2.0.3 There is a bug on the IBM AIX on POWER Systems (64bit), even if it is set correctly, it will not be displayed as GTM/UTC time.

Bug 16310858:db starting with wrong TIMEZONE when starting with SRVCTL

How to diagnose Sysdate return error time issues in a RAC environment