The last post in this series outlined on how to do an initial load of the target database using the “expdp/impdp” method:

In this post I’ll look into how to use event actions to suspend GoldenGate replication for end of day reporting. This is common scenario in many companies: Lets say the business day ends at 18:00 and starting 18:00 reporting shall start on the replicated database. As the reporting job(s) require(s) the data as it was at 18:00 GoldenGate replication shall be suspended for the time of reporting and restarted once reporting finished.

For the replication setup I’ll use the configuration of the last post. No need to re-setup replication as it is already running.

What we need to fulfill the above requirement is an event marker that raises an event (in this case suspend the replication). The easiest method for doing this is to create a so called event_table. So, lets do this.

As we want the event_table to be replicated we’ll need to make sure that GoldenGate knows about our table. For achieving this we’ll stop all extract processes on the source database:

GGSCI (oelgg1.it.dbi-services.com) 1> stop extract *

Sending STOP request to EXTRACT DPPUMP1 ...
Request processed.

Sending STOP request to EXTRACT EXTRCDC1 ...
Request processed.

GGSCI (oelgg1.it.dbi-services.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DPPUMP1     00:00:00      00:00:15    
EXTRACT     STOPPED     EXTRCDC1    00:00:08      00:00:14

On the target database stop the replicat process:

GGSCI (oelgg2.it.dbi-services.com) 4> stop replicat *

Sending STOP request to REPLICAT REPLCDD ...
Request processed.

GGSCI (oelgg2.it.dbi-services.com) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     REPLCDD     00:00:00      00:00:05

On both, the source and target database create our new event table:

SQL> create table hr.events ( event varchar2(50), timestamp date );

Table created.

On the source database we need to add supplemental log data to the table:

GGSCI (oelgg1.it.dbi-services.com) 1> dblogin useridalias DB1 domain admin
Successfully logged into database.

GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 2> add trandata hr.events

2015-09-04 08:49:07  WARNING OGG-06439  No unique key is defined for table EVENTS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table HR.EVENTS.
TRANDATA for scheduling columns has been added on table 'HR.EVENTS'.
GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 3>

Lets restart all extract and replicat processes and check if data gets replicated for the new event table. On the source:

GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 4> start extract *

Sending START request to MANAGER ...
EXTRACT DPPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXTRCDC1 starting


GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPPUMP1     00:20:26      00:00:09    
EXTRACT     RUNNING     EXTRCDC1    00:00:10      00:00:01

And on the target:

GGSCI (oelgg2.it.dbi-services.com) 3> start replicat *

Sending START request to MANAGER ...
REPLICAT REPLCDD starting


GGSCI (oelgg2.it.dbi-services.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLCDD     00:00:00      00:00:05

Verify if the new table is replicated by inserting a row on the source:

SQL> insert into hr.events values ( 'TEST', sysdate );

1 row created.

SQL> commit;

Commit complete.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB1

Lets check if the data is there on the target:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select * from hr.events;

EVENT						   TIMESTAMP
-------------------------------------------------- ---------
TEST						   04-SEP-15

Cool. Our event_table is ready. Now we need to define an event action on that table. On the target system we add an addititional line to the parameter file of the replicat process so that the file looks like:

REPLICAT replcdd
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/replccd.dsc, PURGE
USERIDALIAS DB2 domain admin
MAP hr.*, TARGET hr.*;
MAP hr.EVENTS, TARGET hr.EVENTS, FILTER (@STREQ (EVENT, 'SUSPEND REPLICAT' )), EVENTACTIONS (IGNORE TRANS,STOP);

For this to take effect we’ll need to restart the replicat:

GGSCI (oelgg2.it.dbi-services.com) 7> stop replicat REPLCDD

Sending STOP request to REPLICAT REPLCDD ...
Request processed.

GGSCI (oelgg2.it.dbi-services.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     REPLCDD     00:00:00      00:00:01    

GGSCI (oelgg2.it.dbi-services.com) 10> start replicat REPLCDD

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

Note: Always check the GoldenGate error log when you do modifications on the parameter files.

Lets test if this works by inserting a row into the event_table on the source system that matches our filter:

SQL> insert into hr.events values ('SUSPEND REPLICAT', sysdate);

1 row created.

SQL> commit;

Commit complete.

This should have triggered a stop request for the replicat:

GGSCI (oelgg2.it.dbi-services.com) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLCDD     00:00:00      00:00:04

Hm, does not look like the replicat stopped. Looking at the logfile this might be a hint:

2015-09-07 11:20:18  WARNING OGG-02081  Oracle GoldenGate Delivery for Oracle, replcdd.prm:  Detected duplicate TABLE/MAP entry for source table HR.EVENTS and target table hr.EVENTS. Using prior TABLE/MAP specification.

So lets specify each table in the replicat instead of using a wildcard. The new parameter file for the replicat now looks like this:

REPLICAT replcdd
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/replccd.dsc, PURGE
USERIDALIAS DB2 domain admin
MAP hr.COUNTRIES, TARGET hr.COUNTRIES;
MAP hr.DEPARTMENTS, TARGET hr.DEPARTMENTS;
MAP hr.EMPLOYEES, TARGET hr.EMPLOYEES;
MAP hr.JOBS, TARGET hr.JOBS;
MAP hr.JOB_HISTORY, TARGET hr.JOB_HISTORY;
MAP hr.LOCATIONS, TARGET hr.LOCATIONS;
MAP hr.REGIONS, TARGET hr.REGIONS;
MAP hr.EVENTS, TARGET hr.EVENTS, FILTER (@STREQ (EVENT, 'SUSPEND REPLICAT' )), EVENTACTIONS (IGNORE TRANS,STOP);

Once the replicat is restarted lets do the same test again by doing the insert on the source:

SQL> insert into hr.events values ( 'SUSPEND REPLICAT', sysdate );

1 row created.

SQL> commit;

Commit complete.

Did it work now?

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     REPLCDD     00:00:05      00:00:44

Looks much better and the GoldenGate log file does confirm it:

2015-09-07 11:27:24  WARNING OGG-01283  Oracle GoldenGate Delivery for Oracle, replcdd.prm:  Stopping process due to STOP event for target table HR.EVENTS in file ./dirdat/jj000009, RBA 2284 	STOP request pending end-of-transaction (1 records so far).
2015-09-07 11:27:29  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, replcdd.prm:  REPLICAT REPLCDD stopped normally.

Ready for end of day reporting …

Post Views: 1,676
Thumbnail [60x60]
by
Daniel Westermann