Impdp数据导入过程中exclude参数是否排除index的验证
最近有个省级项目需要迁移大批量数据（大于2T；主要的表均为分区表，并且有二级分区，单表有129个分区，7-8个二级分区）并且迁移过程中需要转换字符集，从GBK转换到UTF-8。而且，涉及到新项目功能上线，迁移时间（停机时间）需要尽快能的短。数据字符集转换和导入时间尽量控制在10H以内。
基于以上考虑，数据迁移和导入分一下几步操作：
1.从现有的测试库上导出表结构数据，导入到目标库，除去索引和约束；没有索引和约束的额外开销，单纯导数据会很快。
2.现有生产库上数据导出，尽可能采用高并发；考虑到新旧服务器CPU核数较多，准备采用parallel为32以上；
3.数据分两步导入目标库：
3.1 第一步纯数据导入，即content=data_only;
3.2 第二步追加索引和约束，即include=index,constraint
4.核对数据，表分析。
以上主要步骤1和3分别验证如下：
impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71:AAB301_341182_P3 exclude=index,constraint
不会导入索引和约束
impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 content=metadata_only exclude=index,constraint,trigger,statistics
不会导入索引和约束；经过验证：TRIGGER,TABLE_STATISTICS 包含在metadata里面,建议去除trigger,statistics
impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 content=metadata_only
索引和约束也会导入
impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71:AAB301_341182_P3 content=data_only
仅导入表数据，不含index,constraint,trigger,statistics
impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71_idx.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 include=index,constraint,trigger,statistics
追加index,constraint,trigger,statistics
实验操作过程记录如下：
[ora11g@dbsrv1 ~]$ impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 content=metadata_only exclude=index,constraint,trigger,statistics
Import: Release 11.2.0.4.0 - Production on Thu Mar 11 20:52:03 2021
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
Master table "SYSTEM"."20210311_ahxnb_ad71" successfully loaded/unloaded
Starting "SYSTEM"."20210311_ahxnb_ad71": system/******** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 content=metadata_only exclude=index,constraint,trigger,statistics
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSTEM"."20210311_ahxnb_ad71" successfully completed at Thu Mar 11 20:52:08 2021 elapsed 0 00:00:05
[ora11g@dbsrv1 ~]$ impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71:AAB301_341182_P3 content=data_only
Import: Release 11.2.0.4.0 - Production on Thu Mar 11 20:52:36 2021
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
Master table "SYSTEM"."20210311_ahxnb_ad71" successfully loaded/unloaded
Starting "SYSTEM"."20210311_ahxnb_ad71": system/******** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71:AAB301_341182_P3 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AHXNB"."AD71":"AAB301_341182"."AAB301_341182_P3" 11.20 MB 1334358 rows
Job "SYSTEM"."20210311_ahxnb_ad71" successfully completed at Thu Mar 11 20:52:43 2021 elapsed 0 00:00:07
[ora11g@dbsrv1 ~]$ impdp system/****** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71_idx.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 include=index,constraint,trigger,statistics
Import: Release 11.2.0.4.0 - Production on Thu Mar 11 20:54:05 2021
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
Master table "SYSTEM"."20210311_ahxnb_ad71" successfully loaded/unloaded
Starting "SYSTEM"."20210311_ahxnb_ad71": system/******** job_name=20210311_ahxnb_ad71 directory=DPDATA dumpfile=ahxnb_20210309_%U.dmp logfile=20210311_ahxnb_impdp_ad71_idx.log PARALLEL=6 remap_schema=AHXNB:AHXNB remap_tablespace=JHK_DATA:XNB_SYSTEM,USERS:XNB_SYSTEM tables=ahxnb.AD71 include=index,constraint,trigger,statistics
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"AHXNB"."SBTBI_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTBI_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAI_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAI_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAU_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAU_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAD_AD71" created with compilation warnings
ORA-39082: Object type TRIGGER:"AHXNB"."SBTAD_AD71" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."20210311_ahxnb_ad71" completed with 8 error(s) at Thu Mar 11 20:54:40 2021 elapsed 0 00:00:34
后续：
采用该办法后，data_only导入大约3小时，追加索引和约束大约10小时。
遇到的问题1：
ORA-31693: Table data object "AHXNB"."AC63":"AAB301_341602"."AAB301_341602_P2014" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39077: unable to subscribe agent ORA-39077: unable to subscribe agent KUPC$A_1_163050148848000 to queue "KUPC$C_1_20210312150031"
ORA-24067: exceeded maximum number of subscribers for queue SYS.KUPC$C_1_20210312150031
后续发现该问题跟impdp导入过程中parallel参数有关系（过大？），后面通过配置parallel=1重新导入前面因出错未导入的数据。怀疑ORA-24067是一个BUG，留待后续继续观察测试。
遇到的问题2：
在解决问题1，补数据的时候遇到：
ORA-31693: Table data object "AHXNB"."AC63":"AAB301_341602"."AAB301_341602_P2014" failed to load/unload and is being skipped due to error:
ORA-04098: trigger 'AHXNB.SBTBI_AC63' is invalid and failed re-validation
将该触发器disable后，数据顺利导入。后续重新enable该触发器。
