Hi guys
I just do a simple test as below on source, this action will cause replicate abending on target. I find this is something about supplemental log which is not added to the table before update? That is to say, any time when we create a table on source in OGG DDL, we have to wait a seconds to let the supplemental log be added, then we can be allowed to do update on this table....?
-- source: issue these commands
DROP TABLE T01;
CREATE TABLE T01 (X INT PRIMARY KEY, Y INT);
BEGIN
FOR I IN 1 .. 10 LOOP
INSERT INTO T01 VALUES (I, I);
COMMIT;
END LOOP;
COMMIT;
END;
/
UPDATE T01 SET Y=100 WHERE X=1;
COMMIT;
-- target: there will be PROCESS ABENDING in ggserr.log
2016-01-20 18:21:56 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, rep1.prm: DDL found, operation [CREATE TABLE T01 (X INT PRIMARY KEY, Y INT) (size 44)].
2016-01-20 18:21:56 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, rep1.prm: DDL is of mapped scope, after mapping new operation [CREATE TABLE VEBACKUP."T01" (X INT PRIMARY KEY, Y INT) (size 55)].
2016-01-20 18:21:56 INFO OGG-00487 Oracle GoldenGate Delivery for Oracle, rep1.prm: DDL operation included [INCLUDE MAPPED], optype [CREATE], objtype [TABLE], objowner [VEBACKUP], objname [T01].
2016-01-20 18:21:56 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [VEBACKUP].
2016-01-20 18:21:56 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, rep1.prm: Executing DDL operation.
2016-01-20 18:21:56 INFO OGG-00483 Oracle GoldenGate Delivery for Oracle, rep1.prm: DDL operation successful.
2016-01-20 18:21:56 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2016-01-20 18:21:56 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch, resulting in 0 affected rows.
2016-01-20 18:21:56 WARNING OGG-01137 Oracle GoldenGate Delivery for Oracle, rep1.prm: BATCHSQL suspended, continuing in normal mode.
2016-01-20 18:21:56 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 193908 in seqno 61.
2016-01-20 18:21:56 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep1.prm: Aborted grouped transaction on 'VEBACKUP.T01', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "VEBACKUP"."T01" SET "Y" = :a1 WHERE "X" = :b0>).
2016-01-20 18:21:56 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 193908 in seqno 61.
2016-01-20 18:21:56 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep1.prm: SQL error 1403 mapping VEBACKUP.T01 to VEBACKUP.T01 OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "VEBACKUP"."T01" SET "Y" = :a1 WHERE "X" = :b0>.
2016-01-20 18:21:56 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 195317 in seqno 61.
2016-01-20 18:21:56 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep1.prm: Error mapping from VEBACKUP.T01 to VEBACKUP.T01.
2016-01-20 18:21:56 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep1.prm: PROCESS ABENDING.
1)
SOURCE extract:
GGSCI (localhost.localdomain) 7> view param ext1
EXTRACT ext1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/aa
DISCARDFILE ./dirdat/ext1_discard.txt, APPEND, MEGABYTES 100
DISCARDROLLOVER AT 3:00
REPORTCOUNT EVERY 1 HOURS, RATE
--TRANLOGOPTIONS ALTARCHIVELOGDEST /opt/fra
--TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.arc
WARNLONGTRANS 1H, CHECKINTERVAL 5M
--DDL INCLUDE ALL
--DDL INCLUDE OBJNAME VEASMS.*
DDL INCLUDE MAPPED EXCLUDE OBJTYPE 'TRIGGER'
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10, REPORT
--exclude
TABLEEXCLUDE VEBACKUP.EX_T1;
TABLEEXCLUDE VEBACKUP.EX_T2;
--replicate table without primary key
--FETCHOPTIONS USEROWID
--TABLE VEBACKUP.T_ALL_TABLES, TOKENS (TKN-ROWID = @GETENV ("RECORD", "ROWID")) KEYCOLS (OWNER) ;
TABLE VEBACKUP.*;
2)SORUCE pump1
GGSCI (localhost.localdomain) 9> view param pump1
EXTRACT PUMP1
USERID ogg, PASSWORD ogg
PASSTHRU
RMTHOST 192.168.0.3, MGRPORT 7809
RMTTRAIL ./dirdat/pa
DISCARDFILE ./dirdat/pump1_discard.txt, APPEND, MEGABYTES 5
TABLE VEBACKUP.*;
3)
TRRGET replicate:
GGSCI (localhost.localdomain) 16> view param rep1
REPLICAT REP1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS32GB18030)
USERID ogg,PASSWORD ogg
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 1048576
ASSUMETARGETDEFS
--HANDLECOLLISIONS
ALLOWNOOPUPDATES
DISCARDFILE ./dirdat/rep1_discard.txt, APPEND, MEGABYTES 100
DDL INCLUDE MAPPED
--DDL INCLUDE ALL
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
BATCHSQL
MAPEXCLUDE VEBACKUP.EX_T1;
MAPEXCLUDE VEBACKUP.EX_T2;
MAP VEBACKUP.*, TARGET VEBACKUP.*;
--replicate table without primary key
--map vebackup.t_all_tables , target vebackup.t_all_tables colmap (usedefaults, row_id = @token ("TKN-ROWID")) keycols (row_id);
--MAP VEBACKUP.TEST6 , TARGET VEBACKUP.TEST6 COLMAP (USEDEFAULTS, ROW_ID = @TOKEN ("TKN-ROWID")) KEYCOLS (ROW_ID);
--debug
--SHOWSYNTAX
--NODYNSQL
--NOBINARYCHARS
When I