DataPump关于跨数据库版本remove数据

利用DATAPUMP来做不同版本数据库之间的导入导出
在此做出声明：从10g开始就有data pump工具，oracle保留了imp/exp工具，但做支持了(即对此工具不做更新操作)；做数据的dump工作，建议用data pump
一、学习一下version这个参数在官方文档里的说明及解释
version={compatible|lastest|version_string}三个value

二、利用DATAPUMP在不同数据库版本之间操作的注意事项：
1、如果你从高版本往低版本导入，一些新特性将会无效。例如10.1没有数据压缩功能
2、在做export时，如果从高版本到低版本，生成的dump文件可以导入到低版本里，但是低版本不支持一些高版本的对象。从10.2.==>10.1上，索引类型的comments就不会被导出到dumpfile里.
3、impdp能读到低版本数据库创建的dump文件
4、impdp不能做直接读高版本的数据库dump出来的dump文件，除非在做dump文件时，加了version参数去指定目标库的版本号。因此，在做dump时，就是设置target库的版本号
5、当通过network link来做dump时，datapump要求远程数据库的版本要与本地数据库的版本一致或者比本地的低。例如：local database：10.2，则remote database必须是10.1or10.2(10GR2里对此的解释)。
      当通过network link来做dump时，数据泵要求源数据库和目标数据库相差不超过一个版本。例如：如果一个库是oracle11g，那么另个库是11gor10g,并且只考察大版本号，不考虑小版本好(11gR2里对此的解释)

三、11g里针对SecureFiles的注意事项
当你使用expdp dump SecureFiles LOBs时，由此产生的行为取决于多方面，包括export的version参数，以及当前ContentType ，以及LOB的archived 和数据缓存。下列情况下将说明这些组合
1、 一个表包含SecureFiles LOBs with ContentType 和 VERSION参数设置的版本号是或低于11.2.0.0.0,那时ContentType不能被导出
2、一个表包含SecureFiles LOBs with ContentType 和 VERSION参数设置的版本号是或高于11.2.0.0.0,那时ContentType被导出和存放在subsequent导入
3、一个表包含SecureFiles LOBs 是 currently archived and the data is cached和 VERSION参数设置的版本号是或低于11.2.0.0.0,那时SecureFiles LOB data is exported and the archive metadata is dropped。在此场景下, 如果VERSION参数设置的版本号是或高于11.1.0.0.0,  SecureFiles LOB becomes a vanilla SecureFiles LOB. 但是如果VERSION参数设置的版本号是或低于于11.1.0.0.0, 那时SecureFiles LOB将变成BasicFiles LOB.
4、一个表包含SecureFiles LOBs 是 currently archived但是数据not cached和VERSION参数设置的版本号是或低于11.2.0.0.0,将会出现ORA-45001错误
5、一个表包含SecureFiles LOBs 是 currently archived and the data is cached和 VERSION参数设置的版本号是或高于11.2.0.0.0，cached data和the archive metadata被导出

建立测试环境：
1、建立data pump环境
SQL> create or replace directory dump_dir as '/u01/dump';
Directory created.
SQL> grant read,write on directory dump_dir to public;
Grant succeeded.
2、创建scott测试表test_pump

四、测试案例
高版本导出，低版本导入
1、export脚本
1)、不带version参数
[oracle@ora11g dump]$ expdp scott/scott directory=dump_dir dumpfile=f.dmp logfile=impdp_f.log tables=test_pump;
Export: Release 11.2.0.3.0 - Production on Thu Jun 26 14:27:08 2014
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dump_dir dumpfile=f.dmp logfile=f.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 216.1 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."TEST_PUMP"                         181.5 MB 4587520 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/f.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:28:49
2)、带version参数
[oracle@ora11g dump]$ expdp scott/scott directory=dump_dir dumpfile=f1.dmp logfile=impdp_f1.log tables=test_pump version=10.2.0.5.0;
Export: Release 11.2.0.3.0 - Production on Thu Jun 26 14:35:43 2014
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dump_dir dumpfile=f1.dmp logfile=f1.log schemas=scott versi
on=10.2.0.5.0
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 216.1 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."TEST_PUMP"                         181.5 MB 4587520 rows
. . exported "SCOTT"."DEPT"                              5.742 KB       4 rows
. . exported "SCOTT"."EMP"                               8.062 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.671 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/f1.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:36:32

2、import脚本
1)、当export时不带version指定版本时，import时报错ORA-39142
[oracle@trsen01 dump]$ impdp scott/scott directory=dump_dir dumpfile=f.dmp logfile=impdp_f.log tables=test_pump;
Import: Release 10.2.0.5.0 - Production on Thursday, 26 June, 2014 14:48:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, 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 "/u01/dump/f.dmp"

2)、当export时带version指定版本号，导入时正常
[oracle@trsen01 dump]$ impdp scott/scott directory=dump_dir dumpfile=f1.dmp logfile=impdp_f.log tables=test_pump;
Import: Release 10.2.0.5.0 - Production on Thursday, 26 June, 2014 14:48:23
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=f1.dmp logfile=impdp_f.log tables=test_pump
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TEST_PUMP"                         181.5 MB 4587520 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:49:57

五、测试结果

从实验结果来看，确实像文档所说的那样，在做data pump的数据处理时，要注意跨数据库版本的事项。

===========================
相关交流信息
QQ群: 330218614
Email: 623009431@qq.com
Blog: http://blog.csdn.net/trsenzhang
============================