Hello Friends,
In this post, we will see the Error and solution to Expdp and Impdp.
Now when we try to import the schema source 19.18 and target 19.16
Import in different databases with diff versions :
Export Was taken with the command below on version 19.18 :
SQL> !expdp system@FlyDB directory=FlyEXP dumpfile=Fly_EXP022.dmp logfile=LOG_Fly2700.log schemas='BIGONE' compression=ALL ENCRYPTION_PASSWORD= MyPassword version=19.16
Export: Release 19.0.0.0.0 - Production on Mon Feb 27 16:34:42 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@FlyDB directory=FlyEXP dumpfile=Fly_EXP022.dmp logfile=LOG_Fly2700.log schemas=BIGONE compression=ALL ENCRYPTION_PASSWORD=******** version=19.16
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "BIGONE"."BIGTAB" 5.826 MB 2000000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/data_mig1/FlyEXP/Fly_EXP022.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 27 16:35:27 2023 elapsed 0 00:00:34
First case when we don't provide the password to decrypt the dump file we get the error ORA-39189, ORA-28362
SQL> !impdp system@PDBFLY directory=FlyEXP dumpfile=Fly_EXP27022.dmp logfile=CDBFly.log schemas=BIGONE
Import: Release 19.0.0.0.0 - Production on Mon Feb 27 16:04:08 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
ORA-39002: invalid opeFLYion
ORA-39189: unable to decrypt dump file set
ORA-28362: master key not found
Solution: ENCRYPTION_PASSWORD= MyPassword
The second case even when we provide the password to decrypt we will get an error for the lower timezone on 19.16 ORA-39405
SQL> !impdp system@PDBFLY directory=FlyEXP dumpfile=Fly_EXP022.dmp logfile=IMP_CDBFly.log schemas=BIGONE ENCRYPTION_PASSWORD= MyPassword
Import: Release 19.0.0.0.0 - Production on Mon Feb 27 16:38:38 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
ORA-39002: invalid opeFLYion
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 40 into a target database with TSTZ version 38.
Kindly follow the below link to resolve the above error
HOW TO UPGRADE DB TIME ZONE USING PATCH ORA-39405
After the Error is resolved with the workaround in the above link, we can continue with the import, and the import should be working fine.
[oracle@dm01db01 ~]$ impdp system@PDBFLY directory=FlyEXP dumpfile=Fly_EXP022.dmp logfile=IMP_CDBFly.log schemas=BIGONE ENCRYPTION_PASSWORD= MyPassword
Import: Release 19.0.0.0.0 - Production on Mon Feb 27 20:43:44 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@PDBFLY directory=FlyEXP dumpfile=Fly_EXP022.dmp logfile=IMP_CDBFly.log schemas=BIGONE ENCRYPTION_PASSWORD=********
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BIGONE"."BIGTAB" 5.826 MB 2000000 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Feb 27 20:44:23 2023 elapsed 0 00:00:28
ConversionConversion EmoticonEmoticon