Daylight Saving Time (DST) - USA - 2007
Introduction: As per the Energy Policy Act of 2005, DST will start from 2 a.m. on the 2nd Sunday in March and end at 2 a.m. on First Sunday in November [Earlier it used to start at 2 a.m. 1st Sunday in April and end at 2 a.m. Last Sunday in October].
Spring Forward: On 2nd Sunday in March, clock will leap ahead from 1:59 a.m. to 3:00 a.m.
Fall Back: On 1st Sunday in November, clock will move back from 1:59 a.m. to 1:00 a.m.
Countries Affected: Canada and Bermuda are making similar changes to be consistent with USA time changes. Computers and applications running in other countries (who don't follow DST) having associated with computers and applications running in the DST implemented counties could also be affected.
Operating System: Once the required patch is applied, the system clock will automatically adjust its system time at the beginning and end of DST. Any dependent applications running on the OS will obtain the system time from the system clock.
Microsoft Daylight Saving Time: Help and Support Center
How it affects the Oracle Database?: Time zone and DST are used in Oracle database at the below two areas. Hence 2 patches are available, 1 for Time zone files and other for OJVM.
[1] Time Zone: Applicable only for Oracle 9.x and 10.x. There are two datatypes TIMESTAMP WITH LOCAL TIME ZONE [TSLTZ] and TIMESTAMP WITH TIME ZONE [TSTZ] and a function TZ_OFFSET which obtains their time-zone information from Oracle's time-zone file which comes as a part of Oracle database installation.
[1a] Database Server Side:
If your database uses any of the above (datatypes or/and functions) and uses the affected time zones, then patch is required, otherwise patch is not necessary. A script is available to assess the use of time zone in the database.
[1b] Client Side: If all the below conditions are met, then time zone file install is required:-
- connects to the affected database
- time zone data type used by client
- client uses the affected time zone
Refer Below Metalink documents:-
- Effects on client and middle-tier of applying time zone patches on the Oracle Database Note:396426.1
- Workarounds when Database time zone patches are not available for your patchset Note:396387.1
[2] Oracle Java Virtual Machine (OJVM) uses time-zone information which is stored in its database. Need to install the patch only on the database side if OJVM is installed, applicable for Oracle database versions from 8.1.7.4 to 10.2.0.3.
Here is a typical (9.2) query to check whether OJVM is installed in your database:-
| column owner format a10 SELECT object_type, owner, status, COUNT(*) FROM dba_objects WHERE object_type LIKE '%JAVA%' GROUP BY object_type,owner,status; OBJECT_TYPE OWNER STATUS COUNT(*) ------------------ ---------- ------- ---------- JAVA DATA SYS VALID 288 JAVA CLASS SYS VALID 9080 JAVA CLASS SYS INVALID 1 JAVA CLASS ORDSYS VALID 870 JAVA CLASS ORDSYS INVALID 12 JAVA SOURCE SYS VALID 8 JAVA SOURCE ORDSYS VALID 8 JAVA RESOURCE SYS VALID 180 JAVA RESOURCE ORDSYS VALID 16 9 rows selected |
Refer to Metalink Note:397770.1 [How to Correctly Check if the Oracle JVM is Installed in the Database].
[3] Here are the things that you got to do:-
[3a] Backup your client and server.
[3b] Run re_tz_views.sql [Refer to Metalink Note:412971.1 - SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a
Database]
| @cre_tz_views.sql spool cre_tz_views_output.txt select * from TZ$TSLTZ_TAB_COLS; spool off drop function TZ$NAMED_TSTZ_VW_COLS_FN ; |
| DBTIMEZONE ---------- -04:00 USAGE_TYPE VALUE DESCRIPTION --------------------------- ---------- ------------------------------------------------------------------------ DBTIMEZONE -04:00 Database Time Zone, if this is an 'offset' then not affected by DST NAMED_TSTZ_TABLE_USE_NONSYS 0 Number of TSTZ columns using named time zones in tables not owned by SYS NAMED_TSTZ_TABLE_USE_SYS 0 Number of TSTZ columns using named time zones in tables owned by SYS NAMED_TSTZ_VIEW_USE_NONSYS 0 Number of TSTZ columns using named time zones in views not owned by SYS NAMED_TSTZ_VIEW_USE_SYS 0 Number of TSTZ columns using named time zones in views owned by SYS TSLTZ_TABLE_USE 0 Number of TSLTZ columns used in tables TSLTZ_VIEW_USE 0 Number of TSLTZ columns used in views TZ_ARGUMENTS 0 Number of PL/SQL objects with Time Zone arguments OWNER NAME TYPE LINE TEXT --------------- ------------------------------ ------------ ---------- ------------------------------------------------------------------------------------------- BABU TESTXSP_STORE_DDL PROCEDURE 25 updatedate=systimestamp,RESOURCE_TYPE=IN_RESOURCE_TYPE BABU TESTXSP_STORE_DML PROCEDURE 39 IN_BABU_SERVICE,IN_SERVICE_TYPE,IN_ONLINK,IN_CONTENT_TYPE,systimestamp, IN_RESOURCE_TYPE ); BABU CREATE_ONLINE_DOCUMENT PROCEDURE 13 insert into TEST_TAB (ACTTIME) values (systimestamp); BABU_RW B_UPDATE_APPL_ALERT_UPDATE TRIGGER 5 SELECT SYSTIMESTAMP INTO :NEW.LAST_UPDATE_STARTED FROM dual; BABU_ADMIN B_INSERT_SERVICE_TXT TRIGGER 13 SELECT SYSTIMESTAMP INTO :NEW.TIME_STAMP FROM dual; BABU_ADMIN B_UPDT_WEB_SERVICES_TXT TRIGGER 2 select sysdate into :new.timestamp from dual; BABU_ADMIN B_INSRT_WEB_SERVICES_TXT TRIGGER 2 select sysdate into :new.timestamp from dual; BABU_ADMIN PKG_SERVICE PACKAGE BODY 18 || 'TO_TIMESTAMP(''' || start_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' || ' AND ' BABU_ADMIN PKG_SERVICE PACKAGE BODY 19 || 'TO_TIMESTAMP(''' || end_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' |
[3c] Identify the correct "utltzuv2.sql" [this script only checks stored TIMESTAMP WITH TIME ZONE data in the database, but NOT the TIMESTAMP WITH LOCAL TIME ZONE data], download it [Refer Table 1 in Metalink Note:359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database] and run the "utltzuv2.sql" to check the if TSTZ datatype is used in any tables in the database.
For 9.2.0.7, download Patch and install patch 5548107 (also to get the utltzuv2.sql script).
Refer to "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note:396670.1"
| SQL> @utltzuv2.sql You current timezone version is 1! Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by version 3 transition rules PL/SQL procedure successfully completed. |
[3d] Save TSTZ data as varchar2.
[3e] Apply patch [Refer Table 2 and 3 in Metalink Note:359145.1
- Impact of 2007 USA daylight saving changes on the Oracle database]
[For 9.2.0.7 DB TimeZone, use Patch bundle 15 (bug 5654905) or later]
[For 9.2.0.7 OJVM, use JVM Timezone Patch 5047902]
[3f] Restore back to TSTZ datatype
[3g] Apply fixes to the clients and middle-tiers.
[3h] Work on already existing TSLTZ data.
[3i] Apply patch for OJVM.
References:
1. Impact of 2007 USA daylight saving changes on the Oracle database - Note:359145.1
2. How to Determine Whether Time Zone Changes Will Affect Your Database - Note:406410.1
3. SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database - Note:412971.1
4. USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products Note:397281.1
5. Timestamps & time zones - Frequently Asked Questions Note:340512.1
Click here for a step-by-step documentation on "Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines"
| Author: | Babu Rangasamy |
| Title: | GIS and Database Consultant |
| Created Date: | February 8, 2007 |
| Contact: | Babu.Rangasamy[at]gmail.com |
