My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.