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:

Yay, VMware Fusion and macOS Big Sur - no longer "NAT good friends" - forgive the double negative and the terrible pun ...

After macOS 11 Big Sur was released in 2020, VMware updated their Fusion product to v12 and, sadly, managed to break Network Address Trans...