[doc]Actions for the DSTv4 update in the 10.2.0.4 patchset (timezone)

最新推荐文章于 2024-10-08 17:39:57 发布

congqiang7296

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量164

点赞数

文章标签：数据库 java

主题: Actions for the DSTv4 update in the 10.2.0.4 patchset
文档 ID: 553812.1 类型: BULLETIN
Modified Date: 22-OCT-2008 状态: PUBLISHED

In this Document
Purpose
Scope and Application
Actions for the DSTv4 update in the 10.2.0.4 patchset
     1) Overview of situations where it's NOT needed to perform. any checks on stored TZ data
     1A) when patching from a lower 10.2.0.X release to 10.2.0.4 or upgrading from 10.1.0.X directly to 10.2.0.4
     1B) When upgrading from 9.0.1.x or 9.2.0.x directly to 10.2.0.4
     1C) when upgrading from 8.1.7 directly to 10.2.0.4
     2) If your current timezone version is higher then 4 (typically v6 or v7)
     2A) when applying 10.2.0.4 to a 10.2.0.x version:
     2B) when upgrading directly to 10.2.0.4 from 9.0.1.x, 9.2.0.x or 10.1.0.x .
     3) If your current timezone version is lower then 4.
     3A) when upgrading from 9.0.1.x or 9.2.0.x:
     3B) when upgrading from 10.1.0.x or updating a lower 10.2.0.X version to 10.2.0.4.
     4) how to see if (user) TZ data may be affected
     6) Do I also need to patch clients?
     7) What if I want to apply the V7 DST patch during the upgrade but are currently not on DSTv7 ?
References

--------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.4
Information in this document applies to any platform.
Oracle RDBMS generic patchset 10.2.0.4
Purpose
To provide a step by step flow see if the DSTv4 (USA 2007) update included in the 10.2.0.4 patchset has implications on your database.

Oracle has 2 datatypes that may have data stored affected by a update of the RDBMS DST defintions, those are TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ).
There is other functionality who use the DST definitions, but this is not affecting directly stored information.

Note that "sysdate" and the "date" datatype do not use the DST definitions provided by Oracle, they rely pure on the Operating System.

The Oracle Java Virtual Machine (OJVM) has also DST information, but has no internal datatypes who store that information.

Scope and Application
These steps should be followed before any Update of 10.2.0.x to the 10.2.0.4 patchset or before any Upgrade directly to 10.2.0.4.

The goal is to see if you
a) have stored information that is affected
b) you are already using a DST version higher then DSTv4 and what then to do.

Used abbreviations:

TZ: TimeZone
TSTZ: TimeStamp with Time Zone
TSLTZ: TimeStamp with Local Time Zone
OJVM: Oracle Java Virtual Machine

Actions for the DSTv4 update in the 10.2.0.4 patchset
1) Overview of situations where it's NOT needed to perform. any checks on stored TZ data
Note: you need to check this before applying or upgrading to 10.2.0.4 !

1A) when patching from a lower 10.2.0.X release to 10.2.0.4 or upgrading from 10.1.0.X directly to 10.2.0.4
Check the current version of the Oracle time zone definitions

SQL> conn / as sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
4
If this reports a version 4 then there is no action to take for the Oracle time zone definitions, apply 10.2.0.4 or upgrade from 10.1 to 10.2.0.4 without any action on TSLTZ or TSTZ data. Follow the upgrade or patchset apply instructions, you can skip any DST related sections.

If this reports a timezone version higher then 4 please see point 2) in this note.
If this reports a timezone version lower then 4 please see point 3) in this note.

1B) When upgrading from 9.0.1.x or 9.2.0.x directly to 10.2.0.4
Check the current version of the Oracle time zone definitions in 9i.

In 9i there is no direct way to see the version of the installed Oracle time zone definitions, use utltzver.sql for this.

1) Installing utltzver.sql

Download and unzip utltzver.sql by clicking on the link, the location of utltzver.sql is not critical, we suggest however to save it in $ORACLE_HOME/rdbms/admin .

2) running utltzver.sql

SQL> conn / as sysdba
Connected.
SQL> @c:\utltzver.sql
Your current timezone version is 4!
If utltzver.sql reports version 4 then there is no action to take for the Oracle time zone definitions , you can upgrade from 9.0.1.x / 9.2.0.x to 10.2.0.4 without any action on TSLTZ or TSTZ data. Follow the upgrade instructions, you can skip any DST related sections.

If utltzver.sql reports a timezone version higher then 4 please see point 2) in this note.
If utltzver.sql reports a timezone version lower then 4 please see point 3) in this note.

note: with DSTv7 applied you see
SQL> @c:\utltzver.sql
Your current timezone version is 7 (or higher) !
check Note 412160.1 to see if there is DST version higher then 7

If you just downloaded utltzver.sql there is no need to check.

1C) when upgrading from 8.1.7 directly to 10.2.0.4
8.1.7 has no timezone data stored, so there is no action to take for the Oracle time zone definitions, upgrade from 8.1.7.4 to 10.2.0.4, you can skip any DST related sections.

2) If your current timezone version is higher then 4 (typically v6 or v7)
2A) when applying 10.2.0.4 to a 10.2.0.x version:
If your current time timezone version if higher then 4 then you will need to apply the same timezone version as found in step 1) after installing the10.2.0.4 patch to the 10.2.0.x software or in other words, you need to re-apply the same RDBMS DST patch before opening the database in the updated software home.

If you also need to patch the OJVM then we suggest to apply the DSTv7 OJVM fix for 10.2.0.4, even if your current RDBMS DST patch is DSTv6. If you want to update the RDBMS DST version from DSTv6 to DSTv7 then see point 7 in this note.
When applying the OJVM DST patch check that your OJVM is correctly functioning before upgrading using Note 397770.1 How to Correctly Check if the Oracle JVM is Installed in the Database.

We suggest to advance to the next section in the 10.2.0.4 patch instructions (typically section 7.6) and to apply the RDBMS DST patch DSTv7 (or DSTv6) and OJVM DSTv7 patch right after installing the 10.2.0.4 patch software (section "Installation Tasks" in the Patchset Readme).

If there is no RDBMS DSTv7 10.2.0.4 patch then you can use any 10.2.0.x DSTv7 patch (for the platform. you use) and apply it manually.

You CANNOT use Database TZ patches on OTHER platforms ( use for example a 10.2.0.2 RDBMS TZ patch for Solaris for a 10.2.0.2 on AIX) but you CAN install manually a Database TZ patch for the same base release on the SAME platform. So, you can use for example for 10.2.0.1 to 10.2.0.4 the 10.2.0.2 patch *for the same platform* manually.
* This is NOT true for OJVM DST patches!
* This may not be true anymore for newer DST patches, only do this when this is explictly stated for a certain DST version, for any DST version up to DSTv7 (and inluded DSTv7) this is possible.

All DSTv6 RDBMS patches are found here http://updates.oracle.com/download/6113507.html
All DSTv7 RDBMS patches are found here http://updates.oracle.com/download/6672979.html
The 10.2.0.4 DSTv7 OJVM patch can be found here http://updates.oracle.com/download/6708377.html

The manual process is (example using 10.2.0.2 (or 10.2.0.3) RDBMS DSTv7 patch):

2Aa) Install the 10.2.0.4 patchset software (section "Installation Tasks" in the Patchset Readme).
2Ab) Download the 6672979 patch for 10.2.0.2 (or 10.2.0.3) and your platform.
2Ac) Unzip the 6672979 patch.
2Ad) Stop all instances using this Oracle_Home.
2Ae) Make a backup of the 2 *.dat files in your $ORACLE_HOME/oracore/zoneinfo directory and remove them.
2Af) Copy the 2 *.dat files and the readme.txt from the unzipped patch 6672979/files/oracore/zoneinfo directory to your $ORACLE_HOME/oracore/zoneinfo directory.
2Ag) If needed, apply the DSTv7 OJVM patch for 10.2.0.4, after applying the OJVM DST patch start all databases in this oracle home (single instance for RAC) and follow the OJVM DST patch readme instructions for each database.
2Ah) Continue with the patchset instructions section "Postinstallation Tasks", you can skip any DST related sections.

2B) when upgrading directly to 10.2.0.4 from 9.0.1.x, 9.2.0.x or 10.1.0.x .
If your current time timezone version if higher then 4 then you will need to re-apply the same RDBMS DST version as found in step 1) after installing the10.2.0.4 patch on top of the 10.2.0.1 software, or in other words, you need to re-apply the same RDBMS DST patch before opening the database in the new software home.

We suggest to apply the RDBMS DSTv7 or DSTv6 patch after installing the 10.2.0.4 software with the Oracle Universal installer.

If you also need to patch the OJVM then we suggest to apply the DSTv7 OJVM fix for 10.2.0.4, even if your current RDBMS DST patch is DSTv6. This is best done right after installing the RDBMS DST patch, no need to follow the OJVM DST patch readme instructions as there is no database yet.

If you want to update the RDBMS DST version from DSTv6 to DSTv7 then see point 7 in this note.

If there are no RDBMS DSTv7 10.2.0.4 patches then you can use any 10.2.0.x DSTv7 patch (for platform. you use) and apply it manually.

The manual process is (example using DSTv7 patch):

2Ba) Install the 10.2.0.1 base release software.
2Bb) Install the 10.2.0.4 patchset software.
2Bc) Download the 6672979 patch for 10.2.0.2 (or 10.2.0.3) and your platform.
2Bd) Unzip the 6672979 patch.
2Be) Make a backup of the 2 *.dat files in your $ORACLE_HOME/oracore/zoneinfo
directory and remove them.
2Bf) Copy the 2 *.dat files and the readme.txt from the unzipped patch 6672979/files/oracore/zoneinfo directory to your $ORACLE_HOME/oracore/zoneinfo directory.
2Bg) Apply the DSTv7 OJVM patch for 10.2.0.4, no need to follow the readme instructions as there is no database yet.
2Bh) Start the upgrade instructions, you can skip any DST related sections.

3) If your current timezone version is lower then 4.
3A) when upgrading from 9.0.1.x or 9.2.0.x:
check if you are storing TZ (TSTZ and TSLTZ) data use this sql query:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/
If this gives no rows then there is no action to take for the Oracle time zone definitions, upgrade from 9.0.1.x or 9.2.0.x to 10.2.0.4 , you can skip any DST related sections in the upgrade instructions.

If this returns rows you will need to check further , goto point 4)

3B) when upgrading from 10.1.0.x or updating a lower 10.2.0.X version to 10.2.0.4.

Check if you are storing *user* TZ (TSTZ and TSLTZ) data use this sql query:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/
Check if there is something outside the Data Dictionary (= other then SYS objects).

A full overview of the Data Dictionary tables returned in 10g / 11g is found in: Note 402614.1 Time Zone Data in the Data Dictionary During a Time Zone File Update

For the sys objects, check if you are using DBMS_SCHEDULER for scheduler$_job and so on.

Note: DBMS_JOBS uses "sysdate" and has no TZ data stored. DBMS_SCHEDULER is a new/different way to schedule jobs in 10g.

These are the standard Oracle provided jobs and schedules for a 10g database:

SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_window);

OBJECT_NAME
--------------------------------------------------------------------------------

WEEKNIGHT_WINDOW
WEEKEND_WINDOW

SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_job);

OBJECT_NAME
--------------------------------------------------------------------------------

PURGE_LOG
FGR$AUTOPURGE_JOB
GATHER_STATS_JOB
AUTO_SPACE_ADVISOR_JOB
RLM$EVTCLEANUP
RLM$SCHDNEGACTION

6 rows selected.

If you have ORACLE_OCM installed in 10g you will also see
MGMT_CONFIG_JOB and MGMT_STATS_CONFIG_JOB jobs, bringing the total to 8.

The standard oracle provided 10g usage of TZ data in DBMS_SCHEDULER is not critical, the Oracle provided jobs don't depend on being executed on a exact hour.

You can have basically 4 situations:

3Ba) If you have no user TZ (TSTZ and/or TSLTZ) data and only the Oracle provided jobs in DBMS_SCHEDULER jobs defined then there is no action to take for the Oracle time zone definitions, upgrade from 10.1.0.x or patch 10.2.0.x to 10.2.0.4, you can skip any DST related sections in the upgrade or patch instructions.
Just do the "ORA-01882" check in the box below after you are on 10.2.0.4. No need to run utltzpv4.sql (=do point 4).

3Bb) If you have user TZ (TSTZ and/or TSLTZ) data and only the Oracle provided jobs in DBMS_SCHEDULER jobs, you can ignore any SYS.scheduler$% columns reported by utltzpv4.sql, just do the "ORA-01882" check in the box below after you are on 10.2.0.4.You need to go to point 4) however to check any user data.

3Bc) If you have no user TZ (TSTZ and/or TSLTZ) data but you have your own DBMS_SCHEDULER jobs defined, and they are time critical, then we suggest to run utltzpv4.sql (see point 4) and see if any rows rows in SYS.SCHEDULER$% are listed. If no rows are found by utltzpv4.sql, there is no action to take on any job. If rows are reported in SYS.SCHEDULER$% then drop your jobs if they are time critical before upgrading to 10.2.0.4 and resubmit them after the update/upgrade is finished. The Oracle provided jobs can be left alone, no need to recreate them. Just do the "ORA-01882" check in the box below after you are on 10.2.0.4 if utltzpv4.sql reports SYS.SCHEDULER$% data.In this case there is no other action (besides maybe dropping and recreating your own DBMS_SCHEDULER jobs and do the 1882 check) to take for the Oracle time zone definitions upgrade from 10.1.0.x or 10.2.0.x to 10.2.0.4, you can skip any DST related sections in the upgrade or patch instructions.

3Bd) If you have user TZ (TSTZ and/or TSLTZ) data and your own DBMS_SCHEDULER jobs, and they are time critical, we suggest to run utltzpv4.sql (point 4) and see if any rows rows in SYS.SCHEDULER$% are listed. If not, then there is no action to take on any job. If rows are reported, drop your own jobs before upgrading to 10.2.0.4 and resubmit them after the update/upgrade is finished. The Oracle provided jobs can be left alone, no need to recreate them, just do the "ORA-01882" check in the box below after you are on 10.2.0.4. Go in any case to point 4) and run utltzpv4.sql to see if you need to take action on user data.

If you have only the standard jobs (or non time critical own jobs) and after upgrading to 10.2.0.4 a "select * from dba_scheduler_jobs;" from the sqlplus found in the oracle_home on the server (!!! this is important !!!) gives "ORA-01882: timezone region %s not found" then you need to run Fix1882.sql mentioned in Note 414590.1. This will not harm the database.

If you do NOT have a ORA-01882 when using the sqlplus found in the oracle_home on the server but you DO have it when connecting from a remote client then it means the remote client has not been updated to DSTv3 or higher. In that case running the script. will NOT solve the issue, you then need to apply the RDBMS DST V3 or higher patch to the client to solve the error. For windows clients see Note 417893.1 How To Apply The V4 DST Patches To Windows Clients or Servers

4) how to see if (user) TZ data may be affected
Note: this is only possible if your current DST version is v3 or lower, if it's V4 or higher then you missed a point in this note

Oracle has 2 kinds of TZ datatypes: TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ).

If you have user data that uses TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) format then this needs to be checked manually. There is no automated way to discover affected data.
Oracle uses by default no TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) in the datadictionary.
The TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) is however not often used.

You can use this query to find if you have doubts, it will return all TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns.
select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%LOCAL TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/

For TIMESTAMP WITH TIME ZONE (TSTZ) data types you need to run utltzpv4.sql to see if you have affected data. Any affected data needs to be saved as plain text (Varchar2) before applying 10.2.0.4 or upgrading to 10.2.0.4 and restored afterwards.

1) Installing utltzpv4.sql

Download and unzip utltzpv4.sql by clicking on the link, the location of utltzpv4.sql is not critical, we suggest however to save it in $ORACLE_HOME/rdbms/admin .

2) running utltzpv4.sql

SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utltzpv4.sql
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Your current timezone version is 3!
.
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
data is affected by version 4 transition rules.
.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.

PL/SQL procedure successfully completed.

After running utltzpv4.sql you can then do:

SQL> select * from sys.sys_tzuv2_temptab;

no rows selected

If a "select * from sys.sys_tzuv2_temptab;" gives no rows then there is no action to take for the Oracle time zone definitions, upgrade from 9i / 10.1 to 10.2.0.4 or apply 10.2.0.4 to a 10.2.0.X version.
You can skip any DST related sections in the upgrade documents or Patchset instructions..

If a "select * from sys.sys_tzuv2_temptab;" give(s) affected columns then you need to back up the reported columns before upgrading to 10.2.0.4. See point 5) on how to do this. For SYS.SCHEDULER$% rows you need to drop and re-submit the jobs (see previous point).

5) How to backup affected TSTZ data.
The "NES" (nested_tab) column of sys_tzuv2_temptab is not populated for regular tables, if this colum contains a "YES" then it means that you have nested tables in your database who use a TIMESTAMP WITH TIME ZONE datatype.
Utltzpv4.sql cannot scan these for affected timezones, it will report any nested table using the TSTZ datatype. You will need to check the nested table definition and see on what this is based.

For the regular tables you can use the approach described and the scripts provided in
Note 550739.1 Usage of utltzuv7.sql before updating time zone files
points
5) Manually performing the backup and restore
and
6) Automating the backup and restore

Backup the affected data before upgrading the database and restore affected data after following the "Postinstallation Tasks" section of the 10.2.0.4 patchset readme note. In other words, restore the affected data after the database is fully upgraded to 10.2.0.4.
6) Do I also need to patch clients?
No, not by default. As long as you are not using DST functionality (store user TZ data or use TZ functions in your application(s) ) there is no need to patch clients. An unpatched client can connect perfectly to a server with a higher DST version.

If you are storing user TZ data and you are not on DSTv4 (or higher) on your clients than we advice patching your clients, but that should already have been done.

If your current TZ version is 4, applying 10.2.0.4 will not change anything, your clients should already be at DSTv4 or not, depending on whether you are using TZ functions and TZ datatypes or not.

If your current TZ version is lower than v4 before applying 10.2.0.4, then it depends if you are using TZ functions and TZ datatypes for user data or not.
If you have no user TZ data but you plan to use the DBMS_SCHEDULER then we advice patching DBA clients with the RDBMS DSTv4 patch.

If your current TZ version is higher than DSTv4 then you have most likely already patched affected clients.
If not please see the related documentation for that DST version.

For links to all DST versions (and more detailed information) please see
Note 412160.1 Updated Time Zones in Oracle Time Zone File patches

7) What if I want to apply the V7 DST patch during the upgrade but are currently not on DSTv7 ?
The steps then are:

Follow steps 1 and 2 in the advisory for DSTv7 Note 549825.1 DSTv7 update (Australia 2008 and more) and the impact on the Oracle Database.
Backup any data that may be affected.
Check that your OJVM is correctly functioning using Note 397770.1 How to Correctly Check if the Oracle JVM is Installed in the Database.
Install the 10.2.0.1 software (if needed) and then the 10.2.0.4 software
Follow the Patchset or upgrade instructions, you can skip any DST related sections in the instructions.
Apply the DSTv7 RDBMS and OJVM patches after the upgrade to 10.2.0.4 (follow the readme for the OJVM DSTv7 patch).
Restore any affected data if needed

Change History

Date

Action

22 oct 2008

Added warning for manual apply of RDBMS dst patches > v7

10 jul 2008

rewrote part of section 3b based on customer feedback

08 Jul 2008

Provided utltzpv4.sql as a zip file for easier download, spelling corrections.

21 may 2008

Provided utltzver.sql as a zip file for easier download.

20 may 2008