Skip to Main Content

Database Software

Relax requirement on impdp blocked by timezone mismatch

User51642 Yong HuangSep 17 2021 — edited Sep 17 2021

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.

Post Details
Added on Sep 17 2021