Using GoldenGate EVENTACTIONS to customize processing, goldengate

Using GoldenGate EVENTACTIONS to customize processing, goldengate

EVENTACTIONS can specify some keywords to trigger some events. The keywords include IGNORE, DISCARD, ABORT, STOP, SHELL, TRACE, and LOG.

For Example：GGSCI (zbdba1) 6> view param ext1EXTRACT EXT1setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )USERID ogg,PASSWORD oggDDL &INCLUDE MAPPED OBJTYPE 'TABLE' &INCLUDE MAPPED OBJTYPE 'IDNEX' &INCLUDE MAPPED OBJTYPE 'SEQUENCE' &INCLUDE MAPPED OBJTYPE 'VIEW' &INCLUDE MAPPED OBJTYPE 'PROCEDURE' &INCLUDE MAPPED OBJTYPE 'FUNCTION' &INCLUDE MAPPED OBJTYPE 'PACKAGE' &INCLUDE MAPPED OBJTYPE 'MATERIALIZED VIEW' &EXCLUDE OPTYPE COMMENTDDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORTREPORTCOUNT EVERY 1 MINUTES,RATEDISCARDFILE ./dirrpt/EXTSA.DSC,APPEND,MEGABYTES 1024THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000DBOPTIONS ALLOWUNUSEDCOLUMNWARNLONGTRANS 2H,CHECKINTERVAL 3Mexttrail ./dirdat/saFETCHOPTIONS NOUSESNAPSHOT,FETCHPKUPDATECOLS,MISSINGROW REPORTtable zbdba.test;GGSCI (zbdba1) 7> view param ext2EXTRACT EXT2passthruRMTHOST 192.168.56.61,MGRPORT 7839,COMPRESSRMTTRAIL ./dirdat/satable zbdba.test;GGSCI (zbdba2) 6> view param rep1REPLICAT rep1ASSUMETARGETDEFSUSERID ogg, PASSWORD oggdiscardfile ./dirdat/rep1_discard.txt,append,megabytes 5DDL INCLUDE MAPPEDDDLOPTIONS REPORT--BATCHSQLDBOPTIONS DEFERREFCONSTDBOPTIONS LOBWRITESIZE 102400HANDLECOLLISIONSDDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20--grouptransops 1--maxtransops 1APPLYNOOPUPDATESMAP zbdba.test, TARGET zbdba.test,FILTER (@STREQ (OWNER, "ZBDBA" )), EVENTACTIONS ( IGNORE TRANS , STOP, SHELL "/ogg/hello.sh" );

If the owner field in the test table is ZBDBA, ignore the transaction, stop the replicat process, and execute the shell script.


Let's test:

Source：SQL> insert into test(owner,table_name) values('ZBDBA','zbdba');1 row created.SQL> commit  2  ;Commit complete.

Target：tail -100f ggserr.log2015-03-09 22:51:01  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rep1.prm:  REPLICAT REP1 started.2015-03-09 22:51:37  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rep1.prm:  No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.2015-03-09 22:51:37  INFO    OGG-01286  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Executing shell command '/ogg/hello.sh' due to SHELL event for target table ZBDBA.TEST in file /ogg/dirdat/sa000004, RBA 1314.2015-03-09 22:51:37  INFO    OGG-01287  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Successfully executed shell command '/ogg/hello.sh'.2015-03-09 22:51:37  WARNING OGG-01283  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Stopping process due to STOP event for target table ZBDBA.TEST in file /ogg/dirdat/sa000004, RBA 1314        STOP request pending end-of-transaction (1 records so far).2015-03-09 22:51:37  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, rep1.prm:  REPLICAT REP1 stopped normally.

Process stopped and script executed
View process:

GGSCI (zbdba2) 7> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                          REPLICAT    STOPPED     REP1        00:00:06      00:07:59   

View the script:

[ogg@zbdba2 ogg]$ cat hello.sh#!/bin/bashtouch zbdba.txt[ogg@zbdba2 ogg]$ ll zbdba.txt-rw-rw-rw- 1 ogg oinstall 0 Mar  9 22:51 zbdba.txt