Make Database Security Patching Consistent

Oracle EBS (E-Business Suite) customers have always been left a little behind by technological innovations happening at Oracle. Since 18c, Oracle Database has offered a new feature called Read-Only Oracle home (OH). However I haven’t noticed much traction for this feature. Looking through EBS 12.2 interoperability, Oracle doesn’t even mention this feature as an option in the first place — so you may wonder why you should even care.

Current IT demands pipelines, automation, repeatability, consistency and many other modern trends. These are hard to achieve with Oracle RDBMS (Relational Database Management System) software as design concepts haven’t changed for decades. Oracle home contains executables, configuration files, log files, audit files and backup files all tangled together called Oracle Software. And as a DBA you have to keep security patches up to date each quarter of the year, meaning staging and applying patches through the dev-uat-prod cycle.

It’s a lot of work to do without some form of automation. However, if you apply patches for every instance using OPatch, how can you guarantee that software you’re running dev on is the same that will eventually land in prod? Maybe in dev you did some back and forth with overlay patches, maybe troubleshot some edge cases and forgot to put some settings back — there are many possible scenarios.

You may think, “But that’s how Oracle does RDBMS patching; we’ve been doing that for decades and so far we haven’t faced an issue.” Well, like me, you might have to someday explain to auditors why OPatch dates differed between uat and prod.

Is there a way to fix it?

Yes there is.

First, let’s look at the software part of OH, and what’s required for Oracle EBS to work. What if Oracle Home setup looked something like this:

${ORACLE_BASE}:
   - CURRENT_OH(s) --> OH_2021_Jan
   - OH_2020_Oct
   - OH_2021_Jan
       - dbs(s) --> ${ORACLE_BASE}/dbs
       - appsutil(s) --> ${ORACLE_BASE}/appsutil
       - network/admin(s) --> ${ORACLE_BASE}/tns_admin
       - rdbms/audit(s) --> ${ORACLE_BASE}/rdbms/audit
   - OH_n
   - dbs
   - tns_admin
   - appsutil
   - audit
 (s) - symlink

Yes, it involves a lot of symlinks, but configuration and runtime is fully decoupled from actual software. Our hands and imagination are free from here on.

Where do we start? Obviously with RDMBS home. To make it easy to understand let’s assume we’re preparing for next-quarter PSU (patch set update) patching. So, as Oracle releases Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document and the newest ETCC (E-Business Suite Technology Codelevel Checker), we start applying patches on dev. We get all the conflicts and one-offs in place, and tests come back showing that it’s ok to move on to uat.

Usually, we would have instructions written in some document, we would have staged a lot of patches and we’d repeat the same steps over and over for all the environments. This immediately opens up the possibility of human error (e.g. not noticing the applied patch failed due to a sqlplus os process, or something similar).

To avoid that, let’s archive the already patched Oracle home, but let’s do it the smart way. We don’t need DBS or logs, appsutil or audit; we can skip all the trash not needed for other environments. (You can also skip OH/.patch_storage if you don’t plan to use OPatch against those new instances, if — having using OH for a while — it’s getting big. But be sure to keep those records somewhere, in case you need them one day). Please see the below example:

cd $ORALCE_HOME;  zip -r /stage/OH_2021_Apr.zip . -x "*appsutil" "*rdbms/audit/*" "exclude_list"

Now we place the archive (OH_2021_Apr.zip) into uat and prepare the software by cloning it (Doc ID 1154613.1). There are a couple of advantages here:

1. We don’t need downtime to patch Oracle Home.
2. It’s consistent with dev, as shown below — it’s understandable, scriptable and can be version managed (the actual script will look more complicated but this is the essence of it).

unzip -q /stage/OH_2021_Apr.zip -d ${ORACLE_BASE}/OH_2021_Apr
cd ${ORACLE_BASE}/OH_2021_Apr
./runInstaller -clone -silent ORACLE_HOME="${ORACLE_BASE}/OH_2021_Apr" ORACLE_HOME_NAME="OH_2021_Apr" ORACLE_BASE="${ORACLE_BASE}"
ln -s ${ORACLE_BASE}/OH_2021_Apr/dbs ${ORACLE_BASE}/dbs
ln -s ${ORACLE_BASE}/OH_2021_Apr/appsutil ${ORACLE_BASE}/appsutil
ln -s ${ORACLE_BASE}/OH_2021_Apr/network/admin ${ORACLE_BASE}/tns_admin
ln -s ${ORACLE_BASE}/OH_2021_Apr/rdbms/audit(s) ${ORACLE_BASE}/tns_admin

What you achieve now is something similar to the adop patching cycle with patch FS being prepared for cutover. As the approved downtime approaches you’re no longer applying patches using OPatch. Rather, stop the current database, flip ${ORACLE_BASE}/CURRENT_OH to point to the newly prepared home ${ORACLE_BASE}/OH_2021_Apr and perform post tasks (in a one-off patch case it may be just startup, but in a CPU case it’s usually datapatch in startup upgrade mode. You can incorporate ETCC at this stage).

For EBS nothing changes as it’s configured to work with ${ORACLE_BASE}/CURRENT_OH. We’ve now managed to apply database security patches across the fleet by running OPatch applied only once on dev instances with sophisticated stop/start procedures. This shows how the apply part also can be easily automated. Note that rollback works as simply as apply; just reversed.

By doing all this we built a pipeline of consistent Oracle RDBMS software across the fleet, repeatable processes minimizing human error and easier-to-automate procedures. We even decreased required downtime for security patching.

A few things to keep in mind

Now one server will have multiple OH installed, and all that metadata will be managed in central inventory. Good practice dictates that you not only install items, but remove old OH (having configured outdated OH on the server can pose a security flaw as it’s possible to use it to start an instance). Whenever you physically remove OH from the server always remember to remove it from inventory as well (runInstaller -delete). This is especially important if you have GRID around.

I’ve already mentioned .patch_storage. If you’ve never looked into this, and have been patching regularly for a considerable period of time, you’ll be surprised by its size. You can distribute OH without this .patch_storage, however that distributed software will lack the capability of rollback patches using OPatch, so always keep .patch_storage in at least one safe place (you can forget this if there’s a routine to clone all envs from prod).

The above information is conceptual. Specifics will need to be tailored to your particular needs and the tooling you have available, but in my opinion, this general approach is helpful to meet current IT needs.

Please let me know of any questions or thoughts in the comment section.

References:

• Master Note For Cloning Oracle Database Server ORACLE_HOME’s Using the Oracle Universal Installer (OUI) (Doc ID 1154613.1)
• Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (Doc ID 2552181.1)