
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
Oracle 20c?!? Your collegaue got a export done on Oracle 20c?
I’m feeling like on Back to the future movie… 😀
*haha*
He tests with 20c right now, yes 🙂 It’s the MAIN code line at the moment 🙂
Cheers,
Mike
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
Hi Barbara,
you please have to tell me more details.
1. You patched your 12c (is it 12.1.0.2 or 12.2.0.1) 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.
Cheers,
Mike
Hi Mike
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 :
FLASHBACK_TIME=systimestamp
TABLES=SCHEMA.TABLE
REUSE_DUMPFILES=Y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_table.dmp
LOGFILE=DATA_PUMP_DIR:expdp_table.log
JOB_NAME=expdp_table
impdp par file :
DIRECTORY=DATA_PUMP_DIR
REMAP_SCHEMA=USER1:USER2
REMAP_TABLESPACE=USER1:USER2
DUMPFILE=expdp_table.dmp
LOGFILE=DATA_PUMP_DIR:impdp_lobtable.log
JOB_NAME=impdp_lobtable
thank you for your support
best regards
Barbara
Hi Barbara,
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.
Cheers,
Mike
Sorry Mike for the delay…
The problem is that we have some instances 19.11 and other instances 12.2.0.1
We had one instance 12.2.0.1 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 12.2.0.1 (12.2.0.1 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 12.2.0.1 to 19.11) and upgrade timezone ?
Maybe it’s better not upgrade TZfile?
Thank you very much
Hi Barbara,
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.
Cheers,
Mike
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?
Hi Leandro,
I would need to see the error – without I can’t say anything unfortunately.
Cheers,
Mike
Hello, Mike.
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:
https://community.oracle.com/tech/developers/discussion/4490678/ora-39405-starting-from-19c-maybe-18c/p1?new=1
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)
Hi Anh,
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?
Thanks
Mike