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:
This is the content of the exp.sh shell script
/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 :
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