Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Leandro.

Asked: April 08, 2022 - 6:53 pm UTC

Last updated: April 28, 2022 - 3:33 am UTC

Version: Oracle 19

Viewed 100+ times

You Asked

Hi there, masters.

I'm facing a rather weird situation regading the TSTZ configuration. A full detailed description of my initial problem is here in this post I made some months ago:

https://community.oracle.com/tech/developers/discussion/4490678/ora-39405-starting-from-19c-maybe-18c/p1?new=1


tl;dr: Oracle 19 does not import data sets if the source has a bigger TSTZ version. This never happened with 12c; in fact we ignored completely the TSTZ version aspect since we don't have any TSTZ data in our data models.

Accordingly to all docs and blog entries I could find on line, the solution is to upgrade the version to at least match the source server's one. But here is ou main problem: we cannot really do it.

Giving we exchange data (both send and recive) from several clients, they can perfectly be on different versions and we will end up with following scenario:


Client X TSTZ is 36, our is 32 = error. We upgrade ours to 36 and then we can import his data and he can also import ours (both versions match).

Client Y TSTZ is 34, our now is 36. We can import his data (our is bigger) but he cannot import ours (since his is lower).


We cannot impose to our clients to be at a X specific version, this is not realist. Some might want/need to be on the latest release while others might not/could not do it.

Was this restriction thought through when imposed in 18c? I cannot believe such scenario was not foreseen. There must be a really silly detail that I'm overlooking on all of this.

So, what are your insights on this situation, masters?

and Connor said...

Thanks for your patience. I asked the DataPump team and got some (sad) news

"In version 21c and later, there are new implementations of TSTZ/timezone support. This new support was backported to *some* 19c variants but not all. Certain things haven’t changed. When you do an export, the timezone info from the database is recorded in the dump file. When you import that dump file, the import database has to be at that version or a newer version. It’s OK to import a dump file with an older timezone version as long as that timezone data file is available. In 12c, there was a lot less checking of timezone versions. Data Pump would note the potential incompatibility and then plough full speed ahead, occasionally resulting in ORA-600’s and crashes…and sometimes it would just work. The additional checking was added to avoid some of the catastrophic failures."

Bottom line - you need to people to align their timezones to avoid data corruptions.

I've logged an enhancement where DataPump should ideally at export time, check if the contents refer to a timezoned column of any sort to ease up this restriction, but that would be a fair way off.


Rating

  (2 ratings)

Comments

Leandro, April 20, 2022 - 1:41 pm UTC

Thanks for your answer, Connor.

"you need to people to align their timezones to avoid data corruptions"

Like I said, this requirement would be impratical for us since we have dozens of clients on Oracle and we can't ask them all to be up to date with the latest version, nor skipping patches or necessary updates to keep the same version as us. Our idea for the moment is using a "translation" Oracle 12 server since this version does not check the TSTZ when importing and generates a lower one when exporting. But this additional step is kind of burdensome :

Client sends his Ora19 dump -> We import it into an Ora12 -> We copy the data into our Ora19

We copy our Ora19 data into the Ora12 server -> we export the Ora12 data -> the client imports the 12c dump into his Ora19

We found a really interesting post by Mike Dietrich that talks about this same situation (I wrote him in the comment section but he did not answer me). In his blog he explains that:

"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."

Source: https://mikedietrichde.com/2019/05/14/data-pump-the-time-zone-pitfalls/

I thank you for taking time to analyze my situation and your effort of asking for an enhancement for DataPump, but I would like to suggest a different solution: adding an option into impdp to ignore the TSTZ check. We already have the VERSION option in expdp where we can define the server compatibility level, so adding a way to import regardless of the timezone settings would solve this situation. Given it would be a very specific option, I would be up to the user to deal with any corruptions or errors during the import.

What do you think? Could such option be considered and, maybe, added to impdp?

Once again, thank you very much for your help.
Connor McDonald
April 27, 2022 - 2:32 am UTC

I think ignoring the timezone check is fraught with danger. Don't forget that dictionary tables also have timezones in some places, which means it would akin to say: "I don't mind if the dictionary gets corrupted".

That sounds like a nightmare for Support :-)

DBA

Carl B., April 27, 2022 - 7:14 pm UTC

The official documentation of Oracle is in contradiction with what you are saying Connor. See here:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-overview.html#GUID-B437B7AE-E36F-422B-ADFF-4ED87F9C4B1B

Where we can find:

"TIMESTAMP WITH TIMEZONE Restrictions

Export and import jobs that have TIMESTAMP WITH TIME ZONE data are restricted.

Understanding TIMESTAMP WITH TIME ZONE Restrictions
Carrying out export and import jobs that have TIMESTAMP WITH TIME ZONE data requires understanding information about your time zone file data and Oracle Database release."


If Oracle don't modify the behavior of these tools, the documentation should be reviewed.

Best regards.

Carl
Connor McDonald
April 28, 2022 - 3:33 am UTC

That is correct - the docs should be more precise. I'll log a bug

Thanks for letting us know.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.