Blogs

Lessons learned from Oracle EBS Database Upgrade to 19c

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.

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 hold on, you cannot use true multi-tenancy with Oracle EBS i.e., you cannot plugin your Oracle E-Business Suite database into an existing multi-tenant container database. Oracle EBS is only certified to run on a single-tenant container database. This means you cannot plug in 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.

 

Familiarize yourself with 19c

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, here are 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

 

Database Gold Images

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 19c Oracle Home setup for other environments and during product upgrades. All you have to do is, unzip to your target oracle home and 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.

 

Pre-upgrade Issues

There are quite a few things to be taken care of before we get to a point of firing up DBUA to upgrade your database to 19c. Here are 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, 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 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. Its recommended to move to case sensitive passwords for EBS well ahead of your 19c upgrade, by following 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 reduce downtime as well

 

Old Patch Edition Cleanup

If you apply Apps patches on a regular basis using EBS 12.2 Online patching feature, Then you might be having 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 adop ahead of time. This cleanup activity consumes quite a bit of time and is apt to be done ahead of time.

 

One-off Patch Issues

Even though the Oracle EBS team maintains its own 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 apply any one-off patches mentioned there, in addition, to the list recommended by Oracle EBS Team. Making a gold image after all these one-off patches helps.

 

Save MOS Notes

If you have spent some time in Oracle world, then this won’t be a surprise for you. Save an offline copy of 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 patches 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!

More from this Author