Hi
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.
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 or WMSYS 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
Regards
Uday
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.