Использование TNS Alias ​​с ojdbc 12.1

Question

Я делювинг Java-приложения, которому необходим доступ к базе данных Oracle. До сих пор я без проблем использовал драйвер ojdbc6. Теперь мы переключаем базы данных на Oracle 12c, поэтому я хотел использовать более новый драйвер JDBC.

Это код

public class jdbctest {  
  public static void main(String[] args) throws Exception {  
  final PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();  
  oracle.jdbc.OracleDriver.main(args);  
  System.setProperty("oracle.net.tns_admin", "c:/Windows/System32/oracle-home/network/admin");  
  pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");  
  pds.setURL("jdbc:oracle:thin:@TNSALIASWITHOUTDOTS");  
  System.out.println("Opening connection...");  
  final Connection connection = pds.getConnection();  
  System.out.println("Connection opened");  
  }  
}  

Это tnsnames.ora (ХОСТ изменился, потому что это что-то в нашей внутренней сети)

TNSALIAS.WITH.DOTS  = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testdomain.de) (PORT = 1521)))(CONNECT_DATA = (SID = EDISW) ))  
TNSALIASWITHOUTDOTS  = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testdomain.de) (PORT = 1521)))(CONNECT_DATA = (SID = EDISW) ))  

Это вывод для всех четырех вариантов (2 драйвера, 2 tnsaliases) jdbc 11.2, TNSALIASWITHOUTDOTS

Oracle 11.2.0.3.0 JDBC 4.0 compiled with JDK6 on Fri_Aug_26_08:19:15_PDT_2011  
#Default Connection Properties Resource  
#Mon Apr 14 09:25:15 CEST 2014  
Opening connection...  
Exception in thread "main" java.sql.SQLException: Exception beim Abrufen der Connection: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:488)  
  at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:163)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1055)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:985)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:966)  
  at jdbctest.main(jdbctest.java:18)  
Caused by: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:368)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:49)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:80)  
  at oracle.ucp.jdbc.DataSourceConnectionFactoryAdapter.createConnection(DataSourceConnectionFactoryAdapter.java:102)  
  at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:82)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.createOnePooledConnectionInternal(UniversalConnectionPoolImpl.java:1776)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.access$600(UniversalConnectionPoolImpl.java:1612)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.createOnePooledConnection(UniversalConnectionPoolImpl.java:588)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.createOnePooledConnection(OracleJDBCConnectionPool.java:170)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:354)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:167)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:148)  
  at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:166)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:1441)  
  at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:82)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1043)  
  ... 3 more  

JDBC 11,2 TNSALIAS.WITH.DOTS

Oracle 11.2.0.3.0 JDBC 4.0 compiled with JDK6 on Fri_Aug_26_08:19:15_PDT_2011  
#Default Connection Properties Resource  
#Mon Apr 14 09:27:50 CEST 2014  
Opening connection...  
Exception in thread "main" java.sql.SQLException: Exception beim Abrufen der Connection: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:488)  
  at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:163)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1055)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:985)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:966)  
  at jdbctest.main(jdbctest.java:18)  
Caused by: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:368)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:49)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:80)  
  at oracle.ucp.jdbc.DataSourceConnectionFactoryAdapter.createConnection(DataSourceConnectionFactoryAdapter.java:102)  
  at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:82)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.createOnePooledConnectionInternal(UniversalConnectionPoolImpl.java:1776)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.access$600(UniversalConnectionPoolImpl.java:1612)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.createOnePooledConnection(UniversalConnectionPoolImpl.java:588)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.createOnePooledConnection(OracleJDBCConnectionPool.java:170)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:354)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:167)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:148)  
  at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:166)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:1441)  
  at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:82)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1043)  
  ... 3 more  

JDBC 12,1 TNSALIASWITHOUTDOTS

Oracle 12.1.0.1.0 JDBC 4.1 compiled with JDK7 on Thu_Apr_04_15:09:24_PDT_2013  
#Default Connection Properties Resource  
#Mon Apr 14 09:30:42 CEST 2014  
Opening connection...  
Exception in thread "main" java.sql.SQLException: Exception beim Abrufen der Connection: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:488)  
  at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:163)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1055)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:985)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:966)  
  at jdbctest.main(jdbctest.java:18)  
Caused by: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLException: ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt  
  at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:368)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:49)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:80)  
  at oracle.ucp.jdbc.DataSourceConnectionFactoryAdapter.createConnection(DataSourceConnectionFactoryAdapter.java:102)  
  at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:82)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.createOnePooledConnectionInternal(UniversalConnectionPoolImpl.java:1776)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.access$600(UniversalConnectionPoolImpl.java:1612)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.createOnePooledConnection(UniversalConnectionPoolImpl.java:588)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.createOnePooledConnection(OracleJDBCConnectionPool.java:170)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:354)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:167)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:148)  
  at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:166)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:1441)  
  at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:82)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1043)  
  ... 3 more  

JDBC 12,1 TNSALIAS.WITH.DOTS

Oracle 12.1.0.1.0 JDBC 4.1 compiled with JDK7 on Thu_Apr_04_15:09:24_PDT_2013  
#Default Connection Properties Resource  
#Mon Apr 14 09:31:46 CEST 2014  
Opening connection...  
Exception in thread "main" java.sql.SQLException: Exception beim Abrufen der Connection: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLRecoverableException: I/O-Fehler: Invalid connection string format, a valid format is: "host:port:sid"   
  at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:488)  
  at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:163)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1055)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:985)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:966)  
  at jdbctest.main(jdbctest.java:18)  
Caused by: oracle.ucp.UniversalConnectionPoolException: Connection kann nicht aus Datenquelle abgerufen werden: java.sql.SQLRecoverableException: I/O-Fehler: Invalid connection string format, a valid format is: "host:port:sid"   
  at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:368)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:49)  
  at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:80)  
  at oracle.ucp.jdbc.DataSourceConnectionFactoryAdapter.createConnection(DataSourceConnectionFactoryAdapter.java:102)  
  at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:82)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.createOnePooledConnectionInternal(UniversalConnectionPoolImpl.java:1776)  
  at oracle.ucp.common.UniversalConnectionPoolImpl$UniversalConnectionPoolInternal.access$600(UniversalConnectionPoolImpl.java:1612)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.createOnePooledConnection(UniversalConnectionPoolImpl.java:588)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.createOnePooledConnection(OracleJDBCConnectionPool.java:170)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:354)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:167)  
  at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:148)  
  at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:166)  
  at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:1441)  
  at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:82)  
  at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1043)  
  ... 3 more  

Для тех, кто не знает немецкий, ORA-01017 означает "неверное имя пользователя / пароль"

Это означает, что каждая комбинация, кроме 12.1 с точками в TNS Alias, может подключаться к базе данных. Какие-либо предложения?

Answer 1

Мы обнаружили, что это ошибка в драйвере ojdbc 11.2.0.4 и выше. Мы создадим отчет об ошибках в Oracle.

Обходной путь должен был бы использовать tnsaliases без точек в их имени.