Tag Archives: upgrade to Oracle 12c

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.

 

Upgrading to Oracle 12c: How different is it from previous versions?

upgrading to Oracle 12c August 11, 2013 1 Comment

1. Preupgrade Utility

Preupgrade utility “utlu121s.sql”  is replaced with “preupgrd.sql”. The new utility provides fixup scripts “preupgrade_fixups.sql” and “postupggrade_fixups.sql” to address issues that might be present both before and after the upgrade.These fixup scripts can be executed interactively.

2. Upgrade Utility

The catupgrd.sql Upgrade utility is replaced with the catctl.pl utility. The new utility allows you to take advantage of CPUs by running the upgrade process in parallel thereby reducing the upgrade time.  This new script is used more like a wrapper on the existing one. Also by default “catuppst.sql” is run when “catctl.pl” is executed. The new script has lot of options , few of them are explained below.

Option “-n” specifies the degree of parallelism, the default is 4 and maximum is 8.
Option “-p” supports rerun of the upgrade by skipping successful steps.

Example: $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -p $ORACLE_HOME/rdbms/admin/catupgrd.sql

3. XML DB

Oracle XML DB is no longer an optional component and therefore cannot be uninstalled. It is automatically installed into SYSAUX tablespace when you create a new 12c database or if you upgrade from prior versions of Oracle.

4. Upgrade Status

A successful upgrade sets the status of database status to UPGRADED. This is different from previous versions where the status is set to VALID.  The database status is set to VALID only if ultrp.sql is executed.