JDBC adapter connection failing after application DB upgraded to Oracle 19c

, , , , , , , ,
#1

Hi All,

Any suggestion to fix this issue??

We are using Webmethods IS 9.7. One of our application DB is upgraded from Oracle12C to 19C. After the upgrade we are facing below issue :

[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=318767104)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

Below configurations used:

|Connection Properties|
|Transaction Type|LOCAL_TRANSACTION|
|DataSource Class|oracle.jdbc.pool.OracleDataSource|
|serverName| Given**|
|user|USER_AAA|
|password|******|
|databaseName|*DBNAME|
|portNumber|1521|
|networkProtocol||
|Other Properties|driverType=thin|
|Connection Management Properties|
|Enable Connection Pooling|true|
|Minimum Pool Size|1|
|Maximum Pool Size|10|
|Pool Increment Size|1|
|Block Timeout (msec)|1000|
|Expire Timeout (msec)|1000|
|Startup Retry Count|0|
|Startup Backoff Timeout (sec)|10|

#2

Hi Sangamesh,

please check the version of your ojdbc7.jar.
You should update to Oracle Driver version 12.1.0.2, which is the last one containing an ojdbc7.jar.
All newer versions only contain an ojdbc8.jar (or additionally an ojdbc10.jar supported with JVMs 10+).
Unfortunately webMethods is not yet supporting any JVMs newer than version 8.

When running in JVM 8 you can switch to a driver version containing an ojdbc8.jar, i.e. version 12.2.0.1 or newer.

Version of the jar can be detected when opening with a zip tool like jar or 7zip and looking at the META-INF/MANIFEST.MF file.

Regards,
Holger

1 Like
#3

Could be a need to update the JAR file as Holger notes, though we’re using ojdbc6.jar with 19c successfully.

Connection refused is typically due to an incorrect port – host is there, not listening on that port. For the DB update, did the listener port of the DB change?

1 Like
#4

Hi Rob,

even when using an ojdcb6.jar is working I would prefer to update to a newer ojdbc7.jar when IS is running in JVM 7 or newer due to benefit from more stability i.e. regarding LOB handling in Thin mode.

Regards,
Holger

#5

Hi Holger,

Can you please let me know which version of Oracle Client you are using?

I have tried with odbc6 and 7 jars as well, but still I am facing the issue.

#6

I agree that using a newer driver is preferable. Given the update of the DB server, this seems like a good opportunity to update the driver since testing will likely be performed due to the DB update.

But IMO, this is not likely the root cause of the error. As we’ve all experienced over the years – trust the error message. It is saying that nothing is listening on port 1521. So either the DB changed from using the default port on the same server, or the updated DB is on a different server. If the driver was the issue, I would think the error message would be different and likely indicate a version/impedence mismatch.

Check connectivity from SQL Developer or TOAD or other tool to confirm the DB. Get the details from the DB team. It is likely a very simple issue that does not have to do with the driver version.

Of course, I could be wrong. And if it turns out to be the driver specifically I’d be very interested in learning the details of how such a problem would result in “connection refused.”

1 Like
#7

Hi reamo

I agree with you , there is a possibility of DB not accepting the connections. DB server not accepting the connections could be due to variety of reasons. Sometimes error messages doesn’t reveal the actual reason for failure. As you said, other sql tools can be used to cross verify whether the TNS listener is listening on the port 1521 or not.

Connection refusal could be due incompatible client and server as well. Sometimes its not possible identify actual cause of the error, when error can happen due to variety of reasons.

Apart from that , there is a compatibility defined by the jdbc driver vendor(oracle) w.r.t client(ojdbc driver) & DB server and the jvm where client is used. It has to be followed. So depending on the DB server and IS server you are using , please use the appropriate jdbc driver referring to vendor JDBC documentation.

Hope this helps.

Thanks
Nagasrikrishna

1 Like
#8

Hi Reamon,

Thanks for your response. I really appreciate.

A small doubt: The databaseName we give in JDBC, should be a SID or a DB service name ?

#9

I’ve never seen “connection refused” for this type of issue.

True, but that does not seem to apply here. As noted, ojdbc6 and 7 are both successfully used with 19c.

My main point – don’t guess at what may fix the issue and trust the error message. The first thing to check for “connection refused” is not the version of the client driver. The first thing to check is “is the server listening on port 1521 on that server.”

1 Like
#10

Depends on the DB listener. Can support both or either. Your DB team will tell you which to use.

#11

Hi Rob,

usually the JDBC Adapter refers to SID as the DatabaseName, whereas the internal IS-JDBC-pooling refers to the ServiceName.

Regards,
Holger

#12

Fair point but I’m not sure adapter vs. internal pool is the decision point. SID vs. service name can be a complex topic. Key aspect: work with the DB team to use the “right” one.

#13

Hi Everyone,

Thanks a lot for your suggestions. We have tried almost all of them but still facing the issues.

When using Service name : Error 12505.
When using SID : Error ORA 28040 , No matching authentication Protocol.

However we are able to connect from SQL Developer now. So checking if it is compatibility issue. We are using WM9.7 version and JDBC adapter 6.5, Any idea if these are compatible latest Oracle versions like 19C DB and 12c Clients??

We have ojdbc14 and 7 in place and Oracle client version 11.2

#14

This is still unlikely to be a driver/server version issue. This is most likely a config issue.

From your original post:

networkProtocol needs to be tcp or tcps

Miminum Pool Size – set this to 0. Not the reason for your connectivity issue, but this should be 0.

*Timeout – the defaults of 1 sec are not useful. Change them to an appropriate value. Again, not the reason for the issue but should be set to something other than 1 sec.

#15

Hi Sangamesh,

please remove the ojdbc14.jar completely (it was built with Java 1.4) and use (replace it with) ojdbc7.jar (which was built with JVM 7).
Update ojdbc7.jar to Oracle client version 12.1.0.2 (the last one which has an ojdbc7.jar available).

ojdbc14.jar has some issues with LOB handling when driver is running in thin mode which is needed by the JDBC Adapter as long as you are not adding the native oci library for the driver.

Additionally check if it is possible for you to update the JDBC Adapter to version 9.0.
See JDBC Adapter users guide for how to migrate the services to match the new services templates (there is a migration service inside the updated Adapter package).

Furthermore you should consider upgrading to wM 9.12/wM 10.x, which provide newer versions of the JDBC Adapter (9.10, 10.1 and 10.3). These versions are running in JVM 8 and therefore support newer versions of ojdbc8.jar, which will make use of the new JDBC 4 features resulting in better performance and stability.

Regards,
Holger

1 Like
#16

Hi Holger,

Thank you so much for your suggestions. The issue is now resolved. Below are the steps followed:

  1. Given SID in place of service name. (database name)
  2. Replaced all the old jars with ojdbc7 , Package reloading and IS restart.

We are now able to connect to the latest oracle 19c DBs. Thanks everyone :slight_smile:

1 Like