Oracle Autonomous DataWarehouse (ADW) connectivity issue

[msdawood]

Hi Team,

I'm facing a weird scenario.

I'm trying to connect oracle fusion from Talend, I've created a connection and tested it from metadata...Connection is established and successful... Even I could retrieve the schemas of the tables.

 

But when i used the same connection in the job I'm getting the below error...

Starting job testoraclefusion at 18:13 14/09/2021.

[INFO ]: farah_dev.testoraclefusion_0_1.testoraclefusion - TalendJob: 'testoraclefusion' - Start.

[statistics] connecting to socket on port 3744

[statistics] connected

[DEBUG]: farah_dev.testoraclefusion_0_1.testoraclefusion - tDBConnection_1 - Start to work.

[DEBUG]: farah_dev.testoraclefusion_0_1.testoraclefusion - tDBConnection_1 - Parameters:CONNECTION_TYPE = ORACLE_RAC | DB_VERSION = ORACLE_18 | RAC_URL = "jdbc:oracle:thin:@(description=(address=(protocol=tcps)(port=1522)(host= <REMOVED>))(connect_data=(service_name=<REMOVED>))(security=(ssl_server_cert_dn=\"C<REMOVED>, OU=Oracle ADB DUBAI, O=Oracle Corporation, L=Redwood City, ST=California, C=US\")))" | SCHEMA_DB = "" | USER = "XXBIP" | PASS = enc:... | PROPERTIES = "javax.net.ssl.trustStoreType=SSO&javax.net.ssl.keyStoreType=SSO&javax.net.ssl.trustStore=F:/Talend/FILES/ADW/cwallet.sso&javax.net.ssl.keyStore=F:/Talend/FILES/ADW/cwallet.sso" | USE_SHARED_CONNECTION = false | SPECIFY_DATASOURCE_ALIAS = false | AUTO_COMMIT = false | UNIFIED_COMPONENTS = tOracleConnection | 

[DEBUG]: farah_dev.testoraclefusion_0_1.testoraclefusion - tDBConnection_1 - Driver ClassName: oracle.jdbc.OracleDriver.

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:854)

at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)

at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)

at farah_dev.testoraclefusion_0_1.testoraclefusion.tDBConnection_1Process(testoraclefusion.java:558)

at farah_dev.testoraclefusion_0_1.testoraclefusion.runJobInTOS(testoraclefusion.java:1180)

at farah_dev.testoraclefusion_0_1.testoraclefusion.main(testoraclefusion.java:885)

Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:571)

at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:548)

at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:682)

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:309)

at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1596)

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588)

... 9 more

Caused by: oracle.net.ns.NetException: Unable to initialize ssl context.

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:346)

at oracle.net.nt.CustomSSLSocketFactory.getSSLContext(CustomSSLSocketFactory.java:307)

at oracle.net.nt.CustomSSLSocketFactory.getSSLSocketEngine(CustomSSLSocketFactory.java:273)

at oracle.net.nt.TcpsNTAdapter.connect(TcpsNTAdapter.java:174)

at oracle.net.nt.ConnOption.connect(ConnOption.java:174)

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:510)

... 14 more

Caused by: oracle.net.ns.NetException: Unable to initialize the key store.

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:619)

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:324)

... 19 more

Caused by: java.security.KeyStoreException: SSO not found

at java.base/java.security.KeyStore.getInstance(KeyStore.java:878)

at oracle.net.nt.CustomSSLSocketFactory.getKeyStoreInstance(CustomSSLSocketFactory.java:767)

at oracle.net.nt.CustomSSLSocketFactory.loadKeyStore(CustomSSLSocketFactory.java:901)

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:610)

... 20 more

Caused by: java.security.NoSuchAlgorithmException: SSO KeyStore not available

at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:159)

at java.base/java.security.Security.getImpl(Security.java:700)

at java.base/java.security.KeyStore.getInstance(KeyStore.java:875)

... 23 more

[FATAL]: farah_dev.testoraclefusion_0_1.testoraclefusion - tDBConnection_1 IO Error: The Network Adapter could not establish the connection

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:854)

at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)

at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)

at farah_dev.testoraclefusion_0_1.testoraclefusion.tDBConnection_1Process(testoraclefusion.java:558)

at farah_dev.testoraclefusion_0_1.testoraclefusion.runJobInTOS(testoraclefusion.java:1180)

at farah_dev.testoraclefusion_0_1.testoraclefusion.main(testoraclefusion.java:885)

Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:571)

at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:548)

at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:682)

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:309)

at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1596)

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:588)

... 9 more

Caused by: oracle.net.ns.NetException: Unable to initialize ssl context.

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:346)

at oracle.net.nt.CustomSSLSocketFactory.getSSLContext(CustomSSLSocketFactory.java:307)

at oracle.net.nt.CustomSSLSocketFactory.getSSLSocketEngine(CustomSSLSocketFactory.java:273)

at oracle.net.nt.TcpsNTAdapter.connect(TcpsNTAdapter.java:174)

at oracle.net.nt.ConnOption.connect(ConnOption.java:174)

at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:510)

... 14 more

Caused by: oracle.net.ns.NetException: Unable to initialize the key store.

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:619)

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:324)

... 19 more

Caused by: java.security.KeyStoreException: SSO not found

at java.base/java.security.KeyStore.getInstance(KeyStore.java:878)

at oracle.net.nt.CustomSSLSocketFactory.getKeyStoreInstance(CustomSSLSocketFactory.java:767)

at oracle.net.nt.CustomSSLSocketFactory.loadKeyStore(CustomSSLSocketFactory.java:901)

at oracle.net.nt.CustomSSLSocketFactory.getKeyManagerArray(CustomSSLSocketFactory.java:610)

... 20 more

Caused by: java.security.NoSuchAlgorithmException: SSO KeyStore not available

at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:159)

at java.base/java.security.Security.getImpl(Security.java:700)

at java.base/java.security.KeyStore.getInstance(KeyStore.java:875)

... 23 more

[statistics] disconnected

Job testoraclefusion ended at 18:13 14/09/2021. [Exit code = 1]

please help me on this

[Anonymous]

Hello,

Did you put the full string from your tns names, including the security part?

Could you please show us your connection string? Please mask your sensitive data.

Best ergards

Sabrina

[msdawood]

@Xiaodi Shi​ yes...I've included the security part...

jdbc:oracle:thin:@(description=(address=(protocol=tcps)(port=1522)(host=<>dubai-1.oraclecloud.com))(connect_data=(service_name=<>_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=<>dubai-1.oraclecloud.com, OU=Oracle ADB DUBAI, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

I think... the same scenario is also faced by other users also...

https://community.talend.com/s/feed/0D53p00007vCm3NCAS

I hope there is solution for this

[Anonymous]

Hello,

Which version of talend you are using?

We met this issue before and it is fixed in V 7.3.1.

Did you include the step to add ojdbc8 jar to Talend Studio components?

Could you try below connection string by filling correct details as per your DB. Those details you can find in tnsnames file.

jdbc:oracle:thin:@(description=(address=(protocol=tcps)(port=1522)(host=))(connect_data=(service_name=))(security=(ssl_server_cert_dn= '')))

Also, in Trust store path and Key store path give the "cwallet.sso" path from the wallet file and leave the password field blank.

Feel free to let us know if it helps.

Best regards

Sabrina

[msdawood]

Hi ... we are using V 7.3.1 only... I've added ojdbc8 jat well...

[msdawood · Accepted solution]

I've Fixed it...

(1) Added below jars

-oraclepki.jar

-osdt_cert.jar

-osdt_core.jar

(2) added keystore using tsetkeystore component

this JIRA helped

https://jira.talendforge.org/browse/TDI-41503?jql=summary%20~%20%22oracle%20autonomous*%22%20OR%20description%20~%20%22oracle%20autonomous*%22%20ORDER%20BY%20lastViewed%20DESC

[Anonymous]

Hello,

Great this issue is fixed. Have you thought of entering our competition?

https://community.talend.com/s/blog/aGn5b000000CaZw/getting-smart-about-talend-tip-of-the-day

There are rewards for winners!

Best regards

Sabrina

[PS1653481830]

Thanks msdawood for the above post. I am facing the same issue. Working the connection in Metadata Connection but failing while calling in Talend job.

Where is the Class path? Just wondering in which path I should copy these jar files?

Thanks again

PS

[PS1653481830]

I am facing the same issues.

Where is the Class path? Just wondering in which path I should copy these jar files?

Thanks and Regards

PS

[Anonymous]

Hello,

You are able to download and install these jar files using the Modules view.

Here is online document about: TalendHelpCenter: Installing external modules manually using the Modules view

And a KB article about: https://community.talend.com/s/article/Resolving-class-and-JAR-related-issues

Best regards

Sabrina