Friday, 23 February 2018

WebSphere Application Server to Oracle - Listener Says No

I saw this: -

The test connection operation failed for data source BPM Business Space data source on server nodeagent at node Node1 with the following exception: java.sql.SQLException: IO Error: The Network Adapter could not establish the connection DSRA0010E: SQL State = 08006, Error Code = 17,002. View JVM logs for further details.

whilst testing an IBM BPM database connection to Oracle 12c.

This was my WAS JDBC Datasource configuration: -


In the WAS Node Agent log, I saw this: -

[23/02/18 21:13:33:393 GMT] 00000066 DataSourceCon E   DSRA8040I: Failed to connect to the DataSource "".  Encountered java.sql.SQLException: IO Error: The Network Adapter could not establish the connection DSRA0010E: SQL State = 08006, Error Code = 17,002
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:303)
at oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:500)
at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:174)
at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:143)
at com.ibm.ws.rsadapter.DSConfigHelper$1.run(DSConfigHelper.java:1267)
at com.ibm.ws.security.auth.ContextManagerImpl.runAs(ContextManagerImpl.java:5477)
at com.ibm.ws.security.auth.ContextManagerImpl.runAsSystem(ContextManagerImpl.java:5603)
at com.ibm.ws.security.core.SecurityContext.runAsSystem(SecurityContext.java:255)
at com.ibm.ws.rsadapter.spi.ServerFunction$6.run(ServerFunction.java:567)
at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java:118)
at com.ibm.ws.rsadapter.DSConfigHelper.getPooledConnection(DSConfigHelper.java:1282)
at com.ibm.ws.rsadapter.DSConfigHelper.getPooledConnection(DSConfigHelper.java:1190)
at com.ibm.ws.rsadapter.DSConfigurationHelper.getConnectionFromDSOrPooledDS(DSConfigurationHelper.java:2075)
at com.ibm.ws.rsadapter.DSConfigurationHelper.getConnectionFromDSOrPooledDS(DSConfigurationHelper.java:1951)
at com.ibm.ws.rsadapter.DSConfigurationHelper.testConnectionForGUI(DSConfigurationHelper.java:2819)
at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at com.ibm.ws.management.DataSourceConfigHelperMBean.testConnectionToDataSource2(DataSourceConfigHelperMBean.java:556)
at com.ibm.ws.management.DataSourceConfigHelperMBean.testConnection(DataSourceConfigHelperMBean.java:484)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:88)
at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
at java.lang.reflect.Method.invoke(Method.java:620)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:292)
at javax.management.modelmbean.RequiredModelMBean$4.run(RequiredModelMBean.java:1261)
at java.security.AccessController.doPrivileged(AccessController.java:311)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:88)
at javax.management.modelmbean.RequiredModelMBean.invokeMethod(RequiredModelMBean.java:1255)
at javax.management.modelmbean.RequiredModelMBean.invoke(RequiredModelMBean.java:1093)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:832)
at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:814)
at com.ibm.ws.management.AdminServiceImpl$1.run(AdminServiceImpl.java:1335)
at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java:118)
at com.ibm.ws.management.AdminServiceImpl.invoke(AdminServiceImpl.java:1228)
at com.ibm.ws.management.connector.AdminServiceDelegator.invoke(AdminServiceDelegator.java:181)
at com.ibm.ws.management.connector.ipc.CallRouter.route(CallRouter.java:247)
at com.ibm.ws.management.connector.ipc.IPCConnectorInboundLink.doWork(IPCConnectorInboundLink.java:360)
at com.ibm.ws.management.connector.ipc.IPCConnectorInboundLink$IPCConnectorReadCallback.complete(IPCConnectorInboundLink.java:602)
at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1881)
Caused by: java.lang.Exception: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:445)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:464)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:594)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:229)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
... 56 more
Caused by: java.net.ConnectException: Connection refused
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:370)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:231)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:213)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:404)
at java.net.Socket.connect(Socket.java:643)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:162)
at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:411)
... 61 more

From an Oracle perspective, I checked the Listener configuration: -

lsnrctl start LISTENER

  LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-FEB-2018 21:16:14

  Copyright (c) 1991, 2016, Oracle.  All rights reserved.

  Starting /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

  TNSLSNR for Linux: Version 12.2.0.1.0 - Production
  System parameter file is /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Log messages written to /home/oracle/app/oracle/diag/tnslsnr/bpm856/listener/alert/log.xml
  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
  STATUS of the LISTENER
  ------------------------
  Alias                     LISTENER
  Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
  Start Date                23-FEB-2018 21:16:14
  Uptime                    0 days 0 hr. 0 min. 0 sec
  Trace Level               off
  Security                  ON: Local OS Authentication
  SNMP                      OFF
  Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/bpm856/listener/alert/log.xml
  Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  The listener supports no services
  The command completed successfully

lsnrctl status listener

  LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-FEB-2018 21:16:17

  Copyright (c) 1991, 2016, Oracle.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
  STATUS of the LISTENER
  ------------------------
  Alias                     LISTENER
  Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
  Start Date                23-FEB-2018 21:16:14
  Uptime                    0 days 0 hr. 0 min. 3 sec
  Trace Level               off
  Security                  ON: Local OS Authentication
  SNMP                      OFF
  Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
  Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/bpm856/listener/alert/log.xml
  Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  The listener supports no services
  The command completed successfully

I also validated the Service Name: -

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 23 21:19:24 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
  select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------
orcl.uk.ibm.com

SQL> 
 exit

Yep, that was the problem - I was trying to connect to the Listener using the hostname bpm856.uk.ibm.com whereas the Listener was configured to use127.0.0.1.

Once I updated the listener: -

vi /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

from: -

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


to: -

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bpm856.uk.ibm.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


and restarted the Listener: -

lsnrctl start LISTENER
lsnrctl start LISTENER

and re-validated the configuration: -

lsnrctl status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-FEB-2018 21:33:20

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bpm856.uk.ibm.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                23-FEB-2018 21:18:08
Uptime                    0 days 0 hr. 15 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/bpm86/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bpm86.uk.ibm.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


we were good to go :-)

No comments: