impdp 高级用法

----impdp 大乌龙

impdp  file=xxpar

Import: Release 19.0.0.0.0 - Production on Wed May 22 00:29:02 2024
Version 19.23.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/product/19.0.0/..par.dmp" for read--为什么找这个文件?? 原来是要parfile=xxpar
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

      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

XX_P3 partition

不会导入索引和约束

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

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

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该触发器

-------------------unusable index

1.纯数据导入，即content=data_only;

　　2.追加索引，即include=index,constraint;

    

       导入过程中，发现：

　　ORA-39083: Object type INDEX:"WXJZCSK"."IDX_ACD8_PRSENO" failed to create with error:
　　ORA-01157: cannot identify/lock data file 5050 - see DBWR trace file
　　ORA-01110: data file 5050: '+DATADG/CXJBDB/B24267126E548D42E0531A14580AE2EF/TEMPFILE/temp.1674.1067731087'

　　Failing sql is:
　　CREATE UNIQUE INDEX "WXJZCSK"."IDX_ACD8_PRSENO" ON "WXJZCSK"."ACD8" ("PRSENO") REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "AHXNB" PARALLEL 1

　　可见，使用impdp追加索引的过程中，哪怕是在impdp语句中配置了parameter参数大于1，建索引的过程parallel依然为1（这或许也是bug）。

       进一步的优化思路：

       1）小表直接导入，大表导入数据前先禁用索引和约束；

        比如：

　　alter index SYS_C00XXXXX unusable;
　　alter table TESTXX disable constraint SYS_C00XXXX;

　　注意：如果表很大的话，主键还有其他非空约束，建议不要disable。如果disable了，一方面减少了导入时的数据唯一性和非空校验，另一方面enable约束的时间也是非常长的。

       2）然后使用数据泵导入数据；

　　导入过程中有一个报错，提示跳过前面被设置成unsable的索引：

　　ORA-39817: index CLCTCXXX.STAF_XXX_NO was ignored due to:

      SKIP_UNUSABLE_INDEXES requested and index segment was initially unusable

--------skip_unusable_indexes             TRUE 也会有这个提示，数据还是insert 进来的

       3）最后并行重建索引，激活约束；

　　alter index idx_XXXXX rebuild online compute statistics parallel 16;
　　alter index idx_XXXXX noparallel;

      alter table TESTXX enable constraint SYS_C00XXXXX;
 

        小结：

　　 在数据导入过程中，挑出比较大的表进行处理。并行创建索引速度明显快很多，总体的数据导入快了很多。经过这样处理，导入总时间缩短到10小时以内。

-----------------

impdp 把表数据和索引分别导入不同的表空间

一、数据导出
expdp test/test directory=dump_dir dumpfile=test.dmp logfile=test_expdp.log tables=userinfo
——利用expdp 语句导出具体数据，如果是导出整个用户下数据，去掉 tables 参数

二、把数据导入数据表空间
impdp test/test directory=dump_dir dumpfile=test.dmp logfile=impdp_data.log remap_tablespace=system:datatb exclude=index
注释：
remap_tablespace=system:datatb ——更换数据的原system表空间为datatb数据表空间
exclude=index ——导入过程中不创建索引，导入除索引外的其它所有对象

三、把索引创建到指定的索引表空间
impdp test/test directory=dump_dir dumpfile=test.dmp logfile=impdp_index.log remap_tablespace=system:idxtb include=index
注释：
remap_tablespace=system:idxtb 更换索引表空间
include=index 创建索引，仅仅导入索引

四、查询确认
select segment_name,tablespace_name from dba_segments where owner=‘TEST’;
通过以上语句确认表数据和索引数据是否导入到指定的表空间中。

一个项目中需要在oracle数据库某个用户下导出1000多个表，导入到测试库做数据分析测试。很少遇到需要导出这么多表的情况，通常都是按schema导出，或者整库导出。考虑到expdp中include参数可以附带查询语句，本次遇到的需要导出大量具体表的建议思路：

　　1） 在数据库中建一个表exptable，把需要导出表的表明插入到该表中；

　　2）利用expdp导出命令的include参数附带select语句查询exptable表，获得需要导出的表名；

　　3）开始导出。

       过程记录如下：

      1 在数据库中建立一个导出列表exptable

       create table exptable （id number（10),tablename varchar2(20));

      2 使用sql*load工具把需要导出的表的名称输入到exptable中：

      2.1 把需要导出的表名另存为txt文本，id和表名之间用空格隔开，每行以逗号结尾；   

      2.2 创建ctl文件：

      [oracle@jxsbwxdb1 ~]$ cat a.ctl

　　load data
　　infile '/home/oracle/exptable.txt'
　　append into table jxsi.exptable
　　fields terminated by ',' trailing nullcols
　　(id,tablename)

      2.3 使用命令sqlldr输入数据

      sqlldr jxsi/****** control=a.ctl

      

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Feb 26 22:39:04 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File: a.ctl
Data File: /home/oracle/exptable.txt
Bad File: exptable.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table JXSI.EXPTABLE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
TABLENAME NEXT * , CHARACTER


Table JXSI.EXPTABLE:
1056 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1056
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Wed Feb 26 22:39:04 2020
Run ended on Wed Feb 26 22:39:04 2020

Elapsed time was: 00:00:00.13
CPU time was: 00:00:00.01

       查询数据确认是否正确导入：

SQL> select count(*) from exptable;

COUNT(*)
----------
1056

　　

       3.expdp导出语句

       

expdp system/****** parfile=exptable.par

cat exptable.par

dumpfile=20200226_exptable_%U.dmp
logfile=20200226_exptable.log
schemas=jxsi
directory=DPDATA
PARALLEL=4
COMPRESSION=all
cluster=N
include=TABLE:"IN (select tablename from exptable where tablename is not null)"

或者：

expdp jxsi/****** directory=DPDATA dumpfile=20200226_exptable_%U.dmp schemas=jxsi include=table:\"in \(select tablename from exptable \)\" logfile=20200226_exptable.log cluster=N compression=all PARALLEL=4

　　

Export: Release 11.2.0.4.0 - Production on Wed Feb 26 22:40:52 2020

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "JXSI"."SYS_EXPORT_SCHEMA_02": jxsi/******** directory=DPDATA dumpfile=20200226_exptable_%U.dmp schemas=jxsi include=table:"in (select tablename from exptable )" logfile=20200226_exptable.log cluster=N compression=all PARALLEL=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 220.7 GB