谈谈主机修改时区对Oracle的影响

Oracle在启动实例时，由pmon进程将读取操作系统相关环境（如系统时区）进内存区域，并在该实例的生命周期内一直保存。
监听启动时，首先会读取操作系统系统时区，但如果数据库监听采用动态注册，那pmon进程会将数据库系统时区信息动态注册至监听。
所以，当操作系统时区发生更改，如果通过监听连接的业务，会读取监听中的时区，所以仍将采用更改前的时区，这将导致数据库时间和操作系统时间不一致，此时进行数据插入，数据将采用监听的时区进行数据插入。
所以为了使得数据库时间和操作系统时间一致性，Oracle官方推荐当操作系统更改时区之后，将数据库进行重启，由pmon进程将修改后的新时区，重新注册至监听。但是如果数据库是7*24小时环境，重启数据库需要付出相当大的代价。那能不能不重启数据库就能达到数据库时间和操作系统时间一致的状态呢？
通过以上的讨论，我们可以得出以下结论：
1、如果业务程序不通过监听连接至数据库，那么数据库和主机时间应当一致。
2、如果监听是静态注册，Pmon进程不动态注册相关信息至监听器里，那么将监听瞬间重启之后，监听将读取修改后的时区，这样通过监听连接的业务程序，也将读取修改后的时区。
但是问题又来了，如果数据库监听端口处于非默认端口（即1521端口），那么只要不设置local_listener，那将不会进行动态注册。那如果是默认监听端口呢？
这里有个小技巧只要将local_listener设为其他端口即可
alter system set local_listener="(address=(protocol=tcp)(host=172.16.4.163)(port=1531))";
如果操作系统时区不修改，我们可以通过修改监听的时区，达到修改时区的目的，即只要修改listsner.ora，增加ENVS='TZ=CST6CDT。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mcstar)
(ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1)
(SID_NAME = mcstar)
#(ENVS='TZ=CST6CDT')
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.163)(PORT = 1521))
)
)
以下为一个客户修改了操作系统时区，导致数据库时间和操作系统时间不一致的解决过程：
可以看到在主机上连接数据库，即不通过监听连接数据库时，系统时间和数据库时间处于一致状态
SQL> conn agent/***
Connected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2011-05-20 15:23:50
但通过监听连接，再显示数据库时间，发现相差14个小时
$ sqlplus "agent/***@zjdw"

SQL*Plus: Release 9.2.0.8.0 - Production on Fri May 20 15:25:34 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

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

TO_CHAR(SYSDATE,'YY
-------------------
2011-05-20 01:25:39
查看监听状态，可以发现监听已经运行178天，且默认监听端口号为1521，但并未出现动态注册
$ lsnrctl status

LSNRCTL for HPUX: Version 9.2.0.8.0 - Production on 20-MAY-2011 15:26:00

Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.3.8)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.8.0 - Production
Start Date 22-NOV-2010 12:42:41
Uptime 178 days 11 hr. 43 min. 18 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oradata/ora9208/product/db_1/network/admin/listener.ora
Listener Log File /oradata/ora9208/product/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***.***.***.***)(PORT=1521)))
Services Summary...
Service "zjdw" has 1 instance(s).
Instance "zjdw", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

进一步查看Oracle参数，local_listener参数并未见异常，于是再次检查alert日志，可以看到listener.ora地址配置错误，导致pmon注册监听出错，于是也就好理解了为什么监听长期处于静态注册
Mon Nov 22 12:40:57 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.8.0.
System parameters with non-default values:
processes = 1000
timed_statistics = TRUE
shared_pool_size = 1056964608
sga_max_size = 8398007384
。。。。。。
PMON started with pid=2, OS id=14867
Mon Nov 22 12:41:01 2010
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=geosoft-)(PORT=1521))'

既然监听处于静态注册状态，pmon不会将保留在内存区域里的老时区动态注册至监听中，所以只要将监听重启，让监听重新获取新主机时区即可。
可以看到重启监听之后，再次通过监听连接数据库，数据时间已经恢复正常。
$ sqlplus "agent/***@zjdw"

SQL*Plus: Release 9.2.0.8.0 - Production on Fri May 20 15:27:26 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

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

TO_CHAR(SYSDATE,'YY
-------------------
2011-05-20 15:27:32

这时又引申出另外一个问题，操作系统时区修改之后，应不应该修改Oracle时区？
显而易见，如果数据库列存储方式并没有采用timezone存储（最常用的有TIMESTAMP，TIMESTAMP WITH TIME ZONE，IMESTAMP WITH LOCAL TIME ZONE），操作系统时区修改显然不用修改数据库时区。
数据库的时区，可以用查看database_properties视图获得，可以看到目前数据库时区为+0:00，即默认和主机时区一致。
SQL> select * from database_properties where property_name='DBTIMEZONE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- ------------------------------
DBTIMEZONE +0:00 DB time zone
可以用以下命令修改数据库时区，重启数据库才能生效
ALTER DATABASE SET TIME_ZONE = '+10:00';
需要注意的是，修改数据库时区仅适用于数据库没有TIMESTAMP WITH LOCAL TIME ZONE字段时才生效。且不会修改已存储在数据库中的时区列，仅对未来数据生效。

Oracle除了数据库时区，还提供了会话级时区，查看会话级时区时将忽略数据库级时区，默认保持和操作时区一致。
SQL> SELECT SESSIONTIMEZONE FROM dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
可以采用如下命令，修改之后会话级别实时生效，
SQL> alter session set time_zone='+10:00';

Session altered.

SQL> SELECT SESSIONTIMEZONE FROM dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+10:00

如前所述Timestamp With local Time Zone 在客户端取数据的时候，会自动转为客户端的时区时间，所以修改会话级时区将影响Timestamp With local Time Zone的取值。
SQL> alter session set time_zone='+10:00';

Session altered.

SQL> select TIMESTP_LTZ from zhoul.TIMESTAMP_TEST;

TIMESTP_LTZ
---------------------------------------------------------------------------
23-MAY-11 04.47.18.000 PM

SQL> alter session set time_zone='+8:00';

Session altered.

SQL> select TIMESTP_LTZ from zhoul.TIMESTAMP_TEST;

TIMESTP_LTZ
---------------------------------------------------------------------------
23-MAY-11 02.47.18.000 PM

当数据库已有TIMESTAMP WITH LOCAL TIME ZONE字段时，将出现以下错误。
SQL> ALTER DATABASE SET TIME_ZONE = '+10:00';
ALTER DATABASE SET TIME_ZONE = '+10:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
当出现这种错误时，可以通过以下脚本查看哪些列是TIMESTAMP WITH LOCAL TIME ZONE
SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
2 from sys.obj$ o, sys.col$ c, sys.user$ u
3 where c.type# = 231
4 and o.obj# = c.obj#
5 and u.user# = o.owner#;
TSLTZCOLUMN
--------------------------------------------------------------------------------
ZHOUL.BIN$o+3YOK3rqpLgQBCsowRAmg==$0.TS_LTZ

那么TIMESTAMP WITH LOCAL TIME ZONE是什么玩意呢？
Timestamp With local Time Zone类型和Timestamp with time zone类似。内部代码是231。和TimpStamp With Time Zone不同的是，这种数据类型会自动把时间转换成服务器的时区时间进行存储。在客户端取数据的时候，会自动转为客户端的时区时间。
TIMESTAMP WITH TIME ZONE类型数据会存储客户端的时区信息，如果指定时区信息（如timestamp '2010-02-01 09:00:00 +09:00'），则按指定时区存储，如果不指定时区（如timestamp '2010-02-01 09:00:00'）默认采用会话时区存储。
TIMESTAMP WITH LOCAL TIME ZONE类型数据不会存储客户单的时区信息，它根据数据库时区对客户端发来的时间进行转换，基于统一的数据库时区存储时间信息，如果用户没有指定时区信息同TIMESTAMP WITH TIME ZONE一样默认采用会话时区。当用户查看该类型数据时，服务器根据会话所属时区对存储的时间数据进行转换，不同时区的会话将返回不同的时间数据。