Data Pump: The Time Zone Pitfalls

Photo by Luis Cortes on Unsplash

Last week a very experienced colleague called me. He had issues with an export dump taken from an Oracle 20c database importing into 19c. It failed. So this blog post is about Data Pump: The Time Zone Pitfalls.

The Case

Usually you will see this issue only when you try to export from a higher version, and then attempt to import into a lower one. But the same thing can happen when you patched your databases partially with a newer time zone patch regardless of the version.

My colleague saw this error:

impdp system/welcome1@//localhost:1521/MYDB directory=MYDIR dumpfile=mydumop.dmp full=yes NOLOGFILE=YES 

Import: Release 19.0.0.0.0 - Production on Wed May 8 14:17:10 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 33 into a target database with TSTZ version 32.

Data Pump clearly tells here what the issue is: The export has been taken from a database with a higher time zone version. It denies the import into a lower time zone version database.

Why is this?

As I pointed out here a long time ago (Full Transportable Export/Import – Things to Know), time zone does not allow or offer any sort of downgrade option. My colleague exclaimed: “But I don’t have any time zone data in that file!”. And he may be right. But Data Pump does not know. And it would need now to scan an entire dump file in order to check if there’s any offending time zone data in the dump. This may take a long time. And hence, it has been implemented to deny such attempts.

The same thing happens of course with Transportable Tablespaces.

And – as you can see here – with a conventional expdp/impdp as well.

Any Workarounds?

Yes, of course, you have options to workaround this. First of all, you could patch the target database with the same time zone patch. But in this particular case, as Oracle 20c gets delivered at the moment internally with DST V.33, I found out that there is no DST V.33 for Oracle 19.3.0 available. One of you will have to request it please. There seems to be no automatic process to deliver a time zone patch for a new release such as 19c. And as some of you know already, the initial drop of Time Zone Patches was created as “Release Update” dependent – which was a crucial mistake.

The problem with this solution: You need to have the patch. And your target database will see downtime. Not a simple solution as you can see.

Another workaround would be to create a database in the source environment you’d like to export from with the matching time zone version so you won’t have that issue with the import afterwards anymore: Create a Database with Non-Default Time Zone. Then copy your data over via database links.

Summary

Make sure you keep time zone settings consistent across your database landscape. If you don’t pay attention during creation, you may end up in this situation where you have to deal with workarounds as Data Pump rejects such attempts. But this is not a Data Pump fault – it is a matter that you can’t downgrade time zone data.

Additional information. April 11, 2022

Since Connor asked me today an interesting question whether the above is still true, we’ve had Bill check with engineering. And this is the answer which I think is worth to be shared with you:

Further Information

• MOS Note: 1574426.1 – Steps tor RDBMS DST to Be Able to Downgrade From 11.2.0.4 , 11.2.0.3 , 11.2.0.2 or 11.2.0.1 Again to the Older Oracle RDBMS Version
• MOS Note: 412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches

–Mike