SYSDATE different from AIX DATE command

ststuber:
  I don't have a "v$timezone_file". I do have a "v$timezone_names" that has columns TZNAME and TZABBREV. There is a record for my timezone: CST.

mrjoltcola:
I ran the commands from my PC and got the same result. The time is still an hour off.

oops sorry, 9i didn't have that

you'll have to use

$ORACLE_HOME/rdbms/admin/utltzver.sql

if you don't have that script then you can download it from metalink

see metalink doc id 412160.1 for the link and description

sdstuber:
  Sorry. I don't have the utility and I don't have an account on metalink.

johnsone:
  Is the timezone patch you referred to an AIX thing or an Oracle thing? I'm thinking the AIX TZ variable is set properly because the AIX "date" command returns the proper value and SQL*PLUS returns the correct date when run on the RS/6000. It's only when I run SQL*PLUS from a Windows PC that I have a problem.

if the sysdate changes based on where you query it from, then it's definitely a client issue as mentioned by mrjoltcola

@sdstuber: I was just about to post before you said this. SYSDATE, by the docs, is based on the time on the server. So it should not change with client settings.

I was referring to the Oracle time zone patch referenced by sdstuber.

I've been playing with the timezone setting on my Windows PC and using SQL*PLUS to query SYSDATE. No matter how I change the PC's setting, it always returns the same (wrong) time.

Let's try this problem from another tack: is there a way to get the server date/time regardless of how the PC is configured?

mrjoltcola,
yes, you're correct  the client (session) settings should not make a difference in SYSDATE (current_date, yes, but not sysdate)

however, the dates are clearly dependent on where the query is run.
hmmm

maybe it's the wrong "where"

jrollins138,
are you  sure you are connecting to the same database?

>>is there a way to get the server date/time regardless of how the PC is configured?

Yes, it has traditionally been SYSDATE. :(

Can you give us some details on version on the PC?

I cannot replicate this problem here. Not that that makes anyone feel any better, for as always, I perform sanity checks even on things I've used for years.

I'm thinking bug in the client. (Either that or I am about to learn something new about Oracle...)

Can we see your environment settings on the client?

sdstuber
  yup, verified database connections. They're pointing to the same DB.

mrjoltcola
  the PC is just a plain-vanilla XP box (SP 3).

I've got another AIX box with Oracle on it. I can't restart the listener on my main system during business hours but I can on this other guy. I'll see if altering the AIX TZ environment variable has an impact.

how about this..

are you connecting with /

or with @ ?

for example

sqlplus /
vs
sqlplus username/password@dbname

when I used / on the server, my current TZ setting overrode the db's timezone and sysdate came back different

when I connected through sql*net, the db's timezone prevailed

sdstuber:
  Interesting question. On the PC, I'm just clicking the SQL*PLUS icon which runs with no command line arguments. It pops a dialog that prompts for login, password and database. It then runs a GUI version of SQL*PLUS

When I ran it from the PC's command line:

sqlplus user/pass@db

It brought up a command line version. The results were the same, however (time is an hour off).

Running "sqlplus /" just produced an error about a TNS protocol adapter error.

Disclaimer: I did some more checking. What I've been referring to as "SQL*PLUS" was, in fact, SQLPlus Worksheet, the graphical version of SQL*Plus. The results have been identical. I apologize for any inconvenience.

That would point back to the listener, then, as johnsone speculated.

The listener spawns a server process, so whatever TZ it was started with is what you get, through the listener.

actually no,  in my test I had a session pre-TZ change open, and post-TZ change with listener bounce and they both returned the same sysdate

The only one time TZ mattered to remote clients is the TZ at the time the DB is started

>>The only one time TZ mattered to remote clients is the TZ at the time the DB is started

Yes, I just finished the same test case and listener TZ did not affect SYSDATE.

Does everyone have Metalink. Read article 399448.1

mrjoltcola:
  Sorry. I don't have it. My company does not have a support relationship with Oracle. (Very long and uninteresting story.)

I've been doing some testing on my second AIX box and I'm convinced the issue has to do with the TZ environment variable. Here's what I think is going on::

- AIX knows how to interpret the additional values in my TZ string (CST6CDT,M3.2.0,M11.1.0). Oracle does not.

- Everybody is looking at what the TZ variable is set to when the system boots up. I tried setting TZ from the shell and then stopping and starting the listener and nothing changed. When I set TZ to "CST6CDT" in /etc/environment and then rebooted the AIX box, everything started behaving predictably.

- I'm pretty sure both IBM and Oracle have fixed everything related to this problem and I'm being plagued by my Luddite tendencies to not stay current.

I'm splitting the points between both respondents because I genuinely appreciate you both hanging in there and helping me work through this.

Thank you very much.

Drat. I wanted to split the points evenly between the three participants. Can someone help me change this?

There is a difference between static listener config and dynamic, and between using SERVICE_NAME vs SID.

Oracle docs say for static configs (using SID) that the environment vars are inherited from the user who started the listener.

I have tested, and there is some difference, but I haven't a clear test case yet, other than to point you to that Metalink doc, and to look at how your listener is configured, and how yoru tnsnames.ora is setup (whether to use SID or SERVICE_NAME).

"Can someone help me change this?"

I have clicked the "Request Attention" link for you to have a moderator reopen the question and let you split the points however you want.

sdstuber:
  Thanks for flagging the moderator for me. I'll reassign points.

mrjoltcola:
  Interesting. My tnsnames.ora is using the "SID = ???" method, rather than SERVICE_NAME. I never knew what the difference was.

I'm less concerned about you closing the question than finding the root of this problem. I will return to this question, sorry I cannot finish my test case right now, but I will say I reproduced some odd behaviour switching between static and dynamic listener config and SID vs SERVICE_NAME. I'd rather not speculate, as I have to run to a meeting, but I'll followup.

I did some playing around with both AIX systems last night. In both cases, simply rebooting the systems had no affect on the symptoms. The one factor that seems to affect everything is the TZ environment variable.

This value is defined in /etc/environment. I set it to "TZ=CST6CDT,M3.2.0,M11.1.0". The "M3,2.0" tells AIX that DST goes into effect on March 2. The "M11.1.0" tells AIX when DST ends November 1. My theory is that AIX knows how to handle all of this but the Oracle client doesn't. (Special comment: I'm not current on either AIX or Oracle. It's very likely that IBM and Oracle have patched both of these situations.)

The symptoms are that the date returned by AIX is correct, the SYSDATE returned by SQL running on the server is correct, but the SYSDATE returned by SQL running on a client PC is one hour off. If I change the AIX date, the two SYSDATE values change correspondingly.

When I set the TZ variable to "TZ=CST6CDT" and reboot, all three dates are the same.

The net of all this is that I either need to update my system/db software or manage the daylight savings time thing manually.

In all cases, SYSDATE comes from the DB server and is affected by the Timezone of the server process.

In the case of dynamic server registration (ie. no static listener entry) your environment variables will be registered by PMON when the service registers with the listener.

It is possible to configure listeners to have different timezones, if you use static entries by adding the TZ to the listener.ora explicitly, using the listener.ora ENVS='' param. Check yours to see if you have one. If so, remove it.

If not, and the symptom persists after after a reboot (where EVERYTHING should have the same TZ) then I would say either:
1) Your auto-start is misconfigured and the TZ var is not set right by the dbstart script.
2) There is a bug somewhere between how the listener is handling DST.

It would help to see your listener.ora file and your .profile or /etc/profile (or however else you are configuring environment).

since you're manually manipulating the DST conversions,  the most likely case I can think of is your database was started with the old (pre-Energy Policy Act of 2005) DST rules which is why your times became off by an hour when the last transition occurred.


or,  are you saying if you use "TZ=CST6CDT,M3.2.0,M11.1.0" and reboot  then your dates and times still aren't in synch?

If you are using AIX 5 and get your support up to date you can get your DST rules applied automatically

If not, you will have to maintain your TZ variables manually with the transition dates and reboot every year before the spring transition to set the correct dates

sdstuber:

What appears to be happening is that AIX knows how to handle the extra stuff on the end of the TZ string but the Oracle PC client doesn't. It may be related to how the client is connecting to the server (SID vs SERVICE_NAME) but the result is that I can't sync up the dates.

I'm reconciled to managing the server time manually without the special AIX functionality. Ah well.

>>What appears to be happening is that AIX knows how to handle the extra stuff on the end of the TZ string but the Oracle PC client doesn't.

I don't think the PC / Client has anything to do with it. Originally we suggested because it seemed the obvious differentiating factor, but after consideration, the real differentiating factor seems to be the LISTENER / remote connections.

You could verify this by connecting from another Oracle client from, say, an AIX account with no TZ variable.

it's the reboot, or more precisely, the restart of the database AFTER changing the TZ that matters
also possibly the restart of the listener if you have TZ settings there.

So, just to be precise.

After your used "TZ=CST6CDT,M3.2.0,M11.1.0" ,  did you reboot?

If so,  did anything alter the TZ  before your database and listeners started?

to be sure of the TZ for your database

please post the results of

ps eww 123456  

where 123456 is the process number of your database pmon process

if you want show all of the pmon processes

ps -ef |grep pmon|awk '{print $2}' | ps eww

I've rebooted after changing the TZ setting in /etc/environment so that I could be absolutely certain what TZ was in effect.

I'll have to postpone this discussion for the rest of the day. I just lost a harddrive in my production box and I'm recovering from that. I'll check back in tomorrow.

I'll run the ps eww command then.

Thanks again for the help.

Thanks again everyone. I appreciate the help.

glad we could help,  did something in the ps eww help?

is the problem truly resolved? if so, what was the final outcome?