Problem:
While importing data on a 19.5 database, Data pump terminated with this error:
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 33 into a target database with TSTZ version 32.
Analysis:
This happened because the target database (where the data is getting imported to) is having a lower TIMEZONE version than the Source DB.
Solution:
Upgrade the TIMEZONE version on the 19.5 target DB from 32 (the default) to 33: [Follow below steps]
Download Patch 28852325: RDBMS - DSTV33 UPDATE - TZDATA2018G:
https://updates.oracle.com/Orion/Services/download/p28852325_190000_Linux-x86-64.zip?aru=23061696&patch_file=p28852325_190000_Linux-x86-64.zip
Extract TIMEZONE V33 files under ORACLE_HOME by applying the patch:
[Can be applied while the DB is up and running, it will place the new TIMEZONE files under ORACLE_HOME]
# unzip p28852325_190000_Linux-x86-64.zip
# cd 28852325
# opatch apply
Do you want to proceed? [y|n]
y
Check the current timezone version:
SQL> select tz_version from registry$database;
TZ_VERSION
----------
32
Check the availability of the new (installed) timezone version on ORACLE_HOME:
SQL> select DBMS_DST.get_latest_timezone_version from dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
33
Before upgrade, perform the pre-upgrade stage steps which will let you know which tables will be impacted by this new TIMEZONE upgrade:
SQL> DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_prepare(l_tz_version);
END;
/
SQL> col property_name for a30
col property_value for a20
select property_name, property_value from database_properties where property_name LIKE 'DST_%' order by property_name;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 33
DST_UPGRADE_STATE PREPARE
View the impacted tables due to this TIMEZONE upgrade: [Which
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;
SQL> EXEC DBMS_DST.find_affected_tables;
SQL> select * from sys.dst$affected_tables;
SQL> select * from sys.dst$error_table;
End the pre-upgrade stage:
SQL> EXEC DBMS_DST.end_prepare;
Now, the actual TIMEZONE upgrade: [Downtime required]
-- Restart the DB in UPGRADE mode:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
-- Upgrade the TIMEZONE:
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
select DBMS_DST.get_latest_timezone_version into l_tz_version from dual;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
-- Restart the DB in Normal mode:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
-- Upgrade ZONE files: [Ideally will impact all tables having "TIMESTAMP WITH TIME ZONE" data type]
SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
Once it's done, the TIMEZONE upgrade will be complete.
Post checks: Check the current TIMEZONE version:
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_33.dat 33 0
Thank you for sharing. I tried hard to find a solution. Straight to the point.
ReplyDeleteThank you, this is useful for me
ReplyDeleteAwesome.
ReplyDeletei am not able to download the patch
ReplyDeleteDoes it require any premium account for doanload
I just tried it now, it's working!
DeleteDid you try to log in to Oracle support portal with your corporate account?
I am on windows 64X. what is the patch file, please?
ReplyDelete