How to diagnose the error time returned by sysdate in rac, racsysdate

How to diagnose the error time returned by sysdate in rac, racsysdate
Recently, some rac environments have been used to access sysdate and return the error time. This problem often occurs when the database link is created through Listener, and, in most cases, it is related to the time zone settings. In this article, we will explain how to diagnose this problem. This article applies to version 11.2.0.2 and later.

First, introduce the knowledge involved in the problem.
1. oracle cluster (GI) has its own time zone and some other configurations since version 11.2.0.2, these configurations are saved in the configuration file <gi_home>/crs/install/s_crsconfig _ <node name> _env.txt.
For example:
TZ = Asia/Shanghai
NLS_LANG = AMERICAN_AMERICA.AL32UTF8
TNS_ADMIN =
ORACLE_BASE =
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 is consistent with the operating system settings. When the operating system time zone changes, the GI time zone also needs to be changed. The basic steps for modifying the cluster time zone are (modify the <gi_home>/crs/install/s_crsconfig _ <node name> _env.txt file and restart the node ).
2. When the database or listner uses the srvctl command or is started with GI, the environment variable inherits the GI time zone. You can also use the following command to manually set the environment variables of the database and listener resources.
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 settings of the database. oracle simply retrieves the system time from the operating system and returns the result (for example, calling the OS function gettimeofday ). Therefore, modifying the time zone of the database is not helpful for this problem. The corresponding server process uses the environment variable TZ to affect the returned system time.

Next, let's briefly introduce the processes that the client will go through when connecting to the database through listener. We will explain it through a specific example. In this example, we use sqlplus to create a database link and collect truss information for the listener process.
1. Connect the client to the database
Sqlplus scott/tiger @ test
2. The listner process receives the corresponding 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 variable for server process.
483742: execve (0x0fffffffff2660, 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 specified.

We can see that the environment variable TZ value is bound to the server process when the server process is created. Of course, if you do not collect truss output for the lisetner. You can also obtain the environment variables of the corresponding process through operating system commands, such as ps eauwww <pid from above>. You can obtain commands of different platforms through the content in MOS note 373303.1. In addition, the above databases are started through GI agent. If the database is manually started (for example, startup command), the output will be different. Of course, when pmon registers the Database service to listener, it also registers its environment variables to the corresponding service.

Therefore, when diagnosing sysdate errors in the RAC environment, we need to check the following information.
1. Set the time zone at the operating system level, and ensure that the operating system command date returns the correct time. For details about how to view the time zone settings of different platforms, see note 1209444.1.
2. Check that the variable TZ In the GI configuration file <gi_home>/crs/install/s_crsconfig _ <node name> _env.txt is consistent with that in the operating system.
3. Check whether the TZ variable is set at the database or listener resource level. If it is set, whether it is consistent with OS and GI.
4. In addition, the environment variable LIBPATH or LD_LIBRARY_PATH of server process will also affect oracle's access to operating system functions. In addition, the GI agent process (applicable to versions 11.2.0.3 and later) automatically clears the following environment variables of the process when starting resources (such as database resources ).
LD_LIBRARY_PATH, SHLIB_PATH (HP-UX), LD_LIBPATH_PATH_64 (Solaris), LIBPATH (AIX)
Therefore, if your database is started using the srvctl command, make sure that the preceding environment variables are set correctly.
Example: srvctl setenv database-d <db_name>-t 'libpath = <gi_home/lib>'
Note: The preceding commands may be different for different Unix platforms.
Therefore, we also need to confirm whether the LIBPATH or LD_LIBRARY_PATH variable of the database resource is set.
Example: srvctl getenv database-d <db_name>
In addition, you need to collect the following information when diagnosing such problems.
1. <gi_home>/crs/install/s_crsconfig _ <node name> _env.txt File
2. Set the Operating System Time Zone (cat/etc/sysconfig/clock) and the Environment Variable TZ. And the environment variables of the pmon process.
3. Environment Variables of database and listener Resources
Example: srvctl getenv database-d <db_name>
Srvctl getenv listener-l <listener name>
4. If the above information is correct, 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 still cannot find the cause of the problem, collect sqlnet trace on the client and server to check whether there is any 'alter session set... 'command modifies the time zone of the session or related variables.
Client sqlnet trace: set the following parameters to the sqlnet. ora file of the client.
Trace_level_client = 16
Trace_directory_client = c: \ tmp => make sure 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 => make sure the path exists.
Trace_timestamp_server = ON

I hope the above explanations will help you diagnose similar problems.