Tuesday 11 July 2017

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I'm tinkering with a new Oracle 12C build, using it as a database for an IBM BPM Advanced 8.5.7 environment.

However, something seems to have gone awry with my WAS -> Oracle configuration, as I see this: -

[11/07/17 06:58:16:482 BST] 000000f1 DataSourceCon E   DSRA8040I: Failed to connect to the DataSource "".  Encountered java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
  DSRA0010E: SQL State = 08006, Error Code = 12,514


in my Node Agent SystemOut.log, when I test a data source connection, and this in the ISC: -


I checked the Oracle listener: -

lsnrctl services

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-JUL-2017 07:02:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bpm857.uk.ibm.com)(PORT=1521)))
Services Summary...
Service "orcl.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB.uk.ibm.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: bpm857.uk.ibm.com, pid: 8429>
         (ADDRESS=(PROTOCOL=tcp)(HOST=bpm857.uk.ibm.com)(PORT=12518))
The command completed successfully


netstat -aon | grep LISTEN | grep 1521

tcp6       0      0 :::1521                 :::*                    LISTEN      off (0.00/0/0)

telnet `hostname` 1521

Trying 192.168.153.132...
Connected to bpm857.uk.ibm.com.
Escape character is '^]'.
telnet>
quit
Connection closed.

and then checked the database itself: -

sqlplus / as sysdba

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

VALUE
————————————————————————————————————————
orcl.uk.ibm.com


cat /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

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

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = bpm857.uk.ibm.com)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bpm857.uk.ibm.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.uk.ibm.com)
    )
  )


which explains a lot.

My JDBC data sources have the Oracle URL like this: -

jdbc:oracle:thin:@//bpm857.uk.ibm.com:1521/orcl

whereas they should be like this: -


Once I changed and saved the configuration, the Test Connection works SO much better: -


No comments:

Visual Studio Code - Wow 🙀

Why did I not know that I can merely hit [cmd] [p]  to bring up a search box allowing me to search my project e.g. a repo cloned from GitHub...