Oracle Time Zone - SAP Note 1019133

[Former Member]

Has anyone applied this patch or skipped the patch?

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

In 2007 the USA’s daylight savings starts earlier and ends later, so Oracle’s table of time zone rules should be changed for America.

The SAP note 1019133 said we probably do not need a patch.

The Oracle Note 396671.1 said, apply it just in case.

SAP Note: 1019133

" In the standard SAP application the JVM and the mentioned datatypes (TSLTZ / TSTZ) are not used. Therefore it's not necessary to apply this patches."

Oracle Doc: 396671.1

"If there is no other use of time zone data than stored TSTZ data, and the utltzuv2.sql script shows that none of these use the affected time zones, then you could choose to not apply the new time zone files (even though in such a situation the application would also be very easy, so the advise would still be to do it "just in case")."

[Former Member]

Good Luck!

[Former Member]

How'd it go?

did you survive?

we made it just fine. well, our SAP instance had some bad settings that needed to be changed, but the database did just fine.

Hope yours went well also.

Norma Jean

[Former Member]

Hi

Have a look on Oracle DOc id 359145.1.You will get every information.

Regards

Vinod

[Former Member]

Even after reading Oracle Doc 359145.1 I still have the same questions:

1) Has anyone applied the patch (Oracle Time Zone - SAP Note 1019133)

2) Should the patch be applied 'Just in Case'

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Oracle Doc: 396670.1

In order to find out how many columns use these types in the database you can use the query:

SQL> select table_name, column_name, data_type from dba_tab_cols where data_type like '%TIME ZONE';

If the result of this query is that there are no stored time zone types in the database, and you also do not use the time zone information in (PL/)SQL, then this means there is no need to apply the new time zone files, and there is certainly no further need to run utltzuv2.sql (since this only checks stored TSTZ data).

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I ran the script in our 9.2.0.6 database and no rows where returned.

For 10.2.0.2 onlt Data dictionary use the timezone,

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

[Former Member]

Is anyone using SAP in the USA, concerned about the day light savings time changes & how it effects ORACLE/SAP?

[Former Member]

Hi

You can avoid the effect of DST by applying patched at the database level, but for the SAP applications you need to contact the SAP vendor for the correct resolution.

If you use the query

select table_name, column_name, data_type from dba_tab_cols where data_type like '%TIME ZONE';

then you can determine if any of this data actually exists in these databases. If it

<b>doesn't then there is no need to apply</b> the patch.

Patches are available only for the supported versions.Basically these types are only affected by this issue if we handle data from after 03/11/2007 using the "affected time zones".

you will really only see a problem with data when you do conversions between time zones.

Regards

Vinod

[Former Member]

1) Since there is no patch for 9i - I assume we do not have to patch 9i.

Is this correct?

2) From SAP Note: 1019133 - "In Oracle 10 the data dictionary uses TSTZ types. Therefore it's more likely that affected data is used. If you're using one of the time zones mentioned in the README file of patch 4689959 (for AIX, even though you are using another platform) you have to apply the patch on the server side."

The American Time Zones are in the README file.

So I am assuming that the patch is needed for 10g.

Is this correct?

[Former Member]

Hi all,

I'm on Oracle 9.2.0.6.0 and had the same result....

no rows found when running utltzuv2.sql.

The Oracle note said if you have any concerns to put in a Oracle SR (metalink) so i did. that was early last week or the week before. Oracle hasn't given me an answer yet, they keep telling me to read the same notes i already have read.

However, we are applying the patch to our non-SAP systems and it seems quite easy. So we are going to apply the patch to our SAP systems (yes i have read the SAP note that says I should not have to patch).

I will post back our results. i should be done patching by next Monday.

thanks,

NJ

[Former Member]

Hi

Ffor the dissupported versions, please follow the following note which gives detail on how to apply the DST files manually.

Note 396387.1 (Workarounds when Database time zone patches are not available for your patchset)

Note 396426.1 ( Effects on client and middle-tier of applying time zone patches on the Oracle Database).

my Posting ref to Oracle Databases with versions 7.3 to 10g.

Regards

Vinod

[Former Member]

1) Where can I find the SAP patch to upgrade Oracle 9.2.0.6 ?

2) Does the SAP/ORACLE client have to be upgraded? If so, where do I get this patch?

3) How do I tell if the JVM client needs to be upgraded for 9.2.0.6 & 10.2.0.2

[Former Member]

1) Where can I find the SAP patch to upgrade Oracle 9.2.0.6 ?

There is no patch for 9.2.0.6, you have to do it manually.

o Download the patch for 9207 or 9208 patch. The relevant files are identical so you can use either of them, only the packaging of the patches is different

o Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the "files/oracore/zoneinfo" directory of the uncompressed patch (or from the relevant .jar file of a patchset). If there is also a readme.txt in this location then make a note of this as well.

o Backup your existing files in $ORACLE_HOME/oracore/zoneinfo - THIS CAN BE VITAL, DO NOT SKIP.

o Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.

o Restart the database (in case of installation on a database), or restart the client applications (in case of client install).

2) Does the SAP/ORACLE client have to be upgraded? If so, where do I get this patch?

??????

3) How do I tell if the JVM client needs to be upgraded for 9.2.0.6 & 10.2.0.2

You run this query and see if rows are returned: select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;

[Former Member]

My remaining question is:

Does the SAP/ORACLE client have to be upgraded?

And if so, where do I get this patch?

The SAP Note 1019133 only says:

"the time zone patch might be needed on the database side and the clients."

[Former Member]

the client patch is the same patch you did for your DB.

if you look in the $ORACLE_HOME/oracore/zoneinfo directory for the client, you will see the same files as the DB.

i believe in most SAP cases, the client will be in the same dir as the DB files.

in my case, i had an additional oracle client on my SAP unix servers because we were tracking and capturing things into another oracle database for a non-SAP related deal.....

that's my 2 cents.

[Former Member]

Also, if you got "no rows selected" for the JVM query, did you do a patch?

if so....what did you do? (i am 9.2.0.6 also).

thanks,

Norma Jean

[Former Member]

<<Also, if you got "no rows selected" for the JVM query, did you do a patch?

if so....what did you do? (i am 9.2.0.6 also).>>

Then you are not using JVM, and you need no patch.

[Former Member]

<<the client patch is the same patch you did for your DB. If you look in the $ORACLE_HOME/oracore/zoneinfo directory for the client, you will see the same files as the DB.>>

I do not see any client files in $ORACLE_HOME/oracore/zoneinfo.

Are the timezone files located here both for the datbase and the client?

Where are the client files you are talking about located?

I thought the clients were located at /oracle/client/92x_64

And I see no timezone files there.

[Former Member]

since the oracle notes had said it is the same patch for the DB and client, i just thought : in an SAP environment, how does a client connect.....

well, our SAP users use the SAP Login GUI thingy.... which gets them to the SAP instance of their choice, ..... and it's the SAP instance that is then actually the "client" for the oracle DB.....

so.... well, the SIDadm account must be indicative of how this client process works for SAP because SIDadm can start and stop the Oracle DB....

so i logged in as SIDadm and looked for the client that way.... i.e.... go to the $ORACLE_HOME/oracore/zoneinfo directory that SIDadm sees and what's there....

in my case it put me in same dir as the DB files.... so must be getting it from same spot.

cuz this was something i just made up as i went along and i was very tired at the time, i also ran a find command from the root directory..... i am on Sun.... my find command was :

find . -name timezone.dat -print

granted there were a lot of directories i did not have perms to see, but it returned only 2 timezone files for me....

the one in the $ORACLE_HOME/oracore/zoneinfo directory

and one in /local..... (which is what we have for some secondary things, not SAP related)....

so try a big system level find command if on Sun, or something similar on your O/S..... if you have your SAP and your DB on the same server.... it's got to be on that server somewhere....

hope that helps.

[Former Member]

So, does anyone know if we need to patch the 'client' for 9.2.0.6?