Oracle GoldenGate : Numcnv_getnumeric - Buffer Overflow When Using Filter "Where" in a Table Statement.

Numcnv_getnumeric - Buffer Overflow When Using Filter "Where" in a Table Statement.

Oracle GoldenGate - Version: 11.1.1 or later


Problem Statement:

Oracle GoldenGate (OGG) extract abends with the following error when using Filter on a numeric column


ERROR OGG-01072 NUMCNV_getNumeric(const char *, size_t, int64_t *, short *, BOOL): Buffer overflow, needed: 21, allocated: 19

The issue happens when using a filter clause against a numeric column if the column length is more than 19 digits. For example:


Extract param file:


EXTRACT ea021
EXTTRAIL ./dirdat/mm
userid ogg, password XXXX
TABLE OGGUSER.LONGTEST, FILTER (ROLLCALL > 0) ;



CREATE TABLE STATEMENT:

CREATE TABLE "LONGTEST"
( "ROLLCALL" NUMBER(31,0),
"NAME" VARCHAR2(43),
PRIMARY KEY ("ROLLCALL")
);



SAMPLE TABLE STATEMENT:

insert into OGGUSER.LONGTEST values (12345678909999999990,'Fredie');
commit;



The extract abend with the error

2012-03-24 18:06:53 ERROR OGG-01072 Oracle GoldenGate Capture for Oracle, ea021.prm: NUMCNV_getNumeric: Buffer overflow, needed: 112, allocated: 19.



Avoiding the error:
If the filter was removed, the extract would capture the record without any problems.
If a record is inserted with column length 18 or less, the filter will work fine.


REASON:

Numeric conversion code throws the above error when an input string that is greater than 19 digits. Here, scale is 0 and the value passed in has a length of 20. Hence digits will be 20. int64_t does not support such big numbers. This occurs when trying to evaluate the filter.
Without the filter, the function is not called to convert to numeric.



SOLUTION:

The solution will be implemented in OGG v12.1.

The workaround is to avoid using the filter for a column which might have more than 19 digits in its input string.

BUG REFERENCE (From metalink):

BUG:13087646 - EXTRACT ABEND WITH NUMCNV_GETNUMERIC WHEN USING FILTER

Conflict detection in an Active-Active environment

****************************************
Conflict detection in an Active-Active environment
****************************************

As Goldengate operates independently of other processes, in an active-active environment. Conflicts can occur when changes are made to the identical data on different databases at the same time. These can occur when the timing of simultaneous changes results in one of the out of sync conditioins:

# A replicated insert attempts to add a row that already exists in the target.
# The before image of a replicated update does not match the current row in the target.
# A replicated delete attempts to remove a row that doesnot exist on the target.

An instance, when UserA on dbA updates a row, and UserB on dbB updates the same row. If userB's transaction occurs before userA's transactions is synchronized to Database B, there will be a confict on the replicated transaction.

In these scenarious, Goldengate doesn't provide default procedures for handling the conflicts and this can be handled with custom conflict resolution scripts that needs to be included in the parameter files.

Here is a small demonstration in an online retail environment:

* An online shopping store is stocked with 20 limited ipods. If the quantity goes to 0, then this item is tagged to be out of stock on the online system.

* Customer A places an order for 15 ipods that came from XYZ database:
sql would be:
"update products set quantity_in_stock = quantity_in_stock -15 where product_name = 'ipod' and quantity_in_stock = 20;"

* Customer B simultaneiously places an order for 4 ipods which came from ABC database.

sql would be:
"update products set quantity_in_stock = quantity_in_stock - 4 where products_name = 'ipod' and quantity_in_stock = 20;"


In the above example, the number of ipods in stock according to the XYZ database is 5 and the number of ipods in stock according to ABC database is 16. When Goldengate attempts to update the ABC database with the transaction from customer A, the transaction will fail because the before image of quantity_in_stock is 20 which does not match the current figure value which is 16. Reversely the transaction replicated to the ABC database will also fail because the before image of quantity_in_stock 20 does not match the current value of 5.

So, unless a conflict resolution script is not written, Goldengate doesnot veirfy target row data before replicating the changes. And this conflict resolution can be handled by writing a sql stored procedure or a query using the SQLEXEC parameter in the replicat parameter file on both the databases.