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):
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 [email protected]) 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 [email protected]) 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.