Forum Stats

  • 3,760,436 Users
  • 2,251,705 Discussions


Relax requirement on impdp blocked by timezone mismatch

User51642 Yong Huang
User51642 Yong Huang Member Posts: 147 Blue Ribbon
edited Sep 17, 2021 10:57PM in Database Ideas - Ideas

According to

Impdp Fails With ORA-39002: Invalid Operation (Doc ID 2482971.1)

ORA-39002 occurs during a Data Pump Import due to "having a Lower version of Oracle Timezone Version in the target database and a Higher version of Oracle Timezone Version in the source database".

It's understandable that import should not proceed if timezone sensitive data will be misinterpreted if wrong timezone is applied to it. But Oracle categorically blocks import even if the data does not have any timezone-related data. My test of importing a single table with a single column of data type INT fails with this error, when the target database (18c) timezone version (31) is lower than that (32) in the source database (19c). This indiscriminate blocking of import reduces database usability unnecessarily.

The suggested fix is to upgrade the target database timezone (unless you create a temporary database with the same, lower version of timezone, import into it, upgrade the timezone, export, and import into the final target). But the upgrade requires total database shutdown, i.e. in case of RAC, not in rolling mode. This makes an apparently small issue a major operation headache.

The data pump team may adopt the strategy of the character set conversion so that a tool or impdp itself scans the dump file for timezone-sensitive data, and we're offered an option to import (with a warning) if the target timezone version is lower because it's determined the dump file has no timezone-sensitive data. If this is not done, during import, if such data is encountered, it's not too late to abort the import at this time.

User51642 Yong Huang
1 votes

Active · Last Updated