FILTER Not Evaluating Before Images Correctly with INSERTALLRECORDS (Doc ID 1596328.1)

Last updated on NOVEMBER 25, 2013

Applies to:

Oracle GoldenGate - Version 11.2.1.0.11 and later
Information in this document applies to any platform.

Goal

FILTER not evaluating before images correctly

Problem Description
---------------------------------------------------
MAP TEST_GG.EMPLOYEES, TARGET TEST_GG.EMPLOYEES, FILTER(ON UPDATE,before.ID
<> ID), INSERTALLRECORDS;


Always equal true even when the ID column has been updated.


Unable to get a transaction log when the primary key changes


DDL for table with supplemental log data

CREATE TABLE "TEST_GG"."EMPLOYEES"
  ( "ID" NUMBER,
  "FIRST_NAME" VARCHAR2(25),
  "LAST_NAME" VARCHAR2(25),
  "EMAIL" VARCHAR2(25),
  "SALARY" NUMBER(9,2),
  PRIMARY KEY ("ID")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ENABLE,
  SUPPLEMENTAL LOG GROUP "GGS_74043" ("ID", "FIRST_NAME", "LAST_NAME",
"EMAIL",
"SALARY") ALWAYS
  ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS"


Extract Parameter

EXTRACT EXT1
SETENV (ORACLE_SID=src)
@ USERID ggs_user, password ******
EXTTRAIL ./dirdat/et

NOCOMPRESSUPDATES
GETUPDATEBEFORES
TABLE TEST_GG.EMPLOYEES;


Replicat

REPLICAT rep3
SETENV (ORACLE_SID=tgt)
ASSUMETARGETDEFS
@ USERID ggs_user,password *****
DISCARDFILE ./dirsql/discard2.txt,purge,MEGABYTES 10
ALLOWDUPTARGETMAP
GETUPDATEBEFORES

IGNOREINSERTS
IGNOREDELETES
GETUPDATES
MAP TEST_GG.EMPLOYEES, TARGET TEST_GG.EMPLOYEES, FILTER(ON UPDATE,before.ID<> ID), INSERTALLRECORDS;
MAP TEST_GG.EMPLOYEES, TARGET TEST_GG.EMPLOYEES, FILTER (ON UPDATE,before.ID=ID);

IGNOREUPDATES
GETINSERTS
GETDELETES
MAP TEST_GG.EMPLOYEES, TARGET TEST_GG.EMPLOYEES;




update employees set id=41 where id=40;

but the record was updated (not inserted on the target)


Stats from replicat

Replicating from TEST_GG.EMPLOYEES to TEST_GG.EMPLOYEES:


*** Total statistics since 2013-10-15 16:30:20 ***
  Total inserts 0.00
  Total updates 2.00
  Total befores 2.00
  Total deletes 0.00
  Total discards 0.00
  Total operations 2.00
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms