Home » Server Options » Replication » Oracle Golden Gate replication (Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized))
Oracle Golden Gate replication [message #668732] Tue, 13 March 2018 02:22 Go to next message
ssumesh
Messages: 5
Registered: May 2015
Location: delhi
Junior Member
Hi All,

We are trying to store counts of DML (DELETE,INSERT & UPDATE) performed on each table via Oracle Golden gate replication.

For example, if there are 1 insert, 1 update performed at source table. Then, in Target it will captured as below.

SQL> select * from SCOTT1.EMP_REP060318;

TABLE_NAME INSERT_COUNT DELETE_COUNT UPDATE_COUNT
-------------------- ------------ ------------ ------------
EMP_EXT060218 1 0 1

Please review below shared source,Target table's structure, extract & replicat file for the reference and suggest.

------------------------------------------------------------------------------------------------------------------------------------- -----------
Table Stucture:

Source Table: SCOTT.EMP_EXT060318
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Target Table: SCOTT1.EMP_REP060318
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(20)
INSERT_COUNT NUMBER(38)
DELETE_COUNT NUMBER(38)
UPDATE_COUNT NUMBER(38)

Extract Parameter file.

EXTRACT EXT0603
USERID ggs_owner@EBSDWSTR, PASSWORD ggs_owner
EXTTRAIL /u01/trails/P1
Table SCOTT.EMP_EXT060318;


Replicat Parameter file.

--this replicate will handle delete operations in separate table besides applying them in base table at target
replicat REP0603
ASSUMETARGETDEFS
userid ggs_owner@EBSDWSTR, password ggs_owner
discardfile /u01/app/oracle/product/12.1.2/oggcore_1/discard/rep0603_discard.txt, append, megabytes 10
REPLACEBADCHAR ESCAPE
ALLOWDUPTARGETMAP
map SCOTT.EMP_EXT060318 , TARGET SCOTT1.EMP_REP060318 SQLEXEC (ID test2, QUERY 'update scott1.emp_rep060318 set insert_count = insert_count+1 where @TOKEN ('TK_TABLE')=
'EMP_EXT060318' AND @TOKEN ('TK_OPTYPE')= 'INSERT'', NOPARAMS));
------------------------------------------------------------------------------------------------------------------------------------- --------------

Thanks


Re: Oracle Golden Gate replication [message #668740 is a reply to message #668732] Tue, 13 March 2018 06:47 Go to previous message
BlackSwan
Messages: 26191
Registered: January 2009
Location: SoCal
Senior Member
If I wanted to capture DML count details I would simply use AUDIT.
If you are satisfied with results from GG then so be it.
Previous Topic: Data streaming For Oracle Goldengate vs Apache Kafka
Next Topic: Dump Process Abend with "Unable to open file"
Goto Forum:
  


Current Time: Wed Oct 17 15:07:19 CDT 2018