This is work in progress document.
Just wanted to share some experience upgrading database from Oracle 11g to Oracle 12c; One thing I want to avoid is just go over best practices instead I want to go over some of the stuff that we encountered.. May be because of bug ; some of them may be because of undocumented changes to Oracle 12c ;Some of them may be because of lack of in-dept research.
First thing that was noticed after upgrade was some of our home grown scripts failed. Further investigation revealed that Oracle 12c does not support sqlplus -sl option ;Instead it has to be sqlplus -s -l. As per Oracle Support, Oracle 12c behavior is correct one and the Oracle 10g/11g behavior was a bug that was fixed in Oracle 12c.
Second issue is one of our internal application failed with following error: “Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol”. There are multiple ways to fix/workaround this issue. One of fix/workaround is to set SQLNET.ALLOWED_LOGON_VERSION_SERVER in sqlnet.ora file to lower Oracle version like 8 or 9 10 . This parameter actually specifies the authentication protocol that a client is allowed to use not the actual version of that client. Therefore even though the parameter value implies Oracle version , the internal check is really against the authentication protocol.
Now now some background about the authentication protocol behavior. In earlier Oracle versions there was a 1-1 relation between authentication protocol and Oracle client version. This behavior changed in Oracle 10g , starting with Oracle 10g, this is no longer a 1-1 relation between authentication protocol and Oracle client version. So what is the problem? the problem lies in the fact that both Oracle 10g and 11g use SHA-1 protocol where as Oracle 12c uses SHA-2 protocol. While SHA-2 protocol by itself is not causing the error, It is the default setting for SQLNET.ALLOWED_LOGON_VERSION_SERVER that is causing the error. In earlier versions , the default was 8 whereas it is 11 in Oracle 12c; therefore all client versions 10 and below may get ORA-28040 error.
Please note that SQLNET.ALLOWED_LOGON_VERSION parameter is deprecated in Oracle Database 12c and replaced with SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT.
Other ways to fix the above issue is to upgrade your client like JDBC drivers to 12c to match authentication protocols.
Tagged: ORA-28040, ORA-28040: No matching authentication protocol, SQLNET.ALLOWED_LOGON_VERSION_CLIENT., SQLNET.ALLOWED_LOGON_VERSION_SERVER, sqlplus -s -l m oracle 12c upgrade, sqlplus -sl, upgrade to Oracle 12c