Hi,
I am working with Oracle RDBMS 12cR2.
We just upgraded from a 11g to a 12c database (thus from SID to Service Name).
We have an application that is deployed in Tomcat and use a JDBC connection in he XML configuration file like:
url="jdbc:oracle:thin:@<database server name>:1521:HERCRONTNS"
username="PEXDEV"
password="demo"
The Network configuration for the 11g database was as follow:
1) listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
2) tnanames.ora
HERCRONTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
)
(CONNECT_DATA =
(SID = HERCRON)
)
)
3) JDBC string for the application:
url="jdbc:oracle:thin:@<database server name>:1521:HERCRONTNS"
username="PEXDEV"
password="demo"
It was working pretty good.
With 12c databases, we have now a pluggable database called HERCRON.
We have now the new Network configuration for the 12g pluggable database:
1) listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
and
2) tnanames.ora
HERCRONTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERCVICE_NAME = HERCRON)
)
)
3) JDBC string for the application:
url="jdbc:oracle:thin:@<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
We don't get anymore the connection to the database (the PDB now).
From the Tomcat log we have:
...
06-Apr-2018 10:50:54.708 WARNING [localhost-startStop-1] org.apache.naming.NamingContext.lookup Unexpected exception resolving reference
java.sql.SQLException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2302)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getLogWriter(BasicDataSource.java:1598)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:596)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:275)
...
or:
url="jdbc:oracle:thin:@//<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
From the Tomcat log we have:
...
06-Apr-2018 10:54:06.167 WARNING [localhost-startStop-1] org.apache.naming.NamingContext.lookup Unexpected exception resolving reference
java.sql.SQLException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2302)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getLogWriter(BasicDataSource.java:1598)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:596)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:275)
at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:321)
at org.apache.naming.NamingContext.lookup(NamingContext.java:841)
at org.apache.naming.NamingContext.lookup(NamingContext.java:160)
...
As explained in:
Oracle Net 12c: How to enable Clients using SID to connect to PDB? (Doc ID 1644355.1)
4) We changed the listener.ora as:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
USE_SID_AS_SERVICE_LISTENER=ON
and restarted the listener.
5) JDBC string for the application:
url="jdbc:oracle:thin:@//<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
From the Tomcat log we still have:
...
06-Apr-2018 11:03:38.501 WARNING [localhost-startStop-1] org.apache.naming.NamingContext.lookup Unexpected exception resolving reference
java.sql.SQLException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2302)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getLogWriter(BasicDataSource.java:1598)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:596)
...
or:
url="jdbc:oracle:thin:@<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
...
06-Apr-2018 11:06:17.734 WARNING [localhost-startStop-1] org.apache.naming.NamingContext.lookup Unexpected exception resolving reference
java.sql.SQLException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2302)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getLogWriter(BasicDataSource.java:1598)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:596)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:275)
at org.apache.naming.factory.FactoryBase.getObjectInstance(FactoryBase.java:94)
...
To summarize:
1) Listener configuration:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
USE_SID_AS_SERVICE_LISTENER=ON
or:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
2) TNS configuration:
HERCRONTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <database server name>)(PORT = 1521))
)
(CONNECT_DATA =
(SID = HERCRON)
)
)
3) JDBC string for the application:
url="jdbc:oracle:thin:@<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
or:
3) JDBC string for the application:
url="jdbc:oracle:thin:@//<database server name>:1521/HERCRONTNS"
username="PEXDEV"
password="demo"
We still get:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
or:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Dis someone face already this situation?
What should be the appropriate configuration for listener.ora, tnsnames.ora, and the JDBC string in the XML configuration file?
Thanks by advance for any tip.
Kind Regards