客户端访问数据库时间快了1小时，如何解决？

davidxu322

oracle环境：aix6.1 oracle RAC 11g 11.2.0.1
现象：从客户端访问数据库获取系统时间比正常时间快1小时，在服务器本地获取时间正常
之前做过的操作：因在安装aix的时候开启了夏令时，在3月12号发现系统时间比北京时间快了一小时，将夏令时关闭，重启了服务器，出现上述现象

1. date
   
2. Thu Mar 14 09:01:32 GMT+08:00 2013
   
3. 
   
4. echo $TZ
   
5. Asia/Shanghai
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> select dbtimezone from dual;
   
10. DBTIME
    
11. ------
    
12. +00:00
    
13. 
    
14. SQL> select sysdate from dual;
    
15. SYSDATE
    
16. -------------------
    
17. 2013-03-14 09:04:53
    
18. 
    
19. SQL> host date
    
20. Thu Mar 14 09:04:56 GMT+08:00 2013
    
21. 
    
22. 客户端
    
23. SQL> conn system/*****@rac
    
24. 已连接。
    
25. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    
26. 
    
27. TO_CHAR(SYSDATE,'YY
    
28. -------------------
    
29. 2013-03-14 10:06:41
    

复制代码

How To Change Timezone for 11gR2 Grid Infrastructure [ID 1209444.1]，文档上面说在oracle 11.2.0.1 grid直接读取操作系统时区，在oracle 11.2.0.2 grid的时区放在$GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt这个文件中
more s_crsconfig_dbs01_env.txt
### This file can be used to modify the NLS_LANG environment variable, which determines the charset to b
e used for messages.
### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

TZ=BEIST-8BEIDT
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
RT_GRQ=ON
TNS_ADMIN=
ORACLE_BASE=

Ling.QIu

那你现在的问题是？

davidxu322

解决客户端访问数据库时间快了1小时的问题

Ling.QIu

系统时间先改 随后改GI 不行重装GI  

问题还有数据库里的数据 因为你是快1小时 所以 这1小时要么等掉 要么删数据

davidxu322

Ling.QIu 发表于 2013-3-14 10:34
系统时间先改 随后改GI 不行重装GI  

问题还有数据库里的数据 因为你是快1小时 所以 这1小时要么等掉 要么 ...

系统时间是正确的，GI根据文档How To Change Timezone for 11gR2 Grid Infrastructure [ID 1209444.1]
11.2.0.1是直接读取的操作系统的时间，试了修改s_crsconfig_spdb1_env.txt的TZ=Asia/Shanghai，重启RAC现象依旧，用linux客户端和windows客户端连接测试情况一样，不会运气这么好遇到bug吧

Maclean Liu(刘相兵

conn system/*****@rac

RAC 系统 ， 需要2个节点上的 date  TZ 信息

su - oracle

date
echo $TZ

2个节点上都需要


select dbtimezone from dual;
select sessiontimezone from dual;

select sysdate from dual;
select systimestamp from dual;


给出如上查询结果

cargoo

这是因为GI在启动DB时，使用的时区还是未改之前的时区。

通过下面方法修改启动DB的时区设定：
srvctl setenv database -d <db_name> -t "TZ=BEIST-8“

davidxu322

dbs01
DBS01:oracle:/home/oracle$date
Fri Mar 15 10:04:21 GMT+08:00 2013
DBS01:oracle:/home/oracle$echo $TZ
Asia/Shanghai

DBS02:oracle:/home/oracle$date
Fri Mar 15 10:04:36 GMT+08:00 2013
DBS02:oracle:/home/oracle$echo $TZ
Asia/Shanghai

服务器本地node1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl1

SQL> select dbtimezone from dual;
DBTIME
------
+08:00

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-15 10:07:12

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
15-MAR-13 10.07.24.299217 AM +08:00

node2
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl2

SQL> select dbtimezone from dual;
DBTIME
------
+08:00

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select sysdate from dual;
SYSDATE
-------------------
2013-03-15 10:08:32

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
15-MAR-13 10.08.41.418304 AM +08:00

客户端
SQL> host date
Fri Mar 15 10:11:07 CST 2013

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl1

SQL> select dbtimezone from dual;
DBTIME
------
+08:00

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-03-15 11:13:54

SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSTIMESTAM
-------------------
2013-03-15 11:14:33

cat /etc/sysconfig/clock
ZONE="Asia/Shanghai"

davidxu322

cargoo 发表于 2013-3-15 10:05
这是因为GI在启动DB时，使用的时区还是未改之前的时区。

通过下面方法修改启动DB的时区设定：

修改s_crsconfig_spdb1_env.txt的TZ=Asia/Shanghai之后已经重启了RAC，在修改服务器的时区后机器也重启了

Maclean Liu(刘相兵

不要用  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  格式

直接

select systimestamp from dual;



其次 你是否能换一台 主机安装一个客户端 并测试上述步骤

davidxu322

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl1

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
15-MAR-13 11.43.56.227298 AM +09:00

换了一台windows的客户端
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl2

SQL> select systimestamp from dual;
SYSTIMESTAMP
------------------------------------------------
15-3月 -13 11.41.17.143192 上午 +09:00

davidxu322

linux客户端本地时间
-bash-4.1$ date
Fri Mar 15 10:50:08 CST 2013

WINDOWS客户端
C:\Users\Administrator>time
当前时间: 10:50:19.42
时区UTC+8

Maclean Liu(刘相兵

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
15-MAR-13 11.43.56.227298 AM +09:00

时区变成 09了

davidxu322

已经看到问题了，dbtimezone，sessiontimezone都是设置的+8，怎么在客户端变成+9了，另外用current_timestamp显示的是+8，正常   
想不太明白，还请Maclean Liu指点，怎样才能把时区变正常

Stone

### This file can be used to modify the NLS_LANG environment variable, which determines the charset to b
e used for messages.
### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

TZ=BEIST-8BEIDT
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
RT_GRQ=ON
TNS_ADMIN=
ORACLE_BASE=

首先，根据Oracle的描述，建议不要手动修改除了NLS_LANG以外的其他参数，所以建议你修改回来到原来的值。
(Sorry, 补充一下，看了看MOS dos描述，这个是可以改的。我原来以为上面的文字是MOS里面的，原来是系统里面的。)

参考这个解决方案，很可能可以修复你的问题：

https://forums.oracle.com/forums/thread.jspa?threadID=957721

I tried everything which were suggested in this post as well as from other websites. Here is the scenario:

1. Installed oracle server on a computer in SGT.
2. 'SELECT TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FROM DUAL' from sqlplus command prompt returned the right time.
3. Changed the system timezone to IST and updated the time.
4. 'SELECT TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FROM DUAL' from sqlplus command prompt returned the right time but connecting from SQLDeveloper or Oracle JDBC and executing the statement gave time 2:30 hours ahead.
5. Tried selecting sessiontimezone and returned same from all.
6. Tried executing the following and output was "Asia/Kolkata"

connect / as sysdba
var s varchar2(4000);
exec dbms_system.get_env('TZ', :s);
print s;

7. SQL> select dbtimezone from dual;

DBTIME
+00:00

8. Modified /etc/sysconfig/clock as mentioned in [http://davidalejomarcos.wordpress.com/2011/02/25/sysdate-returns-wrong-time-time-with-timezone/]
9. Referring to AZ's above post, did a poweroff of the server and started the listener and service and Worked!!!

另外对于8. http://davidalejomarcos.wordpress.com/2011/02/25/sysdate-returns-wrong-time-time-with-timezone/

The Solution:

Quite simple, we have a file on the server called /etc/sysconfig/clock. This file configures the system clock to Universal or Local time.

1
2
3
4
5
[oracle@ssss ~]$ cat /etc/sysconfig/clock

ZONE="America/New_York"
UTC=true
ARC=false
Zone should be :

1
2
3
4
[oracle@sssssss ~]$ cat /etc/sysconfig/clock
ZONE="UTC"
UTC=true
ARC=false
So clients also uses UTC. After amending the value we were back to business.

There is another quick way to fix the problem, providing you do not have local_listener configured on your system as you can manipulate the timezone for your sessions using your listeners. Let me explain, if you connect from the server, you will probably not use the listener, so sysdate should be the same than your servers date and time.

Starting the listener with different timezone (for example, using TZ OS environment variable) you will be able to have two different sysdate results (one if you select from the server and a different one if you connect using the listener).

So, to fix your problem you will need to do the following:

1.- Configure TZ variable on your server (i.e. export TZ=”UTC”)

2.- Stop listener

3.- Start listener

Done.

If you have local_listener, you will need to stop/start your database with the right TZ environment variable to fix the issue.

As always, questions are welcome.

Stone

Stone 发表于 2013-3-15 12:07
首先，根据Oracle的描述，建议不要手动修改除了NLS_LANG以外的其他参数，所以建议你修改回来到原来的值。 ...

补充一下，另外可以参考下MOS docs进一步分析下原因，找下真凶, 看看有莫有效果 ：）

Incorrect SYSDATE shown when connected via Listener in RAC [ID 1390015.1]
How to Check the Environment Variables for an Oracle Process [ID 373303.1]

Good luck

Stone

试了修改s_crsconfig_spdb1_env.txt的TZ=Asia/Shanghai，重启RAC现象依旧

又细看了下，因为你的系统是AIX的，我估计修改的格式很可能不正确，试试下面这个方法：

http://hi.baidu.com/tempcc/item/ab252cfcb35109c30dd1c851

unix主机时间自动变化
aix主机时间慢了一小时
每年四月到八月，AIX会缺省使用夏时制，可以用echo $TZ看时区来判断，时区以DT结尾的就使用了夏时制。如果不想用，可以通过smit--system environment--change show date and time---change time zone using system defined values来改变，在弹出的USE DAYTIME SAVING对话框中选择No,选好相应的时区后重启机器既可。
  
采用夏令时的格式：
< TZ=1,/BEIST-8BEIDT,BEIST-8BEIDT/(BEIST-8BEIDT) Peoples Rep. China
(CUT +8)

不采用夏令时的格式
> TZ=BEIST-8
直接查看/etc/environment的内容就可以知道是否采用夏令时

Stone

cargoo 发表于 2013-3-15 10:05
这是因为GI在启动DB时，使用的时区还是未改之前的时区。

通过下面方法修改启动DB的时区设定：

在AIX上，TZ这么更改，感觉应该莫有问题，楼主可以试下，然后：

a) Restart the CRS
b) Start the database instance and listener via SRVCTL

Good luck

davidxu322

多谢Stone
我也感觉时区改的不对，准备晚上来试试
在网上找到将TZ=BEIST+8问题解决的案例
BEIST+8 、BEIST-8 到底哪个是北京时区？

Stone

davidxu322 发表于 2013-3-15 15:00
多谢Stone
我也感觉时区改的不对，准备晚上来试试
在网上找到将TZ=BEIST+8问题解决的案例

应该是BEIST-8吧，具体详细信息参考。当然这只是可能，最好参考上面帖子里的其他线索，详细查一篇，再决定具体采取什么样的方案 :)
另外我的直觉是最好OS和Grid设置的TZ一致，所以这两个地方你再瞅瞅，因为你上面echo $TZ 是Asia/Shanghai,所以修改的话，建议一致。
这只是参考啦 ~

http://my.safaribooksonline.com/book/-/0738436267/power7-enterprise-server-performance-considerations/287#X2ludGVybmFsX0J2ZGVwRmxhc2hSZWFkZXI/eG1saWQ9MDczODQzNjI2Ny8yODc=

How to know the time zone that is used in the current AIX environment
Check the output of the command (env|grep TZ). If the output format is similar to
“TZ=Asia/Shanghai”, it is an Olson time zone format. If the output format is similar to
“TZ=BEIST-8”, it is a POSIX time zone format.
Performance difference between Olson and POSIX time zones
Although time zone is an environmental variable, it provides a value and does not involve
other functions. But some local time-related subroutines, for example, localtime() and
gettimeofday(), use time zone values. Using other time zone values might cause these
functions’ response times to differ.
In AIX 6.1, when enabling the Olson time zone, the implementation of those subroutines
relies on the ICU library, and the arithmetic of the ICU library, which has been introduced by
the International Components for Unicode, is more complex than the implementation of the
POSIX time zone. Normally, the POSIX time zone value has better performance than the
Olson value.
The Olson time zone penalty might not be a concern in cases where the application looks up
the local time only one time or occasionally, but in cases where the local time-related
subroutines are going to be repeatedly called, or if an application is performance sensitive, it
is a much better option to continue using the POSIX time zone format in AIX 6.1.
In AIX 7.1, the implementation of the Olson time zone does not rely on the ICU library; it uses
native code. So, the performance gains improvements. The performance is similar between
the POSIX and Olson time zones in AIX 7.1.

Setting the POSIX time zone in AIX 6.1 or later
Refer to the following steps to set the POSIX time zone in AIX 6.1 or later:
1.Log in with the root user and edit the /etc/environment:
vi /etc/environment
2.Change the TZ environment variable to POSIX time zone format and save the file, for
example:
TZ=BEIST-8
3.Then, reboot the system.

Good luck

davidxu322

问题解决
Change the TZ environment  TZ=BEIST-8
s_crsconfig_<nodename>_env.txt  TZ=BEIST-8
alter database time_zone='+00:00';
reboot the system
看来是修改的格式不正确

谢谢 Stone ，Maclean Liu

Stone

davidxu322 发表于 2013-3-15 18:27
问题解决
Change the TZ environment  TZ=BEIST-8
s_crsconfig__env.txt  TZ=BEIST-8

非常啊高兴看到问题解决啦!very nice :)