Remember my blog post about time zone problems during import from one year ago? When you try to import into an Oracle database and your source database you exported from had a higher time zone (DST) patch level than the target, the impdp failed with ORA-39405 and stopped. In this blog post I showed several workarounds including some insane ones by Rodrigo and Kamil. And I’ve been contacted by customers and colleagues quite a few times asking for a general remedy.
Photo by Pascal Debrunner on Unsplash
There is a fix for it
Spoiler – you can request a backport for it. Several enhancements got filed over time either by or on behalf of customers, or internally by Rodrigo or the people who run and operate Oracle ADB Serverless.
- ER 34824144 – ORA-39405: DON’T GENERATE A TIME ZONE ERROR IF TIME ZONE DATA DOESN’T EXIST
- ER 35684478 – DATA PUMP: ADB: ADD IMPORT OPTION TO IGNORE LOWER DST VERSION IN TARGET
- Bug 37041429 – DATAPUMP : REMOVE RESTRICTION TO DO IMPORT FROM HIGHER TSTZ VERSION TO LOWER VERSION
No guarantee that I list all of them. Those are the ones I am aware of.
Now, which one is the right one?
Be aware, while I write this in early Feb 2025, there is no one-off available right now. Hence, the above link as of now will not show you any backports. But this will change soon.
As long as this fix is not included in the Data Pump Bundle Patch, you need to check the patch download for this fix. If it exists on top of your RU, you are good to go. Otherwise, you need to request a backport via an SR on My Oracle Support (MOS). Please remember to upload your opatch lsinventory to the SR as well in this case.
Do you have to do anything?
No, you need this fix (or once it is included into the Data Pump Bundle Patch (DPBP) on the target environment. Then everything is supposed to run smoothly in case your source export has been done with a higher DST version than the target database has.
Update 19.27.0
The fix is part of the Data Pump Bundle Patch on top of Oracle 19.27 and newer. So, in case you moved already to 19.27 or newer, and you added the DPBP, this fix is present – and you don’t need to do anything anymore. I know that many of you were waiting for it, and this is a super-smooth correction since it does even do the convert in all directions.
Please be aware of this:
With this fix only tables with tstz column data should fail to import with error ORA-01882 in the case of a region id not present in lower version, and the rest of the import should continue. Also now with the removal of this restriction we can remove the internal parameter _datapump_bypass_tstz_check which is used to bypass the above said restriction.
That means: You won’t need the underscore parameter anymore, and there is only a very little chance that you may see any error during import from now on.
One important additional comment: Oracle Autonomous Database does not have the Data Pump Bundle Patch in place. Hence, you may need to rely on the above underscore.
Further Links and Information
- ORA-39405 with impdp when you have mixed time zone versions
- Patch 37041429 download of existing backports
- MOS Note: 2819284.1 – Data Pump Recommended Proactive Patches For 19.10 and Above
–Mike
Hi Mike,
That’s great news. Any idea about the version to which this is applicable to ?
kr
Hi Ivan,
you can request it on top of any 19c patch level as far as the backport policy allows.
But we will include it into the Bundle Patch soon – this will make backport requests then obsolete and ease the process.
Cheers,
Mike
Was this added to bundle patch?
Simply because such fixes are not RAC Rolling (for now). A new feature will be rolled out called Deferred SQL Patching, and it will allow us to include DPBP into the regular RU.
Cheers,
Mike