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/，如需轉載，請註明出處，否則將追究法律責任。