IT开放社区

数据泵(Data Pump)- 表数据实现断点续传和数据追加的方式

在使用数据泵迁移数据时,如果在导入过程中,导入被意外中断。通常如果数据量小的情况下,用 TABLE_EXISTS_ACTION=TRUNCATE or FRPLACE 这两种方式最为简单方便。如果在数据量大的情况下(比如上T数据),使用以上两种方式就有些悲剧了。今天就研究测试下表数据实现断点续传和数据追加的方式。

场景一:数据源端、目标端表有主键或唯一约束,但是表数据不一致的场景

增加两个参数即可实现表数据追加:

TABLE_EXISTS_ACTION=APPEND

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

以下为测试过程:

SQL> create user test1 identified by test1;

User created.

SQL> create user test2 identified by test2;

User created.

SQL> grant dba to test1;

Grant succeeded.

SQL> grant dba to test2;

Grant succeeded.

SQL> create table test1.test (t_id number,t_name varchar2(15));

Table created.

SQL> alter table test1.test add constraint pk_t_id primary key (t_id);

Table altered.

SQL> insert into test1.test values (1,'wukong');

1 row created.

SQL> commit;

Commit complete.

SQL> !
[oracle@110204 ~]$ expdp \'/ as sysdba\' dumpfile=data_pump_dir:test.dmp nologfile=yes schemas=test1

Export: Release 11.2.0.4.0 - Production on Sat Aug 4 21:54:18 2018

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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=data_pump_dir:test.dmp nologfile=yes schemas=test1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST1"."TEST"                              5.414 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
 /u01/app/oracle/admin/itkf/dpdump/test.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Aug 4 21:54:24 2018 elapsed 0 00:00:06
[oracle@110204 ~]$ impdp \'/ as sysdba\' dumpfile=data_pump_dir:test.dmp remap_schema=test1:test2

Import: Release 11.2.0.4.0 - Production on Sat Aug 4 21:54:43 2018

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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=data_pump_dir:test.dmp remap_schema=test1:test2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST2"."TEST"                              5.414 KB       1 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Aug 4 21:54:45 2018 elapsed 0 00:00:01

[oracle@110204 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 4 21:54:54 2018

Copyright (c) 1982, 2013, Oracle.  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

SQL> select * from test1.test;

     T_ID T_NAME
---------- ---------------
        1 wukong

SQL> select * from test2.test;

     T_ID T_NAME
---------- ---------------
        1 wukong

SQL> insert into test1.test values (2,'bajie');

1 row created.

SQL> commit;

Commit complete.
SQL> !
[oracle@110204 ~]$ expdp \'/ as sysdba\' dumpfile=data_pump_dir:test1.dmp nologfile=yes schemas=test1

Export: Release 11.2.0.4.0 - Production on Sat Aug 4 21:55:56 2018

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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=data_pump_dir:test1.dmp nologfile=yes schemas=test1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST1"."TEST"                              5.429 KB       2 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
 /u01/app/oracle/admin/itkf/dpdump/test1.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Aug 4 21:56:02 2018 elapsed 0 00:00:06
[oracle@110204 ~]$ impdp \'/ as sysdba\' dumpfile=data_pump_dir:test1.dmp remap_schema=test1:test2 table_exists_action=append data_options=skip_constraint_errors

Import: Release 11.2.0.4.0 - Production on Sat Aug 4 22:00:27 2018

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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=data_pump_dir:test1.dmp remap_schema=test1:test2 table_exists_action=append data_options=skip_constraint_errors
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "TEST2"."TEST" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST2"."TEST"                              5.429 KB       1 out of 2 rows
1 row(s) were rejected with the following error:
ORA-00001: unique constraint (TEST2.PK_T_ID) violated

Rejected rows with the primary keys are:
Rejected row #1:
  column T_ID: 1
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Aug 4 22:00:33 2018 elapsed 0 00:00:06

[oracle@110204 ~]$ exit

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 4 22:00:42 2018

Copyright (c) 1982, 2013, Oracle.  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

SQL> select * from test1.test;

     T_ID T_NAME
---------- ---------------
        1 wukong
        2 bajie

SQL> select * from test2.test;

     T_ID T_NAME
---------- ---------------
        1 wukong
        2 bajie

场景二:数据源端有主键或唯一约束,目标端没有

这里实现表数据断点续传,只需要在续传前,为目标端表增加与源端相同主键或唯一约束即可使用以上相同方式实现续传。

总结:

优点:如果在使用数据泵迁移数据过程中,有意外终止或者要求停机时间。使用这种方式可以节约大量重新导入时间或者停库时间。

前提:数据源端的表必须有主键或唯一约束或有合适的列创建,否则该方法不能实现数据追加或断点续传。

已知缺点:导入日志会出现大量错误日志需要审阅,这种追加方式会降低导入效率,但是应该比重新导入要快,这个抽时间再测试吧。

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
    随机文章
      站点信息
      • 文章总数:119
      • 页面总数:2
      • 分类总数:3
      • 标签总数:3
      • 评论总数:1
      • 浏览总数:93285
      左邻右舍

      BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

      IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:dreamerqin@qq.com

      分享:

      支付宝

      微信