Wrong Oracle Database time information
After we had migrated our Oracle Applications 11i system to AIX 6.1 OS, we received complaints that few instances are showing wrong time information.
All concurrent programs were actually showing CET time instead of EST time.
Running a simple statement like below was fetching wrong timing information when the query was run from Application node. Though timing information was found to be correct when checking through database node.
SELECT TO_CHAR(SYSDATE, 'dd-mm-yy hh24:mi:ss') from dual;
Here, first of all, it is important to know that “sysdate” has *NOTHING* to do with DB_TIMEZONE or other timezone related date formats like systimestamp.
The SYSDATE function get time information from operating System time.
So you can not influence the sysdate values by changing database parameters.
SYSDATE returns the current date and time set for the operating system on which the database server resides.
The data type of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.
The operating system parameter which need to focus in such scenario is the ‘TZ’ parameter.
In our case we found out that the sysdate information was correct when it was checked from databse node but it was showing CET time when checked from Application node.
We checked and found that database listener was somehow not starting with correct TZ settings.
The below steps were done :
1.- Configure TZ variable on your server
export TZ=”America/New_York" (or whatever you want it to be)
should show >> America/New_York
2.- Stop listener
3.- Start listener
So starting the listener with different timezone (using TZ OS environment variable) you will be able to have different timezone databases running in same physical hardware and showing correct time.
[Post Views: 2647]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018