IMPDP出现ORA-39001 ORA-39000 ORA-39142错误
目标端IMPDP导入时出现ORA-39001 ORA-39000 ORA-39142错误D:\>impdp a/b@1.1.1.1:1521/pdborcl directory=dp dumpfile=test0301.dmp logfile=impdp_test0301.logImport: Release 12.1.0.2.0 - Production on 星期三 3月 2 09:22
目标端IMPDP导入时出现ORA-39001 ORA-39000 ORA-39142错误
D:\>impdp a/b@1.1.1.1:1521/pdborcl directory=dp dumpfile=test0301.dmp logfi
le=impdp_test0301.log
Import: Release 12.1.0.2.0 - Production on 星期三 3月 2 09:22:26 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
ORA-39001: 参数值无效
ORA-39000: 转储文件说明错误
ORA-39142: 版本号 5.1 (在转储文件 "d:\dp\test0301.dmp" 中) 不兼容
查看源端和目标端数据库版本
源端:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
0
PL/SQL Release 12.2.0.1.0 - Production
0
CORE 12.2.0.1.0 Production
0
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
TNS for Linux: Version 12.2.0.1.0 - Production
0
NLSRTL Version 12.2.0.1.0 - Production
0
目标端:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
0
PL/SQL Release 12.1.0.2.0 - Production
0
CORE 12.1.0.2.0 Production
0
BANNER
--------------------------------------------------------------------------------
CON_ID
----------
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
0
NLSRTL Version 12.1.0.2.0 - Production
0
源端数据库版本12.2,目标端版本12.1,根据MOS文档Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)中
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,12.1的dmp版本位4.1,因此在导入时出现ORA-39142: 版本号 5.1 (在转储文件 “d:\dp\test0301.dmp” 中) 不兼容报错。
解决方法是需要在导出时候加入VERSION=xxx参数,根据MOS文档Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)中
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导出如果需要在12.1导入的话,需要加入参数VERSION=12.1,
修改导出语句:
[oracle@test ~]$ cat expdptest.sh
expdp a/b@1.1.1.2:9999/test directory=dp dumpfile=test0301.dmp logfile=expdp_test0301.log schemas=test CLUSTER=N version=12.1
重新导出,拷贝dmp文件至目标端,导入正常。
更多推荐
所有评论(0)