Oracle DB time differ than UNIX time

[Dennis Handly]

>SEP: It was directed at the author not you.

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.

[Steven E. Protter]

Specific reply requested.

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.

[Dennis Handly]

/etc/TIMEZONE : GMT+3 (RIYADH)
>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. :-(

[Akif_1]

Dear Dennis & Steven,

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.

[T G Manikandan]

What is the output of

SQL> select DBTIMEZONE from dual;

[Akif_1]

Hi Manikandan,

The output are as follows

SQL>select dbtimezone from dual;
-04:00

SQL>select sessiontimezone from dual;
-03:00

Have a wonderful dayjavascript&colon;postAnswerSubmit('submit');
Submit

[Dennis Handly · Accepted solution]

(Do you have the contents for /etc/TIMEZONE?)

>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="-3".

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.

[Volker Borowski]

Hi,

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

[Akif_1]

Thanks Volker,

I restarted lsnrctl,

[Akif_1]

Thanks for solving this issue of difference in time, After making changes in TZ the DB restarted along with listeners , It back to normal time.

Once again i appreciate all who help me to sort this issue.