How to diagnose the time error returned by the RAC Environment Sysdate

A number of times the RAC environment has recently been processed to access sysdate errors in return. And this problem is usually a database link is created by now listener case. And. In most cases, it is associated with the time zone setting. In this article we will explain how to diagnose such problems. This article applies to version number 11.2.0.2 and above.

First, the knowledge involved in the problem is introduced.
1. Starting with the version number 11.2.0.2 Oracle cluster (GI) starts with its own time zone and some other configurations. These configurations are saved in the profile <gi_home>/crs/install/s_crsconfig_< node name >_env.txt.
Like what:
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, then we need to ensure that the GI time zone and operating system settings are consistent, and when the operating system time zone changes, GI time zone also needs to change. The basic steps to change the cluster time zone are (change <gi_home>/crs/install/s_crsconfig_< node name >_env.txt files, restart nodes).
2. The environment variable inherits the GI time zone when the database or Listner uses the Srvctl command or when the GI boot is started. You can also manually set the environment variables for the database and listener resources by using the following commands.
Srvctl setenv database-d <dbname>-T ' tz=< time zone > '
Srvctl setenv listener-l <listenername>-T ' tz=< time zone > '
3. The value returned by Sysdate does not depend on the time zone setting of the database, and Oracle simply fetches the system time back from the operating system (e.g., invoking the OS function Gettimeofday).

Therefore, changing the time zone of the database is not helpful for such a problem. The environment variable TZ used by the corresponding server process will affect the system time returned.

Next, we'll go through those processes when the client connects to the database via listener.

We'll explain it in a detailed example.

In this example, we use Sqlplus to create a database link. and collect truss information for listener process
1.client Connection Database
Sqlplus Scott/[email protected]
The 2.listner process received the appropriate link. and generates 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 variables are 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, suppose you did not collect truss output for Lisetner.

You can also obtain the environment variables for the corresponding process through the operating system commands. For example: PS eauwww <pid from above>. You can obtain commands for different platforms through the contents of MOS note 373303.1.

Other than that. The above database is started by the GI agent, assuming that the database is manually initiated (such as the startup command), then. The output will be different. Of course, Pmon will also register its environment variables to the corresponding service when the database service to listener is registered.

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 how to view time zone settings for different platforms, see 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. Assume that the setting is consistent with the OS,GI setting.

4. In addition, the environment variables Libpath or Ld_library_path of the server process will also have an impact on Oracle's access to operating system functions. And the GI Agent process (applies to version number 11.2.0.3 and above version number) when starting the resource (for example, database Resource) will be the initiative to the process of the following environment variables empty
Ld_library_path, Shlib_path (HP-UX), ld_libpath_path_64 (Solaris), LIBPATH (AIX)
So, assuming that your database was started with the SRVCTL command, you need to verify that the environment variables above are set correctly.
For 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.

For example: Srvctl getenv database-d <db_name>
In addition, when diagnosing such a problem. Need to collect the following information.
1. <gi_home>/crs/install/s_crsconfig_< node name >_env.txt file
2. Operating system time zone settings (cat/etc/sysconfig/clock) and environment variable TZ settings. and the environment variables of the Pmon process.
3. Environment variables for database and listener resources
For example: Srvctl getenv database-d <db_name>
Srvctl getenv Listener-l < Listener Name>
4. Assuming that the above information is not a problem, you will need to collect the truss (or strace) output of the listener process to find the problematic setting environment variable.
5. Assuming that the information in 1-4 is still unable to find a solution to the problem, gather the client and server-side sqlnet trace to confirm whether there is any "alter session set ..." command that changes the time zone or related variables of the session.
Clientsqlnet Trace: Set the following parameters into 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 of the Sqlnet.ora file
Trace_ Level_server=16
Trace_file_server=server
Trace_directory_server=/tmp ==> Ensure that the path exists
Trace_timestamp_ Server=on

I hope that the above interpretation of the diagnostic category seems to be beneficial.

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

How to diagnose the time error returned by the RAC Environment Sysdate