ORA-39142 ORA-39000 转储文件不兼容

原创

Liujun_Deng 2022-08-13 00:23:15 博主文章分类：Oracle ©著作权

文章标签 oracle 版本号 .net 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任

数据库：oracle 11.2.0.4

系统：centos 7.9

问题描述：将dmp文件执行impdp导入时报错ORA-39142：版本号5.1 (在转储文件 "/tmp/DUMP1.DMP" 中)不兼容，如下所示：

[oracle@hisdb1 ~]$ impdp system/oracle_4U@192.168.133.160:1521/orcl directory=imp_dir dumpfile=DUMP1.DMP schemas=rmlis remap_tablespace=%:USERS cluster=n logfile=imprm.log
Import: Release 11.2.0.4.0 - Production on Tue Aug 9 22:36:53 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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 "/tmp/DUMP1.DMP"

异常原因：导致此错误的原因为oracle高版本导出，低版本导入造成.

解决方案：在源端加上version=版本号导出，如version=11.2

以下为MOS文档相关信息：

Data Pump file version.
=======================

     Version      Written by     Can be imported into Target:
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2      12cR1      12cR2        18c        19c
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x   12.1.0.x   12.2.0.x   18.x.y.z   19.x.y.z
------------ --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported  supported  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported  supported  supported  supported  supported
         3.1          11.2.x         no         no         no  supported  supported  supported  supported  supported
         4.1          12.1.x         no         no         no         no  supported  supported  supported  supported
         5.1          12.2.x         no         no         no         no         no  supported  supported  supported
         5.1          18.x.y         no         no         no         no         no         no  supported  supported
         5.1          19.x.y         no         no         no         no         no         no         no  supported

比如：12.2导出的dmp文件版本位5.1,11.2的dmp版本位3.1，所以在导入时会出现ORA-39142:版本号5.1(在转储文件"/tmp/DUMP1.DMP"中)不兼容报错。

Export              Use Export Data Pump parameter VERSION=...
      From                 if dumpfile needs to be imported into a
    Source                Target Database with compatibility level
  Database        (value of init.ora/spfile parameter COMPATIBLE):
      With
COMPATIBLE    10.1.0.x.y    10.2.0.x.y    11.1.0.x.y    11.2.0.x.y    12.1.0.x.y    12.2.0.x.y     18.x.y.z     19.x.y.z
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
10.1.0.x.y             -             -             -             -             -             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
10.2.0.x.y  VERSION=10.1             -             -             -             -             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
11.1.0.x.y  VERSION=10.1  VERSION=10.2             -             -             -             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
11.2.0.x.y  VERSION=10.1  VERSION=10.2  VERSION=11.1             -             -             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
12.1.0.x.y  VERSION=10.1  VERSION=10.2  VERSION=11.1  VERSION=11.2             -             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
12.2.0.x.y  VERSION=10.1  VERSION=10.2  VERSION=11.1  VERSION=11.2  VERSION=12.1             -            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
18.x.y.z    VERSION=10.1  VERSION=10.2  VERSION=11.1  VERSION=11.2  VERSION=12.1  VERSION=12.2            -            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------
19.x.y.z    VERSION=10.1  VERSION=10.2  VERSION=11.1  VERSION=11.2  VERSION=12.1  VERSION=12.2 VERSION=18.x            -
---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------ ------------

所以12.2导出如果需要在11.2导入，需要在导出时加入参数VERSION=11.2

$ cat expdptest.sh  
expdp sys/******@192.168.133.120:1521/orcl directory=expdpdir dumpfile=DUMP1.DMP logfile=expdp.log schemas=scott CLUSTER=N version=11.2

参考网址：

https://blog.csdn.net/m0_37625564/article/details/123222287

https://blog.csdn.net/cimeng0072/article/details/100286810