oracle数据库时,Oracle数据库夏令时问题解答

夏天就快到了，DBA可能会突然遇到夏季“灵异事件”——夏令时。突然发现数据库时间快了一个小时。

数据库环境为：

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /oracle/app/oracle/product/10.2/db

System name:    AIX

Node name:      RESDB2

Release:        1

Version:        6

Machine:        00CD21964C00

Instance name: nrms2

这是时差截图

从时区上可以看出，session的时区并不是中国的时区，CDT美国中部的夏令时时间，所以到了夏时制时，系统时间会发生改变。而数据库的时区依赖于操作系统，所以数据库的时间也会发生改变。对于中国本土企业，如果不希望运行在夏令时下，则需要提前修改时区设置，避免出现时区问题。Linux上的时区研究，请参考：在Linux操作系统上，数据库的时区和以下几个因素有关：1. /etc/localtime这个文件记录的是系统的时区，缺省的数据库由此获得时区信息这个文件是二进制文件，修改该文件的方法是拷贝/usr/share/zoneinfo下的相应时区文件覆盖localtime文件。/etc/sysconfig/clock与localtime配置，定义的是时区名称区域信息等。2./usr/share/zoneinfo/该目录存储所有合法的时区信息,如果会话设置的时区和系统时区不一致，则由此读取时区信息。3.$ORACLE_HOME/oracore/zoneinfo这里存储的文件记录数据库的合法时区，和操作系统提供的信息会进行对比。

-rw-rw-r--. 1 oracle oinstall   1407 Jun  1  2009 readme.txt

-rw-rw-r--. 1 oracle oinstall 408315 Apr 14  2010 timezlrg.dat

-rw-rw-r--. 1 oracle oinstall 160733 Apr 14  2010 timezone.dat

4.Linux上的时区信息可以通过tzselect命令查看，使用timeconfig进行配置修改5.会话级别的时区设置可以如下进行TZ='Asia/Shanghai';export TZ

6.hwclock -w保存时区设置使之重启系统后仍生效关于Oracle的时区选项与时区关系，可以通过strace跟踪进程，清晰的获得整个流程：分析整个过程中的文件读取：

open("/etc/ld.so.preload", O_RDONLY)    = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/tls/x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/lib/tls/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/lib/x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/lib/libsqlplus.so", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/etc/ld.so.cache", O_RDONLY)      = 3

open("/lib64/libdl.so.2", O_RDONLY)     = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/lib64/libm.so.6", O_RDONLY)      = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/lib64/libpthread.so.0", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/lib64/libnsl.so.1", O_RDONLY)    = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/lib64/libc.so.6", O_RDONLY)      = 3

open("/u01/app/oracle/product/10.2.0/db_1/lib/libsqlplusic.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/lib/libociicus.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/lib/libociei.so", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx1boot.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx00001.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx20354.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx10001.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx40011.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx20001.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/nls/data/lx40001.nlb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/sqlplus/mesg/sp1us.msb", O_RDONLY) = 3

open("/u01/app/oracle/product/10.2.0/db_1/sqlplus/mesg/sp2us.msb", O_RDONLY) = 4

open("/u01/app/oracle/product/10.2.0/db_1/sqlplus/mesg/cpyus.msb", O_RDONLY) = 5

open("/usr/share/zoneinfo/Asia/Shanghai", O_RDONLY) = 6

open("/u01/app/oracle/product/10.2.0/db_1/oracore/zoneinfo/timezlrg.dat", O_RDONLY) = 6

open("/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/ocius.msb", O_RDONLY) = 6

可以看到Oracle读取的操作系统时区文件：/usr/share/zoneinfo/Asia/Shanghai这是跟进环境变量的设置读取的，数据库的时区文件则是：/u01/app/oracle/product/10.2.0/db_1/oracore/zoneinfo/timezlrg.dat这整个验证过程和Oracle的字符集完全相同，很有意思。

以上就是《Oracle数据库夏令时问题解答》的全部内容。