Oracle: Hacking Timezone

Product: Oracle Database
Version: 10.x - 12.x

Oracle release DST patch yearly or more frequent.  DBA needs to install the DST patch especially if it is affecting their city.  Some DBA do not install it for good reason, e.g. the DST change is not affecting their city, their software is not using timezone, they want to use old DST format.  Some DBA are ignorant, e.g. it is not PROD env, overlooked, overloaded.

Last year, 2014, there are several major countries have DST change, which causing Oracle database to return wrong time:
Cairo
Fiji
Jamica
Chile
Egypt
Russia

DBA will need to have access to Oracle Support to download the latest DST patch for their specific database, which I am not going to cover here.  DST patch is cumulative, which means if you install v24 over v14, then it will covers all the DST changes.

This post mainly to cover how to identify current DST version, in order to know whether it is responsible to returning wrong time.

Run following SQL, and determine whether all the time are consistent

Alter session set time_zone = 'Europe/Moscow'; -- This is one of the city change to no DST in 2014

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') "sysDate",

  TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') "sysTimestamp",

  TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI')  "curDate",

  TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') "CurTimestamp,

  TO_CHAR(LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR')  "LocalTimestamp,

  DBTIMEZONE ,

  SESSIONTIMEZONE

FROM DUAL;

Following SQL will tell you whether which version of DST patch you are using.  Oracle 12.1.0.2 delivered with v14, which covers up to 2012 DST change, if I'm not mistaken.  The lastest DST version as of Jan 2014 is v24

select filename, version, con_id from v$TIMEZONE_FILE;

If you have access to Oracle database server file system, then you can check following:

Location of timezone file: $ORACLE_HOME/oracore/zoneinfo/

Search for the desire city with DST change, such as Cairo or Moscow, using following command.  You can confirm whether it is missing 2014 DST change, and know that this database is using older DST patch (or no patch):

$ grep Moscow $ORACLE_HOME/oracore/zoneinfo/timezdif.csv

--- Following is the output based on OOTB Oracle 12.1.0.2 which is shown that it is missing 2014 entry

17, Europe/Moscow, 2011,

12, Europe/Moscow, 1919, 1919

11, Europe/Moscow, 1879, 1919

9, Europe/Moscow, 2021,

There is a zoneinfo/readme.txt with will tell the DST version.  Oracle 12.1.0.2 comes with v14, which is 10 version and few years behind now.

You can also do a ls or dir in this directory to find out how many .dat files there.  v14 will have 14 dat binary files, while v24 should have 24 .dat binary files.

If you are running Oracle database 10.x and older, and you don't have access to Oracle Support, you can manually modify the timezone text file.  After Oracle 11.x, Oracle provides binary timezone.dat file which can't be modified.

See https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm for detail instruction

Please note that OS (Windows, UNIX), Java (JRE or JDK), firmware (Cisco route, firewall, load balancer, security system) will required to install the latest DST patch as well to ensure.  Otherwise, you might be unable to enter the door using your swipe access card, or server failed to startup due to inconsistent time check mechanism build into the application