Operating System - HP-UX
1747988 Members
4949 Online
108756 Solutions
New Discussion юеВ

Re: Oracle DB time differ than UNIX time

 
SOLVED
Go to solution
Akif_1
Super Advisor

Oracle DB time differ than UNIX time

Hi,

Iam facing a problem with the time difference between the database and UNIX server using HP-UX 11.11 verson of rp7410 server.

The time which UNIX box shows is 2 hours differ than oracle database 10g.

For Example: UNIX:10:30 ->time
Oracle db shows : 08:30 ->time
/etc/TIMEZONE : GMT+3 (RIYADH)

The output of root user and oracle user (echo $ TZ) is GMT+3 as it is mentioned in /etc/TIMEZONE.


Any help will be appreciated at the earliest.
T(ogether) E(very one) A(chive) M(ore)
20 REPLIES 20
Dennis Handly
Acclaimed Contributor

Re: Oracle DB time differ than UNIX time

What does the following show?
date; date -u; echo $TZ
Dennis Handly
Acclaimed Contributor
Akif_1
Super Advisor

Re: Oracle DB time differ than UNIX time

Dear Dennis,

The output of the following commands are below.

1)date: Sun Dec 30 13:54:30 GMT 2007

2)date -u: Sun Dec 30 16:54:23 UTC 2007

Note: 3hrs difference between this two command and 2 hr difference in database.

3)echo$TZ: GMT+3


I appreciate your help
T(ogether) E(very one) A(chive) M(ore)
Steven E. Protter
Exalted Contributor

Re: Oracle DB time differ than UNIX time

Shalom,

Unix does not calculate time in terms of display time. 10:30 in your home city is 9:30 here.

Unix calculates time based on the number of seconds that has passed since January 1, 1970.

What you are seeing is a display issue.

Your Oracle product has been installed and configured with the wrong Time Zone mask.

All you need to do is correct the Oracle installation.

If the system is displaying wrong then use sam to correct the time zone. I'm aware that your time zone is fully supported in HP-UX.

sEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Dennis Handly
Acclaimed Contributor

Re: Oracle DB time differ than UNIX time

>1)date: Sun Dec 30 13:54:30 GMT 2007
>2)date -u: Sun Dec 30 16:54:23 UTC 2007
>Note: 3hrs difference between this two command and 2 hr difference in database.
>3)echo $TZ: GMT+3

Something terribly wrong. Your GMT/UTC time is NOT about 11:33 AM, about when I'm replying to this thread. If you are in RIYADH (Saudi Arabia) you should probably have GMT-3.

And most of all, you should not name your TZ GMT, unless it is GMT0. You should at least make up a timezone name like SAT or RYT:
export TZ=SAT-3
http://www.timeanddate.com/worldclock/city.html?n=214

The above link shows you are about: Sunday, December 30, 2007 at 2:33:00 PM

Perhaps your confusion is that page shows UTC/GMT +3. But for UNIX's TZ, the value after the zone is the amount you add to the local time to get to UTC, not the other way.

But I'm not sure how this 6 hour difference will map to the 2 hours you see in your database.
Akif_1
Super Advisor

Re: Oracle DB time differ than UNIX time

Dear Dennis,

It is really complicated one, I checked ur given link http://www.timeanddate.com/worldclock/city.html?n=214

But it shows GMT+3 nor GMT-3for Riyadh Saudi Arabia, Anyway what will be the solution to resolve the difference between hp-ux and oracle database 10g.

I appreciate your efforts.
T(ogether) E(very one) A(chive) M(ore)
Dennis Handly
Acclaimed Contributor

Re: Oracle DB time differ than UNIX time

>But it shows GMT+3 not GMT-3 for Riyadh Saudi Arabia

I mentioned above that's the problem. UNIX is completely different than the real world. See environ(5):
http://docs.hp.com/en/B2355-60130/environ.5.html
TZ offset
offset is the value that must be added to local time to arrive at Coordinated Universal Time (UTC).

>what will be the solution to resolve the difference between HP-UX and oracle database 10g.

You must first develop/makeup a timezone name. Then you must update /etc/TIMEZONE with that name and -3 as the offset.
Then you must shutdown your database. (I'm not sure if you have to do that for 6 hours or not?)
Then you must export TZ to that zone and then update your system clock.
You may have to reboot so long running processes are reset to the correct time and zone.

There may be more to get Oracle fixed??
Dennis Handly
Acclaimed Contributor

Re: Oracle DB time differ than UNIX time

>SEP: I'm aware that your time zone is fully supported in HP-UX.

What did you mean by this? Did you mean there is an entry in /usr/lib/tztab? Or that since there is no DST adjustment, you can just make the timezone up?

To me "fully supported" means there is an entry in tztab, even if trivial.
Steven E. Protter
Exalted Contributor

Re: Oracle DB time differ than UNIX time

Dennis,

My post was made while there were no replies. It was directed at the author not you. Obviously we replied at the same time and yours was posted first.

As per ITRC ettiquette, I rarely offer direct comments directed to other repliers.

To make sure there is no doubt.

Saudi Arabia is GMT+3 not GMT-3

The system is a bit messed up. However with use of system utilities and perhaps a little magic from oracle the issues should be correctable.

Regards and good luck to all.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com