My initial experience upgrading database from Oracle 11g to Oracle 12c (Part -1)

Oracle August 4, 2015

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: , , , , , ,