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:

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.

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.