AIX 时区

cole

修改系统时区
# cat /etc/environment
TZ=Asia/Shanghai
重启系统


SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。

windows 客户端登录
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-12-23 22:32:08
比当前系统慢了14个小时

修改为GMT+8 格式
# echo $TZ
GMT+8
# cat /etc/environment
TZ=GMT+8

windows 客户端登录
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-12-24 14:33:27
比当前系统快了2个小时

把dbtimezone  调为 0时区或 +8 时区 ，时间显示还是如上错误
windows端的时区确认没问题，查询Linux下的库时间是正确的。

AIX 6.1  Oracle11g R2 RAC

问题出在哪？ 请指教

cole

在oracle 的crs_home
11.2.0.3/grid/oracore/zoneinfo/readme.txt 有这样一段

Timezones added:
GMT-14
America/Rio_Branco (synonym for America/Porto_Acre)
Asia/Dhaka         (synonym for Asia/Dacca)
Asia/Chongqing     (synonym for Asia/Chungking)
Asia/Macau         (synonym for Asia/Macao)
Asia/Makassar      (synonym for Asia/Ujung_Pandang)
有点莫名其妙

cole

$ echo $TZ
BEIST+8
$
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> select dbtimezone from dual;

DBTIME
------
+00:00
SQL> select sysdate from dual;

SYSDATE
-------------------
2012-12-26 11:58:39

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options 断开

C:\Users\abc>

改为BEIST+8 Oracle11g r2  时间就正确，其它字符识别出来的时区是错的。
晕倒BEIST+8 、BEIST-8 到底哪个是北京时区

cole

原来是bug   DOC ID 1476294.1
[size=130%]11.2.0.3 Wrong Sysdate Returned for Connections Through Listener if Database is Started With srvctl [ID 1476294.1]
In this Document



Symptoms


Cause


Solution


References

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

Applies to:
Oracle Server - Enterprise Edition - Version 11.2.0.3 and later
IBM AIX on POWER Systems (64-bit)
Symptoms

11.2.0.3 Grid Infrastructure with RAC databases, if the database is started with srvctl, connections through listener shows wrong sysdate:

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi') from dual;

TO_CHAR(SYSDATE,
----------------
2012/08/01 11:00

However, when database is started from sqlplus, or when connecting via beq protocol without going through listener, the issue does not happen.

Cause

Due to bug 14063047

Duplicates bug 14108956 bug 14147958

Solution

At the time of this writing, bug 14063047 is still being worked by Development.

The workaround is to set environment variable LIBPATH and restart. To set environment variable:

$ srvctl setenv database -d <dbname> -t 'LIBPATH=<DB_HOME>/lib'



To check environment varilables for a running process:

ps eauwww <pid>




References
BUG:14147958 - TIME IS NOT CORRECT WHEN STARTING WITH SRVCTL
NOTE:1209444.1 - How To Change Timezone for 11gR2 Grid Infrastructure
BUG:14063047 - SEEING A DIFFERENT SYSTIME THROUGH THE LISTENER DEPENDING ON HOW LIBPATH IS SE
NOTE:1424739.1 - ORA-00600 [Unable To Load XDB Library] On AIX Even If LIBPATH And LD_LIBRARY_PATH are set
BUG:14108956 - WRONG TIME IN DATABASE WHEN STARTING WITH SRVCTL
NOTE:1390015.1 - Incorrect SYSDATE shown when connected via Listener in RAC
NOTE:227334.1 - Dates & Calendars - Frequently Asked Questions



不是时区的问题，是bug，通过这样来启动RAC db就没问题，如果通过 crs_start xxx , client 查询到的时间就是错误的。
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 26 17:44:33 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 9219670016 bytes
Fixed Size                  2229144 bytes
Variable Size            1476398184 bytes
Database Buffers         7717519360 bytes
Redo Buffers               23523328 bytes
Database mounted.
Database opened.
SQL>


client 查询
SQL>  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-12-26 17:45:07
SQL>

靠 坑爹，这问题整了 两天

lymaya

那怎么解决呢？请告诉一下具体的。谢谢