Oracle GoldenGate Replicat process requires somehow to identify replicated records. When an UPDATE or DELETE command is replicated the Replicat process needs somehow to identify the row that has to be modified. The obvious solution is to use a PRIMARY KEY on the target table to avoid scanning the whole table to find the row. But what about UNIQUE INDEX? Let’s find out.
The documentation for Oracle GoldenGate is not very strict in this area. It seems that actually everybody knows how the whole mechanism works but it is not written directly in any part of the documentation. This research has been made using Oracle Database 12.1.2 and GoldenGate 12.2.0.1.170221 on Linux64 platform.
1. Expected behavior
According to the documentation for the ADD TRANDATA command:
By default, ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table.
Then the manual refers to and unidentified place in another documentat, which does not bring any new information. Actually the user has to find out what is really happening, as I have analyzed this in my previous post about the ADD TRANDATA command. What the documentation does not say is that when the GoldenGate does not find any PRIMARY KEY, but it does find a UNIQUE INDEX it threats the UNIQUE INDEX as a PRIMARY KEY. The ADD TRANDATA command will add unconditional logging to the columns from the UNIQUE INDEX as if it was a PRIMARY KEY. Actually it works also for UNIQUE CONSTRAINT which is internally exactly the same as the UNIQUE INDEX.
Another important information is that GoldenGate will choose the alphabetically first index name as the primary key. But what happens if after some time another UNIQUE INDEX appears? Let’s find out.
2. The environment
Let’s create the following replication:
- O121EURO – source database
- G122EURO – source GoldenGate
- G122AMER – target GoldenGate
- O121AMER – target database
The table schema is on the source (O121EURO) and target (O121AMER):
1 2 3 4 5 6 | CREATE TABLE jan.person ( person_id NUMERIC NOT NULL, pesel varchar2(11) NOT NULL, name varchar2(20) NOT NULL ); CREATE UNIQUE INDEX jan.person_person_id ON jan.person(person_id); |
First let’s create a Primary Extract eeuro1 on G122EURO:
1 2 3 4 | EXTRACT eeuro1 USERID GGUSER, PASSWORD xxx EXTTRAIL dirdat/ea TABLE jan.person; |
A simple default configuration has been used to configure the process:
1 2 3 4 5 6 | DBLOGIN USERID GGUSER, PASSWORD xxx ADD EXTRACT eeuro1, INTEGRATED TRANLOG, BEGIN NOW ADD EXTTRAIL dirdat/ea, EXTRACT eeuro1 REGISTER EXTRACT eeuro1 DATABASE ADD TRANDATA jan.person START EXTRACT eeuro1 |
The next step is a DataPump process peuro1 – which is created on instance G122EURO with the following configuration:
1 2 3 4 5 | EXTRACT peuro1 PASSTHRU RMTHOST amerhost, MGRPORT 7000 RMTTRAIL dirdat/pa TABLE jan.person; |
It was created using the script:
1 2 3 | ADD EXTRACT peuro1, EXTTRAILSOURCE dirdat/ea ADD RMTTRAIL dirdat/pa, EXTRACT peuro1 START EXTRACT peuro1 |
There is also a Replicat process reuro1 on the instance G122AMER with the following configuration:
1 2 3 | REPLICAT reuro1 USERID GGUSER, PASSWORD ggpwd MAP jan.person, TARGET jan.person; |
And the last element – creation scripts for the Replicat process:
1 2 3 4 | DBLOGIN USERID GGUSER, PASSWORD xxx ADD CHECKPOINTTABLE ADD REPLICAT reuro1, INTEGRATED, EXTTRAIL dirdat/pa START REPLICAT reuro1 |
The replication is set. Let’s initially fill the table with data to test if the replication works:
1 2 3 4 5 6 | INSERT INTO jan.person VALUES(1, '90010101111', 'Jan'); INSERT INTO jan.person VALUES(2, '90020202222', 'Karol'); INSERT INTO jan.person VALUES(3, '90030303333', 'Stefan'); INSERT INTO jan.person VALUES(4, '90040404444', 'Zygmunt'); INSERT INTO jan.person VALUES(5, '90050505555', 'Andrzej'); commit; |
The data is present on both – source and target:
1 2 3 4 5 6 7 8 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan 2 90020202222 Karol 3 90030303333 Stefan 4 90040404444 Zygmunt 5 90050505555 Andrzej |
In the error log of G122EURO we can see that GoldenGate has chosen the UNIQUE INDEX as the primary key:
1 2 | 2017-07-23 09:45:33 INFO OGG-06507 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: MAP (TABLE) resolved (entry jan.person): TABLE "JAN"."PERSON". 2017-07-23 09:45:33 INFO OGG-06509 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: USING the following KEY COLUMNS FOR SOURCE TABLE JAN.PERSON: PERSON_ID. |
That’s exactly what it was supposed to do. Also in G122AMER we can see the same information:
1 2 3 4 | 2017-07-23 09:45:37 INFO OGG-06505 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: MAP resolved (entry jan.person): MAP "JAN"."PERSON", TARGET jan.person. 2017-07-23 09:45:38 INFO OGG-02756 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: The definition FOR TABLE JAN.PERSON IS obtained FROM the trail file. 2017-07-23 09:45:38 INFO OGG-06511 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING following COLUMNS IN DEFAULT map BY name: PERSON_ID, PESEL, NAME. 2017-07-23 09:45:38 INFO OGG-06510 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING the following KEY COLUMNS FOR target TABLE JAN.PERSON: PERSON_ID. |
Everything seems to be perfect here. The replication seems to work.
3. Base scenario – a classic update
First let’s make a simple UPDATE and see what information we have in the GoldenGate trace. On O121EURO let’s run:
1 2 | UPDATE jan.person SET name = 'Jan Jan' WHERE name = 'Jan'; commit; |
Logdump shows the following information in the trail:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Logdump 15 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x0c) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 48 (x0030) IO TIME : 2017/07/23 09:52:31.000.000 IOType : 134 (x86) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 11 AuditPos : 52232720 Continued : N (x00) RecCount : 1 (x01) 2017/07/23 09:52:31.000.000 GGSUnifiedUpdate Len 48 RBA 2319 Name: JAN.PERSON (TDR INDEX: 1) After Image: Partition 12 G s 0000 0014 0000 0005 0000 0001 3100 0200 0700 0000 | ............1....... 034a 616e 0000 0005 0000 0001 3100 0200 0b00 0000 | .Jan........1....... 074a 616e 204a 616e | .Jan Jan BEFORE Image Len 24 (x00000018) BeforeColumnLen 20 (x00000014) COLUMN 0 (x0000), Len 5 (x0005) 0000 0001 31 | ....1 COLUMN 2 (x0002), Len 7 (x0007) 0000 0003 4a61 6e | ....Jan After Image Len 24 (x00000018) COLUMN 0 (x0000), Len 5 (x0005) 0000 0001 31 | ....1 COLUMN 2 (x0002), Len 11 (x000b) 0000 0007 4a61 6e20 4a61 6e | ....Jan Jan |
Everything here is a expected: both BEFORE and AFTER images contain the key (taken from UNIQUE INDEX) 0 column – PERSON_ID and the modified value – column 2 – NAME. During ADD TRANDATA unconditional supplemental log has been added to column 0 (PERSON_ID) – which is treated as if it was a primary key. Perfect. Of course, the UPDATE operation has been successfully replicated to the target site.
4. Test scenario – a new UNIQUE INDEX
In a perfect world all changes should also involve the replication team. But that’s not our case.Let’s assume that the development team has made an optimization and an additional index was added – for both: source and target database. We have a strong DEVOPS approach and the change has been immediately implemented on the production system. The developer, who made the change thought that the change would not impact on the replication since it is just another index for performance. Nobody has checked the influence on the replication. Actually, why should they?
Let’s add the following index on both the source O121EURO an the target O121AMER databases:
1 | CREATE UNIQUE INDEX jan.person_aa_idx ON jan.person(pesel); |
We did not touch anything at this moment – all replication processes are still running. They are not restarted. Nobody noticed the additional index. Actually, nobody is expecting that additional indexes might appear. Let’s run a test UPDATE on the source database (O121EURO):
1 2 | UPDATE jan.person SET name = 'Karol Karol' WHERE name = 'Karol'; commit; |
Everything works fine. We have the same information on the source (O121EURO) and target (O121AMER) site:
1 2 3 4 5 6 7 8 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan 4 90040404444 Zygmunt 5 90050505555 Andrzej |
Everybody is happy. Replication works, as before. And the application is working faster, since there is an additional UNIQUE INDEX. The developer who made the patch is celebrating his victory. He made the application run faster. But … after some time.
4. Replicat process
Let’s restart the Replicat process and check what happens on G122EURO:
1 2 | stop replicat reuro1
START replicat reuro1 |
Next, lets repeat the test – on the source database O121EURO:
1 2 | UPDATE jan.person SET name = 'Stefan Stefan' WHERE name = 'Stefan'; commit; |
Primary Extract is fine, DataPump is fine. Replicat on G122AMER goes abend with the following error:
1 2 3 4 5 6 7 8 9 10 11 12 | 2017-07-23 11:43:09 INFO OGG-06505 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: MAP resolved (entry jan.person): MAP "JAN"."PERSON", TARGET jan.person. 2017-07-23 11:43:09 INFO OGG-02756 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: The definition FOR TABLE JAN.PERSON IS obtained FROM the trail file. 2017-07-23 11:43:09 INFO OGG-06511 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING following COLUMNS IN DEFAULT map BY name: PERSON_ID, PESEL, NAME. 2017-07-23 11:43:09 INFO OGG-06510 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING the following KEY COLUMNS FOR target TABLE JAN.PERSON: PESEL. 2017-07-23 11:43:09 WARNING OGG-01431 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: Aborted grouped TRANSACTION ON 'JAN.PERSON', Mapping error. 2017-07-23 11:43:09 WARNING OGG-01003 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: Repositioning TO rba 2927 IN seqno 0. .... 2017-07-23 11:45:13 WARNING OGG-01431 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: Aborted grouped TRANSACTION ON 'JAN.PERSON', Mapping error. 2017-07-23 11:45:13 WARNING OGG-01003 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: Repositioning TO rba 2927 IN seqno 0. 2017-07-23 11:45:13 ERROR OGG-01172 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: Discard file (dirrpt/REURO1.dsc) exceeded MAX bytes (50000000). 2017-07-23 11:45:18 ERROR OGG-01668 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: PROCESS ABENDING. 2017-07-23 11:45:18 INFO OGG-01971 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: The previous message, 'ERROR OGG-01172', repeated 2 times. |
The pair of messages: “Aborted grouped transaction on ‘JAN.PERSON’, Mapping error.” and “Repositioning to rba 2927 in seqno 0.” is repeated 50 000 times (!), the ggserr.log grows immediately to 120MB. A 48MB discard file and 74MB report file have got created.
Restarting the Replicat reuro1 causes the whole situation to repeat. Why is that happening?
According to the trail the PERSON_ID column should be used as the primary key. This information we have read from the source database (O121EURO). But on the target database (O121AMER) there are two UNIQUE INDEXes:
- jan.person_person_id on jan.person(person_id)
- jan.person_aa_idx on jan.person(pesel);
The latter one is alpabetically first, so the Replicat process chooses this index and (PESEL) to be the primary key for replication. The Replicat process also expects that the trail will contain the PESEL column. That’s awkward. We are using a different primary key on the source and on the target database. The same information is in the error log above: Using the following key columns for target table JAN.PERSON: PESEL.
I have intentionally used all the default settings for every configuration if possible. If I have used the AUTORESTART option it would end in filling up the whole disk where GoldenGate is installed. Someone might say that this is a case for an SR, but I’m sure that Oracle support will find an explanation and will find arguments that this is absolutely a correct behavior and this is a feature. The Replicat process is just tries to reapply the transaction a primary key might change and maybe it will succeed.
What can we do now? Apart from modfying the schema, the only solution here is to force the PRIMARY KEY using the KEYCOLS clause in the Replicat parameter file. Let’s modify the content of the parameter file:
1 2 3 | REPLICAT reuro1 USERID GGUSER, PASSWORD ggpwd MAP jan.person, KEYCOLS(pesel), TARGET jan.person; |
And let’s start the Replicat process again.
1 2 3 4 | 2017-07-23 12:30:51 INFO OGG-06505 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: MAP resolved (entry jan.person): MAP "JAN"."PERSON", KEYCOLS(person_id), TARGET jan.person. 2017-07-23 12:30:51 INFO OGG-02756 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: The definition FOR TABLE JAN.PERSON IS obtained FROM the trail file. 2017-07-23 12:30:51 INFO OGG-06511 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING following COLUMNS IN DEFAULT map BY name: PERSON_ID, PESEL, NAME. 2017-07-23 12:30:51 INFO OGG-06510 Oracle GoldenGate Delivery FOR Oracle, reuro1.prm: USING the following KEY COLUMNS FOR target TABLE JAN.PERSON: PERSON_ID. |
It seems that the world has been rescued and Replicat is working now. No megabytes of discard and logs are being generated. The data has been replicated correctly and we are safe. But, are we really safe? Has everything been set up correctly?
5. Extract process
Let’s for sake restart the primary Extract on G122EURO instance. Maybe we’ll do something more: let’s restart also the Datapump process to be absolutely sure, even though the Datapump does only PASSTHRU operation:
1 2 3 4 | STOP EXTRACT eeuro1 STOP EXTRACT peuro1 START EXTRACT eeuro1 START EXTRACT peuro1 |
Le t’s run another test on the source database O121EURO:
1 2 | UPDATE jan.person SET name = 'Zygmunt Zygmunt' WHERE name = 'Zygmunt'; commit; |
On the source database we have:
1 2 3 4 5 6 7 8 9 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt Zygmunt 5 90050505555 Andrzej |
But what on the target – O121AMER?
Nothing changed:
1 2 3 4 5 6 7 8 9 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt 5 90050505555 Andrzej |
That’s awkward. Let’s look at the logs. Nothing’s there.
Source GoldenGate (G122EURO) error log contains:
1 2 3 | 2017-07-23 12:37:15 INFO OGG-06507 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: MAP (TABLE) resolved (entry jan.person): TABLE "JAN"."PERSON". 2017-07-23 12:37:15 INFO OGG-06509 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: USING the following KEY COLUMNS FOR SOURCE TABLE JAN.PERSON: PESEL. 2017-07-23 12:37:16 INFO OGG-02263 Oracle GoldenGate Capture FOR Oracle, peuro1.prm: Passthru MAP (TABLE) resolved (entry jan.person): TABLE "JAN"."PERSON". |
That’s strange. The Extract now thinks that the primary key is not PERSON_ID – but PESEL field. Just like the Replicat. That’s not what we wanted to have.
But where has the transaction gone? All processes work, but the transaction has not been replicated. Let’s try with another transaction:
1 2 | INSERT INTO jan.person VALUES(6, '90060606666', 'Zenon'); commit; |
After running this transaction on the source database (O121EURO) we have:
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt Zygmunt 5 90050505555 Andrzej 6 90060606666 Zenon 6 ROWS selected. |
But on the target – O121AMER:
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt 5 90050505555 Andrzej 6 90060606666 Zenon 6 ROWS selected. |
Ok, so INSERT operations work. And the transaction is absolutely gone missing.
It seems that messing with PRIMARY KEY and UNIQUE INDEX causes GoldenGate to do strange things with transactions. I have checked the error log and there was no information about any error. Also the trail file does not contain any information that something could be lost. It is like the UPDATE transaction never existed. I have not investigated this problem very deeply to check how to fix it or what else beside of not replicating UPDATE operations is wrong. Maybe this is a known feature fixed in one of the one-off patches for release 12.2.0.1.170221. There are so many one-off’s available for this release that maybe this error is corrected by one of them. I have not had time and enough patience to create an SR.
Let’s try to correct the error and add the proper KEYCOLS for the source Extract (G122EURO):
1 2 | STOP EXTRACT eeuro1 STOP EXTRACT peuro1 |
Let’s modify the PRM file for eeuro1 to:
1 2 3 4 | EXTRACT eeuro1 USERID GGUSER, PASSWORD ggpwd EXTTRAIL dirdat/ea TABLE jan.person KEYCOLS(person_id); |
And start the processes on G122EURO:
1 2 | START EXTRACT eeuro1 START EXTRACT peuro1 |
Next, let’s try with a test transaction on O121EURO:
1 2 | UPDATE jan.person SET name = 'Andrzej Andrzej' WHERE name = 'Andrzej'; commit; |
On source (O121EURO) we have:
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt Zygmunt 5 90050505555 Andrzej Andrzej 6 90060606666 Zenon 6 ROWS selected. |
And on target (O121AMER):
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> SELECT * FROM jan.person; PERSON_ID PESEL NAME ---------- ----------- -------------------- 1 90010101111 Jan Jan 2 90020202222 Karol Karol 3 90030303333 Stefan Stefan 4 90040404444 Zygmunt 5 90050505555 Andrzej Andrzej 6 90060606666 Zenon 6 ROWS selected. |
In the log:
1 2 3 | 2017-07-23 12:54:36 INFO OGG-06507 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: MAP (TABLE) resolved (entry jan.person): TABLE "JAN"."PERSON" KEYCOLS(person_id). 2017-07-23 12:54:36 INFO OGG-06509 Oracle GoldenGate Capture FOR Oracle, eeuro1.prm: USING the following KEY COLUMNS FOR SOURCE TABLE JAN.PERSON: PERSON_ID. 2017-07-23 12:54:38 INFO OGG-02263 Oracle GoldenGate Capture FOR Oracle, peuro1.prm: Passthru MAP (TABLE) resolved (entry jan.person): TABLE "JAN"."PERSON". |
Finally, everything seems to be fine. Of course we have not rescued the missing transaction, but what matters one row in the worlds of millions as my colleague used to say. Nobody will care. Updates are replicating and a proper primary key is being chosen. But really, everything is ok? Not yet.
6. Repeating ADD TRANDATA
Almost everything is fine now. But actually not everything.
Let’s assume that there are some maintenance tasks on the source database and ADD TRANDATA command has to be rerun.
Let’s first stop the Primary Extract:
1 | STOP EXTRACT eeuro1 |
And let’s try to remove the TRANDATA on G122EURO
1 2 | DBLOGIN USERID GGUSER, PASSWORD xxx DELETE TRANDATA jan.person |
EDIT: There was a note here about problems with deleting supplementary logs, but it was my mistake to leave the table locked. Deleting supplementary logs succeeded without problems. I have removed the fragment because it does no bring anything interesting to the topic. Thanks @Arturo from community.oracle.com for pointing my fault.
Next on G122EURO:
1 2 3 4 | GGSCI (nuc.localdomain AS GGUSER@O121EURO) 5> DELETE TRANDATA jan.person Logging OF supplemental redo log DATA disabled FOR TABLE JAN.PERSON. TRANDATA FOR scheduling COLUMNS has been disabled ON TABLE 'JAN.PERSON'. |
This time I would like to add supplemental logging so that PERSON_ID would be the primary key, not PESEL column. This can be achieved by running on G122EURO:
1 2 3 4 5 6 7 8 9 10 11 12 | GGSCI (nuc.localdomain AS GGUSER@O121EURO) 8> ADD TRANDATA jan.person, COLS(PERSON_ID) Logging OF supplemental redo DATA enabled FOR TABLE JAN.PERSON. TRANDATA FOR scheduling COLUMNS has been added ON TABLE 'JAN.PERSON'. TRANDATA FOR instantiation CSN has been added ON TABLE 'JAN.PERSON'. GGSCI (nuc.localdomain AS GGUSER@O121EURO) 9> info trandata jan.person Logging OF supplemental redo log DATA IS enabled FOR TABLE JAN.PERSON. COLUMNS supplementally logged FOR TABLE JAN.PERSON: PERSON_ID, PESEL. Prepared CSN FOR TABLE JAN.PERSON: 453990 |
The command added also supplemental logging for the PESEL column. But that’s a minor issue and we can can live with that. And actually thanks to that the DELETE TRANDATA is now possible and works. Great!
7. Conclusions
- My experiment shows that automatic GoldenGate mechanisms that choose the PRIMARY KEY are not a good way to define a replication. Every table should have explicitly specified primary key (using KEYCOLS clause) or have a PRIMARY KEY constraint. This rule should be met for both: the source and the target database. UNIQUE INDEX (or UNIQUE CONSTRAINT) is not a substitute for PRIMARY KEY constraint.
- If the source schema contains only a UNIQUE INDEX – then the ADD TRANDATA command should be used with the COLS clause.
- Beware of not filling your file system with GoldenGate errorlog. This might happen when you have AUTORESTART options set and different primary key chosen by the Extract process than by the Replicat process.
UPDATE on 02.10.2017: The behavior I have described (updates not being replicated when KEYCOLS was missing in certain conditions) has been classified by Oracle as a bug:
Bug 26553124 – GG IR Missing Certain UPDATE DMLs
Very subtle experiment!
Thank you for the unknown details!