ORA-39142-impdp 版本低問題

redhouser發表於2013-09-27
在使用低版本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
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
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
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"
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
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
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
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
==〉成功匯入
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
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

[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
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
成功匯入。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-773504/,如需轉載,請註明出處,否則將追究法律責任。

相關文章