在使用低版本impdp导入高版本导出的文件时，会报ORA-39142: incompatible version number 2.1 in dump file错误。
可以先导入高版本库中，然后再加VERSION参数导出，最后就可以导入低版本库中。
1，在10.2.0.1.0导入
1.1 版本
[bnet@bnet95 reports]$ sqlplus bnet/obss
[bnet@bnet95 reports]$ sqlplus bnet/obss
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 12 20:17:59 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1.2 尝试导入
[bnet@bnet95 reports]$ impdp userid=bnet/obss@bnet directory=TEST_DIR dumpfile= EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet sqlfile=a.sql
[bnet@bnet95 reports]$ impdp userid=bnet/obss@bnet directory=TEST_DIR dumpfile= EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet sqlfile=a.sql
Import: Release 10.2.0.1.0 - Production on Wednesday, 12 October, 2016 20:05:36
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "/home/bnet/reports/EXP_TRANS_COUNTER.DMP"
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "/home/bnet/reports/EXP_TRANS_COUNTER.DMP"
1.3 相关参数
[bnet@bnet95 reports]$ impdp help=y
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
[bnet@bnet95 reports]$ impdp help=y
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
[bnet@bnet95 reports]$ expdp help=y
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
2，在11.2.0.3.0导入
2.1 版本
[bnet@ZD3DD603:/bnet/reports]$ sqlplus bnet/bnet
[bnet@ZD3DD603:/bnet/reports]$ sqlplus bnet/bnet
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 19 20:19:11 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2.2 获取SQL文件
[bnet@ZD3DD603:/bnet/reports]$ impdp userid=bnet/bnet directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet sqlfile=a.sql
[bnet@ZD3DD603:/bnet/reports]$ impdp userid=bnet/bnet directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet sqlfile=a.sql
[bnet@ZD3DD603:/bnet/reports]$ more a.sql
-- CONNECT bnet
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
传输到WINDOWS上：
-- CONNECT bnet
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
传输到WINDOWS上：
CREATE TABLE "bnet"."LOSS_SHITI"
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(11 BYTE),
"渠道" VARCHAR2(8 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."INACTIVE_SHITI"
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(11 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."XKHKH_SHITI"
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" NUMBER NOT NULL ENABLE,
"笔数" NUMBER
) ;
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(11 BYTE),
"渠道" VARCHAR2(8 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."INACTIVE_SHITI"
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(11 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."XKHKH_SHITI"
( "一级机构" VARCHAR2(100 BYTE),
"二级机构" VARCHAR2(100 BYTE),
"责任中心" VARCHAR2(100 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" NUMBER NOT NULL ENABLE,
"笔数" NUMBER
) ;
2.3 尝试导入
impdp userid=bnet/bnet directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet
ORA-02374: conversion error loading table "bnet"."LOSS_SHITI"
ORA-12899: value too large for column 娓犻亾 (actual: 12, maximum: 8)
ORA-12899: value too large for column 娓犻亾 (actual: 12, maximum: 8)
ORA-02372: data for row: 娓犻亾 : 0X'CAB5CCE5B9F1D4B1'
ORA-02374: conversion error loading table "bnet"."LOSS_SHITI"
ORA-12899: value too large for column 娓犻亾 (actual: 12, maximum: 8)
ORA-12899: value too large for column 娓犻亾 (actual: 12, maximum: 8)
ORA-02372: data for row: 娓犻亾 : 0X'CAB5CCE5B9F1D4B1'
==〉估计是源库GBK编码，目标库AL32UTF8编码，导致插入失败；增加长度：
CREATE TABLE "bnet"."LOSS_SHITI"
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(44 BYTE),
"渠道" VARCHAR2(32 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."INACTIVE_SHITI"
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(44 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."XKHKH_SHITI"
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" NUMBER NOT NULL ENABLE,
"笔数" NUMBER
) ;
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(44 BYTE),
"渠道" VARCHAR2(32 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."INACTIVE_SHITI"
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" VARCHAR2(44 BYTE),
"笔数" NUMBER
) ;
CREATE TABLE "bnet"."XKHKH_SHITI"
( "一级机构" VARCHAR2(400 BYTE),
"二级机构" VARCHAR2(400 BYTE),
"责任中心" VARCHAR2(400 BYTE),
"责任中心号" NUMBER(14,0),
"客户号" NUMBER NOT NULL ENABLE,
"笔数" NUMBER
) ;
2.4 尝试导入
[bnet@ZD3DD603:/bnet/reports]$impdp userid=bnet/bnet directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= exp_trans_counter.log parallel=8 TRANSFORM=segment_attributes:n remap_schema= misecif:bnet TABLE_EXISTS_ACTION=append
Import: Release 11.2.0.3.0 - Production on Thu Nov 19 20:32:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "bnet"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "bnet"."SYS_IMPORT_FULL_01": userid=bnet/******** directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= parallel=8 TRANSFORM=segment_attributes:n remap_schema= TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "bnet"."LOSS_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "bnet"."INACTIVE_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "bnet"."XKHKH_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "bnet"."LOSS_SHITI" 79.65 MB 710821 rows
. . imported "bnet"."INACTIVE_SHITI" 1.196 GB 11905374 rows
. . imported "bnet"."XKHKH_SHITI" 512.8 MB 5094740 rows
Job "bnet"."SYS_IMPORT_FULL_01" successfully completed at 20:33:09
==〉成功导入
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "bnet"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "bnet"."SYS_IMPORT_FULL_01": userid=bnet/******** directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER.DMP logfile= parallel=8 TRANSFORM=segment_attributes:n remap_schema= TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "bnet"."LOSS_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "bnet"."INACTIVE_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "bnet"."XKHKH_SHITI" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "bnet"."LOSS_SHITI" 79.65 MB 710821 rows
. . imported "bnet"."INACTIVE_SHITI" 1.196 GB 11905374 rows
. . imported "bnet"."XKHKH_SHITI" 512.8 MB 5094740 rows
Job "bnet"."SYS_IMPORT_FULL_01" successfully completed at 20:33:09
==〉成功导入
2.5 加VERSION参数导出
[bnet@ZD3DD603:/bnet/reports]$expdp userid=bnet/bnet tables=loss_shiti,INACTIVE_SHITI,XKHKH_SHITI directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER2.DMP logfile= exp_trans_counter2.log VERSION=10.2
Export: Release 11.2.0.3.0 - Production on Thu Nov 19 20:45:30 2015
[bnet@ZD3DD603:/bnet/reports]$expdp userid=bnet/bnet tables=loss_shiti,INACTIVE_SHITI,XKHKH_SHITI directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER2.DMP logfile= exp_trans_counter2.log VERSION=10.2
Export: Release 11.2.0.3.0 - Production on Thu Nov 19 20:45:30 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "bnet"."SYS_EXPORT_TABLE_01": userid=bnet/******** tables=loss_shiti,INACTIVE_SHITI,XKHKH_SHITI directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER2.DMP logfile= VERSION=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.867 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "bnet"."INACTIVE_SHITI" 1.658 GB 11905374 rows
. . exported "bnet"."XKHKH_SHITI" 715.9 MB 5094740 rows
. . exported "bnet"."LOSS_SHITI" 110.8 MB 710821 rows
Master table "bnet"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for bnet.SYS_EXPORT_TABLE_01 is:
/bnet/reports/EXP_TRANS_COUNTER2.DMP
Job "bnet"."SYS_EXPORT_TABLE_01" successfully completed at 20:46:33
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "bnet"."SYS_EXPORT_TABLE_01": userid=bnet/******** tables=loss_shiti,INACTIVE_SHITI,XKHKH_SHITI directory=REPORT_DIR dumpfile=EXP_TRANS_COUNTER2.DMP logfile= VERSION=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.867 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "bnet"."INACTIVE_SHITI" 1.658 GB 11905374 rows
. . exported "bnet"."XKHKH_SHITI" 715.9 MB 5094740 rows
. . exported "bnet"."LOSS_SHITI" 110.8 MB 710821 rows
Master table "bnet"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for bnet.SYS_EXPORT_TABLE_01 is:
/bnet/reports/EXP_TRANS_COUNTER2.DMP
Job "bnet"."SYS_EXPORT_TABLE_01" successfully completed at 20:46:33
[bnet@ZD3DD603:/bnet/reports]$ ls -lrt
-rw-r--r-- 1 oracle dba 1284 Nov 19 20:46 exp_trans_counter2.log
-rw-r----- 1 oracle dba 2647781376 Nov 19 20:46 EXP_TRANS_COUNTER2.DMP
==〉导出文件无法读取
2.6 修改导出目录属性
[bnet@ZD3DD603:/bnet/reports]$ cd ..
[bnet@ZD3DD603:/bnet]$ ls
DailyClean bnet_dump core lost+found oradiag_bnet p307 reports
DataUploading clean_partition.sql init_data.log nohup.out p305 report tmp
[bnet@ZD3DD603:/bnet]$ id
uid=207(bnet) gid=210(bnet) groups=1(staff)
[bnet@ZD3DD603:/bnet]$ chmod g+s reports
[bnet@ZD3DD603:/bnet]$ ls -lrt
drwxrwsrwx 2 bnet bnet 256 Nov 19 20:45 reports
2.7重新导出
[bnet@ZD3DD603:/bnet/reports]$ ls -lrt
total 8894648
-rw-r--r-- 1 oracle bnet 1284 Nov 19 20:52 exp_trans_counter2.log
-rw-r----- 1 oracle bnet 2647781376 Nov 19 20:52 EXP_TRANS_COUNTER2.DMP
[bnet@ZD3DD603:/bnet/reports]$ ls -lrt
total 8894648
-rw-r--r-- 1 oracle bnet 1284 Nov 19 20:52 exp_trans_counter2.log
-rw-r----- 1 oracle bnet 2647781376 Nov 19 20:52 EXP_TRANS_COUNTER2.DMP
3，在10.2.0.1.0导入
[bnet@bnet95 reports]$ impdp userid=bnet/obss directory=TEST_DIR dumpfile= EXP_TRANS_COUNTER2.DMP logfile= exp_trans_counter2.log parallel=8 TRANSFORM=segment_attributes:n
Import: Release 10.2.0.1.0 - Production on Wednesday, 12 October, 2016 21:06:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "BOCNET"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BOCNET"."SYS_IMPORT_FULL_01": userid=bocnet/******** directory=TEST_DIR dumpfile= EXP_TRANS_COUNTER2.DMP logfile= exp_trans_counter2.log parallel=8 TRANSFORM=segment_attributes:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BOCNET"."INACTIVE_SHITI" 1.658 GB 11905374 rows
. . imported "BOCNET"."LOSS_SHITI" 110.8 MB 710821 rows
. . imported "BOCNET"."XKHKH_SHITI" 715.9 MB 5094740 rows
Job "BOCNET"."SYS_IMPORT_FULL_01" successfully completed at 21:08:54
With the Partitioning, OLAP and Data Mining options
Master table "BOCNET"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BOCNET"."SYS_IMPORT_FULL_01": userid=bocnet/******** directory=TEST_DIR dumpfile= EXP_TRANS_COUNTER2.DMP logfile= exp_trans_counter2.log parallel=8 TRANSFORM=segment_attributes:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BOCNET"."INACTIVE_SHITI" 1.658 GB 11905374 rows
. . imported "BOCNET"."LOSS_SHITI" 110.8 MB 710821 rows
. . imported "BOCNET"."XKHKH_SHITI" 715.9 MB 5094740 rows
Job "BOCNET"."SYS_IMPORT_FULL_01" successfully completed at 21:08:54
成功导入。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-773504/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle11g新特性之edition
请登录后发表评论
登录
全部评论
