开发人员反映客户测试数据库环境取到的时间不对,经过多次查看后发现sysdate返回值一直不变
操作系统版本:
Linux CentOS 5.2
数据库版本
Oracle 11.2.0.2
问题描述和诊断过程
经过和客户沟通中得知客户DBA为了项目测试,需要把数据库的时间改掉,但是这个操作系统上面安装有多个实例,不能修改操作系统时间,所以使用下面方法修改了数据库的时间
ALTER SYSTEM SET
FIXED_DATE = 'DD-MON-YY';
但是客户不知道数据库没有自己的时间,也没有办法随便修改时间,设置这个值后sysdate返回值反而就变成了固定值
解决方案
解决方案比较简单,重置FIXED_DATE的值为NONE就可以了
ALTER SYSTEM SET FIXED_DATE=NONE;
总结
Sysdate只是简单调用了操作系统的时间函数(linux是调用"gettimeofday"方法),Oracle数据库可以改变时区,也就是说数据库可以和操作系统的时区不一样(注意"TZ"环境变量要和数据库时区相同,不然通过listener连过来的时区会和数据不一致),但是它没有自己的时间,数据库的时间没有办法通过参数或命令进行修改,如果需要修改时间只能修改操作系统的时间
关于时间和时区Oracle官方解释见metalink[ID 227334.1]
下面为[ID 227334.1]中提供的两个不同的数据库实例使用不同的时区的修改方法
How
do I set up 2 databases with a different SYSDATE / SYSTIMESTAMP time on the
same server?
Note: This is not possible on Microsoft Windows
systems ( from windows NT up to Windows 8) , as far as Oracle is aware
the timezone setting in Windows is "global" for all processes on the
machine and it is not possible to start on windows systems 2 processes who
recieve from the OS a time in a different timezone. For a final statement on
this please contact Microsoft.
The
SYSDATE function simply performs a system-call to the Operating System to get
the time (a call). Therefore you can
not influence the sysdate values by changing database parameters.However, on Unix you can use the OS "TZ" environment
variable to alter the time that the OS will pass on to Oracle (also see above).
Therefore you can use this to influence the time that the OS passes to Oracle
as the "current time", and therefore you can set up multiple
databases on the same machine returning different times in the sysdate
function.
To do this you have 2 options:
1) When NOT using automatic listener
registration simply start a separate listener for each database, and set the TZ
appropriate for the database before you start the corresponding listener. It is
also a good idea to set the "correct" TZ in the Unix environment of
the user who (re)starts the database
If you use port 1521 for one of the listeners, then you should also disable
Automatic Service Registration by setting the following parameter to the
init.ora file:
LOCAL_LISTENER=dummyor , when using SPFILE, doSQL>ALTER SYSTEM SET LOCAL_LISTENER=dummy
SCOPE=SPFILE;
For more information on the background of this
please see:
Note:301420.1Why does sysdate have the Wrong Time Stamp
when Connecting via the ListenerNote:399448.1How To setup TNS listener to Show More
Than one Timezone
2) Alternatively if the only listener
registration is automatic listener registration then this can be used to have 2
databases with on listener and still have different timezones:
* start the listener on port 1521 WITHOUT any database configured in the
listener.ora.
* start each database with the TZ variable you want to use.
PMON will register the database with the
listener and the use TZ will be the one used to start the database, not
listener. If the listener is not running on port 1521 then you need to specify
in EACH database the LOCAL_LISTENER to enable automatic listener registration.
This is also usefull when using MTS.