Why... Why... Why?
This blog is dedicated to documenting error resolution and other tidbits that I discover while working as a Consultant in the Oracle EPM (Hyperion) field. As much of my job revolves around issue resolution, I see an opportunity to supplement the typical troubleshooting avenues such as the Oracle Knowledgebase and Oracle Forums with more pinpointed information about specific errors as they are encountered. Beware, the information found in this blog is for informational purposes only and comes without any warranty or guarantee of accuracy.

EPMVirt: Create your own Oracle Hyperion Virtual Environment:

Thursday, December 1, 2016

Keep Your Hyperion Platform Secure by Patching WebLogic

One of the Hyperion Administration tasks often overlooked is the need to patch the underlying components such as WebLogic. If you subscribe to the Oracle security vulnerabilities, you will find frequent critical patch vulnerabilities released.

For instance, the October CPU release link is:

Searching for WebLogic will bring up a list of vulnerabilities addressed in this CPU cycle.

If you are curious you can search on the CVE number to get more details.

For instance,
CVE 2015-7501 is part of a bug in apache-commons library bundled inside WebLogic (and JBoss, etc).

Looking back at the CPU article under WebLogic:

Following the link to the Oracle Support Note will require an Oracle Support Login. Once logged in, you can find the suggested patch:
WebLogic Server home PSU Patch 23743997

It is always important to read the README file and all instructions that go along with the patch. Let's take a look at a typical example. Most often the process is twofold:
   1) uninstall any old patches
   2) apply the new patch using the bsu command (in Weblogic 10.3.x)

Some useful snippets from the README:
Oracle WebLogic Server Patch Set Update README
========================================================= This README provides information about how to apply Oracle WebLogic Server
Patch Set Update It also provides information about reverting to
the original version. Released: Oct, 2016 Smart Update Details of Oracle WebLogic Server Patch Set Update
-------------------------------------------------------------------------- PATCH_ID - K25M
Patch number - 23743997

Preparing to Install Oracle WebLogic Server Patch Set Update
----------------------------------------------------------------------- - WebLogic Server Patch Set Update (PSU) can be applied on a per-domain basis
(or on a more fine-grained basis), Oracle recommends that PSU be applied on an installation-wide basis.
PSU applied to a WebLogic Server installation using this recommended practice
affect all domains and servers sharing that installation.
- Login as same "user" with which the component being patched is installed.
- Stop all WebLogic servers.
- Remove any previously applied WebLogic Server Patch Set Update and associated overlay patches
Installing Oracle WebLogic Server Patch Set Update - unzip p23743997_1036_Generic.zip to {MW_HOME}/utils/bsu/cache_dir or any local directory Note: You must make sure that the target directory for unzip has required write and executable permissions for "user" with which the component being patched is installed. - Navigate to the {MW_HOME}/utils/bsu directory. - Execute bsu.sh -install -patch_download_dir={MW_HOME}/utils/bsu/cache_dir -patchlist={PATCH_ID} -prod_dir={MW_HOME}/{WL_HOME} Where, WL_HOME is the path of the WebLogic home Reference: BSU Command line interface http://docs.oracle.com/cd/E14759_01/doc.32/e14143/commands.htm To verify the installed patch: b) The following command is a simple way to determine the application of WebLogic Server PSU. $ . $WL_HOME/server/bin/setWLSEnv.sh $ java weblogic.version In the following example output, is the installed WebLogic Server PSU. WebLogic Server PSU Patch for BUG23743997

Translating this into actual commands:
cd Oracle\Middleware\utils\bsu\
bsu.cmd -remove -prod_dir=Oracle\Middleware\wlserver_10.3 -patchlist=UIAL
Checking for conflicts....
No conflict(s) detected
Removing Patch ID: UIAL..
Result: Success
bsu.cmd -install -prod_dir=\Oracle\Middleware\wlserver_10.3 -patch_download_dir=\Oracle\Mid
dleware\utils\bsu\cache_dir -patchlist=K25M

Checking for conflicts....
No conflict(s) detected
Installing Patch ID: K25M..
Result: Success
Testing the patch...

Your environment has been set.
 >java weblogic.version
WebLogic Server PSU Patch for BUG23743997 TUE AUG 30 18:34:42 IST 2016
WebLogic Server  Tue Nov 15 08:52:36 PST 2011 1441050
Use 'weblogic.version -verbose' to get subsystem information
Use 'weblogic.utils.Versions' to get version information for all modules
Success! The version matches that of the README.

Sunday, August 28, 2016

Oracle Open World Session - Live Architecture Demo

I am excited to speak at Open World this year. Please come check out the session. I am trying a live demo this time. Should be fun!

UGF2782: Oracle Hyperion Architecture Live Demo   
Nicholas King

Day:     Sunday, September 18, 2016
Time:    2:15 - 3:00 pm
Room:  Moscone South Room 303
The best way to learn is to jump in. This live demo will explore infrastructure topics using a virtual enviroment. Dive right into general EPM architecture including the new HFM architecture, explanation of Web components and WebLogic, triaging issues in the environment, and tuning tips. This session is a great way to go beyond the slide decks and see the system first hand.

Friday, August 19, 2016

Mistakenly Forgot Admin Credentials While Enabling SSO

Recently I made a dumb mistake. I was trying to enable single sign on (SSO) and hastily set everything up and restarted all services. However, once SSO was enabled I realized that I had not prior to enabling set my SSO user account with admin access in Hyperion. Since SSO was enabled, the admin user was no longer available to use for login because the SSO user is now enforced. With SSO enabled, the 'admin' user becomes obsolete.

In the past versions like this was no big deal as you could always login with your username and password using /interop to login directly to Shared Services. However, in you have to go through Workspace to get to Shared Services, enforcing the SSO login. Without admin privileges on your user account you cannot disable SSO, or make any setting changes at all! I had essentially locked myself and everyone else out of the system.

To restore the system I remembered a setting in Workspace, "Allow Direct Login After SSO Failure". This is a good fallback setting if SSO fails, giving you a way to access Hyperion using the old username/password combo. This would have been ideal to set BEFORE I locked myself out... At this point I can't make any changes to the system due to lack of administrator privileges.

From the help pages in Workspace:

However, I recall that using epmsys_registry we can make changes to the EPM system registry without any credentials. Perhaps it's possible to change this setting. Looking in the epm registry report, it looks as if the field is available in WORKSPACE_LWA.

Using the object id, you can view and change the setting...
/export/u1/app/Oracle/Middleware/user_projects/epmsystem1/bin/epmsys_registry.sh view \#18349408fd0bc34c317d176d1562f332264S7f57

/export/u1/app/Oracle/Middleware/user_projects/epmsystem1/bin/epmsys_registry.sh updateproperty \#18349408fd0bc34c317d176d1562f332264S7f57/@AllowDirectLogonAfterSSO true

(note the object id differs between the screenshot and commands because I took the screenshot from a different system. They should be the same in practice.)

Once I restarted the system this property was in place. However, the ability to login using the username/password combo only appears if SSO fails. There was nothing inherently wrong with SSO, it was working just fine. I had to make SSO fail in order to login. This seemed pretty easy, I just accessed Workspace directly rather than going through the SSO enabled web server. Consequently, the SSO token was not passed and the login prompt appeared. Finally... I was able to login as admin and provision my user id with admin privileges to gain access!

One might argue that the option to allow direct login if SSO fails should always be set. In my opinion, it is way too easy for malicious users to trick the SSO into failing and then gain access to the direct login prompt. This seems to be a security risk. I think using the method presented above,makes it possible to turn it on and off on demand if you need to get a backdoor into the system without it being enabled all the time.

Saturday, July 16, 2016

Running Weblogic Admin Server as a Windows Service

The WebLogic Admin server can be a useful tool in the EPM environment. However, it does not come installed as a Windows service and consequently is not always running. In order to tap into the powerful features in Enterprise Manager or WebLogic admin server you first need to get it up and running as a Windows Service.

The following is a quick script that can help. Create a Windows .bat file called installWLService.bat
Change USERDOMAIN_HOME and WL_HOME to reflect your environment.

set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\EPMSystem
set SERVER_NAME=AdminServer
set WL_HOME=D:\Oracle\Middleware\wlserver_10.3
set MEM_ARGS=-Xms512m –Xmx512m
call "%WL_HOME%\server\bin\installSvc.cmd"

This should install a new Windows service:

beasvc EPMSystem_AdminServer

Quickly Search Logs Using Weblogic Enterprise Manager

One of the most powerful ways to triage issues is the search feature in Weblogic's Enterprise Manager. The search allows one to search through the log files associated with all managed servers in the domain. Said a different way, it searches all Weblogic logs at once without even needing to login to a server. So if you have a 10 server footprint you would not have to log into 10 different servers to view the logs. 

There are many benefits to the search feature:
  • View all lines from the last hour
  • View only messages with criticality set to ERROR so you're not searching through lots of info and debug information
  • Web-based interface no need to log into individual servers
One caveat to remember is the search only covers Weblogic logs. There are many other logs in Hyperion that this will not hit. However, it takes about 2 minutes to check, and usually the front end can give you a very good idea of what the error is. It's a great place to start troubleshooting.

To get started, start up the WebLogic admin server:

To access Enterprise Manager use the same url you would use to get to the admin server, but replace /console with /em

for instance:  http://server:7001/em

Once logged in,
WebLogic Domain -> EPMSystem -> Logs

Here you can see the powerful search interface. In this case we are searching 46 log files within the last hour for Errors. 

A detailed view of the results here show clearly Shared Services is failing due to a locked database account.

To sum up, it took about 60 seconds to login to EM and open the log interface to find this error. This can be a pretty powerful first place to start when troubleshooting issues. 

For this to be most useful, you should run the Weblogic admin server as a service so it is always available to login and look at logs. In Unix this is just a matter of starting the process. In Windows, you might want this quick tip to install the Admin Server as a Windows service:

Wednesday, May 18, 2016

Error in ConfigTool: TNS-04612: Null RHS

I recently came across this error in the config tool while trying to reconfigure the database connections.

 [EPMCFG] [ERROR] [EPMCFG-01020] [oracle.EPMCFG] [tid: 30] [ecid: 0000LJ5CfaK8dplqwsZf6G1NFBXo00000E,0] [SRC_CLASS: com.hyperion.config.wizard.impl.RunAllTasks] Error: [[
java.io.IOException: TNS-04612: Null RHS for "my_tns"
at com.hyperion.cis.config.TNSParser.writeTnsOraFileEntry(TNSParser.java:189)
at com.hyperion.cis.config.AbstractProductDBConfigurator.updateDatabaseConnectionConfigurationFiles(AbstractProductDBConfigurator.java:854)
at com.hyperion.config.wizard.impl.RunAllTasks.executeDbConfigTask(RunAllTasks.java:681)
at com.hyperion.config.wizard.impl.RunAllTasks.execute(RunAllTasks.java:306)
at com.hyperion.config.wizard.impl.RunnAllTasksState.run(RunnAllTasksState.java:92)
at java.lang.Thread.run(Thread.java:662)


What is happening is the config tool is trying to parse the tnsnames.ora under the bundled Oracle client, Oracle\Middleware\user_projects\config\dbclient and failing. I added some additional TNS entries for connecting to external databases to this tnsnames file. The config tool was trying to parse these additional entries and crashing. In order to successfully complete the config tool, I had to rename the existing tnsnames.ora to tnsnames.ora.bak and then run the config tool. This creates a brand new tnsnames.ora file. After successful configuration it was necessary to merge the changed TNS descriptors for "HFMTNS" and "BPMA_ALIAS" from the new tnsnames.ora into the .bak file. Finally, revert tnsnames.ora.bak file to its original name, tnsnames.ora.

Saturday, April 23, 2016

Troubleshooting HFM on Windows

HFM on and higher versions has a significantly different architecture than previous versions. This difference makes troubleshooting issues much harder. The old tricks seem out of date and it is easy to feel lost in the new version. Unfortunately, getting lost and unable to solve problems can lead to downtime and project delays. I wanted to share some basic tips to help get started in Specifically, I wanted to focus on Windows because I have covered Linux in previous posts.

First, let's take a real error while creating a brand new HFM application.

Here we get the error: EPMHFM-66054: The system was unable to find the Datasource process for application. Essentially each HFM application spawns its own process in HFM and this application crashed while trying to initialize. This could be due to any number of things...

The first thing to note is the old HFMErrorLogViewer from previous versions is gone. The HFMErrorLogViewer was a great tool because it collected all the backend log messages into one convenient place. Now it looks like things are spread out over many logs. 

First, HFM is broken down at a high level into the Weblogic/ADF components and the Java HFM Wrapper Engine. They appear as Windows services,

The Web Tier consists of the Weblogic applications & UI. These logs can be found under the WebLogic domain,

The Java Server is the more interesting of the two for debugging because most significant errors can be traced to the backend. One thing to realize is HFM is still only partially Java based. There are still significant portions of HFM representing legacy code written in C. These low level functions are wrapped into this Java framework to make it look seamless. 

Let's take a look at some of the backend logs.

One of the main logs to investigate is under 
The hsx-server log will have most details regarding the HFM backend service, similiar to the messages you would see previously in the HFMErrorLogViewer.


startDatasourceProcess] Starting a new datasource process for the application TESTHFM999[2016-04-23T00:29:43.500-07:00] [FM] [NOTIFICATION] [] [oracle.FM.HSXDSLM.oracle.epm.fm.dslm.DatasourceProcessManager] [tid: 12] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4] [SRC_CLASS: oracle.epm.fm.dslm.DatasourceProcessManager] [SRC_METHOD: getDSCommandLineArguments]  Starting the application TESTHFM999 on the ports : DSManagementPort port 10001 and DSHandler Port 10002
[2016-04-23T00:29:43.532-07:00] [FM] [NOTIFICATION] [] [oracle.FM.HSXDSLM.oracle.epm.fm.dslm.DatasourceProcessManager] [tid: 12] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4] [SRC_CLASS: oracle.epm.fm.dslm.DatasourceProcessManager] [SRC_METHOD: isSingleInstance]  Is Single Instance ? true
[2016-04-23T00:29:43.532-07:00] [FM] [ERROR] [EPMHFM-65925] [oracle.FM.HSX.SERVER.oracle.epm.fm.common.HsxServerConfig] [tid: 12] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4] [SRC_CLASS: oracle.epm.fm.common.HsxServerConfig] [SRC_METHOD: getDSHangTimeoutPeriod] 
[2016-04-23T00:29:44.485-07:00] [FM] [NOTIFICATION] [] [oracle.FM.HSXDSLM.oracle.epm.fm.dslm.DatasourceProcessManager] [tid: 14] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4:21] [SRC_CLASS: oracle.epm.fm.dslm.DatasourceProcessManager] [SRC_METHOD: onProcessFailed] Datasource process for application TESTHFM999 stopped.
[2016-04-23T00:29:44.500-07:00] [FM] [NOTIFICATION] [] [oracle.FM.HSXDATAACCESS.oracle.epm.fm.dal.manager.ApplicationManagerDALImpl] [tid: 14] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4:21] [SRC_CLASS: oracle.epm.fm.dal.manager.ApplicationManagerDALImpl] [SRC_METHOD: deleteCCLockRecordsForServer]  Table TESTHFM999_CC_LOCKS does not exist
[2016-04-23T00:29:44.563-07:00] [FM] [WARNING] [] [oracle.FM.HSXDSLM.oracle.epm.fm.dslm.DatasourceManagerImpl] [tid: 12] [ecid: 005CI7TJTRS8dplqwsedMG0006Vu001PXQ,0:1:4] [SRC_CLASS: oracle.epm.fm.dslm.DatasourceManagerImpl] [SRC_METHOD: pingDatasource] Unable to invoke ping on XDS management service for the application TESTHFM999 , 

From this log it is evident that each HFM application creates a new datasource. A datasource is a separate process that handles all the processing for a particular application. The datasource process is launched after creating the application, or on the fly as the application is accessed. 

This spawned datasource process creates a new logfile for each application. For instance, a log file is created with the application name, such as xfm.odl.TESTHFM000-1.log. 

Having a look:


[2016-04-23T00:29:44.453951-07:00] [XFM] [ERROR:1] [EPMHFM-07392] [XFM] [ecid: XDS.0000.0000.0000.0001] [File: XfmODBC.cpp] [Line: 203] [userId: ] [appName: TESTHFM999] [pid: 19988] [tid: 20008] [host: ] [nwaddr: ] [errorCode: 800402B5] [srcException: NotSpec] [errType: 1] [dbUpdate: 2] [] An unknown error occurred in an ODBC object. [[1 record: SQLSTATE = 08001; NATIVE ERROR = -4; MSG = [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead. This may have occurred because the server requires Oracle Advanced Security. To enable the driver to use OAS, please use the DataIntegrityLevel and/or EncryptionLevel connect options.]] 
[2016-04-23T00:29:44.453951-07:00] [XFM] [ERROR:1] [EPMHFM-07406] [XFM] [ecid: XDS.0000.0000.0000.0001] [File: XfmODBC.cpp] [Line: 615] [userId: ] [appName: TESTHFM999] [pid: 19988] [tid: 20008] [host: ] [nwaddr: ;] [errorCode: 800402C3] [srcException: XfmExc] [errType: 1] [dbUpdate: 1] [] An error occurred in creating an ODBC connection. [[08001]] 
[2016-04-23T00:29:44.469577-07:00] [XFM] [ERROR:1] [EPMHFM-07406] [XFM] [ecid: XDS.0000.0000.0000.0001] [File: XfmODBC.cpp] [Line: 700] [userId: ] [appName: TESTHFM999] [pid: 19988] [tid: 20008] [host: ] [nwaddr: ] [errorCode: 800402C3] [srcException: XfmExc] [errType: 1] [dbUpdate: 1] [] An error occurred in creating an ODBC connection. [[Failed to create DB connection.]] 
So it looks like ODBC is involved here. Essentially, the underlying database connection in HFM is now ODBC. This was done to support HFM on Linux. The ODBC layer is how Oracle choose to make the code OS agnostic.

The ODBC connection is controlled by the Merant DataDirect 7.1 ODBC Driver. This is similar to how external Essbase database connections were created in the past. Looking at the error message, we are getting Connection Dead. This is actually a common problem with the ODBC DataDirect drivers and encrypted database connections. It is possible to reproduce this outside of HFM by testing the ODBC driver by hand:

Open Microsoft ODBC Manager, and add a connection.

HFM uses the DataDirect 7.1 Oracle Wire Protocol

Entering in the basic database connection details:

The exact error message appears, outside of HFM. 

This error message also happens to indicate where to look to solve the issue. In this case, the Oracle Database Listener connection requires an encrypted connection. Unsecured connections are rejected. So we can simply configure the ODBC connection to use the same encryption as the Oracle Listener.

sqlnet.crypto_checksum_server = REQUIRED
sqlnet.crypto_checksum_types_server = (SHA1)
sqlnet.encryption_types_server = (AES256)

Oracle Database Listener Settings

In the ODBC connection there are Advanced Security parameters which allow us to easily match the listener settings.

This is great. However, now we need to figure out where the ODBC connection is stored in HFM in order to configure these advanced properties.

HFM creates a new datasource in the Oracle client called HFMTNS. This info is entered during config tool, and automatically added to the tnsnames.ora in the HFM Database client folder
D:\Oracle\Middleware\dbclient64\network\admin. The ODBC connection leverages this HFMTNS connection information.

Taking a step back, since all of this is actually wrapped into a Java Server container, one additional place for logs is the Windows service start log:



Here there is some very interesting information about how this ODBC connection is stored.

[2016-04-23T10:29:07.179806-07:00] XDS: XFMDataSource process starting...[2016-04-23T10:29:07.179806-07:00] XDS: Processing command line arguments...[2016-04-23T10:29:07.179806-07:00] XDS: Initializing XfmJHsxServerWrapper...[2016-04-23T10:29:07.179806-07:00] XDS: Getting database settings from HIT registry ...[2016-04-23T10:29:07.257929-07:00] XDS: Transforming DB connect info from JHsxServer into a DB connect string[2016-04-23T10:29:07.257929-07:00] Connection string: DRIVER={DataDirect 7.1 Oracle Wire Protocol};SRVR=HFMTNS;UID=...;PWD=**********;ENS=1;AN=HFM[2016-04-23T10:29:07.257929-07:00] XDS: Initializing static parameters...[2016-04-23T10:29:07.257929-07:00] XDS: Initializing critical errors log...[2016-04-23T10:29:07.257929-07:00] XDS: Critical errors log: D:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\TEST23.oracle_critical_errors.log[2016-04-23T10:29:07.257929-07:00] XDS: Initializing ODL logging...Found trace level'TRACE:32' in the logger node[2016-04-23T10:29:07.367305-07:00] XDS: Initializing DB logging...[2016-04-23T10:29:07.820454-07:00] XDS: Failed to run XFMDataSource. Unexpected error occured ...[2016-04-23T10:29:07.836077-07:00] XDS: Right before exit
From the log message it looks like the ODBC connection is created on the fly by transforming the database connection information used by the Java HFM Service, JHsxServer. This comes from the database credentials used when running the HFM config tool during configuration. I find this design unfortunate because there are going to be slight differences between how Java drivers and the DataDirect drivers work for non trivial connections. Looking at the Config Tool advanced properties, there are options for SSL database connections, but all are focused on JDBC (Java) connectivity.

In this case, we see that the DataDirect based encryption properties cannot be set. Since there does not seem to be any way to change the advanced properties, The investigation has hit a dead end and Oracle SR needs created to get this fixed.