ORACLE DATE PROBLEM

[mani.manavi]

Hi Everybody
I installed and configured oracle real application cluster 10g on Hp-Ux 11.23 (two nodes) and before install it I set the HP-UX timezone to GMT+5. The problem is when I reboot the servers or the CRS and connect to oracle database and use

select sysdate from dual;

to get time, the time is different from HP-UX time, to solve this problem I shutdown the oracle and start it up, but after that I have different time in CRS logs like crsd.log and oracle alertlog. the most intresting thing is when I down the listener and up it without down the oracle database the oracle database time set to CRS time and different with HP-Ux time.

is this problem form Timezone?
how can solve it?

thank anyway

[Dennis Handly]

>I set the HP-UX timezone to GMT+5

India should have: TZ=IST-5:30
Not just 5. Are you 30 minutes off?

[Yogeeraj_1]

hi,

what does these queries show?:

select tz_offset(dbtimezone) from dual;

select sysdate, sysdate+(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval('0
'||substr(tz_offset( DBTIMEZONE ),2, 5)||':00')
from dual;

select sysdate, new_time(sysdate,'gmt','edt'), new_time(sysdate,'gmt','est')
from dual;


kind regards
yogeeraj

[Yogeeraj_1]

hi,

Also, see this extract from Oracle Globalization Docs, quote:

"The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the SET TIME_ZONE clause, then Oracle uses the time zone of the operating system of the server. If the operating system's time zone is not a valid Oracle time zone, then the database time zone defaults to UTC. Oracle's time zone information is derived from the public domain time zone data available at

ftp://elsie.nci.nih.gov/pub/

Oracle's time zone information may not reflect the most recent time zone data available from this site."

what is the value specified in your /etc/TIMEZONE file?


revert
kind regards
yogeeraj

[mani.manavi]

hi
I think you get my problem wrong because
my problem is not oracle database because when I restart the oracle database its time will be correct, the problem is the time of crs and listener and they give the time to oracle.

thanks anyway