Some time ago one of our providers sent us a datapump dump file to load in one of our databases, we proceed as usual but when it was time to do the real import this error appeared:
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32
Ups, what happened? After a brief googling we found the cause of the issue, the origin database uses a timezone version newer than the one used on our local database, and thus impdp avoids loading the data, ok… what does this mean?
What is the timezone version, what do the timezone files contain and why do we have to upgrade them?
The modification of dates for Daylight Saving Time (or other causes) on different countries are tracked on a public domain database backed by the ICANN. To stay up to date with the latest changes, you must upgrade the timezone information from time to time. This information allows you to assure that a certain date/hour has existed or is presumable that exists on the future.
The Oracle database stores this information on timezone files and uses them to check what dates/times are allowed (which dates and times exist on a certain year and location). These timezone files are updated from time to time due to changes decided by each country. Also note that this files are independent of database versions.
On the Oracle documentation we can find the explanation and some examples of problems issued in case of migrating data from a database with the old timezone version to one with a newer one. Basically, if you are unlucky, you can find yourself in the position where:
- An existing time becomes a non-existing (invalid) time
- A time becomes duplicated
- A duplicated time becomes a non-duplicated time.
And due to previous changes it is also possible that the execution order for some database jobs change.
Ok, but we need to import the data so what can we do?
Basically we have two options:
Request the provider for the same data in the old “exp/imp” format (in case none of the contents of the data exported avoid this). It is a less friendly format, specially in case you need to do some transformations during import, but these tools don’t “care” for TSTZ versions and don’t complain. If the provider cannot resend you the data, it is possible to do it yourself, creating a temporary database with the necessary timezone to import with impdp and export with exp to obtain the dump in the old format.
The other option is to upgrade the timezone on the destination database. This implies some downtime (proportional to the number of tables with dates with timezone columns), and might cause trouble if you send information via expdp to others that haven’t made this upgrade. On the internet you can find lots of sites explaining the procedure to change the timezone, but it is also well explained on official documentation.
Regards
True False Null 