goldengate（16）--级联删除约束对数据复制的影响

Disabling triggers and cascade delete constraints

示例：cascade delete constraints（级联删除约束）

源端操作：

1.创建表GIS_TEST.CLASS

create table gis_test.CLASS(ID VARCHAR2(2) not null, CLASS_NAME VARCHAR2(20));
alter table gis_test.CLASS add constraint PK_CLASS primary key (ID);

插入数据：

insert into gis_test.class values(1,'yuwen');
insert into gis_test.class values(2,'shuxue');
insert into gis_test.class values(3,'yingyu');
commit;

SQL> select * from gis_test.class;

ID CLASS_NAME
-- --------------------
1 yuwen
2 shuxue
3 yingyu

2.创建表GIS_TEST.STUDENTS
create table gis_test.STUDENTS(ID VARCHAR2(4) not null, CLASS_ID VARCHAR2(2) not null, STU_NAME VARCHAR2(20), STU_AGE NUMBER);
alter table gis_test.STUDENTS add constraint PK_STU primary key (ID);
alter table gis_test.STUDENTS add constraint FK_STU foreign key (CLASS_ID) references gis_test.CLASS (ID) on delete cascade;

插入数据：

insert into gis_test.students values(10,1,'zhang','12');
insert into gis_test.students values(11,1,'wang','14');
insert into gis_test.students values(12,3,'li','11');
commit;

SQL> select * from gis_test.students;

ID   CLASS_ID STU_NAME                STU_AGE
---- -------- -------------------- ----------
10   1        zhang                        12
11   1        wang                         14
12   3        li                                11

该表STUDENTS 列CLASS_ID 有外键，且级联表CLASS列ID,也就是说，当CLASS表删除的时候，会级联删除表STUDENTS的记录

3.数据库字典表查看

SQL> select * from dba_constraints where wner='GIS_TEST' and constraint_type='R' ;

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
------------------------------------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
GIS_TEST FK_STU R STUDENTS GIS_TEST PK_CLASS CASCADE ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2013-03-21

4.删除CLASS表记录

delete from gis_test.class where id=1;
commit;

SQL> select * from gis_test.class;

ID CLASS_NAME
-- --------------------
2 shuxue
3 yingyu

级联删除了STUDENTS 两条记录：

SQL> select * from gis_test.students;

ID CLASS_ID STU_NAME STU_AGE
---- -------- -------------------- ----------
12 3 li 11

目标端操作：

目标端查看记录，还是没有删除，而且replicat进程出现异常，状态为abended.

查看discard file:

Current time: 2013-03-21 22:40:14
Discarded record from action ABEND on error 1403

Aborting transaction on /u01/app/oracle/ggs/dirdat/rtrail/hr/pp beginning at seqno 20 rba 12980
error at seqno 20 rba 13104
Problem replicating GIS_TEST.STUDENTS to GIS_TEST.STUDENTS
Record not found
Mapping problem with delete record (target format)...
*
ID = 10
*

Continuing to discard records up to the last discarded record from action ABEND

Operation discarded from seqno 20 rba 12980
Aborted delete from GIS_TEST.CLASS to GIS_TEST.CLASS (target format)...
*
ID = 1
*
Process Abending : 2013-03-21 22:40:14

Oracle GoldenGate Delivery for Oracle process started, group REP_HR discard file opened: 2013-03-21 22:46:12

Current time: 2013-03-21 22:46:14
Discarded record from action ABEND on error 1403

Aborting transaction on /u01/app/oracle/ggs/dirdat/rtrail/hr/pp beginning at seqno 20 rba 12980
error at seqno 20 rba 13104
Problem replicating GIS_TEST.STUDENTS to GIS_TEST.STUDENTS
Record not found
Mapping problem with delete record (target format)...
*
ID = 10
*

Continuing to discard records up to the last discarded record from action ABEND

Operation discarded from seqno 20 rba 12980
Aborted delete from GIS_TEST.CLASS to GIS_TEST.CLASS (target format)...
*
ID = 1
*
Process Abending : 2013-03-21 22:46:14

上面的黑体字代表什么意思？

就是说，在目标端库CLASS表复制了delete操作，但是STUDENTS表却在进行delete操作的时候，却说没有记录（Record not found），为什么？根本原因是表CLASS与STUDENTS之间有删除级联关系（cascade delete constraints）造成了STUDENTS表找不到记录的错误。

解释一下：

a.源端表CLASS 进行了DELETE操作,

b.那么源端表STUDENTS就要进行级联DELETE,

c. 那么这两个DELETE操作都要复制到目标端

d .目的端表CLASS 复制DELETE操作,没有问题

e. 目标端表STUDENTS 复制源端级联DELETE的操作，没有问题

f. 这个时候，因为目标端表CLASS进行了DELETE,那么目标端表STUDENTS也要进行级联delete,这个与（e）是没有关系的，因为STUDENTS表的数据被（e）步骤的时候已经DELETE掉了，所以，本步骤就找不到数据了，因此报错。

解决的方法是，在目的端表STUDENTS禁止使用外键：

alter table gis_test.students modify constraint FK_STU disable;

SQL> select * from dba_constraints where wner='GIS_TEST' and constraint_type='R';

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
GIS_TEST FK_STU R STUDENTS GIS_TEST PK_CLASS CASCADE DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 2013-03-21

表STUDETNS禁止外键之后，

再次启动start replicat rep_hr,

rplicat进程状态running

目标端也成功看到了数据。

SQL> select * from gis_test.class;

ID CLASS_NAME
-- --------------------
2 shuxue
3 yingyu

SQL> select * from gis_test.students;

ID CLASS_ID STU_NAME STU_AGE
---- -------- -------------------- ----------
12 3 li 11

ITPub博客

goldengate（16）

最新文章