Saturday, February 22, 2020

EPM 11.2 - More Than a Cupful of Java

If your IT department runs deep-scans of your Oracle EPM server filesystems looking for older/vulnerable versions of Java, this post is certainly for you.

This blog has previously written about how Oracle Jan 2020 Critical Patch Update Affects EPM 11.2  In the post linked here, you'll find information about a newer version of Java SE 8 that's applicable to EPM  (You can expect quarterly Java 8 updates until it reaches end-of-life... so get used to this if you aren't moving to the Cloud)

So let's say you've downloaded the newer Java and want to replace the content of your pre-existing Java folders with the newer one.  Just how many Java locations do you have on your EPM 11.2 server, anyway???

Being a UNIX nerd from the beginning of my IT career, I was just dying to take the Cygwin utilities for MS Windows for a spin on my EPM 11.2 sandbox server.  Here's what I found!

CD \Oracle\Middleware
C:\cygwin\bin\find . -type f -name java.exe -print -exec {} -version ;

java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) Client VM (build 25.171-b11, mixed mode)
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) Client VM (build 25.171-b11, mixed mode)
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

java version "1.6.0_35"
Java(TM) SE Runtime Environment (build 1.6.0_35-b52)
Java HotSpot(TM) Client VM (build 20.10-b01, mixed mode)

Ewww!!!  Java 6?  See Java 6 Not Eradicated in EPM 11.2

java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)
java version "1.8.0_162"
Java(TM) SE Runtime Environment (build 1.8.0_162-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.162-b12, mixed mode)
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)

That's 10 separate java instances.  Now that you finished scrolling down and witnessed The Matrix fly past your eyes, here's some thoughts.

The "dbclient32" and "dbclient64" folders only exist if you selected them when you used EPM's installTool.cmd wizard.  (Picking either FDMEE or HFM will cause the Oracle DB clients to be automatically selected... you may deselect them if you're using MS SQL Server exclusively).

The "dbclient32" folder is the only 32-bit Java you'll have.  Keep this if you intend to use the SQL Developer tool, which is embedded within the dbclient32 folder structure.  SQL Developer in this release still expects a 32-bit Java.  All of the other folders listed above are 64-bit.

As of this writing, Java 1.8.0_241-b26 (Java SE 8 Update 241) is the version Oracle published in their latest Critical Patch Update.  This information will become dated starting in mid-April 2020, but the concepts described here will remain the same.

Do not be tempted to rename the \Oracle\Middleware\jdk1.8.0_181 folder or your EPM 11.2 system breaks!  This folder name is hard-coded throughout the system (Windows Registry, batch scripts, and other files).  This may be a topic for another blog entry.  For now, to get security-compliant you might back up the 181 folder and then replace its contents with the contents of 241.

I'm out of Java coffee.  Time to stop blogging.

Tuesday, February 11, 2020

SmartView Essbase Ad-Hoc and EPM 11.2

Here's a short(er) blog entry about Hyperion / Oracle EPM 11.2, I promise!

Are you getting a pop-up error in SmartView when trying a top-level SmartView ad-hoc retrieve from Essbase?  In my case, I got a weird Java message complaining about "essggridoption.setAncestorTop()".

Our culprit here is a combination of the order by which Essbase patches were automatically applied when EPM 11.2 was installed, and perhaps some funny business involving how files revert to read-only status in Windows Server 2019.

The EPM 11.2 installer automatically applies these Essbase patches where appropriate:

Your problem here is this:

The Essbase Java API files used by Analytic Provider Services (APS) doesn't match what the Essbase server expects.  There's an Essbase API protocol mis-match here!

The patches automatically applied by the EPM 11.2 installtool are stored here:
Sure enough, there's a subfolder here for 29749671.

Stop your EPM services on the APS host machine(s).

Attempt to copy \Oracle\Middleware\oracle_common\OPatch\Patches\patchFiles\29749671\files\common\EssbaseJavaAPI\\lib\
choosing to overwrite the existing files.

One of three things will happen:
  1. The copy happens OK and you may restart services.
  2. It complains that a Java process is using one of the files you're trying to overwrite.  You'll have to use your Windows Task Manager to hunt down and terminate those processes (or reboot).  Then try again.
  3. It complains the target file is read-only.  If this is the case, right-click the target file, click Properties, and remove the Read-Only flag.  Then try again.
#2 above happened to me.  After I killed the offending process, replaced the files and restarted services, SmartView worked as expected!

Saturday, February 8, 2020

EPM 11.2 Distributed Environment - Yup, Another RCU Post!

My blog entry EPM 11.2 RCU and you apparently struck a nerve, so consider this a follow-up!

Before we get to the "do this, do that" part, let's examine how we can determine if our attempt to deploy Hyperion / Oracle EPM 11.2 web services across multiple machines is... well, botched.

Part 1 - Is my distributed deployment botched?

Let's assume you've followed the instructions within the post linked above, and you have successfully created your WebLogic domain and have deployed a few things on machine #1 (EPM Foundation, Oracle HTTP Server, and perhaps a few supporting modules such as APS, EAS, CalcMgr, etc).  Now you're in the EPM System Configurator on machine #2 and are getting the dreaded red X when you try the Deploy To Application Server task.

user_projects\EPMINSTANCE\diagnostics\logs\configtool.log on machine #2 offers nothing beyond the vague and dreaded InvocationTargetException Java stack trace error.

By the way, ERROR messages prior to this point may typically be ignored.  You will first see "ERROR: Jars manifest check failed with message "Some referenced jars do not exist" and then a little further into the log you'll see "env.isWindows is not a function".  Don't waste your time troubleshooting those, as they're unrelated to your issue.

Once you see InvocationTargetException, close out of the log and now open wlst_debug.log in the same directory.  This points us to the underlying issue!

Do you see "WLSTException: No element JDBCSystemResource was found" at the top of a lengthy Java stack trace error?  If so, look just a few lines above.  The message doesn't look like an error at first glance:

2020-02-08 13:38:53,392 INFO  [1] - find JDBCSystemResource!JdbcResource!JDBCDriverParams "opss-audit-DBDS!opss-audit-DBDS!NO_NAME_0" as obj5
How could this be bad?  It is an [INFO] message after all, not an error.

It turns out there's an OPSS schema that's created by the RCU utility described within my earlier post.  The WebLogic Scripting Tool (WLST) can't find the OPSS jdbc definition files on machine #2 because they don't exist.  More than likely, this folder on your machine #2 is empty.

If you find yourself nodding your head at this point, yes.... your distributed deployment is botched.  But it can quickly be rescued!

(Note: Don't be tempted to copy those files over from machine #1.  They contain references to the RCU prefix and I haven't tested what might happen if you do that -- I suspect it would be something bad?)

Part 2 - How to fix machine #2 so we can deploy web services there?

Follow these steps:
  1. Login to your WebLogic Admin Console on machine #1 (http://machine1:7001/console) as epm_admin and click "Release Configuration" if you see that button is available.
  2. On machine #2, blow away user_projects\domains\EPMSystem -- all files & subfolders.
  3. On machine #2, launch RCU (Middleware\oracle_common\bin\rcu.bat)
  4. Use the "create" option and choose a new prefix.  Pick something sensible like the EPMINSTANCE name (assuming you aren't deploying as "epmsystem1" everywhere) or machine #2's hostname.
  5. Pick all checkboxes except for the Oracle Data Integrator ones.
  6. Pick the option to use the same password for all schemas.  This way is much easier.
  7. Let 'er rip and close the RCU utility when done.

Last steps...

I've read in the comments on the RCU blog entry that Oracle Support is telling some people to use different "schemas" for each machine's RCU database. 

When in doubt, go with Oracle's suggested approach of 1 Oracle Schema or 1 MS SQL Database per RCU Prefix (machine), just in case you run into trouble later and need to open an SR.  This means more things for your DBA to back up on a regular basis, but it may cause less confusion for Oracle Support as you work through the SR together.

Let's take a look at the back-end database.  In this example I'll use MS SQL Server 2016.

I have more machines deployed than are visible within this screenshot, but hopefully the concept comes across!

Each EPMINSTANCE ("machine" if you are not scaling vertically) has its own RCU "prefix" assigned.  In this example, "DEV" and "LANDO".  These schema users were created by the RCU utility.

I'm using same SQL database for these and so far things are working OK.

Should you want to cluster out even further for High Availability etc., you might consider either using Oracle's suggested approach (1 database/schema for each EPMINSTANCE), or at least one per each cluster....

... e.g, EPMRCU0 has the RCU prefixes for Foundation0, CalcMgr0, EAS0, etc.  Then EPMRCU1 could have the *1 servers, EPMRCU2 could have the *2 servers, and so on.

Getting back to the task at hand, copy from machine #1's EPMSystem11R1\common\config\ folder to the same location on #2.  Edit it on machine #2 and change the "schemaPrefix=" line to the prefix you created for machine #2.  You shouldn't need to edit the two SysDBA*= lines.  If you aren't using the same SQL database / Oracle schema, you can wipe out the RcuSchemaPassword= value and add the plain text password (the password will be automatically encrypted if machine #2 becomes un-botched) and also DbURL= as appropriate.

Finally, go back into the configtool and re-attempt your deployment.  So that you don't have to re-read through old log entries, you might rename EPMINSTANCE\diagnostics\logs\config to some other name before you do this.

In Closing

Yes, I did botch one of my machines on purpose... I wanted to see what would happen if I skipped the RCU step for a machine where I did NOT intend to deploy EPM Foundation.  Why did I do this?  (Aside from enjoying pain)

Hop out to the EPM 11.2 documentation landing page on Oracle's site, and review:

Deployment Options Guide
    Section 2.1 Clustering Java Web Applications
        "When scaling Oracle Hyperion Foundation Services (underline emphasis mine), 
        you must create a new schema using RCU and edit on each
        machine in the deployment."

This specific underlined sentence fragment led me to believe RCU was only need on machines where Foundation would be deployed.  Silly me for taking the documentation seriously!

Another related point.  See:

Installation and Configuration Guide
    Section 6.2 Creating Infrastructure Schemas Using Repository Creation Utility
        "You must run RCU on each machine in your environment."

I'd add one caveat here; RCU is only needed on machines where you want to deploy WebLogic Managed Servers (Planning Web, HFM Web, CalcMgr, etc).  Essbase, Essbase Studio, DRM, HFM Java Server, and other non-WebLogic processes don't need it because they are independent from WebLogic.  So the general rule of thumb here is: if EPM System Configurator creates a user_projects\domains\EPMSystem folder on the machine, that machine needs RCU.

Time to enjoy the rest of my weekend.  Be safe out there, and don't ask me about Oracle SOA!

Monday, February 3, 2020

January 2020 Oracle Critical Patch Update affects EPM 11.2

So you've spun up your shiny new Hyperion / Oracle EPM  And now... it is time to patch!

I haven't come across a comprehensive patching guide for EPM 11.2 yet, so here's my attempt to create one.  This guide assumes you've patched EPM 11.1.x.x in the past and are familiar with Oracle's "OPatch" utility.

As usual, assume you will take an outage and shut down the EPM stack across all servers before patching (ONE environment at a time, please!!  "Gee Dave, what could possibly go wrong?")

Oracle's quarterly "Critical Patch Update" (CPU for short) was published in mid-January 2020 right on schedule.  As expected, there are impacts to EPM 11.2, even though EPM isn't mentioned by name.

For your reference, there's a link to Oracle's JAN2020CPU (as they call it) announcement:
Oracle JAN2020CPU Security Alert

In a nutshell, here's what is believed to apply to EPM

Java SE 8

EPM ships with Java SE 1.8 Update 181.  This is "old" as it apparently was published in the APR2019CPU, and Java has been updated every Quarter since then.  The version we should use as of January 2020 is Java SE 1.8 Update 241.

The patch # for Java SE 8 is 18143322 and you should hop into and bookmark that patch.  Oracle will re-use this patch # every time a new Java 8 patch is issued.  Make your patch search screen look like this so you can find it quickly:

This is not a "patch"; it is a full install.  You can install it on just one server's C: drive, deselect the "public JRE" option within the install wizard, and accept the other default prompts.  When finished, copy it to your D/E/F drive as appropriate to your various Hyperion servers and save it as \Oracle\Middleware\jdk8

Once you've finished this task, you can uninstall it via the Windows Control Panel from the C: drive on the one server you installed it to.

You will then face a decision that is worthy of a separate blog post:  Do you replace the content of \Oracle\Middleware\jdk1.8.0_181 or do you update the Windows Registry and various bat/cmd files to replace all references of jdk1.8.0_181 to jdk8?  Replacing the content of the jdk1.8.0_181 folder is the fastest and easiest solution by far... until somebody in IT notices and asks why a vulnerable version of Java is installed.

Before you decide right away, remember Java 8 will likely be updated by Oracle every 3 months.

Oracle WebLogic / Fusion Middleware

(For my friends on the functional non-infrastructure side, here's why you care):  Hyperion does not function without WebLogic / FMW running behind the scenes.  When you start up web services like EPM Foundation, Planning Web, HFM Web, EAS Server, etc., you are starting an "Oracle WebLogic Managed Server".  When WEbLogic / FMW has a security vulnerability, then you should consider your EPM system to be equally vulnerable.

The patch # for this is 30675853 and it is "WebLogic PSU 191217.1425".  This is a cumulative patch.

The very good news is we no longer need to use the BEA Smart Update (bsu.bat) utility!  In fact, that entire directory structure doesn't exist anymore.  We now use good ol' Oracle OPatch.

The "-oh" parameter you pass to \Oracle\Middleware\OPatch\opatch.bat is \Oracle\Middleware

In addition to updating Oracle WebLogic, it also updates some files within the \Oracle\Middleware\oracle_common directory hierarchy (Oracle ADF / JDeveloper and the like).

Oracle HTTP Server

(For my friends on the functional non-infrastructure side, here's why you care): OHS is the front-end proxy for EPM Workspace.  This is what listens to port 19000 / 19443.  As stated in the WebLogic section above, if OHS can be exploited then so too can your EPM system.

The patch # for this is 30687404 and it is "Oracle HTTP Server PSU 191219.2319" .  This is also a cumulative patch and you also use OPatch to apply it.
(Update: Credit to "RJ" in the comments below who pointed out I had pasted an incorrect patch ID #.  30687404 is the correct patch ID # to use as of this writing)

The "-oh" parameter you pass to \Oracle\Middleware\ohs\OPatch\opatch.bat is \Oracle\Middleware\ohs

Oracle EPM / Hyperion

No references to EPM 11.2 in JAN2020CPU as mentioned earlier.

Essbase Suite

No references to Oracle Essbase/APS/EAS in JAN2020CPU.  There is a separate Oracle Inventory on your EPM 11.2 server(s) for Essbase Suite, if installed, so you can still patch it if you want some of the newer Essbase-related bugfixes.

Here's what you have out of the box in EPM where Essbase Suite is concerned:

29260139 Essbase Studio Server
29260160 Essbase Admin Server 
29749671 Analytic Provider Services
29749652 Essbase RTC
29749662 Essbase Server

Oracle Data Integrator

ODI is referenced within JAN2020CPU, but I'd avoid it for now until more information is forthcoming.  If you want to take a look, the patch # is 29778645 for "ODI BUNDLE PATCH (CPU)".  This patch updates the ODI Studio thick client, which expects some minor tweaks to ODI's Master repository.

You can apply this patch if your ODI is standalone and is NOT the one bundled with FDMEE  This is because the patch wants you to run the Oracle Upgrade Assistant utility, which expects that the ODI Master and Work repositories were built through the Repository Creation Utility ("RCU") rather than by the EPM System Configuration tool when you ran the "Configure Database" step for FDMEE.

We will likely need to wait for an FDMEE patch, which I'd expect to include a new *.drv file that instructs FDMEE to update the ODI repository when FDMEE is restarted.

Clear As Mud???

One question I'm asked by customer IT departments from time to time:  "Do we really need to do this?"

Here's one way to answer IT's question.  Forward Oracle's JAN2020CPU advisory article, linked at the top of this blog entry.  Here's just one specific example from the article:

I highlighted in red some, but not all, of the important nuggets of information:
  • CVE#. IT can paste into their favorite Internet search engine to learn more.
  • Product.  Oracle WebLogic Server is the technology behind pretty much all of Hyperion's web services in 11.2, except for DRM.
  • Remote Exploit without Auth.  A "yes" means an attacker doesn't need a userID/password for the server or application in order to cause mischief.
  • Base Score.  This is on a scale of 1 to 10, with 10 being the most serious.
  • Supported Versions Affected.  I highlighted 10.3.6 for our friends who are still on EPM or -- those use WebLogic 10.3.6.  EPM uses WebLogic
Give this example to your IT counterpart, along with the link to Oracle's advisory article, and ask if IT is willing to live with the scores listed throughout the article.  (Again, the above is just 1 example, but there are many ranging from the low 4s to the high 8s and 9s).

Be mindful the JAN2020CPU advisory mentions everything Oracle; Oracle Database, MySQL, Java, Hyperion, Oracle Financials, etc.  This makes reading through the entire advisory a daunting task.  I advise doing so only after ingesting my caffeine than it took you to get all the way to this paragraph!

You can mitigate any panic by explaining the on-premises Oracle EPM system typically sits behind a corporate firewall and is not exposed to the public Internet. By applying these various patches, you are mitigating risk of a disgruntled employee wanting to cause mischief or steal data before their last day.