If we stop the GoldenGate replication: Will transactions running at that time on the source will be replicated before replication stops?

This is the next post on this GoldenGate series:

• Installing and patching Oracle GoldenGate 12c to the latest release
• Setting up a sample replication with GoldenGate
• Performing an initial load with GoldenGate (1) – file to replicat
• Performing an initial load with GoldenGate (2) – expdp/impdp
• Using GoldenGate event actions to suspend replication for end of day reporting

One question that pops up when stopping the replication by using an event marker is: What happens to the transactions that are running when the event occurs? Can we be sure that all transactions that are running when the event occurs are replicated to the target once they are committed? Or does the replication stop immediately?

For the test setup I created a new table in the HR schema and added it to the GoldenGate replication the same way as the event table in the last post. The definition of the table is:

SQL> desc hr.dummy_insert
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 A						    NUMBER(38)
 B						    VARCHAR2(50)
 C						    DATE

The script to populate the test table is:

declare
  ln_count pls_integer := 1000000;
begin
  for i in 1..ln_count
  loop
    insert into hr.dummy_insert (a,b,c)
           values ( i, lpad(i,50,'A'), sysdate );
  end loop;
end;
/
commit;

On my test environment this will run long enough to insert the event into the event table in a separate session while the inserts are running. So in the first session I’ll start the data load and in the second session immediately afterwards I’ll send the event to stop the replicat on the target.

Session 1 (on the source):

SQL> @do_inserts.sql

Session 2 (on the source, 1 one or two seconds later);

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

1 row created.

SQL> commit;

Commit complete.

What is the status of the replicat on the target?

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

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

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

This is as expected. How many rows do we have in the target table?

SQL> select count(*) from hr.dummy_insert;

  COUNT(*)
----------
	 0

SQL> 

Hm. Not good. This means when we stop the replicat on the target and there are ongoing transactions on the source they will not be written to the database. They are written to the trail files though (check the Goldengate log file):

2015-09-07 13:36:35  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/jj000127.
2015-09-07 13:36:35  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/jj000128 (byte -1, current EOF 0).
2015-09-07 13:36:35  INFO    OGG-01735  Oracle GoldenGate Collector for Oracle:  Synchronizing ./dirdat/jj000128 to disk.
2015-09-07 13:36:35  INFO    OGG-01971  Oracle GoldenGate Collector for Oracle:  The previous message, 'INFO OGG-01735', repeated 1 times.
2015-09-07 13:36:35  INFO    OGG-01670  Oracle GoldenGate Collector for Oracle:  Closing ./dirdat/jj000128.
2015-09-07 13:36:35  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/jj000129 (byte -1, current EOF 0).

Once you restart the replicat the transaction will be replayed:

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 78> start replicat REPLCDD

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

After some time the rows will be there on the target:

SQL> select count(*) from hr.dummy_insert;

  COUNT(*)
----------
   1000000

SQL> 

But this is not sufficient if the end of day reporting depends on all transactions that are started (but not completed) when the event to stop replication is send. Currently I do not have a solution for this. According to Oracle support the only solution to automatically achieve this is to use GoldenGate Veridata which must be separately licensed. If anybody had the same issue and solved it somehow I’d be happy to hear about it.