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

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.

BrightStar helps organization upgrade from 11g to 12c to make the organization future ready. Contact us at info@bslion.in today

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s