Loading....

Oracle Goldengate has an event marker system which enables the GoldenGate processes to perform some defined action when a specific event occurs which is recorded in the trail file.

The event record is a record will trigger the event action and this is specified using the FILTER or WHERE clause in the TABLE statement of an Extract parameter file or the MAP statement of a Replicat parameter file. It can also be specified using an SQLEXEC query or a stored procedure.

In the same Extract or Parameter file in which we specified the event record, we will use the EVENTACTIONS keyword to specify what action is to be taken by the process.

EVENTACTIONS could be specified via the keywords like IGNORE, DISCARD, ABORT, STOP, SHELL, TRACE, LOG which denote what actions should be taken now that the specified record criteria has been met.

Please refer to the Chapter ‘Customizing Oracle GoldenGate Processing” (page 276) of the Oracle GoldenGate Windows and UNIX Administrator’s Guide.

In this simple example we will see a test case where we are taking an export of the replicated table on the target server after data processing has completed on the source server.

We have a job status table and a record is being inserted into that table to denote that the processing is now complete and we can take a backup of the table which we are doing using Data Pump.

This is the Extract parameter file

EXTRACT ext1
USERID idit_prd, PASSWORD idit_prd
RMTHOST insodb02, MGRPORT 7809
RMTTRAIL ./dirdat/cc
TABLE idit_prd.myobjects ;
TABLE idit_prd.ops_job_status ;

This is the Replicat parameter file

REPLICAT rep1
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1")
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.myobjects, TARGET idit_prd.myobjects;
MAP idit_prd.ops_job_status, TARGET idit_prd.ops_job_status , FILTER (@STREQ (STATUS, "PROCESSING COMPLETE" )), EVENTACTIONS ( IGNORE TRANS , STOP, SHELL "/home/oracle/exp.sh" );

To explain this simply …

We have an event table called OPS_JOB_STATUS.

We have a FILTER clause which specifies the criteria for the event which is look for the string “PROCESSING COMPLETE” in the column STATUS of the OPS_JOB_STATUS table.

When this event occurs, the EVENTACTIONS clause specfies now what to do which is:

  • Ignore the transaction and do not replicate that insert into the OPS_JOB_STATUS table on the target side.
  • Stop the replicat process.
  • Run the UNIX shell script (on the target server since replicat process runs on target) exp.sh
  • This is the content of the exp.sh shell script

    #!/bin/ksh
    /opt/oracle/product/server/10.2.0.4.5/bin/expdp idit_prd/idit_prd parfile=/home/oracle/exp.par

    and the Data Pump parfile exp.par contents are :

    tables=MYOBJECTS
    directory=dumpdir
    logfile=dumpdir:exp.log
    dumpfile=myobjects.dmp

    Okay – so now on the target we see tha the replicat process rep1 is running fine

    GGSCI (insodb02) 12>  !
    info replicat rep1
     
    REPLICAT   REP1      Last Started 2011-04-01 13:28   Status RUNNING
    Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
    Log Read Checkpoint  File ./dirdat/cc000019
                         2011-04-01 13:06:05.059982  RBA 931
    

    Now let us assume that on the source server, that some month end processing has been completed and these records have been now replicated on the target database.

    We would like to take an export of the target table now as a kind of backup.

    We insert a row into the OPS_JOB_STATUS table

    SQL> insert into ops_job_status
      2  values
      3  (sysdate,'PROCESSING COMPLETE');
     
    1 row created.
     
    SQL> commit;
     
    Commit complete.
    

    Let us now see what has happened to the Replicat process.

    We see that it has stopped and as required a Data Pump job to export the table has been executed via the shell script which has fired as well.

    We can see that the export dumpfile myobjects.dmp has been created in the required Data Pump export directory.

    GGSCI (insodb02) 13> !
    info replicat rep1
     
    REPLICAT   REP1      Last Started 2011-04-01 13:28   Status STOPPED
    Checkpoint Lag       00:00:08 (updated 00:00:04 ago)
    Log Read Checkpoint  File ./dirdat/cc000019
                         2011-04-01 13:38:15.017514  RBA 1107
     
     
    oracle@insodb02:/u01/oracle > ls -lrt
     
    -rw-r--r--   1 oracle     dba           1051 Apr  1 13:38 exp.log
    -rw-r-----   1 oracle     dba        5009408 Apr  1 13:38 myobjects.dmp
    
    Please follow and like us:

    Last Update: May 4, 2017  

    April 5, 2011 152 Gavin Soorma
    Total 0 Votes:
    0

    Tell us how can we improve this post?

    + = Verify Human or Spambot ?

    Add A Knowledge Base Question !

    You will receive an email when your question will be answered.

    + = Verify Human or Spambot ?

    , , ,

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    *
    *

    Social media & sharing icons powered by UltimatelySocial
    Back To Top

    Add A Knowledge Base Question !

    You will receive an email when your question will be answered.

    + = Verify Human or Spambot ?