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.

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.

Thursday, July 16, 2015

Oracle Learning Library - Hidden Gems

The Oracle Learning Library has a myriad of tutorials on Hyperion and related technologies. They are often overlooked as the interface is filled with out of date and somewhat scrawny material. However, there are some hidden gems. Much of the material in the tutorials goes beyond the basics found in the Oracle Documentation, and they can shed light on some of the more poorly documented topics.
First, it can be a great way to explore the technology stack. For instance, getting some intro tutorials on WebLogic, Linux, and Oracle RDBMS.

Some of the Hyperion articles are in depth and cover a lot. They are rich with screenshots.

The main Oracle Learning Library site for Hyperion is:
https://apexapps.oracle.com/pls/apex/f?p=44785:2:::NO:2,RIR,CIR:P2_TAGS:Hyperion

Basic install instructions detailing how to scale to multiple nodes.
Installing and Configuring Oracle® Hyperion Financial Close Management 11.1.2.4.000 in a Multi-Node Environment 
http://www.oracle.com/webfolder/technetwork/tutorials/obe/hyp/FCM11.1.2.4-MultiNodeInstall/FCMMultiNodeInstall.html

Basic knowledge for training new recruits.
Intro to Financial Management 
http://download.oracle.com/technology/products/hfm/demos/hfm1112overview/HFM_LessonIndex.htm

One of my favorites...
Deploying and Configuring Data Relationship Management Web Service API 
http://www.oracle.com/webfolder/technetwork/tutorials/obe/hyp/DRM11.1.2-WebServicesAPI/index.htm

Oh, is that how you're supposed to do it?
Installing and Configuring EPM System 11.1.2 with SSL Enabled on All Layers 
http://www.oracle.com/technetwork/middleware/performance-management/tutorials/index-087654.html

Getting up to speed on those pesky task flows...
Automating Tasks in Oracle® Hyperion Financial Management, Fusion Edition 11.1.2 
http://www.oracle.com/webfolder/technetwork/tutorials/obe/hyp/HFM11.1.2_TskAuto/Tsk_Auto.htm

Sunday, June 21, 2015

Hyperion Auditing - In Practice vs. Theory

At first glance the Shared Services audit features in Hyperion seem complete. However, in practice, the user interface seems to be clunky and hard to use.

The audit features are found in Shared Services. First, auditing has to be enabled in order to capture any auditing information. This can be accomplished by entering Shared Services and selecting Administration -> configure auditing. Select "Enable Auditing". Most of the auditing for the various products are simply LCM. In other words, you will see only LCM operations being audited. However, under Shared Services there is a little more detail:

The option that is most interesting is User Provisioning. Many organizations require fine grained auditing of users as they are provisioned and deprovisioned from applications. This can aid in meeting specific SOX requirements. Let's take a closer look at this in practice.

First, let's look at the Shared Services Auditing user interface. This is found in Administration -> Audit Reports -> Security Reports.

The default report shows 30 days of history. It can be confined to a specific date range too. You can search by "performed by". However, if you are unsure who performed the action, this field may not be very useful. Finally, you can narrow down by product name. In this case the product is Shared Services for checking provisioning information.



One of the first challenges becomes sifting through the interface. It is impossible to sort or narrow
down by Task, which is the operation being audited. Consequently, the display is overwhelmed with "authenticate" requests from users using the system. Secondly, only 50 items can be displayed on the page at a time. Let's say you want to review audit information over a range of 10 days. Now the pagination ("1of x") comes into play. One has to sort though multiple pages of info, most of which is irrelevant authentication information. What about finding when a user was provisioned over the last year? Forget about it.

Assuming you do find a particular item of interest. In this case, the admin user provisioned "testuser1" to an application. Notice anything missing? What application? For this information you have to click the checkmark in the options for "Detailed View".


The detailed view shows the full detail. This now tells us that testuser1 was provisioned by admin to the PLANDEMO application as role Administrator.


This display is perfectly fine, but it is difficult to search around repeatedly for looking for specific information.

One way to get at the data is to tear off the clunky user interface and head to the database, targeting exactly the info you want. A rough, basic query can be put together quickly. From there it is possible to do much more powerful querying. Additionally, the audit information accumulates very quickly. Keeping this data around can grow to huge sums of data. Using a query over the user interface can help sift through millions of rows quickly.

select STARTTIME, USER_NAME, ARTIFACT_NAME, ATTRIBUTE_NAME, attribute_curr_value from 
  SMA_AUDIT_FACT NATURAL JOIN SMA_TASK_DIM
  NATURAL JOIN SMA_AUDIT_ATTRIBUTE_FACT 

 where artifact_name like '%testuser1%' and 
 TASK_NAME like '%Provision User%'
 ORDER BY STARTTIME, AUDIT_FACT_ID, ARTIFACT_NAME;

Results of query:

Again we find that testuser1 was provisioned by admin to the PLANDEMO application as role Administrator.

Friday, June 5, 2015

Silent Install and Configuration for EPM

One of the keys to reproducible installation and configuration is using a response file to store the configuration. The response file allows for rapid, scripted deployment across multiple environments. It also helps ensure others can easily repeat the installation.

The basis for EPMVirt is using response files for install/configuration. This allows for a building a process for a completely scripted Hyperion environment.

The install was recorded like this:
/u0/install/epm/installTool.sh -record /u0/automation/epm/silentInstall.xml

The install is invoked like this:
/u0/install/epm/installTool.sh -silent /u0/automation/epm/silentInstall.xml

The config tool was recorded like this:
/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -record /u0/automation/epm/EPMconfig_Foundation.xml

The config tool is invoked like this:
/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_Foundation.xml


For a closer look, let's dive into the files:
silentInstall.xml

<?xml version="1.0" encoding="UTF-8"?>
<HyperionInstall>
  <HyperionHome>/u0/Oracle/Middleware</HyperionHome>
  <UserLocale>en_US</UserLocale>
  <ActionType>0</ActionType>
  <SelectedProducts>
        <Product name="foundation">
            <ProductComponent name="foundationServices">
                <Component>hssWebApp</Component>
                <Component>staticContent</Component>
                <Component>weblogic</Component>
            </ProductComponent>
            <ProductComponent name="Calc">
                <Component>CalcWebApp</Component>
            </ProductComponent>
        </Product>
        <Product name="essbase">
            <Component>essbaseWebApp</Component>
            <Component>essbaseApsWebApp</Component>
            <Component>essbaseApsWebAppSamples</Component>
            <Component>essbaseStudioService</Component>
            <Component>essbaseStudioServiceSamples</Component>
            <Component>essbaseService</Component>
            <Component>essbaseServiceSamples</Component>
        </Product>
        <Product name="reportingAndAnalysis">
            <ProductComponent name="raFramework">
                <Component>raFrameworkWebApp</Component>
                <Component>raFrameworkService</Component>
            </ProductComponent>
            <ProductComponent name="fr">
                <Component>frWebApp</Component>
            </ProductComponent>
        </Product>
        <Product name="planning">
            <Component>planningWebApp</Component>
        </Product>
        <Product name="disclosure">
            <Component>disclosureWebApp</Component>
        </Product>
        <Product name="hfm">
            <Component>hfmAdmClient</Component>
            <Component>hfmWebApps</Component>
            <Component>hfmService</Component>
        </Product>
        <Product name="erpi">
            <Component>erpiWebApp</Component>
        </Product>
        <Product name="profitability">
            <Component>osloWebApp</Component>
            <Component>osloWebAppSamples</Component>
        </Product>
    </SelectedProducts>
  <ProductHomes/>
  <UpgradeCleanUp/>
  <UninstallCleanUp>false</UninstallCleanUp>
</HyperionInstall>


Silent Config - EPMconfig_Foundation.xml
It is pretty straight forward. Each product has its own section, and under each section is a task that you would find in the config tool. Following there are a series of bean objects which define the configuration values for each component.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<products>
  <instance>/u0/Oracle/Middleware/user_projects/epmsystem1</instance>
  <enable_compact_deployment_mode>true</enable_compact_deployment_mode>
  <auto_port_tick>true</auto_port_tick>
  <product productXML="Foundation">
    <tasks>
      <task>applicationServerDeployment</task>
      <task>FndCommonSetting</task>
      <task>preConfiguration</task>
      <task>relationalStorageConfiguration</task>
      <task>WebServerConfiguration</task>
    </tasks>
    <bean name="main">
      <bean name="applicationServerDeployment">
        <bean name="WebLogic 10">
          <property name="adminHost">localhost</property>
          <property name="adminPassword">AgzKbSiBZt2xNcQYXYjZ7qMeHz0qv6U7PosgZx76RSdPJqnOCohak8JSWBpC8ngw</property>
          <property name="adminPort">7001</property>
          <property name="adminUser">epm_admin</property>
          <beanList name="applications">
            <listItem>
              <bean>
                <property name="compactPort">9000</property>
                <property name="compactServerName">EPMServer</property>
                <property name="compactSslPort">9443</property>
                <property name="component">Shared Services</property>
                <beanList name="contexts">
                  <listItem>
                    <property>interop</property>
                  </listItem>
                </beanList>
                <property name="enable">true</property>
                <property name="port">28080</property>
                <property name="serverName">FoundationServices</property>
                <property name="sslPort">28443</property>
                <property name="validationContext">interop</property>
              </bean>
            </listItem>
          </beanList>
          <property name="BEA_HOME">/u0/Oracle/Middleware</property>
          <property name="domainName">EPMSystem</property>
          <property name="manualProcessing">false</property>
          <property name="remoteDeployment">false</property>
          <property name="serverLocation">/u0/Oracle/Middleware/wlserver_10.3</property>
        </bean>
      </bean>
      <bean name="customConfiguration">
        <property name="AdminEmail"/>
        <property name="adminPassword">2CvVUAlFeGfG1/SW1TS3u6b8wcJouqEEKp6s0KfyD806sQuDkm2LbJLNkUt4iY0S</property>
        <property name="adminUserName">admin</property>
        <property name="common_lwa_set">false</property>
        <property name="enable_SMTPServer_Authentication">false</property>
        <property name="enable_ssl">false</property>
        <property name="enableSslOffloading">false</property>
        <property name="externalUrlHost"/>
        <property name="externalUrlPort"/>
        <property name="filesystem.artifact.path">import_export</property>
        <property name="isSSLForSMTP">false</property>
        <property name="relativePaths"/>
        <property name="relativePathsInstance">filesystem.artifact.path</property>
        <property name="SMTPHostName"/>
        <property name="SMTPMailServer"/>
        <property name="SMTPPort">25</property>
        <property name="SMTPPortIncoming">143</property>
        <property name="SMTPServerPassword"/>
        <property name="SMTPServerUserID"/>
      </bean>
      <bean name="httpServerConfiguration">
        <property name="displayVersion">10.3.6</property>
        <property name="port">9000</property>
        <property name="protocol">http</property>
        <bean name="Proxy">
          <property name="path"/>
          <property name="port">9000</property>
          <property name="useSSL">false</property>
        </bean>
        <property name="sharedLocation">use_local_instance</property>
      </bean>
      <bean name="lwaConfiguration">
        <beanList name="batchUpdateLWAComponents"/>
        <beanList name="deploymentLWAComponents"/>
      </bean>
      <bean name="relationalStorageConfiguration">
        <bean name="ORACLE">
          <property name="createOrReuse">create</property>
          <property name="customURL">false</property>
          <property name="dbIndexTbsp"/>
          <property name="dbName">HYPDB</property>
          <property name="dbTableTbsp"/>
          <property name="dropRegistry">true</property>
          <property name="encrypted">true</property>
          <property name="host">epmvirt</property>
          <property name="jdbcUrl">jdbc:oracle:thin:@EPMVirt:1521:HYPDB</property>
          <property name="password">u/3u8zGjUgl6ekXFWdmCw8Ep992dW5WySl5q22W5Ty6kvzPM8FFJegduUsHaVXah</property>
          <property name="port">1521</property>
          <property name="SSL_ENABLED">false</property>
          <property name="userName">EPM_HSS</property>
          <property name="VALIDATESERVERCERTIFICATE">true</property>
        </bean>
      </bean>
      <property name="shortcutFolderName">Foundation Services</property>
    </bean>
  </product>
  <product productXML="workspace">
    <tasks>
      <task>applicationServerDeployment</task>
    </tasks>
    <bean name="main">
      <bean name="applicationServerDeployment">
        <bean name="WebLogic 10">
          <property name="adminHost">localhost</property>
          <property name="adminPassword">VH+syQvfsdYnKKP6VHA7OVvVTOa5kHSulb6MOJuJVQAJxGGVM12fO+fo0QDTp4//</property>
          <property name="adminPort">7001</property>
          <property name="adminUser">epm_admin</property>
          <beanList name="applications">
            <listItem>
              <bean>
                <property name="compactPort">9000</property>
                <property name="compactServerName">EPMServer</property>
                <property name="compactSslPort">9443</property>
                <property name="component">Workspace</property>
                <beanList name="contexts">
                  <listItem>
                    <property>workspace</property>
                  </listItem>
                </beanList>
                <property name="enable">true</property>
                <property name="port">28080</property>
                <property name="serverName">FoundationServices</property>
                <property name="sslPort">28443</property>
                <property name="validationContext">workspace/status</property>
              </bean>
            </listItem>
          </beanList>
          <property name="BEA_HOME">/u0/Oracle/Middleware</property>
          <property name="domainName">EPMSystem</property>
          <property name="manualProcessing">false</property>
          <property name="remoteDeployment">false</property>
          <property name="serverLocation">/u0/Oracle/Middleware/wlserver_10.3</property>
        </bean>
      </bean>
      <bean name="httpServerConfiguration">
        <property name="contextRoot">workspace</property>
        <property name="host">null</property>
        <property name="port">19000</property>
        <property name="protocol">http</property>
      </bean>
      <bean name="lwaConfiguration">
        <beanList name="batchUpdateLWAComponents"/>
        <beanList name="deploymentLWAComponents"/>
      </bean>
      <property name="shortcutFolderName">Workspace</property>
    </bean>
  </product>
</products>