The local time of Oracle11gRAC is different from that of listener.

After the customer's production database server is restarted, the local time is different from the listener connection time, which is 13 hours slower than our actual time.

After the customer's production database server is restarted, the local time is different from the listener connection time, which is 13 hours slower than our actual time.

Background

After the customer's production database server is restarted, the local time is different from the listener connection time, which is 13 hours slower than our actual time.

Operating system version: AIX 6.1

Database Version

Oracle 11.2.0.2 RAC

Problem description and diagnosis process

1. the time difference between local direct login and local query by listener is 13 hours.

Bash-2.05b $ sqlplus "/as sysdba"

SQL> select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yyyy-MM-DDHH24: MI: ss'

--------------------------------------

2012-03-27 15:17:52

Bash-2.05b $ sqlplus test/testaa @ spprod

SQL> selectto_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yyyy-MM-DDHH24: MI: ss'

--------------------------------------

2012-03-27 02:18:50

SQL>

2. according to the above inference, check the operating system time zone and the database time zone and find that the time zone is the same, both are positive 8, indicating that the operating system time zone or the database settings are correct, at this time, it may be caused by a listener error or a sysdate remote value problem.

Bash-2.05b $ echo $ TZ

Asia/Shanghai

Bash-2.05b $

View database Time Zone

SQL> select dbtimezone from dual;

DBTIMEZONE

------------

+ 08:00

3. according to the above inference, there may be a problem with listener or a problem with sysdate values. Let's take a look at the principle of sysdate values. According to metalink [ID 227334.1], SYSDATE and SYSTIMESTAMP are just simple calls to the operating system to get the time. That is to say, the value of sysdate comes from the operating system, and oracle does not process the value of sysdate, so there is no problem with sysdate.

4. sysdate troubleshooting, that is, the listener problem. Continue to read and find that the operating system time is inconsistent with the listener time mentioned in this document, this may occur because the time zone at database startup is inconsistent with the current operating system time. You need to restart the database and listener, in another document, [ID 301420.1] is also caused by this situation. The solution is to restart the database and listener.

5. Because it is a RAC environment, we plan to restart one node in one node, so we will restart the instances and CRS of the two nodes one by one.

Stop Node 2 instance

Bash-2.05b $ srvctl stop instance-d spprod-I spprod2

Switch to the root user and stop CRS

Bash-2.05b #/sporacle/11202/grid/bin/crsctl stop crs

The above crs is started and then the instance is started. Next we repeat the above operation and restart the first node.

Stop instance 1

Bash-2.05b $ srvctl stop instance-d spprod-I spprod1

Switch to the root user to stop CRS

Bash-2.05b #/sporacle/11202/grid/bin/crsctl stop crs

6. After the restart, I found that it was the same as the original one, indicating that it was not the problem. Is it also related to the time zone for managing the ASM instance under the grid user? The instance time zone under the two node grids is '+ 100'. Therefore, the company queries the other 11g rac environment of the company and finds that the same is true for other environments. However, this problem can be basically ruled out in other environments.

7. the problem has not been solved. I can only continue To check it. Finally, I found the following document: How To Change Timezone for 11gR2Grid Infrastructure [ID 1209444.1]. the document above describes how to directly read the operating system time zone from oracle 11.2.0.1 grid and put the time zone in oracle 11.2.0.2 grid at $ GRID_HOME/crs/install/s_crsconfig _ In the _ env.txt file, you need to modify the file if you need to adjust the time.

8. View s_crsconfig _ according to the document _ _Env.txt finds that the time zone is TZ = CST6CDT, which is just 13 hours different from our time zone, so modify TZ = Asia/Shanghai

Bash-2.05b # more s_crsconfig_spdb1_env.txt

### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.

### For example, a new charset can be configured by settingNLS_LANG = JAPANESE_JAPAN.UTF8

### Do not modify this file doesn't change NLS_LANG, or under thedirection of Oracle Support Services

TZ = CST6CDT

NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1

RT_GRQ = ON

TNS_ADMIN =

ORACLE_BASE =

Modify the TZ = Asia/Shanghai of s_crsconfig_spdb1_env.txt (do not forget to modify both nodes)

9. After modification, restart the database and CRS to solve the problem.

10. Summary

It is estimated that the operating system time zone is CST6CDT when oracleCRS is installed. Someone has modified the operating system time zone and database time zone after the installation.