生产环境下，oracle不同用户间的数据迁移。第一部分

；任务名称：生产环境下schema ELON数据迁移至schema TIAN

########################################

测试一：测试参数

数据泵数据导出：
expdp system/oracle SCHEMAS=ELON directory=EXPDP_DIR dumpfile =ELON.dmp logfile=ELON_exp.log
确认两个用户有没有重名的对象：
SQL> select * from dba_objects where object_name in (select object_name from dba_objects where owner='ELON') and owner ='ELON_TEST';
no rows selected
数据泵数据导入：
impdp system/oracle directory=EXPDP_DIR dumpfile=ELON.dmp logfile=ELON_TEST_impdp.log remap_schema=ELON:ELON_TEST

导入过程中的报错信息：
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_INFO_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_INFO_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."T_INFO" ADD CONSTRAINT "FK_INFO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_N_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_N_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."T_BANKCARD" ADD CONSTRAINT "FK_N_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"ELON_TEST"."FK_BO_ID" failed to create with error:
ORA-02298: cannot validate (ELON_TEST.FK_BO_ID) - parent keys not found
Failing sql is:
ALTER TABLE "ELON_TEST"."BOR" ADD CONSTRAINT "FK_BO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE
经过排查，3张子表与比父表都多了一行数据，造成创建外键约束失败
SQL> select * from "ELON_TEST"."T_INFO" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
125509 128043 2018032938791546 D 0
SQL> delete from "ELON_TEST"."T_INFO" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
1 row deleted.
SQL> ALTER TABLE "ELON_TEST"."T_INFO" ADD CONSTRAINT "FK_INFO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE;
Table altered.
SQL> select * from "ELON_TEST"."T_BANKCARD" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
89890 128043 scott
SQL> delete from "ELON_TEST"."T_BANKCARD" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
SQL> ALTER TABLE "ELON_TEST"."T_BANKCARD" ADD CONSTRAINT "FK_N_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE

SQL> select * from "ELON_TEST"."BOR" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
89890 128043 40000 0 1 0 0 0 system_FK016 09-4月 -18
SQL> delete from "ELON_TEST"."BOR" a where not exists ( select 1 from "ELON_TEST"."QUEST" b where b.ID=a.N_ID);
SQL> ALTER TABLE "ELON_TEST"."BOR" ADD CONSTRAINT "FK_BO_ID" FOREIGN KEY ("N_ID") REFERENCES "ELON_TEST"."QUEST" ("ID") ENABLE

总结：
1：生产环境，动态数据写入，子表行数多与父表，在导入外键约束时，失败
2：在生产环境，有新数据写入的情况下，进行一致性逻辑导出，可以使用：参数fiashback_time或flashback_scn 可以实现数据一致性。flashback_time=systimestamp

方法二， 生成当前的scn，该生成动作会促使scn+1

SYS >select current_scn from v$database; 

CURRENT_SCN

-----------

11608775792 

 

expdp system/xxxxxx SCHEMAS=SCOTT directory=DIR_RMAN_BACKUP dumpfile=SCOTT.dmp logfile=SCOTT_exp.log flashback_scn=11608775792 

如果报ORA-39150，就用下面的选项：flashback_time=to_timestamp(localtimestamp)      flashback_time=to_timestamp_tz(systimestamp)