DST issue after patched. 1

[DrD123]

Hi.

We applied the appropriate DST patches and time zone files for both Oracle 9.2 and to our Solaris 9 test server.
We tested by changing the server time to March 11, 2007 @1:50am... we see the date/time change takes in the OS, also when checking the dB AFTER the time change date-time fields are correct.

NOW the issue/question>>>

When our apps are connected during the time change an odd occurrence happens.
We are currently -5GMT. When the time changes we SHOULD be -4GMT.

We have apps that have a persistent connection to the dB that do inserts, updates, etc...

Here's an example:

Before the time change a record would get inserted and a timezone w/timestamp field shows date processed of 2007-03-11 01:51:54.523306 -05:00
After the time change new records show 2007-03-11 02:00:00.017011 -05:00.

It should be 2007-03-11 03:00:00.017011 -04:00. Since 02:00:00 wouldn't exist. Odd thing is whether 2am -5GMT or 3am -4GMT they both = 7am GMT.

The functions for the insert get the date processed info from CURRENT_TIMESTAMP.

When a "new" connection is made AFTER the time change, the -4GMT shows correctly, also if the app is restarted AFTER the time change.

Anyone else have this oddity, or a hint on what causes it or how to correct without an application bounce?

Thanx... the DST clock is ticking.
D123

 

[Scunningham99]

Hi

We have applied the timezone patches top o/s. Not applied any database patches.

I logged on a connection before I rolled the date forward to 11th March 11:59

then re-queried the sysdate from dual which had changed in persistent connection. (note this was an idle connection)

TO_CHAR(SYSDATE,'DD-
--------------------
11-MAR-2007 01:59:55
1 row selected.
SQLWKS> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual
2>
TO_CHAR(SYSDATE,'DD-
--------------------
11-MAR-2007 03:00:03



Sy UK

 

[DrD123]

Hi Sy,

Thanx for the feedback, I'll give that one a try.

Although in our functions we're using current_timestamp, which is giving us the odd output.

Could you try this when/if you get the chance, and see if all is well:

select current_timestamp from dual;

thanx
DrD

 

[Scunningham99]

Hi

Looks like its an issue with current_timestamp with persistent connections (see below)

persistent connection before and after time change:-

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11-MAR-07 01.59.50.324655 AM -08:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11-MAR-07 02.00.14.605272 AM -08:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11-MAR-07 02.00.19.336605 AM -08:00


New connection :-

QL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual
2 ;

TO_CHAR(SYSDATE,'DD-
--------------------
11-MAR-2007 03:00:49

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11-MAR-07 03.00.59.067673 AM -07:00

So looks like we have the same issue as yourself.

Hope this Helps...


Sy UK

 

[DrD123]

Hi Sy,

Thanx for giving a shot.

During that time we recieved an email back from ORacle Support and they logged it as bug #5900435.

Not much time left before DST March 10/11... let's see Oracle can get the fix w/o a work around.

Thanx
DrD

 

[karluk]

Thank you very much for this bug reference, DrD123. I am the one in charge of DST testing for our Oracle servers on March 11, so it is very helpful to know in advance what to expect. We'll have to wait and see if Oracle can provide a fix, but it certainly appears that this issue might require a shut down and restart of all databases after 2:00 a.m.

 

[DrD123]

Hi karluk,

From my testing on our various apps and even with just the SQL prompt... a dB restart isn't necessary (if the DST patches, OS patches are applied correctly, because part of the DST patch is a dB restart)... just the apps need to terminate their connection and reconnect. Then everything works as it should.
At lease it did in our environment.

DrD

 

[karluk]

Understood. I'm guessing that we'll still shut down all the databases, assuming Oracle doesn't come up with a better solution. There's nothing like a database bounce to ensure that all application connections are fresh.

Fortunately we have a little time to investigate our options.