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

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

测试一：测试参数

数据泵数据导出：

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)