sysdate返回值不变的问题处理

背景

 

开发人员反映客户测试数据库环境取到的时间不对，经过多次查看后发现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 "gettimeofday" 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=dummy
or , when using SPFILE, do
SQL>ALTER SYSTEM SET LOCAL_LISTENER=dummy SCOPE=SPFILE;

For more information on the background of this please see:

Note:301420.1 Why does sysdate have the Wrong Time Stamp when Connecting via the Listener Note:399448.1 How 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.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21605631/viewspace-747222/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21605631/viewspace-747222/