TZ parameter & sysdate

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)

echo $TZ
should show >> America/New_York

2.- Stop listener

lsnrctl stop
3.- Start listener


lsnrctl start
________________________________________________________

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.