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:

Tuesday, January 31, 2012

Opatch Not Displaying Correct Patches (Opatch Accuracy)

I was recently tasked with checking the patch level on a large scale implementation which I was not familiar with. The first action was to do the opatch -lsinventory on each server to get a list of applied patches. After meticulously categorizing each patch level indicated by lsinventory I discovered some inconsistencies. This led me to question the accuracy of the results of the lsinventory command. Consequently, I started to analyze the summary log file and discovered that some of the patch numbers were actually applied to the server which were not being returned by opatch. The summary file is:
G:\Oracle\Middleware\EPMSystem11R1\cfgtoollogs\opatch\opatch_history

In the summary file it lists the particular log file for each opatch operation.
For example,
G:\Oracle\Middleware\EPMSystem11R1\cfgtoollogs\opatch\opatch2012-01-17_17-37-26PM

So I dug a little deeper and analyzed the opatch logs for the patches which were not showing up in lsinventory. It appears the patch WAS successfully applied. In addition, to confirm my suspicions, I compared file by file using WinDiff directory comparison. Two servers, which should be identical, showed inconsistencies in the opatch lsinventory. The results showed, even with lsinventory implying the patch levels were different, the binary WinDiff comparison as well as the opatch logs confirmed the correct patches had been applied to both servers.

To further illustrate this point, the following knowledgebase article identifies that some commands can completely destroy the patch level information that opatch contains:
ID 1357748.1


IMPORTANT! Running createinventory.bat discards all patching information, reverting to a simple list of installed products, even if there are installed patches on the system. This does not affect the correct operation of the products and patches, but it could cause issues if a new patch must be applied over an existing patch. Because there is no record of the existing patch in the inventory, OPatch will refuse to apply the newer patch. The only way to restore the patch information in the inventory is to re-apply the patches.




What good is a patching tool which has the possibility of permanently truncating your patch history?

My conclusion is that the lsinventory does not necessarly give the correct results when it comes to patching, which is disappointing when using opatch as a patching tool.

So, assuming that opatch -lsinventory is inaccurate, what is an alternative method for determining the patch levels within the environment?  As indicated previously, each opatch operation is carefully logged. It is possible to review the logs and determine which patches have been successfully applied. However, over time many logs can accumulate.

Using windows grepping utility, AstroGrep, it is possible to quickly sift through the log directory and point out the patch instances which were successful. For example, search for the string, "check OK: Patch ID (.+) is registered"

Wednesday, January 25, 2012

EPMA - Troubleshooting

There are two main EPMA components, an Internet Information Services (IIS) component and a WebLogic component. It is helpful to know the difference to pinpoint issues in EPMA. Since they are completely different technologies, spending time looking into one part while the problem is in another is wasteful. One can narrow down where to look by these urls:
 
IIS Component:
http://hostname/hyperion-bpma-server/Sessions.asmx

Application Server Component(WebLogic/WebSphere)
http://hostname:19091/awb/conf/AwbConfig.xml

Deleting an EPMA HFM Application not Listed in the Application Library

Problem: The HFM EPMA application does not show up in EPMA application library in Workspace, yet it is listed in the list of HFM applications. The intention is to delete the rogue application because it is partially deployed and not used. Trying to delete the application from HFM gives a message saying "This application is not registered in Shared Services". The next logical step is to try and register the HFM application in Shared Services, however registration fails with the error:

This is a BPMA application. The functionality must be done through BPMA.
Show Details:
Error Reference Number: {F79F0798-39B7-4565-91A3-3E929F9F16FD}

Since the application is not listed in the EPMA application library, it is not possible to re-register the application. Here's a trick to enable the registration with Shared Services in order to delete the application from HFM:

Open the HFM relational database, Each application has it's own set of tables.
Look for the APP_NAME_PROPERTIES table,

Change HFM APPMODE property from EPMA to HFM in the table. Be sure to commit any changes.

This flips the application back to a normal HFM application. Next, open Workspace Classic Application Administration and register the application.

Finally, in Shared Serivces provision yourself as administrator to the HFM application and delete the application from HFM.



Tuesday, January 10, 2012

SSL and Load Balancing in 11.1.2.1 OHS -> IIS

In 11.1.2.1 if you are using SSL and Load Balancing from OHS to IIS (ProxyPass, ProxyPassReverse) then you will need this knowledgebase article. The OHS to IIS connection is used for the Hyperion modules, HFM, FDM, DRM, EPMA. The solution disables the SSL Caching because it's not handled correctly and you will receive a ton of errors.

Financial Management Error "Bad Gateway. Request could not be processed. Invalid response received by proxy or gateway server" When Browsing Content Within an Application [ID 1385480.1]

Applies to:

Hyperion Financial Management - Version: 11.1.2.1.000 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

Users receive the following error messages a few minutes after they start accessing Financial Management (HFM) application content (e.g. opening HFM applications, opening HFM web forms, etc.):

Bad Gateway
Request could not be processed. Invalid response received by proxy or gateway server


Notes:
  • Users access the Workspace user interface through OHS (Oracle HTTP Server),
  • SSL has been enabled on both OHS (Workspace web server) and IIS (HFM web server).

The below error message is being returned in the OHS error log at the time of the issue:

[tid: 3044] [user: SYSTEM] [VirtualHost: epmdev.hyperion.com:0]
ssl_engine_kernel.c:2363: Inter-Process Session Cache: request=REM status=OK
id=65376265333730333164633838663538373 (session dead)^M
[2011-11-23T22:28:23.2350-08:00] [OHS] [NOTIFICATION:32] [OHS-9999] [core.c]
[host_id: vm0sgennai127] [host_addr: 192.168.0.127] [pid: 4440] [tid: 3044]
[user: SYSTEM] [VirtualHost: epmdev.hyperion.com:0] (OS 10054)

Cause

The error code 10054 visible in the OHS log file indicates that the error occurred on the other side of the connection (i.e. on the IIS side in this case), causing the connection to drop. In particular, IIS is dropping the connection with OHS on asp pages during the SSL handshake, causing users to receive a '502 Bad Gateway' error message when attempting to access HFM content through Workspace.

This issue only occurs if the session cache is enabled on the OHS side. The session cache allows existing SSL sessions to be resumed ('cheaper handshake'). While resuming existing SSL sessions between the OHS and the IIS components, IIS drops the connection during the handshake. This is when the issue occurs.

Solution

IIS is unable to handle the resuming of cached SSL sessions properly. Thus, disabling such session caching prevents the issue from occurring.

Please find below the steps to be followed for disabling the session caching on the OHS side:

Note: These steps have to be followed on any server hosting the Oracle HTTP Server (OHS) component.
  1. Navigate to the below folder:
    <ORACLE_HOME>\Middleware\user_projects\epmsystem1\httpConfig\ohs\config\OHS\ohs_component
  2. Make a backup copy of the file SSL.conf
  3. Open SSL.conf in a text editor (ie. Windows Notepad)
  4. In the file find the following section:
    # Inter-Process Session Cache:
    # Configure the SSL Session Cache: First the mechanism
    # to use and second the expiring timeout (in seconds).
    SSLSessionCache "shmcb:${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/ssl_scache(512000)"
    SSLSessionCacheTimeout 300
    Modify this file section, so that it looks like this:
    # Inter-Process Session Cache:
    # Configure the SSL Session Cache: First the mechanism
    # to use and second the expiring timeout (in seconds).
    # SSLSessionCache "shmcb:${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/ssl_scache(512000)"
    # SSLSessionCacheTimeout 300
    SSLSessionCache none
    SSLSessionCacheTimeout 15
  5. Save and close the file.
  6. Restart the OHS component for the changes to be taken into account - for instance by restarting the 'Oracle Process Manager (ohsInstanceXXXXXXXX)' service.

Friday, January 6, 2012

Oracle EPM with SSL

Configuration SSL for Hyperion is a bit of an art form. It is somewhat cumbersome and can lead to many hours of debugging when something goes wrong. For those looking for a good start to SSL check out this link:

Installing and Configuring Oracle® Hyperion Enterprise Performance Management System 11.1.2 with SSL Enabled on All Layers


http://www.oracle.com/technetwork/middleware/performance-management/tutorials/index-087654.html

Sunday, January 1, 2012

Configuring the Financial Reporting Print Server in 11.1.2.1

The Print Service comes with the FR Client installation package on edelivery.oracle.com.

It is not installed automatically... Follow these instructions:

Configuring Financial Reporting Print Server


Financial Reporting Print Server is used to generate PDF reports. Before you configure Print
Server, ensure the following:
• At least one Planning server is configured and running
• Financial Reporting Studio is installed on a client host machine (CLIENTHOST1)
To configure Print Server:
1. Using the deployment account, log into the EPM System Clients host
(CLIENTHOST1).
2. Using a text editor, open C:\Program Files
(x86)\Oracle\FinancialReportingStudio\products\financialreporting\install\bin\FRSetupPrintServer.properties.
3. Enter the following information:
FRWebServer=http://epminternal.mycompany.com:19000
AdminUser=admin
AdminPassword=EPM Administrator’s password
4. Save and close FRSetupPrintServer.properties.
5. Run FRSetupPrintServer utility.
The print server should be ready to use after you run the FRSetupPrintServer utility.

There is a log file generated by this utility and you should check it to be sure it ran correctly. Also, in control panel-> printers on the Financial Reporting node you should see some virtual printers named FRPrint(1,2,3...). If there are no printers listed - the script is failing... Also check the windows services to ensure the FR print service is listed.

HFM - Registry Key that has been Marked for Deletion

Problem Description:
HFM Application will not open from workspace - this was not reported by the end users and caught before it was an issue - error recieived is:

Warning Illegal operation attempted on a registry key that has been marked for deletion.
Code: -2147023878

Description: An error occurred processing the result from the server.

Trace: Error Reference Number: {61D370EC-BF81-46B9-9D06-B2323C58653C}<BR>Num: 0x800703fa;Type: 0;DTime: 9/11/2011 9:10:10 AM;Svr: server;File: CHsxClient.cpp;Line: 497;Ver: 11.1.2.1.000.3082;<BR>Num: 0x800703fa;Type: 0;DTime: 9/11/2011 9:10:10 AM;Svr:server;File: CHsxClient.cpp;Line: 899;Ver: 11.1.2.1.000.3082;<BR>Num: 0x800703fa;Type: 0;DTime: 9/11/2011 9:10:10 AM;Svr: server;File: CHsxClient.cpp;Line: 3671;Ver: 11.1.2.1.000.3082;<BR>Num: 0x800703fa;Type: 0;DTime: 9/11/2011 9:10:10 AM;Svr:server;File: CHFMwManageApplications.cpp;Line: 2722;Ver: 11.1.2.1.000.3082;


Resolution:
 Symptoms
In Financial Management (HFM) application , you may receive the following error message when attempting various HFM related operations such as Journal Entries, connecting via Smartview to HFM, or with Financial Reporting:
Illegal operation attempted on a registry key that has been marked for deletion.

An error has occurred. Please contact your administrator.
Error Number:70
Error Description:Permission denied
Error Source:Microsoft VBScript runtime error
Page On which Error Occurred:/hfm/loadextract/ExtractJournals.asp
Changes
This issue occurs only in EPM systems deployed on Windows 2008 server, so if a OS upgrade had occurred, this error may be thrown.
Cause
The identity user initially logged on to the server when the application launched. The issue happens when the identity user logs off and the COM+ application can no longer read registry keys in the profile of the identity user because of a new User Profile Service functionality of forcing the unload of the user profile on Windows 2008 when the user logs off.
Solution
To resolve this issue, make the following change on all Financial Management servers in the environment:
1. Run group policy editor (gpedit.msc)
2. Expand to: Computer Configuration -> Administrative Templates -> System -> UserProfiles -> Do not forcefully unload the user registry at user logoff
3. Check the settings and change it from “Not Configured” to “Enabled”
4. Reboot the servers

 

Creating an FDM Application Without the DBA

For organizations where getting a DBA's dedicated time is close to impossible, here are some notes regarding how to go about creating the FDM application while minimizing requirements for the DBA.


FDM: Does not need DBA privileges to create an application, assuming the schema/tablespaces are created ahead of time.

Create the user id, and schemas for FDM. Oracle recomends this be in a new instance of Oracle, not with the other EPM products, so that a multiple of performance and other settings can be applied at the instance level which may help FDM.

These Oracle Privileges are safe for just about any Hyperion product - if possible, set these for every schema:
Create Procedure, Create Sequence, Create Session, Create Table, Create Trigger, Create View, and Query Rewrite.
Separate tablespaces are recommended:
   FDM (Default)
   FDM_WORK_TABLES
   FDM_WORK_TABLE_INDEXES
   FDM_DATA_MAP_SEG
   FDM_DATA_SEG

There is an entire hundred page DBA guide for FDM:


If something goes wrong and there is already a partial application with tables in the FDM schema and you need to recreate an application in the same schema:

Oracle Knowledgebase:
Message "DATABASE/SCHEMA ALREADY EXISTS" When Creating a New Financial Data Quality Management Application [ID 966752.1]



Each FDM Application requires a UNIQUE/BLANK user schema inside the Oracle DB. If the message comes back stating that it already exists then it is not unique or blank and already contains data.... and this violates the FDM policy.

Perform either of the following actions:
1. Drop/Recreate the User Schema
2. Create a new User Schema

You can run sqldeveper to export the schema metadata including “DROPS” which will allow you to drop all tables, indexes, keys…etc. However this leaves some objects in the recycle bin that fdm is finding still when trying to create the app saying “schema already exists”

Alternatively you can execute the following query against the User Schema being used for the FDM Application to ensure that zero records are returned; indicating it is blank:

select * from user_objects;
or
select OBJECT_NAME from user_objects;



There are some objects that might be left in the recycle bin of type "LOB" even if the schema is "clear"



SQL> purge recyclebin;

Recyclebin purged.


App should now create successfully.