ORA-12514, TNS:listener does not currently know of service requested in connect descriptor DSRA0010E: SQL State = 08006, Error Code = 12,514

So 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: 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. View JVM logs for further details.

when testing a JDBC data source connection between WebSphere Application Server 8.5.5.11 and Oracle 12c.

This is what appears in the WAS Node Agent log ( SystemOut.log ) : -

[3/7/17 14:47:23:940 UTC] 00000086 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
...

Caused by: java.lang.Exception: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

The URL configured within the data source was: -

jdbc:oracle:thin:@oracle.novalocal:1521/orcl

and I knew that the WAS box could resolve the hostname of the Oracle box, and vice versa.

I'd also ensured that the listener port ( 1521 ) was accessible between the two boxes, which I'd tested via Telnet (!) : -

telnet oracle.novalocal 1521

which proved the network connection ……..

Or did it ?

On the Oracle box, I started to dig into the problem, by checking the listener: -

lsnrctl status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-MAR-2017 05:46:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.novalocal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                07-MAR-2017 05:44:10
Uptime                    0 days 0 hr. 2 min. 18 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.novalocal)(PORT=1521)))
Services Summary...
Service "orcl.novalocal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.novalocal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

and then looking at the Listener trace: -

tail -f /home/oracle/app/oracle/diag/tnslsnr/oracle/listener/trace/listener.log

...

07-MAR-2017 05:44:56 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wasadmin))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=9.20.65.171)(PORT=32860)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
…

Note the port listed in the trace: -

32860

Ah-ha, I thought, and added that port into the firewall rules that sit between the two boxes ( I'm using OpenStack so we have a Security Group and some Rules.

Same error as before, and this time the Listener trace showed: -

07-MAR-2017 05:51:53 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wasadmin))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=9.20.65.171)(PORT=32882)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

In other words, the port number is changing ….

So I went round and round the rabbit hole …

… before realising that, if Oracle is showing the port range that WAS is using, I must be getting past the OpenStack "firewall" 

Then I found and read this: -

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

which said, in part: -

…

I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

select value from v$parameter where name='service_names'

…

This led me to check the Listener using tnsping : -

tnsping `hostname`

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 07-MAR-2017 15:55:48

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

Used parameter files:
/home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=::1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=9.20.65.75)(PORT=1521)))
OK (0 msec)

and then check the Service Name: -

sqlplus / as sysdba

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

VALUE
————————————————————————————————————————

orcl.novalocal

at which point the screams could be heard half-way across London.

Yes, the Service Name in Oracle did NOT match that in the WAS JDBC Data Source :-(

Once I changed it to: -

jdbc:oracle:thin:@oracle.novalocal:1521/orcl.novalocal

it just worked

[3/7/17 16:00:22:394 UTC] 00000086 DSConfigurati I   DSRA8203I: Database product name : Oracle
[3/7/17 16:00:22:395 UTC] 00000086 DSConfigurati I   DSRA8204I: Database product version : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[3/7/17 16:00:22:396 UTC] 00000086 DSConfigurati I   DSRA8205I: JDBC driver name  : Oracle JDBC driver
[3/7/17 16:00:22:396 UTC] 00000086 DSConfigurati I   DSRA8206I: JDBC driver version  : 12.1.0.1.0
[3/7/17 16:00:22:398 UTC] 00000086 DSConfigurati I   DSRA8025I: Successfully connected to DataSource.

So I know where I went wrong on the WAS / BPM side.

When I created the Deployment Environment, I specified orcl as the Database Name: -

bpm.de.db.1.databaseName=orcl

whereas it should've been orcl.novalocal 

However, I'm suspecting that I messed up the database configuration using dbca on the Oracle side.

Still, c'est la vie ...

For the record, the JDBC URL could / should have been: -

jdbc:oracle:thin:@//oracle.novalocal:1521/orcl.novalocal

i.e. the use of a double slash ( // ) before the host/domain name wasn't relevant / important.

PS For the record, I could've also run this command in SQLPlus: -

Show parameter service_name  

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string orcl.novalocal

*UPDATE*

I appear to have successfully updated the Service Name in Oracle, thanks to this: -

Cannot change service name for Oracle

This is what I did: -

sqlplus / as sysdba

SQL> alter system set db_domain='' scope=spfile;

System altered.

SQL> alter system register;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size    8623832 bytes
Variable Size  687868200 bytes
Database Buffers 1811939328 bytes
Redo Buffers    8151040 bytes
Database mounted.
Database opened.

SQL> Show parameter service_name  

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string orcl

SQL> Exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

lsnrctl status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 08-MAR-2017 05:41:43

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.novalocal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                07-MAR-2017 16:33:35
Uptime                    0 days 13 hr. 8 min. 8 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.novalocal)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Once I did this, I was able to revert my JDBC data sources back to this: -

jdbc:oracle:thin:@//oracle.novalocal:1521/orcl

and everything *appears* to work.

Now to start my clusters ....