Quick Tip: Complete DCOM Re-Configuration

I want to keep this post short so I'll dive right into the issue.

When you first configure a server with HFM components on it the first time you configure DCOM it will run a "pre-configuration" task. This involves registering some DLL files and using DISM to reconfigure the server.

Any subsequent runs of the "Configure DCOM" task in HFM will miss out this pre-configuration step. So if you find yourself needing to re-configure DCOM (perhaps due to Windows patching) it is possible that not everything required will re-register.

The way to fix this is simple, we just run the commands below (fit for your environment, obviously):

cd /d E:\Oracle\Middleware\user_projects\epmsystem1\bin
resetConfigTask.bat -product HFM -task preConfiguration

Then when you go to run the "Configure DCOM" task in the configtool it will also include this "pre-configuration" step as well.

You may have read about this resetConfigTask.bat script elsewhere online, I actually found about the command here: http://epm-errors.blogspot.co.uk/2013/01/resetconfigtaskbat.html

DRM Application Update Timing Out

Another quick post: if you find that when you try to apply updates to a DRM application the utility times out trying to connect to the database, make sure there isn't an "@" sign in the database user's password!

Encountered this strange issue recently, hope it helps someone!

DRM Admin Password Expiry Fix

I realise I haven't done a blog post in a while, unfortunately work has been super busy recently, so I decided to drop a quick tip here.

I have had issues with the DRM admin password expiring. This is obviously not a situation anyone wants and it seems that DRM ignores the "no password expiry" option as well. The only fix I have found is running the SQL script below:

UPDATE [DRM].[dbo].[RM_User]
      SET [d_password_expire_date] = '2035-01-01 01:01:01.00'
      ,[b_exclude_from_timeout] = 1
      ,[b_no_password_expiration] = 1
      ,[b_exclude_from_lockout] = 1
 WHERE c_user_name = 'ADMIN'
GO

Obviously altering the name of the DRM database for your own.

The Real SQL Server Pre-Requisites for EPM

Oracle have published some pre-requisites for SQL Server databases to be used by EPM. You will typically see these mentioned:

ALTER DATABASE DB_XXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE DB_XXX SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE DB_XXX SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DB_XXX SET MULTI_USER ;


However that really doesn't tell the whole story! I had to go through an Oracle Support Request to find this out but there are actually much more than that. 

The script below will run the full pre-requisites on all databases prefixed with "EPM_". I like to use prefixes on the databases as it keeps things neat, and it also means we can perform operations on more than 1 database at a time without worrying about screwing up anyone else's databases! We also assume the EPMA database ends with "EPMA" and the FDMEE database ends with "FDMEE". The SQL user is assumed to be called "hyperion".

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''EPM_%''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT;
ALTER DATABASE ? SET ANSI_NULL_DEFAULT OFF;
ALTER DATABASE ? SET ANSI_NULLS OFF;
ALTER DATABASE ? SET ANSI_PADDING OFF;
ALTER DATABASE ? SET ANSI_WARNINGS OFF;
ALTER DATABASE ? SET ARITHABORT OFF;
ALTER DATABASE ? SET CONCAT_NULL_YIELDS_NULL OFF;
ALTER DATABASE ? SET QUOTED_IDENTIFIER OFF;
ALTER DATABASE ? SET RECURSIVE_TRIGGERS OFF;
ALTER DATABASE ? COLLATE SQL_Latin1_General_CP1_CI_AS;
END'
EXEC sp_MSforeachdb @command

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%EPMA''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE ''%FDMEE''
BEGIN
USE ?
SELECT DB_NAME() As DatabaseName
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ? SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ? SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ? SET MULTI_USER WITH NO_WAIT ;
END'
EXEC sp_MSforeachdb @command

ALTER LOGIN hyperion WITH DEFAULT_LANGUAGE = us_english

It is also worth mentioning that EPM requires the languages on all users and databases to be "us_english". Again, had to go through an SR to find that one out as well!

Based on feedback in the comments and some tuning guides for FDMEE and HFM the default is now for READ_COMMITTED_SNAPSHOT to be off for all databases except EPMA and FDMEE.

This does contradict the recommendations here, though, which say to turn on READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION for all databases except HFM:
https://docs.oracle.com/cd/E57185_01/EPMIS/ch03s04s02s01.html

Edit: the collation previously mentioned was case sensitive. This is against Oracle recommendations and has been amended.

Serving Edited EAS Console JNLP File

There are 2 problems with EAS that, as far as I know, have been around since version 11.1.2.1:
  

1. EAS Console will not be able to connect when using an SSL-offloaded, load-balanced EAS URL (such as https://example.com/easconsole/)

2. Periodically EAS will need to be restarted. The EAS Console will not be able to authenticate successfully  and the logs will be filled with “response already committed” errors.

The fix for both of these errors is to edit the “easconsole.JNLP” file. With the first we must add “:443” to the EAS Console URLs, to ensure that SSL communication will happen over port 443. For the second we have to add a line with ‘<update check="background"/>’ to the file.

In order to serve up this edited JNLP file with EAS, we must make a few changes to OHS. First we must add a directory called “easconsole” under the “E:\Oracle\Middleware\EPMSystem11R1\common\epmstatic\” folder, and place the edited JNLP file into it.

Then we must add the JNLP MIME type to OHS by editing the file “E:\Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\config\OHS\ohs_component\mime.types”. We must add in the following line:

application/x-java-jnlp-file jnlp

Then we must edit the file “E:\Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\config\OHS\ohs_component\epm_rewrite_rules.conf” (change as appropriate for other versions of EPM).  We must add the following line:

RewriteRule ^/easconsole/easconsole.jnlp /epmstatic/easconsole/easconsole.jnlp [PT]

Restarting OHS will put this new configuration into place. Congratulations, your “Launch” button just became useful again!

11.1.2.4: Useful Resources

So I will dump all of the useful EPM 11.1.2.4 links I have found so far into this consolidated post. If you find any more please let me know!

 ---

EPM 11.1.2.4: General Information

Feature Rich! Summary of Oracle EPM 11.1.2.4 New Features

EPM 11.1.2.4 Documentation

EPM 11.1.2.4 is certified with Internet Explorer 11

Life Cycle Management Changes in 11.1.2.4
easy error tackling - new Error Message Guides in EPM 11.1.2.4
Thoughts on deprecated Essbase 11.1.2.4 features and the future of EAS

EPM 11.1.2.4: Installation & Configuration

EPM 11.1.2.4 Installation and configuration

EPM Test Drive Quattro

Taking 11.1.2.4 for a spin in the cloud

Firing Up 11.1.2.4 with John A. Booth’s Amazon Image
HFM 11.1.2.4 Running On Commodity Linux

EPM 11.1.2.4: Planning

Planning 11.1.2.4 New Features

EPM 11.1.2.4: HFM

A brief glance at HFM 11.1.2.4 from a techie perspective

Say Hello To HFM 11.1.2.4
Financial Management 11.1.2.4
HFM 11.1.2.4 under the covers – the new architecture  
HFM 11.1.2.4 and Copy App Utility - NOT! 

EPM 11.1.2.4: FDMEE

FDMEE 11.1.2.4 has been released
FDMEE 11.1.2.4...the show must go on!  

EPM 11.1.2.4: DRM

DRM 11.1.2.4 New Features Released! Official from Oracle!
DRM 11.1.2.4.301 Patch Details

EPM 11.1.2.4: Calc Manager

Calc Manager 11.1.2.4 New Functions

---

For  all of the blogs above I highly suggest bookmarking them - they are certainly much more useful than this one!

Posts to Come

Just a quick note to say: I'm still here! I will create some posts in the next few weeks to discuss the following topics:

EPM 11.1.2.3.500 -> 11.1.2.4 upgrade
DRM 11.1.2.4 installation
HFM 11.1.2.4 - upgrade path, recommendations, etc