Re: DST - the root note

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 14 Feb 2007 15:54:32 +0000
Message-ID: <7765c8970702140754g7e4b5cb9h8bd137d4e302b770@mail.gmail.com>

There is however a note on how to confirm whether the patch is working as anticipated

6. How can I test if the OJVM patch has been applied correctly? The following example creates a Java Stored Procedure which uses the built-in Java knowledge about time zones. In this example the JSP returns the offset from standard time in milliseconds. Therefore the expected answers are 0 when the given time zone is in standard time, or 360000 when the given time zone is in DST.
create or replace java source named "OffsetFromStandard" as

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.TimeZone;

public class OffsetFromStandard
{
  public static int getDSTOffset(
    String timezone,
    int year,
    int month,
    int mday,
    int hour,
    int min,
    int sec)
  {
    TimeZone tz = TimeZone.getTimeZone(timezone);     GregorianCalendar c = new GregorianCalendar(tz);     c.set(year, month-1, mday, hour, min, sec);     return c.get(Calendar.DST_OFFSET);
  }
}
/

alter java class "OffsetFromStandard" resolve /

CREATE OR REPLACE function get_dst_offset (timezone VARCHAR2,

                                               year NUMBER,
                                              month NUMBER,
                                               mday NUMBER,
                                               hour NUMBER,
                                                min NUMBER,
                                                sec NUMBER) RETURN NUMBER AS

LANGUAGE JAVA NAME 'OffsetFromStandard.getDSTOffset (java.lang.String, int, int, int, int, int, int) return int';
/
After this has been set up we can use this JSP to check if the correct offset rules are in place. Note that we divide the result of the JSP in order to get the offset in hours rather than milliseconds. The result will therefore be either 0 (timestamp is in Standard Time) or 1 (timestamp is in DST):
SQL> select get_dst_offset('America/Los_Angeles', 2007, 3, 11, 10, 0, 0)/(60*60*1000) as OFFSET_FROM_STANDARD_TIME from dual;

OFFSET_FROM_STANDARD_TIME

---

1

SQL>
So in this example we show that a date of March 11th 2007, 10:00am in time zone America/Los_Angeles is indeed using DST, which means that the new time zone rules are in place, since this is the new start date of DST. Note that if you test this with your own time zone that you will need to use the known Java time zones, which are case sensitive. If the result of the query on America/Los_Angeles is correct (1), and a result for your own time zone seems incorrect (0), it is most likely that there either was a typo in your own time zone name, or this is not a valid Java-known time zone (in the version you're currently working in).

I'd be tempted to run this on each instance of a test database. As well as the SR!

cheers

Niall

On 2/14/07, Jeffery Thomas <jeffthomas24_at_gmail.com> wrote:
> Thanks Jared -- actually I've already read all this and already documented
> it for internal corporate purposes. However -- I'm new to RAC and I did
> not see any RAC-specific instructions.
>
>
>
>
> On 2/14/07, Jared Still <jkstill_at_gmail.com> wrote:
> > You better start reading. :)
> >
> > The patch is multipart:
> >
> > 1) utltzuv3.sql - located TZ data - version dependent
> > 2) timezone files - patch for OH
> > 3) Oracle JVM patch
> > 4) possible client patches.
> >
> > Jared
> >
> >
> >
> >
> > On 2/14/07, Jeffery Thomas < jeffthomas24_at_gmail.com> wrote:
> > > What is the proper way to patch on RAC where each node has its own
> ORACLE_HOME?
> > > Shutdown all the instances, patch each respective ORACLE_HOME, bring
> back up
> > > one instance and rebuild the JVM there, and then bring up the
remaining
> instances?
> > >
> > > Or would it suffice to simply take down one instance, patch that
> ORACLE_HOME and rebuild the JVM in the one instance?
> > >
> > > Thanks,
> > > Jeff
> > >
> > >
> > >
> > >
> > >
> > > On 2/12/07, Jared Still <jkstill_at_gmail.com> wrote:
> > > > or node if you prefer. :)
> > > >
> > > > This document is the place to start - ML Note 403659.1
> > > >
> > > > Don't be misled by its title "2007 Daylight Saving Time Changes For
> Oracle Applications"
> > > >
> > > > It contains to the top links to E-Business Suite, RDBMS, iAS, etc.
> > > >
> > > > ML Note 412789.1 details information on DST webcasts.
> > > >
> > > > --
> > > > Jared Still
> > > > Certifiable Oracle DBA and Part Time Perl Evangelist
> > > >
> > >
> > >
> >
> >
> >
> > --
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
> >
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 14 2007 - 09:54:32 CST