topic Re: Oracle 9i and Daylight Saving in Operating System - HP-UX

Oracle 9i and Daylight Saving

Albert Goodman — Mon, 02 Jan 2006 19:13:55 GMT

Hi,

Does anyone know of a good approach to take in relation to the automatic switch back of system clock after daylight saving ends on a system running Oracle 9i?

As I understand it, when daylight saving ends, the system clock switches back from 2am to 1am and that this can cause the database some grief. To be safe, it was suggested that the database should be stopped prior to say 1am for just over an hour and restarted after the clock has switched back. Does anyone know of a better way?

We are using NTP to synchronise our system clock with an external time source.

Thanks in advance and points will be allocated.

Re: Oracle 9i and Daylight Saving

Indira Aramandla — Mon, 02 Jan 2006 20:42:14 GMT

Hi Albert,

Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly. The datetime value is sufficient for Oracle to determine whether daylight savings time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight savings goes into or comes out of effect.

For example, in the US-Pacific region, when daylight savings goes into effect, the time changes from 2 AM. to 3 AM. The one hour interval between 2 and 3 AM. does not exist. When daylight savings goes out of effect, the time changes from 2 AM. back to 1 AM, and the one-hour interval between 1 and 2 AM is repeated.

To resolve these boundary cases, Oracle uses the TZR and TZD format elements, as described in Table 2-15. TZR represents the time zone region in datetime input strings. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbreviated form of the time zone region with daylight savings information. Examples are 'PST' for US/Pacific standard time and 'PDT' for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view.

Refer to Note:149120.1 "Oracle9i Daylight Savings feature in Datetime datatypes" in Metalink. The purpose of this note is to introduce this Daylight Savings feature.

Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly.

Indira A

Re: Oracle 9i and Daylight Saving

Yogeeraj_1 — Tue, 03 Jan 2006 01:14:46 GMT

hi Albert,

also allow me to add the Indira's explanation above that there are no issues in the database, only in applications that use the database.

All things inside the database are governed by the SCN, a number, a ticker -- it is not based on the wall clock.

Applications can be affected as all of a sudden there are two 1AM's on a given day.

hope this helps too!
kind regards
yogeeraj

Re: Oracle 9i and Daylight Saving

Albert Goodman — Tue, 03 Jan 2006 02:16:21 GMT

We've also got SAP R/3 running. I suppose that complicates things somewhat?

Re: Oracle 9i and Daylight Saving

Tvs — Tue, 03 Jan 2006 03:18:10 GMT

Hi

The hp server take the automatic daylight saving from the following file. and hp server can automatically adjust as per the day light settings.

The time zone setting is in /etc/TIMEZONE

#cat /etc/TIMEZONE

TZ=GMT0BST
export TZ

this entry is taking the parameter from /usr/lib/tztab

# grep GMT0BST /usr/lib/tztab

# Greenwich Mean Time, British Summer Time
GMT0BST
0 3 25-31 3 1983-1984 0 BST-1
0 3 23-29 3 1985-1990 0 BST-1
0 3 25-31 3 1991-1995 0 BST-1
0 2 25-31 3 1996-2038 0 BST-1
0 1 25-31 10 1983-1985 0 GMT0
0 1 23-29 10 1986-1994 0 GMT0
0 1 18-24 10 1995 0 GMT0
0 1 25-31 10 1996-2038 0 GMT0

for more information regarding the settings and parameter u can find in.

# man tztab

if any application is ruuning pls stop all the application ( like sap , oracle) at the time of day light changings.
for example in UK the winter day light change is happening on October 31 at 1.00 clock . it can effect the constitencey of the application.

regards

tvs

Re: Oracle 9i and Daylight Saving

Yogeeraj_1 — Tue, 03 Jan 2006 03:55:24 GMT

hi

In Oracle 9i and up, timezone support is built-in to the TIMESTAMP WITH TIMEZONE datatype.

I don't know if the SAP R/3 know how to benefit from that. Maybe you should contact support.

kind regards
yogeeraj

Re: Oracle 9i and Daylight Saving

Brian Crabtree — Tue, 03 Jan 2006 13:45:39 GMT

Albert,

For where we are, we bring SAP down for the fall back portion, per SAP.

Not sure if that helps.

Brian

Re: Oracle 9i and Daylight Saving

Albert Goodman — Tue, 03 Jan 2006 17:49:20 GMT

Thank you all for your kind responses. I was hoping for a more innovative approach but Brian thanks for alluding to SAP Note 102088

I will close this thread and open another one to see if anyone know how to turn off daylight saving on a HP-UX 11i system in the least intrusive way. I believe I can fudge the tztab table but am not sure if it is wise