ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/u01/app/oracle/expdp_dept.dmp"
Cause:
The above error messages usually occurs when you perform export from higher database release version and import into lower release version i.e. here, the higher release version is 12c and lower release version is 11g.
#Query to check DBID of database in Oracle SQL> select name,open_mode,dbid from v$database; [oracle@test oracle]$ expdp directory=DIR dumpfile=expdp_dept.dmp logfile=expdp_dept.log tables=C##RUPESH.dept Export: Release 12.2.0.1.0 - Production on Sun Jun 10 20:10:04 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: system@ORCLPDB Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@ORCLPDB directory=DIR dumpfile=expdp_dept.dmp logfile=expdp_dept.log tables=C##RUPESH.dept Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "C##RUPESH"."DEPT" 5.507 KB 4 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/expdp_dept.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jun 10 20:10:41 2018 elapsed 0 00:00:26 [oracle@test oracle]$ ls -ltr expdp_dept* -rw-r----- 1 oracle oinstall 188416 Jun 10 20:10 expdp_dept.dmp -rw-r--r-- 1 oracle oinstall 1047 Jun 10 20:10 expdp_dept.log #Copy exported table dump to target DB server [oracle@test oracle]$ scp expdp_dept.dmp oracle@clone:/u01/app/oracle/ oracle@clone's password: expdp_dept.dmp 100% 184KB 29.0MB/s 00:00 [oracle@clone oracle]$ hostname clone.localdomain [oracle@clone oracle]$ ls -ltr *dept* -rw-r----- 1 oracle oinstall 188416 Jun 10 20:11 expdp_dept.dmp [oracle@clone oracle]$ pwd /u01/app/oracle #Import the table into target DB [oracle@clone oracle]$ impdp directory=DIR dumpfile=expdp_dept.dmp logfile=impdp_dept.log tables=C##RUPESH.dept remap_schema=C##RUPESH:RUPESH remap_tablespace=USERS:TEST Import: Release 11.2.0.4.0 - Production on Sun Jun 10 20:13:43 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in dump file "/u01/app/oracle/expdp_dept.dmp" |
Solution:
To fix this issue, you need to add version parameter in source database while taking export backup. Refer below steps to perform export import using version parameter.
#Query to check DBID of database in Oracle SQL> select name,open_mode,dbid from v$database; [oracle@test oracle]$ expdp directory=DIR dumpfile=version_expdp_dept.dmp logfile=version_expdp_dept.log tables=C##RUPESH.dept version=11.2 Export: Release 12.2.0.1.0 - Production on Sun Jun 10 20:14:37 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: system@ORCLPDB Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers. Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@ORCLPDB directory=DIR dumpfile=version_expdp_dept.dmp logfile=version_expdp_dept.log tables=C##RUPESH.dept version=11.2 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "C##RUPESH"."DEPT" 5.507 KB 4 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/version_expdp_dept.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jun 10 20:15:12 2018 elapsed 0 00:00:22 #Copy exported dump file to target server [oracle@test oracle]$ scp version_expdp_dept.dmp oracle@clone:/u01/app/oracle/ oracle@clone's password: version_expdp_dept.dmp 100% 140KB 24.1MB/s 00:00 #Import the exported dump into target db [oracle@clone oracle]$ impdp directory=DIR dumpfile=version_expdp_dept.dmp logfile=version_impdp_dept.log tables=C##RUPESH.dept remap_schema=C##RUPESH:RUPESH remap_tablespace=USERS:TEST Import: Release 11.2.0.4.0 - Production on Sun Jun 10 20:16:32 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA directory=DIR dumpfile=version_expdp_dept.dmp logfile=version_impdp_dept.log tables=C##RUPESH.dept remap_schema=C##RUPESH:RUPESH remap_tablespace=USERS:TEST Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "RUPESH"."DEPT" 5.507 KB 4 rows Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jun 10 20:16:38 2018 elapsed 0 00:00:02 |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !
Thank you for your comment !