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.

Saturday, April 23, 2016

Troubleshooting HFM 11.1.2.4 on Windows

HFM on 11.1.2.4 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 11.1.2.4. 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 
Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm
The hsx-server log will have most details regarding the HFM backend service, similiar to the messages you would see previously in the HFMErrorLogViewer.

oracle-epm-fm-hsx-server.log

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:

xfm.odl.TESTHFM000-1.log

[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] [11.1.2.4.200.5149] 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] [11.1.2.4.200.5149] 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] [11.1.2.4.200.5149] 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:

D:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services

HyS9FinancialManagementJavaServer-sysout.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.


Thursday, March 24, 2016

My Kscope16 Sessions

I'll be speaking at Kscope16 in Chicago June 26-30.



A Tour of WebLogic as It Relates to EPM
When: Jun 27, 2016, Session 5, 3:15 pm - 4:15 pm
WebLogic forms the foundation of any EPM system by providing the Java container to run EPM applications. This presentation takes a tour of WebLogic, starting with fundamentals and working through interesting concepts as they relate to EPM. Topics include tuning, Enterprise Manager integration, security, monitoring, and logging. Learning WebLogic provides the framework to help you keep services running, improve performance, alert you when something is wrong, and much more.

Tips and Tricks for Managing Your EPM Infrastructure
When: Jun 29, 2016, Session 14, 10:15 am - 11:15 pm
Learn tips and tricks for managing your EPM environment like a pro. The presentation starts with keeping tabs on the environment, such as identifying a known good state to help quickly spot issues. Then it dives into debugging and troubleshooting techniques for tackling real-world problems. Finally, the presentation reveals more proactive approaches for managing the environment, such as tuning and patching strategies.

Sunday, March 6, 2016

Why No Recent Blog Updates?

The past few months I have not been updating the blog because of an important new project. My twin daughters, Aidan and Evelyn were born in December and have been the center of my attention as they are getting used to their strange new world.

Stay tuned for new content soon!

Monday, November 30, 2015

Time to Upgrade EPM?

It might be time for an upgrade for slightly older EPM releases. Below is the timeline for backwards support for fixes on the EPM platform. To sum up the table, the 11.1.2.1 and 11.1.2.2 versions are already sunsetted for patches. Additionally, the 11.1.2.3 version will no longer be supporting new patches and fixes as of February 2016. Given that typically not much work is completed near the end of year, it may come as a surprise in February. It is also possible that Oracle releases a patch greater than .700 which may extend the window for 11.1.2.3. At the very least, the table indicates that it is necessary to move rather quickly and frequently on the patch releases to keep up to date.

Oracle's official statement is:
PLEASE NOTE: When the grace period end date has been reached, no additional fixes will be developed.  The grace period is defined as the period of time following the release of a new patch set or patch set update during which Oracle will create new fixes for both the new and previous patch set or patch set update, allowing customers time to plan for and install the new version. 
Oracle Enterprise Performance Management 11g Grace Periods for Error Correction (Doc ID 1590676.1)

Note: The info is subject to change.


Wednesday, October 28, 2015

Open World Update - Oracle 12c High Availability


Quick OOW update on the Database track...

Today I attended the interesting session at Oracle Open World 2015:
Deep-Dive into High Availability with the Next Release of Oracle Database 12c [CON8827].
By Wei Ming Hu, Vice President - High Availability Technologies, Oracle

New Database 12.2 Updates

Oracle is shifting to the concept of Elastic Sharding, referencing many times in the talk about Google's sharded infrastructure for large scaling. This is essentially multiple independent nodes acting as a single logical database. These nodes do not require any shared storage or expensive clustering infrastructure. This is almost the exact opposite of RAC which requires intensive shared storage and networking requirements.

It appears this is Oracle's solution to moving into the cloud. In the cloud expensive clusterware technologies are not appropriate.

What is the underlying technology to Elastic Sharding? Active Data Guard. Oracle is beefing up the Active Data Guard technology to run the internals.
- Changes from logical sql level to block level replication.
- Redo logs will be applied to standby nearly instantly.
- Preserve all sessions during failover - allowing for online patching...etc.
- Allows for In Memory database.
- Seamlessly detect failures and evict nodes for repair.

While I am sure there always use cases, my opinion is RAC is going to be less important going forward.



Sunday, September 20, 2015

SHA2 SSL Certificates are Coming

A relatively low key change is being embraced quickly by the security industry and it may impact Oracle Hyperion installations. When using SSL certificates there is a specific encryption algorithm used to sign the certificates. This algorithm for a long time was SHA1. However, SHA1 is an old technology and can now be cracked easily. It is no longer secure to use SHA1. Consequently, the industry is swiftly moving to SHA2. As of Jan 1st, 2016 no SHA1 certificates can be issued, forcing the change to SHA2.

One of the better writeups I found on the issue is:
http://www.infoworld.com/article/2879073/security/all-you-need-to-know-about-the-move-to-sha-2-encryption.html

What does this mean?
Some older software has trouble with the SSL handshake using SHA2. For instance with Oracle Database, SHA2 was only supported starting with 11.2.0.3.
http://docs.oracle.com/cd/E11882_01/server.112/e41360/chapter1_11203.htm
Since then some products have been back ported to support SHA2, but likely require patches. The SHA2 change mostly impacts integrating with older systems using SSL. However, anything touching SSL can be impacted. It will be necessary to identify and test any integration points in your environment with SSL. A few examples include:

  • WebLogic
  • LDAP providers
  • Single Sign On providers
  • SSL database connections (DRM, FDM, Essbase...etc)
  • SOA / Web Services interactions
Don't panic. Most Hyperion sites do not make heavy use of SSL. Perhaps just offloading at the Load Balancer. However, sites that have integrated SSL more thoroughly in the environment may be impacted.


Browsers are already starting to call out insecure sites. For instance taking a look at Wells Fargo in Chrome, points out the connection to www.wellsfargo.com is using an obsolete cipher suite.

Taking a look at the certificate details:

However, most sites have already converted. The google.com cert shows the SHA2 certificate.


Your company will be upgrading soon, if not already. Consequently, it's time to start testing your SSL connections with SHA2 to make the transition smoothly.

Saturday, August 22, 2015

SSL Tip: Converting a JKS file into an Oracle Wallet


When implementing SSL with Oracle technologies such as OHS an Oracle Wallet is required. The Oracle Wallet is Oracle's proprietary keystore for holding identity and trust SSL keys. Often though, a company will provide the Java Keystore (jks) format. Here is a trick to convert the JKS into an Oracle Wallet via the command line using the orapki command from an Oracle Client installation.

# create a new wallet. If C:\wallet_temp already exists, remove it first.
orapki wallet create -wallet c:\wallet_temp -auto_login_local
orapki wallet create -wallet c:\wallet_temp\ewallet.p12 -auto_login

# Remove all the default trusts from new wallet for a truly empty wallet
orapki wallet remove -wallet c:\wallet_temp\ewallet.p12 -trusted_cert_all

# using an existing jks file, import all contents into the new wallet
orapki wallet jks_to_pkcs12 -wallet c:\wallet_temp\ewallet.p12 -keystore c:\wallet_temp\my_java_keystore.jks -jkspwd "my_java_keystore_password"

At this point the new wallet is stored in C:\wallet_temp and ready to use.