今天部署一套單例項12cr2到生產環境時，客戶端連線時報錯誤ORA-28040

由於之前profile用的是11g未做目錄修改直接使用導致此類問題
sqlnet.ora新增如下解決
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
貼上mos相關解釋
文件 ID 2023160.1
On : 10.2.0.4 version, Thin JDBC driver

When trying to connect to Oracle database 12.1.0.2 using JDBC 10.2.0.4 (using file ojdbc14.jar), error ORA-28040 No matching authentication protocol is returned even though sqlnet.ora file on server side is changed to include :

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

and listener is restarted (stop/start).

According to Document: 401934.1 Starting With Oracle JDBC Drivers, JDBC 10.2.1 must be able to connect to Oracle 12.1.0. But, it does not.
If JDBC is replaced with 11.2.0 (file ojdbc5.jar), there is no error.

ERROR
-----------------------
Using /oracle/client/10x_64/instantclient/ojdbc14.jar
-------- Oracle JDBC Connection Testing ------

Found JDBC Driver!

Querying Time and DB Name from database

Connection Failed! Check output console
java.sql.SQLException: ORA-28040: No matching authentication protocol

  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
  at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOsesskey(T4CTTIoauthenticate.java:294)
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:357)
  at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:441)
  at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
  at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
  at java.sql.DriverManager.getConnection(DriverManager.java:582)
  at java.sql.DriverManager.getConnection(DriverManager.java:185)
  at OracleJDBC.main(OracleJDBC.java:35)

The issue is caused by the listenerr.ora file referencing the incorrect ORACLE_HOME:

# listener.ora Network Configuration File: /u01/app/oracle/product/db/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = soa)
      (ORACLE_HOME = /u01/app/oracle/product/db/102)                           <=======
      (SID_NAME = soa)
    )
)

....

Change the ORACLE_HOME in the listener to point to the 12c installation:

# listener.ora Network Configuration File: /u01/app/oracle/product/db/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = soa)
      (ORACLE_HOME = /u01/app/oracle/product/db/12c)                           <=======
      (SID_NAME = soa)
    )
)

....

jdbc版本過低或oracle_home配置錯誤，導致ORA-28040

相關文章