Data Pump: The Time Zone Pitfalls

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:

At first a big bold recommendation
    1. Agree on a common timezone version to use and always upgrade them at the same time in your environments
    2. Unfortunately, there is currently no way to know what timezone version is associated with a dumpfile except for starting an import operation. It will fail almost immediately if there’s a timezone mismatch we don’t support. You are correct that the information is in the master table, and the master table from the export is stored in the dump file set, but there’s no master table to query until you start the import.
How import w.r.t timezone works in 21c and later.
As long as the target database for any type of import has the same or a newer timezone version than the source database, the import can proceed. It also doesn’t matter what older versions are being imported or how many imports are done concurrently or even if the target database is in the midst of a timezone upgrade. It will all work.

How import w.r.t timezone works in 19c and earlier.
The 21c functionality was backported to 19c cloud variants but NOT to the RUs. If you are running on 19c RUs, there is no change to what I described above. The same rule is true as far as the target has to be the same or newer than the source database to proceed. However, they may be limited to what versions can be imported concurrently if there are multiple source database timezone versions. With the old method, there is the primary timezone version of the target and a single secondary version at any given time.

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

Share this: