海量数据处理_数据泵分批数据迁移

根据系统规划，需要将包含2亿条记录的表从A系统迁移到B系统（均为AIX+ORACLE10.2）,主要问题是需要在半个小时的投产窗口内完成迁移。
根据此要求，采用分批expdp/impdp方式实现。假设投产日为D日：
  * 第一批，在D-1日8点,迁移D-1日之前（  * 第二批，在D日0点,迁移D-1日(>=D-1且

  如果投产日D日为20110709,则各批次数据为:
  * 第一批，在20110708日8点,迁移20110708日之前的余额数据;
  * 第二批，在20110710日0点,迁移20110708当日的余额数据;

操作说明：
1,导出
1.1第一批导出
expdp user/pwd parfile=expdp_HIS_BAL.par
注:请根据投产时间修改导出数据范围。

1.2,第二批导出
expdp user/pwd parfile=expdp_HIS_BAL2.par
注:请根据投产时间修改导出数据范围。

2,导入
2.1创建表
sqlplus user/pwd @create_table_ghb.sql

2.2第一批导入
impdp user/pwd parfile=impdp_HIS_BAL.par
注:请根据导出文件修改导入文件列表。

2.3第二批导入
impdp user/pwd parfile=impdp_HIS_BAL2.par
注:请根据导出文件修改导入文件列表。

2.4删除列,创建索引,收集统计信息(根据需要执行)
sqlplus user/pwd @create_index_ghb.sql

----------------------------------------------------------
附件1,expdp_HIS_BAL.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL%U.dmp
logfile=exp_HIS_BAL.log
tables=HIS_BAL
QUERY=HIS_BAL:"WHERE upddat< to_date('20110708','YYYYMMDD')"
exclude=index

附件2,expdp_HIS_BAL2.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL2%U.dmp
logfile=exp_HIS_BAL2.log
tables=HIS_BAL
QUERY=HIS_BAL:"WHERE upddat >= to_date('20110708','YYYYMMDD') and upddat< to_date('20110709','YYYYMMDD')"
exclude=index

附件3,impdp_HIS_BAL.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL%U.dmp
logfile=imp_HIS_BAL.log
TABLE_EXISTS_ACTION=APPEND
remap_schema=bocnet:mh

附件4,impdp_HIS_BAL2.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL2%U.dmp
logfile=imp_HIS_BAL2.log
TABLE_EXISTS_ACTION=APPEND
remap_schema=bocnet:mh

附件5,create_table_ghb.sql:
CREATE TABLE HIS_BAL
( UPDDAT DATE not null,
  ...
)
NOLOGGING
PARTITION BY RANGE(UPDDAT)
( partition p200912 values less than (to_date('20100101','YYYYMMDD')),
  partition p201001 values less than (to_date('20100201','YYYYMMDD')),
  partition p201002 values less than (to_date('20100301','YYYYMMDD')),
  partition p201003 values less than (to_date('20100401','YYYYMMDD')),
  partition p201004 values less than (to_date('20100501','YYYYMMDD')),
  partition p201005 values less than (to_date('20100601','YYYYMMDD')),
  partition p201006 values less than (to_date('20100701','YYYYMMDD')),
  partition p201007 values less than (to_date('20100801','YYYYMMDD')),
  partition p201008 values less than (to_date('20100901','YYYYMMDD')),
  partition p201009 values less than (to_date('20101001','YYYYMMDD')),
  partition p201010 values less than (to_date('20101101','YYYYMMDD')),
  partition p201011 values less than (to_date('20101201','YYYYMMDD')),
  partition p201012 values less than (to_date('20110101','YYYYMMDD')),
  partition p201101 values less than (to_date('20110201','YYYYMMDD')),
  partition p201102 values less than (to_date('20110301','YYYYMMDD')),
  partition p201103 values less than (to_date('20110401','YYYYMMDD')),
  partition p201104 values less than (to_date('20110501','YYYYMMDD')),
  partition p201105 values less than (to_date('20110601','YYYYMMDD')),
  partition p201106 values less than (to_date('20110701','YYYYMMDD')),
  partition p201107 values less than (to_date('20110801','YYYYMMDD')),
  partition p201108 values less than (to_date('20110901','YYYYMMDD')),
  partition p201109 values less than (to_date('20111001','YYYYMMDD')),
  partition p201110 values less than (to_date('20111101','YYYYMMDD')),
  partition p201111 values less than (to_date('20111201','YYYYMMDD')),
  partition p201112 values less than (to_date('20120101','YYYYMMDD')),
  partition p201201 values less than (to_date('20120201','YYYYMMDD')),
  partition p201202 values less than (to_date('20120301','YYYYMMDD')),
  partition p201203 values less than (to_date('20120401','YYYYMMDD')),
  partition p201204 values less than (to_date('20120501','YYYYMMDD')),
  partition p201205 values less than (to_date('20120601','YYYYMMDD')),
  partition p201206 values less than (to_date('20120701','YYYYMMDD')),
  partition p201207 values less than (to_date('20120801','YYYYMMDD')),
  partition p201208 values less than (to_date('20120901','YYYYMMDD')),
  partition p201209 values less than (to_date('20121001','YYYYMMDD')),
  partition p201210 values less than (to_date('20121101','YYYYMMDD')),
  partition p201211 values less than (to_date('20121201','YYYYMMDD')),
  partition p201212 values less than (to_date('20130101','YYYYMMDD')),
  partition pmax values less than (maxvalue)
)
;

附件6,create_index_ghb.sql:
prompt 1,更改日志属性
alter table HIS_BAL logging;

prompt 2,analyze table:
begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'HIS_BAL',degree => 8);
end;
/

prompt 3,create index

CREATE UNIQUE INDEX PK_HIS_BAL ON HIS_BAL(...)
  LOCAL
  PARALLEL 8
  NOLOGGING;

ALTER INDEX PK_HIS_BAL logging noparallel;

ALTER TABLE HIS_BAL ADD CONSTRAINT PK_HIS_BAL PRIMARY KEY(...)
USING INDEX
LOCAL;

create index IDX_HIS_BAL_UPDDAT on HIS_BAL(UPDDAT)
local
parallel 8
nologging;

ALTER INDEX IDX_HIS_BAL_UPDDAT logging noparallel;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-697549/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-697549/