Environment
Linux Webserver, Apache Tomcat/9.0.34, Ords 20.2, Oracle 12.1
Problem
Ords running in tomcat and configured for multiple database connections. New clone of existing database has been configured with new ords database connection. However, any connection attempt via Apex throws the error:-
The connection pool named: |xxxxxxxxxx| is not correctly configured, due to the following error(s): Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error: ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
The config files are not configured with a SID:-
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM http://java.sun.com/dtd/properties.dtd>
<properties>
<comment>Saved on Thu Sep 10 13:52:36 EDT 2020</comment>
<entry key="db.hostname">psdbnnn.xxxxx.com</entry>
<entry key="db.password">@nnnnnnnnnnnnnnnnnnnnnnnnnnn</entry>
<entry key="db.servicename">xxxxxxxx.nnnnn.hostname.com</entry>
<entry key="db.username">APEX_PUBLIC_USER</entry>
</properties>
The listener logs on target system are showing connection routed OK but not resolved and no reference to SID:-
29-SEP-2021 18:10:43 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=abcd1))(SERVICE_NAME=psdbnnn.xxxxx.com)) * (ADDRESS=(PROTOCOL=tcp)(HOST=123.456.789.47)(PORT=35246)) * establish * xxxxxxxx.nnnnn.hostname.com * 0
29-SEP-2021 18:10:43 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=abcd1))(SERVICE_NAME=)) * establish * 12504
29-SEP-2021 18:11:10 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=abcd1))(SERVICE_NAME=)) * establish * 12504
Tomcat logs do not show exact jdbc connection string used on webserver.
Tnsping from webserver fine.
I can sqlplus connect to database from webserver no problem.
All other database connections from webserver work OK.
Any ideas on how I can debug this specific database connection issue?
Thanks
Paul