Forum Stats

  • 3,770,593 Users
  • 2,253,137 Discussions
  • 7,875,502 Comments

Discussions

ORA-39405 starting from 19c (maybe 18c)?

Hi there, community.

I'm facing a rather weird behaviour in 19c. We have this client that sent us an export made from a 19c database (not sure about the release, he didn't send the log), but when trying to import in our site we hit the said "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".

Our database (19.0.0.0) is indeed TSTZ 32:


SQL> SELECT * FROM v$timezone_file;

FILENAME        VERSION   CON_ID

-------------------- ---------- ----------

timezlrg_32.dat       32     0

SQL>


The only solution is to apply a patch on our site to upgrade this server's TSTZ to 34 in order to import.


But now the plot thickens: importing the very same dump in a 12.2 database works perfectly. This database TSTZ? Let's check:


SQL> SELECT * FROM v$timezone_file;

FILENAME        VERSION   CON_ID

-------------------- ---------- ----------

timezlrg_18.dat       18     0

SQL>


So riddle me this, Batman: how come a TSTZ 34 cannot import into a 32, but imports correctly in to a 18 (which is far behind)?


The only logical explanation is that before 19c (maybe 18?), this value was never really checked/used during imports? This would make sense since the only mentions I found about this error on google are relatively recent, dating from 2018 onwards.


I did not look up the change log versions, but is it a normal behavior? Is somewhere in the docs mentionned that starting from 19 (or 18) higher versions of TSTZ could not be imported into lower versions?

Answers

  • Mr.Leo
    Mr.Leo Member Posts: 39 Red Ribbon
    edited Oct 28, 2021 6:14PM

    Just a follow up: we tried importing the said dump into a 18c database with a TSTZ 31, but then we hit a "different" error:


    Import: Release 11.2.0.3.0 - Production on Thu Oct 28 12:02:39 2021

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

    ORA-39002: invalid operation



    This seemed to have nothing to do with the 39405, but after a brief research we found this note:



    Looks like this behavior was in fact introduced in 18 with a rather obscure error code and in 19 the message was updated to be more clear.


    Are my conclusions right? Did Oracle realy add this restriction between versions? I tested again and again on different servers and had the same results over and over, so its clear to me that this is not a bug nor an unexpected situation.


    Did anyone else experience this before?

  • Mr.Leo
    Mr.Leo Member Posts: 39 Red Ribbon
    edited Oct 29, 2021 3:11PM

    Another update.


    After some more research we found out that in fact the TSTZ check was always part of the import process (since 11.2 it seems) as per this note:



    But the very first sentence on this note is quite clarifying:


    For export and import jobs that have TIMESTAMP with TIME ZONE data, successful job completion can depend on:


    The reason why we never hit this error before (and completely ignored this TSTZ charactristic) is that our data models do not have any TIMEZONE column.


    So the 12.2 database imports correctly because it does not check the TSTZ since there's no timezone columns, but the 18 and 19 databases fail because they are wrongly comparing it.


    This starts sounding like a bug? If there's no timezone in the dump no comparing should be performed.