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.
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 188.8.131.52.0 - Production on Wed May 8 14:17:10 2019 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.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.
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.
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:
- MOS Note: 1574426.1 – Steps tor RDBMS DST to Be Able to Downgrade From 18.104.22.168 , 22.214.171.124 , 126.96.36.199 or 188.8.131.52 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
Oracle 20c?!? Your collegaue got a export done on Oracle 20c?
I’m feeling like on Back to the future movie… 😀
He tests with 20c right now, yes 🙂 It’s the MAIN code line at the moment 🙂
We upgrade from ora12 to ora19,timezone file too, and we have no problems during upgrade, BUT…. now we have oracle12 instances (TZ file 26) and oracle19 instance (TZ file 32), so when we expdp from ora19 and impdp in ora12 we have “ORA-39367: Statistcis are being skipped. Cannot locate the time zone version 32 file”. This could be a problem for us, in production environment: what do you suggest ? can we not upgrade TZ file ?
thank you very much
you please have to tell me more details.
1. You patched your 12c (is it 184.108.40.206 or 220.127.116.11) database to TZ V.26
2. You used the time zone scripts utltz_upg_check.sql and utltz_upg_apply.sql to make the changes to your databases
3. Then you export with expdp ==> I’d be interested in the par file you are using
4. Then you import with impdp ==> I’d be interested in the par file you are using
And at this stage, you receive ORA-39367?
I’d be interested in the exact error stack from the data pump log file.
Did you open an SR for this? It is something I wouldn’t expect.
1) we had 12c database with TZ v.26
2) we upgraded it to 19c with DBUA (anche we check “UPGRADE TZ ALSO” ): now we have 19c database with TZ v.32
3) we did expdp from 19c database and try to import in a 12c database and we’ve got ORA-39367
Here the par files:
expdp par file :
impdp par file :
thank you for your support
now I’ve got it.
Do you have TZ V32 in your 12c home? I see that you’ve applied V36 but the default most likely is V26.
When the import can’t find the matching time zone file, it may reject it.
Can you please check in $ORACLE_HOME/oracore/zoneinfo whether you have the missing time zone files?
If they aren’t there, please try the simple workaround and copy the missing files from your 19c home into your 12c home.
Sorry Mike for the delay…
The problem is that we have some instances 19.11 and other instances 18.104.22.168
We had one instance 22.214.171.124 and upgraded it to 19.11 with DBUA (dbua upgrade also timezone file to “TZ file 32”)
Now when we expdp tables from 19.11 and try to impdp them in another instance 126.96.36.199 (188.8.131.52 has TZ file 26) we’ve got
Error code: ORA-39367
Description: Statistcis are being skipped. Cannot locate the time zone version string file.
Cause: Importing statistics requires that the Oracle Data Pump load the source version of the time zone file. This version cannot be located.
Action: Install the latest time zone version files.
Tables are imported but without statistics
So the question is: what do you suggest to do with DBUA (from 184.108.40.206 to 19.11) and upgrade timezone ?
Maybe it’s better not upgrade TZfile?
Thank you very much
this error is intentional as you can’t downgrade time zone.
When you transport data from a database with a higher time zone version to a database with a LOWER time zone version, Data Pump will reject the import.
You need to apply the matching patch of the higher time zone version to the database home you are importing into.
In an ideal scenario, you should make sure that you have the same time zone version everywhere. This will make it easier.
Hey Mike, we’re experiencing a strange case with an export from an Oracle 19 with TSTZ 34 not importing into a Oracle 19 TSTZ 32, bur importing correctly in a Oracle 12.2 with a TSTZ 26.
The weirdest thing is that we don’t have timezone columns. So the Oracle 12.2 correctly knows there’s no timezone and performs the import, but the 19 wrongly think there is, and refuses to import due to the difference.
We’re kind of puzzled here and suspect of a bug.
Got any adivce?
I would need to see the error – without I can’t say anything unfortunately.
Well, the error is quite clear, the plain 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.
I post the question on the Oracle community here:
and later added more details and the result of other tests. I suggest to discuss this case on that platform since it’s more suitable for replies.
Dear Mike, I’m using Oracle 19c, after upgrading time zone 34, when I import Data still get an error
“Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32”
If you have any advice, please help me know (The dump file in Thailand, and import in India)
can you please check in the TARGET database:
SELECT * FROM v$timezone_file;
And can you please copy&paste the complete par file you are using plus the complete failure log?