With Extended Support of Oracle Database 11gR2 ending in 2020 and Database 12cR1 extended support deadline coming up in 2022, we are seeing increased demand from customers to upgrade their Oracle E-Business Suite (EBS) database to the latest supported version. Oracle 19c Database Release update 19.12 is the current version that is certified to work with Oracle EBS. Check out the Oracle Lifetime Support Policy for Technology Products Guide document, which gives more details on the support deadline of different releases.

Database Releases and Support Timelines

Source: Release Schedule of Current Database Releases (Doc ID 742060.1)

A lot of customers are running their Oracle E-Business Databases on the 11gR2 and 12cR1 versions as they are the only releases certified to run Oracle E-Business Suite by the Oracle ATG team.

Oracle made the multi-tenant (PDB) option mandatory to use with Oracle E-Business Suite with 19c. But you cannot use true multi-tenancy with Oracle EBS (i.e., you cannot plugin your Oracle E-Business Suite database into an existing multitenant container database). Oracle EBS is only certified to run on a single-tenant container database. This means you cannot plug other PDBs into a Container Database that currently has an EBS PDB attached. Let’s hope Oracle makes true multi-tenancy possible in the future for Oracle E-Business Suite.

OK, let’s come to the main topic of this blog which is the lessons learned during 19c upgrades of Oracle E-Business Suite Databases.

 

Oracle Application DBAs who don’t deal with Other Core Oracle databases on a regular basis might have not caught up with new 19c naming conventions like Release Updates (RU) and Release Update Revisions (RUR) etc. Check out the below webinar by the Oracle Upgrade team that explains in very good detail the new number conventions, RU vs RUV, patch release schedule, patching guidelines, etc. Number convention discussion starts at video time mark 16:33

 

 

I know that the above webinar is quite long so here are a few tidbits from the video:

  • Release Updates ( e.g., 19.10, 19.12, etc) are like PSU patch releases. They include Security Updates, Regression Fixes, Functionality fixes, and Optimizer fixes (turned off)
  • Going from 19. x to 19. y is done by applying a patch. It’s not an upgrade
  • Release Update Revisions (RUR) are Old Release Update plus New Security updates and regression fixes. And Only 2 RURs are released per RU. This means after 6 months of a RU release, you need to get on to the next RU to keep up with security updates
  • Always go to the latest RU ( 19.12 as of Sep 2021) to get the latest security & bug fixes

By default, the Oracle EBS Upgrade note asks us to first install 19.3 and then patch it up with the latest RU. On top of the latest RU ( currently 19.12), there are usually one-off patches recommended by the Oracle EBS team. Doing all these steps manually during a production downtime window is time-consuming and can be automated by using the Gold Images feature.

Once you have a fully patched-up Oracle Home with the latest RU and One-offs, you can create a gold image using the below command:

$ORACLE_HOME/runInstaller -silent -createGoldImage -destinationLocation /patch_stage/

You can use this GoldImage zip file for the 19c Oracle Home setup for other environments and during product upgrades. All you have to do is, unzip to your target Oracle home fire up the uninstaller, and configure the Oracle Home. It saves a lot of time, as you don’t have to deal with applying RU and one-off patches again and again.

 

There are quite a few things to be taken care of before we get to the point of firing up DBUA to upgrade your database to 19c. Here are a few things that come to my mind, that can be tackled well ahead of time before you attempt to upgrade. This helps cut down your 19c upgrade downtime window quite a bit.

hCheck

Oracle EBS team recommends running hcheck.sql to identify any data dictionary-specific issues in your database. This SQL typically identifies any orphaned objects or segments in the database. The fixes to these issues usually require updates to underlying data dictionary tables like tab$ and seg$ etc. If you are not brave enough to touch those tables, then you might spend considerable time with Oracle Support on SRs. So tackling hcheck fixing activity ahead of time will save you considerable time.

 

Character Set Issues

Oracle 19c Database only supports UTF8 or AL16UTF16 for the National character set. Don’t confuse this with Normal Database Character Set which is represented by NLS_CHARACTERSET. National Character Set is the charset used by NVARCHAR2, NCHAR, and NCLOB data types. It’s different from the default database character set of the database. Most default DB installations have NCHAR set to either UTF8 or AL16UTF16. But for some reason, if you have any other charset setup, then you have to do a National Character set conversion.

Use the below query to check yours:

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;

 

sec_case_sensitive_logon

Oracle EBS by default uses sec_case_sensitive_logon=false, which means all passwords are case-insensitive. So PAssWord and password both work the same for the EBS database. It is recommended to move to case-sensitive passwords for EBS well ahead of your 19c upgrade, by following the MOS note Enabling the Oracle Database 11g Case-Sensitive Password Feature for Oracle E-Business Suite Release 12.1.1+ (Doc ID 1581584.1).

Taking this password migration outside of the 19c upgrade helps reduce the moving parts during the upgrade and reduces downtime as well.

 

Old Patch Edition Cleanup

If you apply App patches regularly using the EBS 12.2 Online patching feature, you might have quite a bit of Patch editions created in your database. There are some unintended consequences of having too many patch editions in the database like performance issues with SQLs against data dictionaries.

Also, Oracle EBS Teams wants us to clean up old patch editions before attempting to do a 19c DB upgrade. So it is better to clean up these editions using actualize_all and cleanup mode full options in adopt ahead of time. This cleanup activity consumes quite a bit of time and is apt to be done ahead of time.

 

Even though the Oracle EBS team maintains its list of One-off patches required on top of 19c Oracle Home, I encountered numerous cases of extra one-off patches required. If your customer uses tools like GoldenGate, Apex, or other reporting tools with the Oracle EBS database, then there are chances you might hit bugs that are not tackled by the One-off patch list maintained by the Oracle EBS team.

So I recommend checking this MOS note Oracle Database 19c Important Recommended One-off Patches (Doc ID 555.1) and applying any one-off patches mentioned there, in addition, to the list recommended by the Oracle EBS Team. Making a gold image after all these one-off patches helps.

 

If you have spent some time in Oracle world, then this won’t be a surprise for you. Save an offline copy of the MOS notes that you reference. Oracle keeps updating the MOS notes quite often. Whenever a newer AD/TXK or 19c Release Update is released, Oracle EBS updates the notes with newer versions and one-off patch lists for those specific versions. So there is a very high chance that MOS notes content changes in the middle of your upgrade project.

And you will be out of luck if you want to reference old patch lists or reference commands. Of course, you can go the long route of opening an SR and asking Oracle support for the older version of the MOS note, but who likes opening SRs with Oracle Support?

With that, I will wrap up this blog, and good luck with your 19c database upgrade!