Sunday, May 6, 2012

Simulate effects without "DEFERREFCONST"

To simulate the issue, make sure following DBOPTIONS not enabled on Replicat.

--DBOPTIONS DEFERREFCONST


DDL statement on Source Database (with DDL replication is on)
alter session set current_schema=bbadmin;
drop table tbl_p purge;
drop table tbl_c purge;
create table tbl_p (pid number,name varchar2(20));
alter table tbl_p add constraint tbl_p_pk primary key (pid);
create table tbl_c (cid number,pid number,name varchar2(20));
alter table tbl_c add constraint tbl_c_pk primary key (cid);
alter table tbl_c add constraint tbl_c_tbl_p_fk foreign key (pid) references tbl_p on delete cascade not deferrable;
create index tbl_c_n1 on tbl_c(pid);
insert into tbl_p values (1,'a');
insert into tbl_p values (2,'b');
insert into tbl_c values (100,1,'c');
insert into tbl_c values (200,2,'c');
commit;

delete from tbl_p where pid=2;


[oracle@ncsdb02 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  1 2012 00:55:59

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*, detail

REPLICAT   RBBP01    Last Started 2012-05-06 22:38   Status ABENDED
Checkpoint Lag       00:10:35 (updated 00:02:52 ago)
Log Read Checkpoint  File /u01/ggs/dirdat/ta000013
                     2012-05-06 22:28:18.921409  RBA 11633

  Extract Source                          Begin             End            

  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-02 23:32  2012-05-06 22:27
  /u01/ggs/dirdat/ta000012                2012-05-02 23:20  2012-05-02 23:32
  /u01/ggs/dirdat/ta000011                2012-05-02 22:42  2012-05-02 23:20
  /u01/ggs/dirdat/ta000007                2012-05-01 21:45  2012-05-02 22:42
  /u01/ggs/dirdat/ta000006                2012-05-01 07:51  2012-05-01 21:45
  /u01/ggs/dirdat/ta000005                2012-05-01 07:46  2012-05-01 07:51
  /u01/ggs/dirdat/ta000005                2012-05-01 07:14  2012-05-01 07:46
  /u01/ggs/dirdat/ta000004                2012-04-30 22:39  2012-05-01 07:14
  /u01/ggs/dirdat/ta000003                2012-04-29 12:06  2012-04-30 22:39
  /u01/ggs/dirdat/ta000002                2012-04-28 18:17  2012-04-29 12:06
  /u01/ggs/dirdat/ta000000                * Initialized *   2012-04-28 18:17
  /u01/ggs/dirdat/ta000000                * Initialized *   First Record   


Current directory    /u01/ggs

Report file          /u01/ggs/dirrpt/RBBP01.rpt
Parameter file       /u01/ggs/dirprm/rbbp01.prm
Checkpoint file      /u01/ggs/dirchk/RBBP01.cpr
Checkpoint table     ggs_admin.ggschkpt
Process file         /u01/ggs/dirpcs/RBBP01.pcr
Stdout file          /u01/ggs/dirout/RBBP01.out
Error log            /u01/ggs/ggserr.log





File: $GGS_HOME/ggserr.log

2012-05-06 22:28:00  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 started.
2012-05-06 22:28:05  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT RBBP01 starting.
2012-05-06 22:28:19  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Aborted grouped transaction on 'BBADMIN.TBL_C', Database error 1403 (No data found).
2012-05-06 22:28:19  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  SQL error 1403 mapping BBADMIN.TBL_C to BBADMIN.TBL_C No data found.
2012-05-06 22:28:19  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Error mapping from BBADMIN.TBL_C to BBADMIN.TBL_C.
2012-05-06 22:28:19  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  PROCESS ABENDING.
2012-05-06 22:28:19  INFO    OGG-01237  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.

File: $GGS_HOME/dirrpt/RBBP01.dsc

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:28:00

Current time: 2012-05-06 22:28:19
Discarded record from action ABEND on error 1403

No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
                         error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*

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

Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:28:19

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:33:54

Current time: 2012-05-06 22:33:54
Discarded record from action ABEND on error 1403

No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
                         error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*

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

Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:33:54


[oracle@ncsdb02 ggs]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



Logdump 1 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 2 >pos 11633
Reading forward from RBA 11633
Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 5 >detail on
Logdump 6 >usertoken detail
Logdump 7 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42) 
RecLength  :     9  (x0009)   IO Time    : 2012/05/06 22:28:18.921.409  
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         58       AuditPos   : 33719824
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/05/06 22:28:18.921.409 Delete               Len     9 RBA 11633
Name: BBADMIN.TBL_P
Before Image:                                             Partition 4   G  b  
 0000 0005 0000 0001 32                            | ........2 
Column     0 (x0000), Len     5 (x0005) 
  
Logdump 8 >reclen 1000
Reclen set to 1000
Logdump 9 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42) 
RecLength  :    11  (x000b)   IO Time    : 2012/05/06 22:28:18.921.409  
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x02)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         58       AuditPos   : 33720668
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/05/06 22:28:18.921.409 Delete               Len    11 RBA 11757
Name: BBADMIN.TBL_C
Before Image:                                             Partition 4   G  e  
 0000 0007 0000 0003 3230 30                       | ........200 
Column     0 (x0000), Len     7 (x0007)