Monday, 24 July 2017

java.sql.SQLException: IO Error: The Network Adapter could not establish the connection DSRA0010E: SQL State = 08006, Error Code = 17,002

I saw this: -


The test connection operation failed for data source BPM Business Process Choreographer 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 JDBC data sources configured to connect to a newly-minted Oracle 12c database.

I checked the Oracle box to ensure that the listener was running: -

netstat -aon | grep LISTEN

tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp6       0      0 :::36620                :::*                    LISTEN      off (0.00/0/0)


but I wasn't able to connect using telnet from either the BPM 8.5.7 VM or, more interestingly, the Oracle box itself: -

telnet oracle 1521

Trying 192.168.153.131...
telnet: connect to address 192.168.153.131: Connection refused

so it felt more like networking or, to be more accurate, nOtworking :-)

I again checked the listener: -

lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JUL-2017 15:21:04

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                24-JUL-2017 13:26:23
Uptime                    0 days 1 hr. 54 min. 41 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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(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


I then started to dig into the OS more: -

sestatus 

SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          disabled
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      28


service firewalld status

Redirecting to /bin/systemctl status  firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

Jul 24 12:31:09 oracle.uk.ibm.com systemd[1]: Starting firewalld - dynamic firewall daemon...
Jul 24 12:31:10 oracle.uk.ibm.com systemd[1]: Started firewalld - dynamic firewall daemon.
Jul 24 11:33:20 oracle.uk.ibm.com systemd[1]: Stopping firewalld - dynamic firewall daemon...
Jul 24 11:33:20 oracle.uk.ibm.com systemd[1]: Stopped firewalld - dynamic firewall daemon.


Wondering whether selinux was the problem, I changed the configuration: -

vi /etc/selinux/config

changing from: -

SELINUX=enforcing

to: -

SELINUX=disabled

and rebooted, but to no avail.

I then tried this: 

telnet localhost 1521

from the Oracle box itself.

Which worked ….

So I revisited the lsnrctl status again 


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

I started wondering whether the listener was "bound" to the 127.0.0.1 address ….

So I reconfigured it …

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

changing 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 = localhost)(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 = oracle.uk.ibm.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

and restarted it: -

lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JUL-2017 15:41:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.uk.ibm.com)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JUL-2017 15:41:56

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/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.uk.ibm.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.uk.ibm.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                24-JUL-2017 15:41:56
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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.uk.ibm.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


and now telnet works: -

telnet `hostname` 1521

Trying 192.168.153.131...
Connected to oracle.uk.ibm.com.
Escape character is '^]'.
^]quit

telnet>
quit
Connection closed.

More importantly …

No comments: