记一次通过监听连接数据库显示SYSDATE异常解决方案

               前两天遇到一件奇怪的事情，通过CRT登陆主机查看OS系统和数据库sysdate是一致的，但是通过PL/SQL DEVELOPER 查询数据库时间却比OS系统时间晚了近16个小时，以下为分析过程：

1、通过Toad工具连接数据库查询时间

    

2015/7/25 02:54:24

2、查看OS时间

grid@orasuse1a:/u01/product/11.2/grid/log/orasuse1a/cssd> date
Sat Jul 25 17:55:57 CST 2015

----比较两个时间相差15个小时

3、查看监听状态：

grid@orasuse1a:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2015 18:14:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                25-JUL-2015 02:50:46
Uptime                    0 days 0 hr. 0 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/product/11.2/grid/network/admin/listener.ora
Listener Log File         /u01/product/grid/diag/tnslsnr/orasuse1a/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "sdscm" has 1 instance(s).
  Instance "sdscm1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

grid@orasuse1a:~> date
Sat Jul 25 18:15:24 CST 2015

通过红色部分可以看出异常所在，正是监听时间异常，导致客户端通过监听连接数据库的事件异常，那么是哪里或那个参数控制了监听的启动

时间呢？？带着这个问题查询MOS (How To Change Timezone for 11gR2 Grid Infrastructure (Doc ID 1209444.1))

Grid Time Zone Setting

Once OS default timezone is changed, make sure:

1. For 11.2.0.1, shell environment variable TZ is set correctly for grid user and root.
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct time zone.

The TZ must be exactly as what the OS supports, i.e.:

grep TZ s_crsconfig_<nodename>_env.txt 
TZ=US/Pacific

4、查看本机该参数文件内容

grid@orasuse1a:/u01/product/11.2/grid/crs/install> cat s_crsconfig_orasuse1a_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 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=US/Pacific     ----此处显示US时区，时间异常
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
TNS_ADMIN=
ORACLE_BASE=

5、修改该参数文件

grid@orasuse1a:/u01/product/11.2/grid/crs/install> cat s_crsconfig_orasuse1a_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 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=Asia/Shanghai   ----修改此处为亚洲时区
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
TNS_ADMIN=
ORACLE_BASE=

6、重启CRS服务

orasuse1a:/u01/product/11.2/grid/bin # ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.crsd' on 'orasuse1a'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.sdscm.db' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'orasuse1a'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.orasuse1a.vip' on 'orasuse1a'
CRS-2677: Stop of 'ora.orasuse1a.vip' on 'orasuse1a' succeeded
CRS-2672: Attempting to start 'ora.orasuse1a.vip' on 'orasuse1b'
CRS-2677: Stop of 'ora.sdscm.db' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'orasuse1a'
CRS-2677: Stop of 'ora.registry.acfs' on 'orasuse1a' succeeded
CRS-2676: Start of 'ora.orasuse1a.vip' on 'orasuse1b' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'orasuse1a'
CRS-2677: Stop of 'ora.asm' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'orasuse1a'
CRS-2677: Stop of 'ora.ons' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'orasuse1a'
CRS-2677: Stop of 'ora.net1.network' on 'orasuse1a' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'orasuse1a' has completed
CRS-2677: Stop of 'ora.crsd' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.ctssd' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.evmd' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.asm' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'orasuse1a'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'orasuse1a'
CRS-2677: Stop of 'ora.evmd' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.crf' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'orasuse1a' succeeded
CRS-2677: Stop of 'ora.asm' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'orasuse1a'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'orasuse1a'
CRS-2677: Stop of 'ora.cssd' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'orasuse1a'
CRS-2677: Stop of 'ora.gipcd' on 'orasuse1a' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'orasuse1a'
CRS-2677: Stop of 'ora.gpnpd' on 'orasuse1a' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'orasuse1a' has completed
CRS-4133: Oracle High Availability Services has been stopped.

orasuse1a:/u01/product/11.2/grid/bin # ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

7 、查看监听启动时间

grid@orasuse1a:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2015 18:14:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                25-JUL-2015 18:13:46
Uptime                    0 days 0 hr. 0 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/product/11.2/grid/network/admin/listener.ora
Listener Log File         /u01/product/grid/diag/tnslsnr/orasuse1a/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "sdscm" has 1 instance(s).
  Instance "sdscm1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

8、查询OS系统时间

grid@orasuse1a:~> date
Sat Jul 25 18:15:24 CST 2015   ----此时两个时间一致。

9、通过客户端查询数据库时间

   

2015/7/25 18:16:24

---时间也正常

至此，时间异常的问题解决