数据泵(expdp/impdp)从高版本导入低版本

郑全

数据泵(expdp/impdp)跨版本导入
 
本文讲述了数据泵(expdp/impdp)怎么从高版本导入到低版本。以及数据泵(expdp/impdp)在导出时需要注意到问题。

一、起因
数据泵(expdp/impdp)在Oracle数据库10g时，跨版本导入和导出的问题还不太明显，但是到了Oracle数据库11g和Oracle数据库11gR2的版本时，数据泵(expdp/impdp)在跨版本导入和导出时体现的问题，越来越明显。

 

二、问题演示
我来演示一下，从Oracle数据库11g用数据泵(expdp)导出数据库内容，然后用数据泵(impdp)导入到Oracle数据库10g当中。

从11.2.0.4版本的Oracle数据库中，用expdp导出：

[orcl@prod ~]$ expdp test/test dumpfile=d_tmp:new.exp TABLES=new
Export: Release 11.2.0.4.0 - Production ON Mon JAN 16 15:31:20 2012
Copyright (c) 1982, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** dumpfile=d_tmp:new.exp tables=new
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."NEW" 6.593 KB 63 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/tmp/new.exp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:01
1234567891011121314151617 [orcl@prod ~]$ expdp test/test dumpfile=d_tmp:new.exp TABLES=new Export: Release 11.2.0.4.0 - Production ON Mon JAN 16 15:31:20 2012 Copyright (c) 1982, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** dumpfile=d_tmp:new.exp tables=new Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "TEST"."NEW" 6.593 KB 63 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is: /tmp/new.exp Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:01

然后导入到10.2.0.4

[orcl2@prod2 ~]$ impdp test/test directory=d_tmp dumpfile=new.exp
Import: Release 10.2.0.4.0 - 64bit Production ON Monday,16 JAN, 2012 15:51:40
Copyright (c) 2003, 2007, Oracle. ALL rights reserved.
Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
ORA-39001: invalid argument VALUE
ORA-39000: bad dump file specification
ORA-39142: incompatible version NUMBER 3.1 IN dump file "/tmp/new.exp"
12345678 [orcl2@prod2 ~]$ impdp test/test directory=d_tmp dumpfile=new.exp Import: Release 10.2.0.4.0 - 64bit Production ON Monday,16 JAN, 2012 15:51:40 Copyright (c) 2003, 2007, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options ORA-39001: invalid argument VALUE ORA-39000: bad dump file specification ORA-39142: incompatible version NUMBER 3.1 IN dump file "/tmp/new.exp"

出现了因为版本过高无法导入的错误。

 

三、解决办法
在用expdp导出时，加入特定参数”VERSION=”后面跟上参数的版本。比如我要导入到的Oracle数据库版本为10.2.0.1 则这样写：VERSION=10.2.0.1 即可。

[orcl@prod ~]$ expdp test/test dumpfile=d_tmp:newtest.exp TABLES=new version=10.2.0.4
Export: Release 11.2.0.3.0 - Production ON Mon JAN 16 16:22:40 2012
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, OLAP, DATA Mining AND REAL Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** dumpfile=d_tmp:newtest.exp tables=new version=10.2.0.4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."NEW" 6.406 KB 63 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/tmp/newtest.exp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:28:40
1234567891011121314151617 [orcl@prod ~]$ expdp test/test dumpfile=d_tmp:newtest.exp TABLES=new version=10.2.0.4 Export: Release 11.2.0.3.0 - Production ON Mon JAN 16 16:22:40 2012 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, OLAP, DATA Mining AND REAL Application Testing options Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** dumpfile=d_tmp:newtest.exp tables=new version=10.2.0.4 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "TEST"."NEW" 6.406 KB 63 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is: /tmp/newtest.exp Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:28:40

正常导入：

[orcl2@prod2 ~]$ impdp test/test directory=d_tmp dumpfile=newtest.exp
Import: Release 10.2.0.4.0 - 64bit Production ON Monday, 16 JAN, 2012 16:35:30
Copyright (c) 2003, 2007, Oracle. ALL rights reserved.
Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Master TABLE "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=d_tmp dumpfile=newtest.exp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."NEW" 6.406 KB 63 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 16:45:20
123456789101112 [orcl2@prod2 ~]$ impdp test/test directory=d_tmp dumpfile=newtest.exp Import: Release 10.2.0.4.0 - 64bit Production ON Monday, 16 JAN, 2012 16:35:30 Copyright (c) 2003, 2007, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options Master TABLE "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=d_tmp dumpfile=newtest.exp Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."NEW" 6.406 KB 63 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 16:45:20