- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Oracle DB time differ than UNIX time
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 01:48 AM
тАО12-30-2007 01:48 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 02:23 AM
тАО12-30-2007 02:23 AM
Re: Oracle DB time differ than UNIX time
date; date -u; echo $TZ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 02:31 AM
тАО12-30-2007 02:31 AM
Re: Oracle DB time differ than UNIX time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 03:00 AM
тАО12-30-2007 03:00 AM
Re: Oracle DB time differ than UNIX time
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 03:21 AM
тАО12-30-2007 03:21 AM
Re: Oracle DB time differ than UNIX time
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 03:34 AM
тАО12-30-2007 03:34 AM
Re: Oracle DB time differ than UNIX time
>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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 03:48 AM
тАО12-30-2007 03:48 AM
Re: Oracle DB time differ than UNIX time
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 03:57 AM
тАО12-30-2007 03:57 AM
Re: Oracle DB time differ than UNIX time
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??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 04:16 AM
тАО12-30-2007 04:16 AM
Re: Oracle DB time differ than UNIX time
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 04:40 AM
тАО12-30-2007 04:40 AM
Re: Oracle DB time differ than UNIX time
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 04:57 AM
тАО12-30-2007 04:57 AM
Re: Oracle DB time differ than UNIX time
Yes, that is obvious. I was wondering why you said "fully supported"? Did you have the TZ value that Akif should use? If this was in tztab, Akif wouldn't have made this mistake.
>Saudi Arabia is GMT+3 not GMT-3
The former is not how it is expressed in TZ. It is XXX-3 for Saudi Arabia.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 08:29 AM
тАО12-30-2007 08:29 AM
Re: Oracle DB time differ than UNIX time
For Example: UNIX:10:30 ->time
# though I think I know this.
What method specifically was used to get this information?
Is the system configured to connect to an NTP server?
Oracle db shows : 08:30 ->time
What method, mysql or whatever was used to obtain this ifnormation?
Oracle is well known for ignoring system norms and handling items its own way. If the system is set up For Riyadh, and the database was configured with one of Oracles wizards (gizards I like to call them) then the entire situation could be Oracle's fault.
/etc/TIMEZONE : GMT+3 (RIYADH)
Though I've read the thread and Dennis' incorrect parsing of my posts carefully its still not clear to me the entire problem.
So more questions:
Does the OS system time display correctly?
Are there errrors in /var/adm/syslog/syslog.log
What does the nptdate function on your system show?
What this command show (if you use ntp) does:
clifford:/# ntpq -p
Example
remote refid st t when poll reach delay offset disp
==============================================================================
LOCAL(1) LOCAL(1) 3 l 19 64 377 0.00 0.000 10.01
+bigus 10.15.0.8 3 u 20 64 377 0.23 -0.567 0.08
*thost gederb. 3 u 36 64 377 0.20 -0.642 0.14
The format is changed due to internal security regulations at my company.
I'm also kind of wondering why there has been no response to my original queries?
Inquiring minds want to know.
SEP
Once again. Shalom.
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 09:11 AM
тАО12-30-2007 09:11 AM
Re: Oracle DB time differ than UNIX time
>SEP: Though I've read the thread and Dennis' incorrect parsing of my posts carefully
What incorrect parsing?
All I said was the format of TZ for Saudi Arabia must look something like:
TZ=XXX-3
You are correct that the time is 3 hours ahead of UTC. And if you were saying that using TZ=GMT+3 is the same as a time that is 3 hours behind UTC (i.e. Brazil), you were also correct.
Akif: What is the exact content of /etc/TIMEZONE?
(From your echo, it is 6 hours off.)
>SEP: it's still not clear to me the entire problem.
Yes, 6 is still not 2. :-(
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 10:30 PM
тАО12-30-2007 10:30 PM
Re: Oracle DB time differ than UNIX time
Please find an enclosed file which is an outpout of /usr/lib/tztab.
My problem is that the time of unix box is differ than oralce 10g database.
Please follow below details for more clarification which extract from different commands.
$ echo $TZ
GMT+3
$ntpq -p ( not avilable)
$date
Mon Dec 31 09:16:25 GMT 2007
$date -u
Mon Dec 31 12:16:45 UTC 2007
SQL> select to_char (sysdate,'HH:MI:SS') from
dual;
TO_CHAR(
--------
07:17:24
Note after above time effect my cron job also not running on specified time 11:59PM and it start early morning at 5:59, May i stop/start cron services or all issued killed once i restart database.
# crontab -l
59 23 * * * /u01/export/abc.sh
#cat /var/adm/cron/log
> CMD: /u01/export/abc.sh
> root 8000 c Mon Dec 31 05:59:43 GMT 2007
< root 8000 c Mon Dec 31 08:13:01 GMT 2007
Thanks for your patience, I do appreciate your helping hand on time.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 11:23 PM
тАО12-30-2007 11:23 PM
Re: Oracle DB time differ than UNIX time
SQL> select DBTIMEZONE from dual;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-30-2007 11:33 PM
тАО12-30-2007 11:33 PM
Re: Oracle DB time differ than UNIX time
The output are as follows
SQL>select dbtimezone from dual;
-04:00
SQL>select sessiontimezone from dual;
-03:00
Have a wonderful dayjavascript:postAnswerSubmit('submit');
Submit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-31-2007 12:18 AM
тАО12-31-2007 12:18 AM
Solution>Please find an enclosed file which is an output of /usr/lib/tztab.
There is a line missing for "Central Standard Time": CST6CDT
Otherwise there is no dummy zone for Saudi Arabia like there is for South Africa.
>My problem is that the time of unix box
Your first problem is that your HP-UX system time isn't correct. It is 6 hours fast. Please correct that before you start on fixing your Oracle time.
>$ echo $TZ
GMT+3
As I said before, this isn't a valid TZ setting for 2 reasons:
1) It's for Brazil.
2) If you use GMT, you must use an offset of 0, otherwise the time will be confusing. I also doubt you want to use TZ="
SQL>select dbtimezone from dual;
-04:00
SQL>select sessiontimezone from dual;
-03:00
These may explain your Oracle differences?
As I mentioned before:
Shutdown Oracle.
You need to change /etc/TIMEZONE. You can make up a timezone name like:
TZ=SAST-3
Or:
TZ=RIYADH-3
Then export TZ to the same value.
Set your system clock.
And probably reboot so cron and other demons are reset.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-03-2008 02:48 PM
тАО01-03-2008 02:48 PM
Re: Oracle DB time differ than UNIX time
allthough it may be late to throw this one in.
Also the listener needs a proper TZ environment before it is started. If restarted from cron, what you see can exactly happen. The shadowprocess on tcp connection is created with listener time environment. This could explain the diffrence for your session time.
Best regards
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2008 09:41 PM
тАО01-04-2008 09:41 PM
Re: Oracle DB time differ than UNIX time
I restarted lsnrctl,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2008 09:48 PM
тАО01-04-2008 09:48 PM
Re: Oracle DB time differ than UNIX time
Once again i appreciate all who help me to sort this issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-04-2008 11:15 PM
тАО01-04-2008 11:15 PM