FDMEE: EPMHFM-66076: HFM Server unavailable,connection could not be established

Hi Friends,

In this post, we will explore error EPMHFM-66076: HFM Server unavailable: connection could not be established which was encountered while running data load in Oracle Hyperion Financial Data Quality Management (FDMEE) from SAP (source) to Oracle Hyperion Financial Management (HFM) (target) application.

Here I want to emphasize the point that this issue had occurred in our newly built Oracle Hyperion environment, so you can think of the solution given below as a ‘MUST DO’ activity once you are done with the installation and configuration of any new Hyperion environment.

You can refer the following Oracle Hyperion Financial Management (HFM) documentation for details:

https://docs.oracle.com/cd/E57185_01/HFMAD/apbs06s01.html

Note: 

• This post has been written and associated activities have been demonstrated on Oracle Hyperion Financial Data Quality Management (FDMEE) version 11.1.2.4.210.
• Hyperion Financial Management (HFM) application servers and Hyperion Financial Data Quality Management (FDMEE) application servers mentioned in this post have Windows Server 2012 R2 operating system.
• It's very important to test any workaround/fix first in a non-Prod environment, and move/apply it to PROD only when you are fully sure about its workability.
• There will be outage required on Hyperion Financial Management (HFM) and Hyperion Financial Data Quality Management (FDMEE) applications, so make sure you inform your user base in advance before you apply this fix in your Hyperion environment. 

Description of Issue:

While running the Oracle Hyperion Financial Data Quality Management (FDMEE) data load from the SAP source system to target Oracle Hyperion Financial Management (HFM) application, we noticed that all loads are getting failed with following common error message reported in FDMEE data load run logs:

2020-04-23 03:12:48,882 DEBUG [AIF]: intersectionCheckXml(\\NetworkPath\EPM_Fileshare\FDMEE_DATA_DEV\FDMData\outbox\HFMAPP_VALIDATE.xml, false, 10000)

2020-04-23 03:12:48,866 ERROR [AIF]: Error: Server SERVERNAME is unavailable, connection could not be established.

2020-04-23 03:12:48,882 DEBUG [AIF]: oracle.epm.fm.common.exception.HFMException: EPMHFM-66076: Server SERVERNAME is unavailable, connection could not be established.

Caused by: org.apache.thrift.transport.TTransportException: java.net.BindException: Address already in use

2020-04-23 03:12:48,882 ERROR [AIF]: HFM Server is unable to process the number of requests.  Please review the data and try again.

2020-04-23 03:12:48,882 ERROR [AIF]: HFMException: Server SERVERNAME is unavailable, connection could not be established.

2020-04-23 03:12:48,906 ERROR [AIF]: The script has failed to execute:

2020-04-23 03:12:48,906 FATAL [AIF]: Error in Comm.executeJythonScript

Traceback (most recent call last):

  File "<string>", line 557, in executeJythonScriptFile "E:\apps\OracleEPM\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_VALIDATE.py", line 108, in <module>raise Exception("There was an exception in intersectionCheckXML method")

Exception: There was an exception in intersectionCheckXML method

2020-04-23 03:12:48,910 FATAL [AIF]: Error in CommData.validateData

Traceback (most recent call last):

  File "<string>", line 4499, in validateData

  File "<string>", line 578, in executeVBScript

  File "<string>", line 557, in executeJythonScript

The error message indicates that Python script HFM_VALIDATE.py is unable to connect to your Hyperion Financial Management (HFM) application servers in order to perform intersection validation of your target Hyperion Financial Management (HFM) application for the data load run.

In initial checks, we found that both the Hyperion Financial Management (HFM) application servers are up and running and HFM apps are accessible from the Workspace without any issue. 

Cause:

Actually whenever there is a heavy load or large data volume processing on the Windows servers (HFM and FDMEE servers), the default port limit may be exceeded causing disconnection to the server as the server will stop receiving any new TCP/IP connections.

So the port limit (MaxUserPort) and wait time (TcpTimedWaitDelay) of the server has to be fine-tuned in order to fulfill all connection requests coming to the server. 

MaxUserPort 

The MaxUserPort parameter sets the maximum number of user ports that TCP/IP can assign when an application requests an available user port from the system. You can adjust the value of this parameter to optimize network throughput. The MaxUserPort parameter applies only to the Windows operating system. The default start port value is 5000 and the maximum end port value you can put is 65535. The recommended value is 65534. This range is in addition to well-known ports that are used by services and by applications.

TcpTimedWaitDelay 

The TcpTimedWaitDelay value determines the length of time that a connection stays in the TIME_WAIT state before being closed. While a connection is in the TIME_WAIT state, the socket pair cannot be reused. So you can configure the TcpTimedWaitDelay to cause TIME_WAIT sockets to clear more quickly. The Default TcpTimedWaitDelay value is 240 seconds (4 mins). The recommended value is 30 seconds.

Solution:

If you see the above mentioned error messages in Hyperion Financial Data Quality Management (FDMEE) data load logs, then perform the following steps to fix this issue:

1- Login to all your Hyperion Financial Management (HFM) application servers and Hyperion Financial Data Quality Management (FDMEE) servers one by one and make the following registry changes.

2- First take a backup of the server registry.

3- Open Windows Registry Editor and goto:

 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters

Note: By default, these two keys ("MaxUserPort" and " TcpTimedWaitDelay") does not exist in the Windows server registry.

4- Create a new DWORD with name ‘MaxUserPort’ and value ‘65534' (=00fffffe in hexadecimal) as shown below.

Right-click on Parameters and select New—DWORD (32-bit) value

Enter the name as MaxUserPort 

Right-click and select Modify

Enter the value as 0000fffe (=65534 in decimal)

MaxUserPort entry is added in the registry.

5- Now create a new DWORD with the name ‘TcpTimedWaitDelay’ and value ’30' (=0000001e in hexadecimal) as shown below.

Right-click on Parameters and select New—DWORD (32-bit) value

Enter the name as TcpTimedWaitDelay

Right-click and select Modify

Enter value as 0000001e (=30 in decimal)

TcpTimedWaitDelay entry is added in registry.

6- Once you are done with adding above two registry entries in all the HFM and FDMEE servers, reboot all the HFM and FDMEE servers for the changes to take effect.

7- Once the servers are up, start HFM and FDMEE services on respective servers.

8- Now trigger your FDMEE data load to HFM and you will not see the ‘HFM Server unavailable’ error any more.

As per Oracle update, this issue has been addressed in unpublished Bug 25616928 - minimize calls to hfm metadata api during intersection validation. So you should try to install Oracle Hyperion Financial Data Quality Management (FDMEE) Release 11.1.2.4.210 Patch Set Exception (PSE): 25616936 in your Oracle Hyperion environment and check whether the issue gets fixed or not.

That's all for this post.

I hope this article has helped you.

Your suggestions/feedback are most welcome.

Keep learning and Have a great day!!!