在使用低版本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

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 12 20:17:59 2016

Connected to:
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

1.2 嘗試匯入
[bnet@bnet95 reports]$ impdp 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

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"

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]$ 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

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 19 20:19:11 2015

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

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]$ 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上：

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
   ) ;

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-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-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
   ) ;

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

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
==〉成功匯入

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

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

[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

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

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

成功匯入。

ORA-39142-impdp 版本低問題

相關文章