Identifying And Adjusting Incorrect Data After Upgrading Database Time Zone Files Without Using Correct Pre- And Post-Install Actions

	Doc ID:	415912.1	Type:	BULLETIN
	Modified Date :	04-JAN-2008	Status:	PUBLISHED

In this Document
  Purpose
  Scope and Application
     Affected products
  Identifying And Adjusting Incorrect Data After Upgrading Database Time Zone Files Without Using Correct Pre- And Post-Install Actions
     Impact on stored data of updating time zone files
     Which data is impacted?
     Old vs New data
     Scheduler data vs. User data
     Using the TZDetect.sql script
     Determining how long TZDetect will take to run
     ORA-1882 when running TZDetect.sql
     ORA-942 when running TZDetect.sql
     Dealing with Scheduler Data reported in TZDetect output
     Dealing with User Data reported in TZDetect output
     The TZDetect.sql script
     Change Log
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2 to 10.2
Oracle Server - Standard Edition - Version: 9.2 to 10.2
Information in this document applies to any platform.

Purpose

This note should only be used if you have applied updated Oracle time zone files on a database, and did not follow the correct pre- and post-install actions.

Time zone files can be installed as a one-off patch, or as part of a patchset or a Windows patch-bundle. If you applied these time zone files in any of these ways, and used the utltzuv2.sql script to check data and subsequently used the results to save and corrected this, then all your data is fine and there is no need to use this note.

This note discusses the effects of not using these pre- and post-install steps, and provides the script TZDetect.sql to identify any data which might be incorrect at this moment.

NOTE: It is impossible to automate the process of finding the incorrect data completely.
If the script in this note finds no data, then there is no further cause for alarm.
If the script in this note does find data, then this is very likely to contain "false positives". In this case you are advised to read this note carefully, so that the results of the script can be interpreted in the right way.

Scope and Application

There are 4 versions of the Oracle time zone files:

• Version 1 ships with the Oracle9 database
• Version 2 ships with the Oracle10 database
• Version 3 can be installed through one-off patch 4689959 on both Oracle 9 and 10.
  Also included in the 10.2.0.3 patchset.
  Also included in Windows patch bundles: 9.2.0.7 bundle 13 to 15 , 9.2.0.8 bundle 2 to 4, 10.1.0.5 bundle 9 to 11, 10.2.0.2 bundle 9 to 13
• Version 4 can be installed through one-off patch 5632264 on both Oracle9 and 10.
  Will also be included in the 10.1.0.6 and 10.2.0.4 patchsets (neither are available at this moment).
  Also included in Windows patch bundles: 9.2.0.8 bundle 5 and higher, 10.1.0.5 bundle 12 and higher, 10.2.0.3 bundle 1 and higher.
  

This note discusses the actions you can take to investigate your data if you have applied version-3 or version-4 time zone files through any of these means, without using the correct pre- and post-install actions.

Affected products

In order to be affected by these issues you have to use TIMESTAMP WITH TIME ZONE data in the database.

• 10g customers may have Scheduled jobs data which may be affected but a) jobs scheduled AFTER the installation of the patch are not affected, and b) only jobs scheduled between 11-Mar-2007 and 1-April-2007 inclusive are affected. They might be scheduled one hour later than they should be. MOST scheduled jobs are Oracle-default performance data collection jobs and so being off one hour is usually of no impact. Only user-defined jobs or Oracle-defined jobs which have had their schedule modified by the customer are potentially affected. This note will discusses how to detect and correct job scheduler data.
• Oracle products which store data in the database are generally NOT affected. Here is a partial list of products NOT affected by this issue:
  • Oracle Application Server
  • Oracle EM Grid Control
  • Content Services and ContentDB
  • Oracle Secure Enterprise Search
  • Oracle E-Business Suite (other than user-added columns)
  • Siebel CRM
  • Peoplesoft Enterprise
  • J.D. Edwards EnterpriseOne
  • Oracle Demantra
• Customers whose applications (3rd party or developed in house) use TIMESTAMP WITH TIMEZONE data may be affected
  • Customers who insert additional timestamps following the installation of the affected software will not easily be able to distinguish correct data from uncorrected data.
  • They should take action to discover if they are in fact affected and take steps to correct their data based on their own analysis of how this affects their application. The provided script below will help them do this.
    
  • We have confirmed that SAP Applications are not affected (other than user-added columns)
    

Identifying And Adjusting Incorrect Data After Upgrading Database Time Zone Files Without Using Correct Pre- And Post-Install Actions

Impact on stored data of updating time zone files

Data with datatype TIMESTAMP WITH TIME ZONE (TSTZ) is stored in the database in the UTC time zone. All TSTZ data is converted to and from UTC when it is inserted or retrieved.
The conversions are done based on the current rules known to Oracle, which are stored in the time zone files. If you have stored data in the past (under old rules), for which the DST rules have changed, then this data will be retrieved differently after the time zone files are updated. Therefore this data should be detected before the upgrade, and corrected afterwards in order to store it under the new rules. If this was not done then the remainder of this note will address how to detect this data afterwards and how to adjust it so that it regains the original meaning.

Note that this issue does not affect the DATE datatype in any way.

Which data is impacted?

Data that is impacted by this is TSTZ data which is in the time frames for which DST rules have changed. For example in 2007 under the old rules DST used to start in the USA on April 1st, but under new rules it will start on March 11th. If data was stored between these dates using old rules, then an incorrect conversion to UTC would have meant that the data was stored incorrectly. However, this only becomes very noticeable after the new time zone files are installed. Under the new rules the data is converted to a different timestamp than before. The normal pre- and post-install actions are designed to circumvent this and store the data under the new rules after they are in place. As an example if you stored TIMESTAMP'2007-03-20 14:00:00 US/Eastern' and then applied the new time zone files without pre- and post-install actions, the resulting timestamp would now be '2006-03-20 15:00:00 US/Eastern'.
The affected date ranges are all the periods at the start and end of DST for which the DST rules have changed. Any data outside of these affected date ranges is not affected.

Old vs New data

If data is inserted after the new time zone files are in place, then this data is stored correctly. It is not possible to automatically detect a difference between the "bad old" and "good new" data. For example, if you stored a TIMESTAMP'2007-03-20 15:00:00 US/Eastern' after the new time zone files were in place, then it would be stored in exactly the same way as the data from the previous example.
It is therefore not possible to automatically detect the bad data. The only thing that can be done is to detect data in the affected date ranges. After that it needs to be decided which data is already good, and which data is incorrect and needs to be adjusted.

Scheduler data vs. User data

In Oracle10g the DBMS_SCHEDULER package uses TSTZ data types, potentially with updated time zones. Therefore there is a chance that scheduler data might be affected by these problems.
Oracle can provide more guidance on scheduler data than on "user" data, therefore the results of the TZDetect.sql script are split between User and Scheduler data.

Using the TZDetect.sql script

The result of running TZDetect is split in a number of categories in Oracle10.  In Oracle9 "category 1" is the only relevant and reported category.

• Category 1: Normal user data
• Category 2a: Default scheduled jobs
• Category 2b: User defined scheduled jobs (or defined by other Oracle software)
• Category 3a: Default schedule windows
• Category 3b: User defined schedule windows
• Category 4: Job logging history
• Category 5: Window history

In order to precisely detect if any of the data in the database is affected by time zone changes, the exact differences in time zone rules for the North American changes of 2007 and later are known to the script. The script splits the data found for each of the categories mentioned above:
Data which is not affected by any issues. This can be either data using different time zones, or timestamps for which the DST information has not changed (for example TIMESTAMP'2007-05-05 14:00:00 US/Eastern' is not be affected since this is in DST regardless of the rules that are in place)
Data which falls in the date ranges for which DST information has changed. This data will need to be judged to see if it's "bad old" or "good new" data (see discussion on "Old vs New Data" above):

• If any of these values were inserted into the database before the new time zone files were applied, then they will now be out of sync by 1 hour, and therefore they are incorrectly represented.
• If these values were inserted into the database after the new time zone files were applied, then there is nothing wrong with them and they are represented correctly.
  

In the next sections we will first discuss possible scheduler data that is returned by TZDetect (Category 2 to 5), followed by a discussion of the "user" or "other" data (Category 1).

Determining how long TZDetect will take to run

TZDetect checks all your data of the TIMESTAMP WITH TIME ZONE type. Therefore the total size of your database is unrelated to the amount of work the script does. If you only have scheduler data then it will typically finish inside a minute. If there is a lot of user TSTZ data then this can take longer.

In order to determine how much work the script will have to do you can use the query:

select c.owner, c.table_name, c.column_name

          from dba_tab_cols c, dba_objects o

         where c.data_type like '%WITH TIME ZONE'

           and c.owner=o.owner

           and c.table_name = o.object_name

           and o.object_type = 'TABLE';

The tables owned by SYS in this output will typically only take a matter of seconds to be scanned.


For user tables you can check the number of rows in the reported tables. If the number of rows in these tables is in the 'tens of millions' range, then the script could take a considerable time to finish (10+ minutes), and you are advised not to run this in full production. Alternatively you can run the script in a backup version of the database. As long as the backup was created after the new time zone file patches were applied, and it runs from a Oracle home with the same version time zone files, it can also be used to determine the "old bad" data.

ORA-1882 when running TZDetect.sql

If the TZDetect.sql script fails with a ORA-1882 error it is likely that you hit the issues reported in

Note 414590.1 Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade

You are advised to use the fix1882.sql script from that note to fix those errors, and then continue using this note.

ORA-942 when running TZDetect.sql

When running TZDetect.sql it is normal to receive a number of ORA-942 errors when objects are dropped that do not exist.

Also on Oracle9 it is normal to receive 6 ORA-942 errors at the start of the script during the creation of a number of views (which are not applicable on Oracle9). These are errors like:

 

                                          SCHEDULER$_JOB SJ,

                                          *

ERROR at line 6:

ORA-00942: table or view does not exist

These errors are normal on Oracle9. After these errors there should be no further errors. If you receive any further errors then please download the latest version of the script, and if the problem persists raise a SR with Oracle support.

 

Dealing with Scheduler Data reported in TZDetect output

Category 2a: Default scheduled Jobs


If you have any results in this section then there are standard scheduler jobs which contain timestamps in the affected timeframe. This could be completely normal depending on when this script is running. Usually if you installed the new time zone files more than a week before running this, then all this data should be correct, because all these entries will then have been created under the new rules.





In order to make sure of this you can select from the view AFFECTED_TSTZ_CAT2A which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_JOBS to find more information on the listed jobs.





If there are specific entries here that you feel should be adjusted then please use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to change the parameters of these jobs.





Category 2b: User defined scheduled jobs (or defined by other Oracle software)


If you have any results in this section then there are non-standard scheduler jobs which contain timestamps in the affected timeframe. This could be completely normal depending on when this script is running and depending on when these entries were created. If they were created after the time zone files were installed, then the data should be ok.





In order to make sure of this you can select from the view AFFECTED_TSTZ_CAT2B which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_JOBS to find more information on the listed jobs.





If there are specific entries here that you feel should be adjusted then please use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to change the parameters of these jobs.





Category 3a: Standard schedule windows


If you have any results in this section then there are standard "scheduler windows" which contain timestamps in the affected timeframe. This could be completely normal depending on when this script is running. Usually if you installed the new time zone files more than a week before running this all this data should be correct, because all these entries will then have been created under the new rules.





In order to make sure of this you can select from the view AFFECTED_TSTZ_CAT3A which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_WINDOWS to find more information on the listed windows.





If there are specific entries here that you feel should be adjusted then please use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to change the parameters of these windows.





Category 3b: User defined schedule windows


If you have any results in this section then there are standard "scheduler windows" which contain timestamps in the affected timeframe. This could be completely normal depending on when this script is running and depending on when these entries were created. If they were created after the time zone files were installed, then the data should be ok.





In order to make sure of this you can select from the view AFFECTED_TSTZ_CAT3B which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_WINDOWS to find more information on the listed windows.





If there are specific entries here that you feel should be adjusted then please use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to change the parameters of these windows.





Category 4: Job logging history


If you have results in this section then this is historic data, and if this was created after the new time zone files were applied then the data will be correct.





You can select from the view AFFECTED_TSTZ_CAT4 which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_JOB_LOG to find more information on the listed job history. In DBA_SCHEDULER_JOB_LOG you will find a log date which shows exactly when this entry was added, you can compare this with when the time zone files were applied to have a better idea if this data is correct or not.


Oracle makes no use of this data, so if there is "old bad" data in this category, and you do not care about it then you can just ignore it, or the history can be purged.


If this data is important to you, then the data can be adjusted directly in the SYS.SCHEDULER$_JOB_RUN_DETAILS table to be set to the correct values.





Category 5: Window history


If you have results in this section then this is historic data, and if this was created after the new time zone files were applied then the data will be correct.





You can select from the view AFFECTED_TSTZ_CAT5 which will show you the details of the timestamps in this category. Use DBA_SCHEDULER_WINDOW_LOG to find more information on the listed window history. In DBA_SCHEDULER_WINDOW_LOG you will find a log date which shows exactly when this entry was added, you can compare this with when the time zone files were applied to have a better idea if this data is correct or not.


Oracle makes no use of this data, so if there is "old bad" data in this category, and you do not care about it then you can just ignore it, or the history can be purged.


If this data is important to you, then the data can be adjusted directly in the SYS.SCHEDULER$_WINDOW_DETAILS table to be set to the correct values.

Dealing with User Data reported in TZDetect output

Determining whether User data is currently incorrectly represented


The TZdetect script creates a table AFFECTED_TSTZ for all "User data" (Category 1) which falls in the affected date ranges. Each row in AFFECTED_TSTZ represents a value in the database for which DST information has changed between the old and new time zone files.





It can be hard to determine which data needs to be corrected, and which data is already correct. In general there are a number of methods that can be used. These are, in order of ease of use:

• Do nothing, accept that certain timestamps might be out of sync by an hour. This is a good option if the out-of-sync timestamps are only used for logging purposes and the exact values are not very important. The potential gain of adjusting them will need to be compared with the amount of work involved in that.



• Let the users be in charge: Users often know their data best, especially in applications used for scheduling etc., often the easiest solution to this issue is to tell the users to check their entries, and update them if needed. In that case no further action on the database is needed.



• Determine 'manually' which data has been updated or inserted after the time zone files were upgraded. This data will be correct, so we can concentrate on the rest of the reported data. Any data which is represented in AFFECTED_TSTZ, and which was already present in the database at the time the new time zone files are installed, should be adjusted. It is not possible to find out when a row was last updated automatically, but if there is any data in the same row that makes this possible then that can be used (for example if there is a column that contains a logging date or a sequence number etc.)



• Determine which data has been updated or inserted since the time zone files were upgraded by means of using a backup. If backups are available then these can be used to get to a point just before the time zone files were installed, and the data can then be compared. Any data from AFFECTED_TSTZ which already existed at that moment, needs to be adjusted.
  
  
  The easiest way to do this is:
  
  
  
  
  
  
  
  • Keep the database with the AFFECTED_TSTZ table in tact.
  
  
  
  • Set up another database as the backup, back in time to the moment just before the patch was applied.
  
  
  
  • Create a database link from the "current" database to the "backup" database.
  
  
  
  • Select the exact rows as reported in AFFECTED_TSTZ from the dblink, then for each of these rows we can determine:
    
    
    
    
    
    
    • If the row didn't exist in the backup, then it is newly inserted and therefore it is inserted under the new rules, and it is correct.
    
    
    
    • If the data is different in the 2 databases then it has been updated since the patch was applied, and therefore we can conclude it was stored under the new rules.
    
    
    
    • If the data is still the same, then this is data which needs to be adjusted, because it was data for which the pre and post-install steps had been needed, but were not used in the past.
    
    
    
    
  
  
  
  • Note that in order to use this method the time zone files used by the backup database are not relevant, since the data will come across the database link without being interpreted by the backup database. The data will only be interpreted by the current database. Therefore the "old" and "new" values can be correctly compared in this way.
  
  
  
  

Further Steps for User data

• Adjust the incorrectly stored data in the database to be correct.
  
  There are multiple ways of doing this:
  
  
  
  
  • Identify the data in the tables and update the data directly.
    
    
  
  
  
  • Automate this process by using the AFFECTED_TSTZ table. If you decide on this step then the next action is to remove all rows from the AFFECTED_TSTZ table which are correct, so that the only rows left in this table represent values in the database that need to be adjusted. After this you can contact Oracle Support to get access to the script TZAdjust.sql from non-published internal note 415913.1, which will adjust the data represented in the AFFECTED_TSTZ table.
    
    
    
  
  
  
  



• Go "back" to a point in time before the time zone files were upgraded, and re-do the upgrade by using the correct pre- and post-install steps. In certain scenarios it might be possible to then use this backup as a "logical standby database", in order to "re-play" the actions that have happened since then, and bring the database back up to the "current" level. If these scenarios are needed then please raise a Service Request with Oracle Support to assist with this.
  
  

The TZDetect.sql script

To use please download TZDetect.sql.

Change Log

2007-03-06: Uploaded TZDetect.sql version 1.0.2, which resolves some problems when running the script on Oracle9.