ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-39142-impdp 版本低问题

ORA-39142-impdp 版本低问题

原创 Linux操作系统 作者:redhouser 时间:2013-09-27 19:36:06 0 删除 编辑
在使用低版本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 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"
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809151