Integration Oracle GoldenGate tool to Vertica database

namdp2551999

Hi,

Does anyone config Oracle GoldenGate with Vertica? I want to replicate some tables from Oracle to Vertica, but seem like not working.

s_crossman

Hi. Not sure if this will help or not. About 4 years ago we did a technical exploration of Vertica and Oracle GoldenGate for Big Data, It was just after they added the generic Java connectivity support. I've attached the exploration doc that was the result of the testing. Note that it was from back in 2018 using Vertica 9 and GoldenGate 12.3, our source was MySQL not Oracle, and there was no Vertica connector just the generic Java one. There were some limitations found that exposed it as non performant and those are hightlighted. A lot has changed in Vertica since then, and presumably a lot has changed in GoldenGate. So the steps in this doc may not match current software. There are other migration tools that do have Vertica connectors and work well. Qlik Replicate and Pentaho Data Integration are a couple. Info on them can be found on our Tech Partner page https://www.vertica.com/documentation/vertica/technology-partner-integrations

I hope it helps.

s_crossman

I found the link to the original post on the GoldenGate testing. https://forum.vertica.com/discussion/240140/vertica-and-oracle-goldengate-for-big-data-jdbc-handler-a-technical-exploration

namdp2551999

Thank you so much for you help. I'll try and reponse.

namdp2551999

I was try and it's is sucessfull. Thank you so much.

danghuuanh

We tried your guide then sucessful, but the performance slow. Our Vertica cluster on 7 VMs node and Oracle goldengate on one same node of Vertica cluster.

s_crossman

Yes, as noted in tech exploration guide the JDBC handler processes records row by row, so it's more adept at OLTP type environments. At the time it didn't support the batchsql feature. A quick check in the Oracle GoldenGate 19.1 release docs indicates nothing has changed. This was the major reason we noted it didn't seem to be a good fit with Vertica.
"17.2.1 Single Operation Mode
The JDBC Handler performs SQL operations on every single trail record (row operation) when the trail record is processed by the handler. The JDBC Handler does not use the BATCHSQL feature of the JDBC API to batch operations."

danghuuanh

@s_crossman said:
> Yes, as noted in tech exploration guide the JDBC handler processes records row by row, so it's more adept at OLTP type environments. At the time it didn't support the batchsql feature. A quick check in the Oracle GoldenGate 19.1 release docs indicates nothing has changed. This was the major reason we noted it didn't seem to be a good fit with Vertica.
> "17.2.1 Single Operation Mode
> The JDBC Handler performs SQL operations on every single trail record (row operation) when the trail record is processed by the handler. The JDBC Handler does not use the BATCHSQL feature of the JDBC API to batch operations."

We using Oracle goldengate 21.5 not 19.1. Could Vertica config one node using READ UNCOMMITED to DML faster. We can use this node only for load data.


Thanks and Regards!

s_crossman

For some reason the OGG 21.1 docs link for JDBC Handler points to 19.1 https://docs.oracle.com/en/middleware/goldengate/big-data/21.1/gadbd/using-jdbc-handler.html

There is only database and session level control over isolation levels. There is no node specific control. The only theoretical solution would be to make sure native load balance is not enabled at the client end (OGG Vertica properties file gg.handler.jdbcwriter.connectionURL). This means the node in the connection string would be the only one connected to by OGG. Transactions would still be distributed. Then setting the session level isolation level in the Connection string.

Unfortunately everywhere in the docs It shows this must be some sort of compatability mode because
READ UNCOMMITTED is converted automatically converted to READ COMMITTED.

And this particular note seems to be the root reason for the conversions.
Doc page url http://doc-build1.verticacorp.com/12.0.2-hotfix/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/ChangingTheTransactionIsolationLevel.htm

Note: The Connection interface also defines several other transaction isolation constants (READ_UNCOMMITTED and REPEATABLE_READ). Since Vertica does not support these isolation levels, they are converted to READ_COMMITTED and SERIALIZABLE, respectively.

So unforutnately setting the read_uncommitted isn't actually possible and would have no impact on the session or performance.

Any chance OGG supports exporting data to a csv file? If so it might be faster to export to csv and then copy into Vertica. Then it's all done in a single transaction. Just a thought.

Regards,

danghuuanh

Dear Crossman,

We tried to use OGG cause the transaction in database source have update statements.
The previous solution using Kafka JDBC sink quite fast but not support update statement. The Copy from Kafka cannot cause duplicate records.


Thanks and Regards!