We are the midst of a mission critical implementation that involves an active-active setup. We have chosen GoldenGate to manage the distribution of data between the two sites (soon to be three).<\/p>\n
This is the first of a series of articles and tips on our findings as we use the software.<\/p>\n
We have spent a lot of time understanding the conflict detection and resolution capabilities of the software, and wanted to “break it” so we could document how to fix it. In our case, we simply inserted the exact same row on each database at the same time. Each failed with an ORA-0001 error, otherwise known as “unique constraint violated”.<\/p>\n
Since this was a contrived test case, the fix was fairly simple. We simply wanted to skip the transaction on each database and continue with replicat processing. In real life, it will rarely if ever be this simple in terms of a “real” problem.<\/p>\n
There are two ways to do this, the first of which is much easier. We show the second method for the sake of completeness, as well as shedding some light on another GoldenGate utility.<\/p>\n
FIRST METHOD:<\/p>\n
In this method, we run the replicat executable at the command line, rather than ussing ggsci to run it internally. We pass our replicat parameter file to it, as well as the skiptransaction argument. We could also skip several transactions if we knew the CSN (commit sequence number) at which we wanted to start.<\/p>\n
After we wait a few seconds and are confident it has gotten past the transaction to be skipped, we issue a kill against our replicat process, and then restart it. Finally, we show the tail of our ggserr.log file to show the transaction was skipped and that our replicat has been successfully restarted.<\/p>\n
\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# replicat paramfile dirprm\/cmhrep.prm skiptransaction\r\n\r\n\r\n\r\nOpened trail file \/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000005 at 2012-03-11 15:42:13\r\n\r\n2012-03-11 15:42:13 INFO OGG-01370 User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 44.16.324, position Seqno 5, RBA 377497.\r\n\r\nMAP resolved (entry ATL.TEST):\r\n map ATL.TEST, target cmh.test, sqlexec (id check_conflict, on update, beforefilter, query \"select d from cmh.test where c = :p1\", params (p1 = c)), filter (on update, before.d <> check_conflict.d, raiseerror 9999);\r\nUsing following columns in default map by name:\r\n C, D\r\n\r\nUsing the following key columns for target table CMH.TEST: C.\r\n\r\n\r\nWildcard MAP resolved (entry ATL.*):\r\n map ATL.TEST, target cmh.TEST;\r\nDetected duplicate MAP entry. Using prior MAP specification.\r\n\r\nQuit\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# ggsci\r\n\r\nOracle GoldenGate Command Interpreter for Oracle\r\nVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100\r\nLinux, x86, 32bit (optimized), Oracle 11g on Oct 4 2011 23:53:33\r\n\r\nCopyright (C) 1995, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\n\r\nGGSCI (expressdb1) 1> start cmhrep\r\n\r\nSending START request to MANAGER ...\r\nREPLICAT CMHREP starting\r\n\r\n\r\nGGSCI (expressdb1) 2> exit\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# tail -f ggserr.log\r\n2012-03-11 15:42:12 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, cmhrep.prm: REPLICAT starting.\r\n2012-03-11 15:42:13 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, cmhrep.prm: REPLICAT started.\r\n2012-03-11 15:42:13 INFO OGG-01370 Oracle GoldenGate Delivery for Oracle, cmhrep.prm: User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 44.16.324, position Seqno 5, RBA 377497.\r\n2012-03-11 15:42:39 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start cmhrep.\r\n2012-03-11 15:42:39 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.50 (START REPLICAT CMHREP ).\r\n2012-03-11 15:42:39 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT CMHREP starting.\r\n2012-03-11 15:42:39 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, cmhrep.prm: REPLICAT CMHREP starting.\r\n2012-03-11 15:42:40 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, cmhrep.prm: REPLICAT CMHREP started.\r\n<\/pre>\nSECOND METHOD:<\/p>\n
We start by showing the error at the bottom of our ggserr.log file.<\/p>\n
\r\n2012-03-07 20:42:44 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: OCI Error ORA-00001: unique constraint (CMH.ORDER_PK) violated (status = 1), SQL .\r\n2012-03-07 20:42:44 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep1.prm: Aborted grouped transaction on 'CMH.ORDERS', Database error 1 (OCI Error ORA-00001: unique constraint (CMH.ORDER_PK) violated (status = 1), SQL ).\r\n2012-03-07 20:42:44 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 6343157 in seqno 0.\r\n2012-03-07 20:42:44 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep1.prm: SQL error 1 mapping ATL.ORDERS to CMH.ORDERS OCI Error ORA-00001: unique constraint (CMH.ORDER_PK) violated (status = 1), SQL .\r\n2012-03-07 20:42:44 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep1.prm: Repositioning to rba 6343157 in seqno 0.\r\n2012-03-07 20:42:44 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep1.prm: Error mapping from ATL.ORDERS to CMH.ORDERS.\r\n2012-03-07 20:42:44 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep1.prm: PROCESS ABENDING.\r\n<\/pre>\nWe then get the current RBA in use by the replicat. This is the actual byte position in the trail file at which the replicat process will issue an fseek() call and begin processing when started.<\/p>\n
\r\n\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# ggsci\r\n\r\nOracle GoldenGate Command Interpreter for Oracle\r\nVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100\r\nLinux, x86, 32bit (optimized), Oracle 11g on Oct 4 2011 23:53:33\r\n\r\nCopyright (C) 1995, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\n\r\nGGSCI (expressdb1) 1> info rep1\r\n\r\nREPLICAT REP1 Last Started 2012-03-07 19:32 Status ABENDED\r\nCheckpoint Lag 00:06:26 (updated 00:24:59 ago)\r\nLog Read Checkpoint File \/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000000\r\n 2012-03-07 20:36:18.000204 RBA 6343157\r\n\r\n<\/pre>\nFrom the information above, we need the name of the current trail file (\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000000) and the RBA.<\/p>\n
We need to start the replicat process at the next record after the RBA above (6343157), since this one has the problem. Please note that we can’t simply increment the RBA by 1, as the RBA is an address in the file that must start with a format that GoldenGate recognizes as valid. If we change the replicat to start from an invalid address, it will throw an exception similar to the following in the ggserr.log file.<\/p>\n
\r\nIncompatible record in \/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000004, rba 3926317 (getting header).\r\n<\/pre>\nTo get the “real” address of the next record at which we would like to start processing, we use the logdump executable provided as part of the stock GoldenGate installation.<\/p>\n
\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# logdump\r\n\r\nOracle GoldenGate Log File Dump Utility\r\nVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100\r\n\r\nCopyright (C) 1995, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\n\r\nLogdump 422 >open \/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000000\r\nCurrent LogTrail is \/u01\/app\/oracle\/acfsdata\/ggate001\/oracle\/dirdat\/r1000000\r\nLogdump 423 >position 6343157\r\nReading forward from RBA 6343157\r\nLogdump 424 >next\r\n\r\n2012\/03\/07 20:36:18.000.204 Insert Len 78 RBA 6343157\r\nName: ATL.ORDERS\r\nAfter Image: Partition 4 G s\r\n 0000 0005 0000 0001 3100 0100 2800 0000 2437 3032 | ........1...(...$702\r\n 6631 3463 642d 6233 3032 2d34 6261 632d 3863 6161 | f14cd-b302-4bac-8caa\r\n 2d65 3565 6130 6336 6464 3939 3400 0200 1500 0032 | -e5ea0c6dd994......2\r\n 3031 322d 3033 2d30 373a 3230 3a33 363a 3138 | 012-03-07:20:36:18\r\n\r\nLogdump 425 >next\r\n\r\n2012\/03\/07 20:54:29.001.164 Insert Len 113 RBA 6343347\r\nName: ATL.ORDERS\r\nAfter Image: Partition 4 G b\r\n 0000 0028 0000 0024 6563 3831 3331 3965 2d66 3234 | ...(...$ec81319e-f24\r\n 372d 3438 6337 2d39 3831 662d 6266 3138 3637 3735 | 7-48c7-981f-bf186775\r\n 3938 3366 0001 0028 0000 0024 3462 3462 6565 6330 | 983f...(...$4b4beec0\r\n 2d63 6138 392d 3436 6639 2d38 6262 332d 6333 3665 | -ca89-46f9-8bb3-c36e\r\n 3266 6537 6432 3736 0002 0015 0000 3230 3132 2d30 | 2fe7d276......2012-0\r\n 332d 3037 3a32 303a 3534 3a32 39 | 3-07:20:54:29\r\n\r\nLogdump 426 >\r\n<\/pre>\nBecause we looked at the discard file we specified in the replicat parameter file, we knew the offending record had 1 for the primary key value…yeah, as I noted, the test case is contrived \ud83d\ude42<\/p>\n
In logdump, we first open the trail file obtained above. We then typed position RBA, where RBA is the RBA we obtained in the “info replicat” command above, and then “next”. This takes us to the position in trail file from which GoldenGate would start processing. As noted, we want to move to the next record and obtain its RBA. We simply type next, and see the RBA from which we would like to start processing is 6343347. We then exit from logdump, and alter our replicat to start from this RBA. Please note if your replicat is already running (it couldn’t be in our test case), you will have to stop it before issuing the alter command below.<\/p>\n
\r\nexpressdb1:oracle:ecomm1:\/u01\/app\/oracle\/acfsdata\/ggate001\/oracle# ggsci\r\n\r\nOracle GoldenGate Command Interpreter for Oracle\r\nVersion 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100\r\nLinux, x86, 32bit (optimized), Oracle 11g on Oct 4 2011 23:53:33\r\n\r\nCopyright (C) 1995, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\n\r\n\r\nGGSCI (expressdb1) 1> alter replicat rep1, extrba 6343347\r\nREPLICAT altered.\r\n\r\n\r\nGGSCI (expressdb1) 2> start rep1\r\n\r\nSending START request to MANAGER ...\r\nREPLICAT REP1 starting\r\n\r\n\r\nGGSCI (expressdb1) 3> exit\r\n<\/pre>\nUsing either method above, if you issue a “stats replicat yourrepname” you should see transactions being processed by the replicat.<\/p>\n","protected":false},"excerpt":{"rendered":"
We are the midst of a mission critical implementation that involves an active-active setup. We have chosen GoldenGate to manage the distribution of data between the two sites (soon to be three). This is the first of a series of…<\/p>\n