EPM: Log4j vulnerability/security-threat in EPM 11.1.2.4

We all have heard about the Log4j vulnerabilities (CVE-2021-44228 and CVE-2021-45046) reported recently in Dec 2021. 

The Apache Software Foundation has released a security advisory to address a remote code execution vulnerability (CVE-2021-44228) and a denial of service vulnerability (CVE-2021-45046) affecting Log4j versions 2.0-beta9 to 2.15 ( a system running Apache Log4j version 2.15 or below i.e. Affected Versions are Apache Log4j versions 2.0--2.15.0). A remote attacker could exploit these vulnerabilities to take control of an affected system by executing arbitrary code. The recommendation is to upgrade to the latest Log4j 2.16.0 or applying recommended mitigations immediately. 

It has been determined that Log4j vulnerability impacts EPM (Enterprise Performance Management) application too via the Apache Log4j open source component it ships (EPM ships the log4j Java library as a jar file to be used by the applications like HFM, FCM etc.).

So its imperative to take mitigation steps to alleviate the impact associated with Log4j vulnerability for Oracle Enterprise Performance Management (EPM).

Currently we are in the process of upgrading our existing EPM 11.1.2.4 environment to EPM 11.2. So we though to know the impact of Log4j vulnerability/security-threat first on EPM 11.1.2.4 as we are sure that EPM 11.2.x is impacted by it.

What could be better option than checking with vendor Oracle itself about the impact and mitigation plan of Log4j vulnerability in EPM 11.1.2.4?

So those who still have EPM 11.1.2.4 up and running (as of 28th Dec 2021), you should know that:

EPM 11.1.2.4 is NOT AFFECTED by Log4j vulnerability/security-threat, as confirmed by Oracle support and shown below. EPM 11.1.2.4 uses log4j 1.x library which is not impacted by Log4j vulnerability (CVE-2021-44228 and CVE-2021-45046) reported for Apache Log4j version 2.x (i.e. Affected Versions are Apache Log4j versions 2.0--2.15.0).

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!!!

Share this post.

Read More

EPM: Batch Script to backup essbase.sec file on Oracle Essbase server

As part of your EPM backup strategy, its important to backup your Essbase security file (essbase.sec) on daily basis to handle unforeseen issues in your Oracle EPM Essbase server/application.

Below is one batch script that you can use to backup/copy the latest essbase.sec file from your source (Essbase server) and paste it into a destination (Essbase server itself or any network share) renaming it with today's date for easy identification during restoration.

@echo off

:: Format today's date in YYYYMMDD format
for /f "delims=" %%a in ('wmic OS Get localdatetime ^| find "."') do set "dt=%%a"
set "YYYY=%dt:~0,4%"
set "MM=%dt:~4,2%"
set "DD=%dt:~6,2%"
set "today_date=%YYYY%%MM%%DD%"

:: Source path
set sourcepath=E:\apps\oracle\epm\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin

:: Destination path
set destinationpath=E:\EPMBackup\Essbase_Backup\BKPFiles

:: Log path
set logfile=E:\EPMBackup\Essbase_Backup\Log\Essbase-Sec-File-Copy_%today_date%.log

:: Copy the latest essbase.sec file
for /f %%i in ('dir "%sourcepath%\essbase.sec" /b/a-d/od/t:c') do set NewestFile=%%i
echo %today_date% >>%logfile%
echo ---------------->>%logfile%
echo %NewestFile% >>%logfile%
copy "%sourcepath%\%NewestFile%" "%destinationpath%\%NewestFile%_%today_date%" >>%logfile%

Notes:

• I assumed that you have your Essbase component installed and configured on a Windows server.
• If your Essbase is installed on Linux/Unix server, you can create a bash/shell script accordingly based on this same logic.
• You can change source path, destination path and log file path as per your Essbase server and requirement.
• Schedule this script in Windows Task scheduler on Essbase server to run everyday at a fixed time.

On successful run, everyday following two files will be created i.e. essbase.sec backup file and a corresponding log file.

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!!!

Share this post.

Read More

Unable to add/connect or expand Essbase server in EAS (Essbase Administration Services) console

Issue:

When we try to add/connect a new Essbase server or expand an existing Essbase server in EAS (Essbase Administration Services) console to see applications list etc., it keeps loading/rolling for long time and eventually Essbase server doesn’t get added or expanded being hanged, as shown below:

Adding new Essbase server:

Expanding existing Essbase server:

Investigation:

We check the easserver.log for the timestamp and noticed following error:

Log file path: E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\EssbaseAdminServices0\logs\easserver.log

Error:

[2021-10-18T09:17:26.196-06:00] [EssbaseAdminServices0] [ERROR] [ESSEAS-24206] [oracle.epm.essbase.eas] [tid: 18] [userId: <anonymous>] [ecid: 00jBJTZ37vAB1FwDwFj8CW0002es0000Zt,0:1:4] [APP: EAS#11.1.2.0] Failed to handle request for com.essbase.eas.essbase.defs.ServerCommands.Connect. See below stack trace for more information. Possible cause for this exception is missing java archive for this request to handle. Check application server WEB-INF/lib folder or CLASSPATH, if the required java archive file is available.[[
com.essbase.eas.framework.server.defs.ApplicationException: java.net.SocketException: socket write error: Connection aborted by peer
.....
.....
Caused by: java.net.SocketException: socket write error: Connection aborted by peer

Solution that worked for us:

Note: We have Essbase component installed on Linux server and EAS service installed on Windows 20212 R2 server.

Error "socket write error: Connection aborted by peer" is usually caused by writing to a connection that had been aborted by the peer before getting the full response. It means that the other side aborted the connection. Since we are facing this error on client side (EAS console), then the Essbase server side must be the one aborting the connection. In most cases this can be caused either by the timeout issue (e.g. the response takes too much time or server is overloaded with the requests), or the client sent the SYN, but it didn't receive ACK (acknowledgment of the connection termination) from the other side.

We first checked with Network team to see if there is any network issue between the source (EAS server) and the target (Essbase server). But they confirmed it was all ok at Network end.

Then we decided to reboot our Essbase Linux server as the connection was getting aborted from that side.

Steps:

• Stop Essbase and EAS services.
• Reboot Essbase (Linux) server.
• Start Essbase service and then EAS service.
• Login to EAS console and try to add a new Essbase server or expand an existing Essbase server. It worked fine.

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!!!

Share this post:

Read More

Oracle Middleware Home, EPM Oracle Home and EPM Oracle Instance

In this post, we will see three important directories related to Oracle EPM installation and configuration:

1. Oracle Middleware Home
2. EPM Oracle Home
3. EPM Oracle Instance

Oracle Middleware Home

In an Oracle EPM system, Oracle Middleware home consists of Oracle WebLogic Server home and EPM Oracle home. You can setup your Oracle Middleware home either on a local file system on the server or on a remote network shared location. 

When you do EPM installation and configuration first-time, you need to define Oracle Middleware Home, which will be then used for all further EPM product installations on that system. 

Oracle Middleware Home installation directory is referred as MIDDLEWARE_HOME with default location as Oracle/Middleware on your EPM system.  For example it could be:

MIDDLEWARE_HOME : E:\apps\OracleEPM\Middleware

Below is an example of MIDDLEWARE_HOME directory structure:

EPM Oracle Home

EPM Oracle Home resides within Oracle Middleware Home and contains following things:

• Files/folders related to EPM applications/products installed on that EPM system
• Files/folders related to common internal components used by these EPM applications/products .

You cannot change the EPM Oracle Home location, so before starting the installation and configuration of EPM applications on a system make sure you have allocated sufficient disk space to your EPM Oracle Home drive to accommodate all the EPM applications/products that you are going to install on that system. 

The default EPM Oracle home location is MIDDLEWARE_HOME/EPMSystem11R1. EPM Oracle home location is defined in the system environment variable called EPM_ORACLE_HOME. For example it could be:

EPM_ORACLE_HOME : E:\apps\OracleEPM\Middleware\EPMSystem11R1

In a distributed EPM environment setup, the EPM Oracle home directory structure must be the same on each machine of that EPM environment. For example, if the path for EPM Oracle home is E:\apps\OracleEPM\Middleware\EPMSystem11R1 on the first machine you configure, it must be E:\apps\OracleEPM\Middleware\EPMSystem11R1 on all the other machines in the deployment.

Below is an example of EPM_ORACLE_HOME directory structure:

EPM Oracle Instance

EPM Oracle Instance contains one or more EPM components like Oracle HTTP Server (OHS), Oracle Essbase Server, one or more Java web applications in one or more domains. 

Unlike EPM Oracle Home, EPM Oracle instance can reside anywhere; it need not to be within the Oracle Middleware home directory.

The default location for the EPM Oracle instance referred to as EPM_ORACLE_INSTANCE is MIDDLEWARE_HOME/user_projects/epmsystem1. For example it could be:

EPM_ORACLE_INSTANCE : E:\apps\OracleEPM\Middleware\user_projects\epmsystem_web

Java web applications are deployed to MIDDLEWARE_HOME/user_projects/domains/domainName for example it could be: E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem.

For a distributed EPM environment, you need to create a new EPM Oracle instance on each machine of the environment. But if you are installing all EPM products on a single machine, then all EPM products are configured under a single EPM Oracle instance what you create for the first product configuration.

Below is an example of EPM_ORACLE_INSTANCE directory structure:

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!!!

Share this post:

Read More

How to change temporary (temp) directory in Oracle Essbase Server

Recently we got a critical alert where /tmp directory on our Essbase Linux server (Version: 11.1.2.4.033) was utilizing more than 90% of total allocated space. When we checked, there were many temporary Outline(.otl) files created under the /tmp directory. Most of them were of few KBs size but couple of files were really huge-sized of around 2gb each causing this space crunch alert under /tmp directory. Below is some of those huge sized .otl files listed:

-rw-r----- 1 epmadmin sapsys 2049736704 May 22 12:39 0oKEzW.otl
-rw-r----- 1 epmadmin sapsys 1370583040 May 22 07:43 MsnP8R.otl
-rw-r----- 1 epmadmin sapsys 2049994752 May 22 12:53 spQfrr.otl
-rw-r----- 1 epmadmin sapsys 1360232448 May 22 07:43 wpWopg.otl

Temp directory mostly contains files that are required temporarily. Many Essbase activities use this to create lock files and for temporary storage of data. When an Essbase outline is opened, it creates such temp files. Other activities like HPCM application deployment to Essbase and FDMEE data load to Essbase, Metadata load to Essbase etc. also can create these temporary files. 

Usually, Essbase doesn't produce such huge sized temporary files. Mostly you will see the size in KBs only for such temp files. These temporary files are normally deleted automatically when the related operation is over. But on some occasions you will observe they are not automatically deleted.

We noticed that when these huge sized temporary files (around 2gb per file) are created under /tmp directory on Essbase Linux server, it not only causes /tmp directory go out of space with 100% utilization but also the following issues encountered at the same time:

1. Essbase server gets hanged.
2. FDMEE data load to Essbase gets failed.
3. Unable to load Essbase applications in FDMEE 'Target Application' interface.

Apart from the above listed ones, there might be even more problems. All in all, these issues point towards 'Essbase server disconnection' at that particular point of time. 

So in order to fix it, as a temporary workaround, we moved all those huge-sized temporary .otl files from /tmp directory to some other location and it fixed all the issues. You can also delete these temp files, if not required.

In a Windows server, the temporary directory is set by the environment variable TEMP. In UNIX/LINUX server, the temporary directory is /tmp filesystem. As /tmp is a Linux/Unix OS Filesystem, it is not recommended to use OS file system for application usage because it may lead to even server crash if all /tmp space is consumed at any point of time. So the safe way out is to have a separate temp folder assigned for Essbase application usage other than OS /tmp directory.

Workarounds

As mentioned above, instant workaround is to move/delete these temporary files to clear up some space under /tmp directory.

But as a permanent fix, we decided to have a separate temp directory assigned to Essbase application, that we will discuss ahead.

Changing the Default Location for Essbase Temporary Files on Linux Platforms:

On Linux platforms, Essbase uses OS /tmp directory as the default location for creating temporary files.

To create temporary files in a different location, you must set the temp directory environment variable in opmn.xml and in setEnv.sh configuration files.

To set TEMP/TMP environment variable in opmn.xml

1- Find opmn.xml file on your Essbase server located at below path: /apps/oracle/epm/Middleware/user_projects/epmsystem_ess/config/OPMN/opmn/opmn.xml

2- In opmn.xml file, look for the following default entries:

<environment>
<variable id="TEMP" value="/tmp"/>
<variable id="TMP" value="/tmp"/>
</environment>

3- Change the default values of TEMP and TMP variables to your desired location as shown below and save the opmn.xml file :

<environment>
<variable id="TEMP" value="/apps/oracle/epm/Middleware/user_projects/epmsystem_ess/tmp"/>
<variable id="TMP" value="/apps/oracle/epm/Middleware/user_projects/epmsystem_ess/tmp"/>
</environment>

To set TMPDIR in setEnv.sh

1- Find setEnv.sh file on your Essbase server located at below path: /apps/oracle/epm/Middleware/user_projects/epmsystem_ess/bin/setEnv.sh

2- By default there will be no entry set for TEMPDIR in setEnv.sh (means your OS /tmp directory will be used to store temporary files). 

To change the default location, add the following entry at the end of setEnv.sh file and save it:

TEMPDIR="/apps/oracle/epm/Middleware/user_projects/epmsystem_ess/tmp"
export TEMPDIR

Note: You have to restart Essbase services to make these changes effective.

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!!!

Share this post:

Read More

Password Policies and Account Lockout Measure on EPM native 'ADMIN' and External Directory Users

As part of EPM Application audit and security control, most of us would have got this requirement to apply User account lockout measures on all EPM users whenever there is some specified number of consecutive failed login attempts.

Being no exception we too faced this question. Our security team had released following 'Account lockout' requirements to be implemented in EPM:

• Account lockout after 6 or less consecutive failed login attempts.
• Re-enable locked account after 30 minutes of lockout.

So we explored the available options in EPM that I have shared below.

We know there are mainly two types of users in EPM system:

1. Hyperion Shared Services Native users (Admin etc.)
2. External directory users (Microsoft Active Directory-MSAD, LDAP-enabled user directory, Oracle Internet Directory-OID etc.)

Lets see both one by one.

Hyperion Shared Services Native Directory users (Admin etc.)

There is no inbuilt account lockout and password policies setting for EPM Native Directory users including EPM Shared Services ‘Admin’ account. Therefore EPM Shared services 'Admin' account never gets locked after any number of unsuccessful login attempts (due to the obvious reason that if ADMIN gets locked in EPM then none can unlock it). 

On entering wrong password for EPM native ‘Admin’ account, it keeps throwing following error without being locked:

EPMCSS-00301: Failed to authenticate user. Invalid credentials. Enter valid credentials.

You can mitigate this risk for native 'Admin' user failed login attempts by creating a script or using any log analyzing tool to monitor Framework.log present on all Foundation servers (E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\FoundationServicesN\logs\Framework.log) which records the messages for both successful and failed login attempts made by EPM native 'Admin' user.

External directory users (Microsoft Active Directory-MSAD or an LDAP-enabled user directory such as Oracle Internet Directory-OID)

There is no settings within EPM application (till EPM 11.2 release) which can be used to control password policies and account lockout measures for external directory users. 

For external directory users (MSAD, LDAP, OID etc.), password policies and Account lockout measure on failed login attempts can be enforced and controlled at external directory side setup only. So you can check with your external AD team and define password policies for external AD users to specify how many logon attempts to allow before locking out end users and how they will be unlocked. 

EPM System honors all locks controlled by the password policies for the external user directory. Whatever Account lockout policy is set there by your Active Directory (AD) team, EPM system will simply follow that.

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!!!

Share this post:

Read More

EPM 11.1.2.4: EPMA is not compatible with Google Chrome and Microsoft Edge browsers

To get the context of the issue discussed in this blog, I would suggest you to first read following two posts:

EPM 11.1.2.4: Microsoft Edge and Google Chrome browsers support patching : Part-1
EPM 11.1.2.4: Microsoft Edge and Google Chrome browsers support patching : Part-2

In order to make EPM 11.1.2.4 environment compatible to run on Google Chrome and Microsoft Edge browsers we apply some patches mentioned in the following knowledge article: 

Browser Support Added for Google Chrome and Microsoft Edge with EPM System Releases Beginning with Release 11.1.2.4 - Doc ID 2675883.1

Post patching when we validated our EPM environments, all applications like Workspace, Essbase, HFM, HPCM, DRM, FDMEE are working fine. We haven't faced any showstopper issue/error in these applications as such.

But its important to note here that EPMA is not compatible with Google Chrome and Microsoft Edge browsers. It not like EPMA doesn't work at all on Google Chrome and Microsoft Edge browsers. You may face issue while using few of the EPMA functionalities.

One such issue is when we try to create Import Profile in EPMA Dimension Library (EPMA dimension library--File--Import--Create profile--New Profile--File Properties), there is a blank screen coming in place of "File Properties" page in Chrome and Edge browsers. While the same works fine in Internet Explorer browser. Please notice following screenshots depicting the same:

In Google Chrome and Microsoft Edge browsers (Blank Screen):



In Internet Explorer browser (Working):



So you can recommend your end users to use IE (Internet Explorer) only for EPMA related work. For other applications, Google Chrome and Microsoft Edge work all fine.

That's all for this post. Please let me know in comment box how the latest browsers support patching goes for you.

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!!!

Share this post:

Read More

EPM 11.2 : 5 Ways To Find Oracle WebLogic Server Version in EPM 11.2

In this post, we will explore 5 ways to find Oracle WebLogic Server version in EPM (Enterprise Performance Management) 11.2 environment. Knowing your WebLogic Server version comes in handy especially when you are planning to apply latest Oracle patches to make WebLogic servers security-compliant in your EPM 11.2 setup. 

EPM (Enterprise Performance Management) 11.2 by default comes with Oracle WebLogic Server 12c (12.2.1.3). Since in Oracle WebLogic Server 12c, the installation directory structure has changed, the usage of registry.xml / java weblogic.Version is a bit different than 11g release. 

Method-1: Oracle WebLogic Server Administration Console

1-Login to your WebLogic Admin console.

2-In the left hand panel, under 'Domain Structure' menu, navigate to Environment—Servers and click on ‘AdminServer’ from the server list. Now click on the ‘Monitoring’ tab and you will see below screen showing your WebLogic version (WebLogic Server 12.2.1.3.0 as shown below):

Method-2: AdminServer.log

1-On WebLogic Admin server, goto the path: E:\Oracle\Middleware\user_projects\domains\EPMSystem\servers\AdminServer\logs

2-Open AdminServer.log file and search for "Version: WebLogic Server".

3-You will get output like below which will tell you your WebLogic server version (WebLogic Server 12.2.1.3.0 as shown below):

Method-3: registry.xml file

1-On WebLogic Admin server, goto the path E:\Oracle\Middleware\inventory.

2-Open registry.xml file and search for “WebLogic Server”.

3-You will get output like below which will tell you your WebLogic server version (WebLogic Server 12.2.1.3.0 as shown below):

Method-4: java weblogic.version

1-On WebLogic Admin server, open command prompt as an Administrator and goto the following path:

cd E:\Oracle\Middleware\wlserver\server\bin

2-Now run below command to first set environment variables for WebLogic server and then display your WebLogic Server version (WebLogic Server 12.2.1.3.0 as shown below): 

setWLSEnv.cmd
java weblogic.version | findstr "WebLogic Server" 

3-For Linux server, command will be like below:

setWLSEnv.sh 

java weblogic.version | grep "WebLogic Server"

Method-5: viewInventory.cmd

1-On WebLogic Admin server, open command prompt as an Administrator and goto the following path:

cd E:\Oracle\Middleware\oui\bin

2-Now run below command to see your WebLogic Server version (WebLogic Server 12.2.1.3.0 as shown below):

viewInventory.cmd | findstr "Distribution:"

3-For Linux server, command will be like below:

viewInventory.sh | grep "Distribution:"

All the 5 methods are equally applicable to Linux servers as well (with the corresponding change in command/script), in order to find Oracle WebLogic server version.

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!!!

Share this post:

Read More

EPM 11.2: How to check FDMEE application patch version

Its important to know the current patch version of your EPM (Enterprise Performance Management) products/applications, especially when you are planning to apply any new Oracle patches or want to check the compatibility of existing EPM applications with any other products/software/tools.

For FDMEE (Financial Data Quality Management, Enterprise Edition), you can check its current patch version using following methods:

1. AIF_version.xml (more reliable method):

This is the most reliable method because when you apply any new patch to FDMEE, this is the file which will be updated with the new patch level so it always shows the current version of your FDMEE application.

On FDMEE server, you can find AIF_version.xml file in the following folder:

E:\apps\OracleEPM\Middleware\EPMSystem11R1\products\FinancialDataQuality\xml\AIF_version.xml

When you open this AIF_version.xml file, FDMEE version will be displayed as shown below:

2. Workspace (Not always the true reflection of the current patch level of EPM products)

To see your EPM products versions in Workspace, navigate to: 

Workspace-->Help-->About Oracle Enterprise Performance Management System Workspace, Fusion Edition

You would have seen on many occasions that the EPM products version number shown in Hyperion Workspace do not match the patch versions that have been recently applied in an EPM environment.

It happens because EPM java web applications versions shown in Workspace are sourced from Shared Services registry (logical web application entry--displayVersion property).

Further the versions displayed in Shared Services registry are sourced from some property/configuration files of the respective EPM products which get updated immediately after applying any new patches. For example, in case of FDMEE, its is AIF_version.xml file as mentioned in method-1.

Sometimes, applications version shown in Workspace or the displayed version shown in Shared Services registry does not get refreshed from the respective source file of that application. And that's when you see a mismatch in version shown in Workspace and the actual application version. And that's why method-1 is more reliable one to know the FDMEE current patch version.

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!!!

Read More

Oracle DRM (Data Relationship Management) Application Logs

To access Oracle DRM (Data Relationship Management) application related activities log, there are two methods:

1. Auditing Transactions, Jobs, and Requests through DRM Web Client
2. Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Auditing Transactions, Jobs, and Requests through DRM (Data Relationship Management) Web Client

Oracle Data Relationship Management records a history of different activities performed within an application for audit purposes. 

• The transaction history logs all operations performed including changes to version data, application metadata, and user security. 
• The job history tracks the completion of long running processes such as imports, blenders, and exports. 
• The request history maintains a record of change requests submitted from external applications.
  

For further details you can read DRM (Oracle Data Relationship Management) Suite User's Guide.

You can query and view transactions, requests, and job history using the Audit task wizard in DRM Web Client navigating through Home --> Audit as shown below:

The audit wizard is used to define the parameters and criteria for the query. The wizard content is organized across Source, Filters, and Columns tabs to guide input. Although the wizard tabs are ordered to gather input in a sequence, you can navigate between any tabs.

Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Extracting DRM Transaction History:

In DRM application database schema, there is a table name RM_TRANSACTION_HISTORY that contains a row for each transactional update made to your DRM repository.

Below is all the columns listed what RM_TRANSACTION_HISTORY table consists of: 

Name                                       Null                     Type            

-------------------------- -------- --------                    ------- 

I_TRANSACTION_ID           NOT NULL         NUMBER(38)      

I_DETAIL_ID                         NOT NULL         NUMBER(38)      

D_TIMESTAMP                                                 TIMESTAMP(6)    

I_SESSION_ID                                                   NUMBER(38)      

C_USER_NAME                    NOT NULL        NVARCHAR2(100)  

C_ACTION                                                         NVARCHAR2(40)   

C_ACTION_LEVEL                                          NVARCHAR2(40)   

C_VERSION_ABBREV                                    NVARCHAR2(255)  

I_VERSION_ID                                                  NUMBER(38)      

C_HIERARCHY_ABBREV                              NVARCHAR2(255)  

I_HIERARCHY_ID                                           NUMBER(38)      

C_NODE_ABBREV                                           NVARCHAR2(255)  

I_NODE_ID                                                       NUMBER(38)      

C_NODE_DESCR                                             NVARCHAR2(255)  

C_PROPERTY_ABBREV                                  NVARCHAR2(255)  

I_PROPERTY_ID                                               NUMBER(38)      

B_LEAF                                     NOT NULL     NUMBER(38)      

C_ACTION_DESCRIPTION                             NVARCHAR2(1024) 

X_FROM_VALUE                                                NCLOB           

C_FROM_ORIGIN                                               NVARCHAR2(1024) 

X_TO_VALUE                                                      NCLOB           

C_TO_ORIGIN                                                     NVARCHAR2(1024) 

I_ORIGINATING_ID                                             NUMBER(38)      

I_ORIGINATING_DETAIL_ID                             NUMBER(38)      

C_OBJECT_NAME                                                NVARCHAR2(255)  

I_DOMAIN_ID                                                       NUMBER(38)      

C_DOMAIN_ABBREV                                          NVARCHAR2(255)  

I_WORKFLOW_REQUEST_ID                              NUMBER(38)      

I_WORKFLOW_REQUEST_ITEM_ID                  NUMBER(38)      

C_NODE_TYPE                                                       NVARCHAR2(255)  

As we see, RM_TRANSACTION_HISTORY columns include: Action (e.g. Add Node, Insert Node, Delete Node, and ChangeProp), Node, Hierarchy, Version and Property Name values as well as Node Description, Leaf flag, to and from Property values for ChangeProp operations.

You can query this RM_TRANSACTION_HISTORY table to extract DRM application related logs for a particular time period.

For example, if you want to extract DRM Application log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Transaction_History where D_TIMESTAMP between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIMESTAMP;

Output:

Note: Its preferable to use SQL Developer to extract DRM application logs from backend schema. Make sure in SQL Developer, you have set the date and time format same as mentioned in above query.

Extracting DRM Job History:

DRM Job history is stored in a table name RM_JOB_HISTORY that consists of following columns:

You can query this table RM_JOB_HISTORY to extract all job run details.

For example, if you want to extract DRM Job History log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Job_History where D_TIME_STARTED between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIME_STARTED;

Output:

Extracting DRM Request History:

DRM Request history is stored in a table name RM_REQUEST that consists of following columns:

You can query this table RM_REQUEST to extract all change requests submitted from external applications.

select * from RM_REQUEST;

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!!!

Read More

EPM 11.1.2.4: Microsoft Edge and Google Chrome browsers support patching : Part-2

You may like to read Part-1 first to understand the background of Microsoft Edge and Google Chrome browsers support patching in Hyperion/EPM 11.1.2.4 setup. 

In this post, I will tell how it worked for us. We have recently applied these browsers (Microsoft Edge and Google Chrome) compatibility patches in our EPM/Hyperion 11.1.2.4 environment. 

As discussed in last post, below is what we need to apply in order to make your EPM/Hyperion 11.1.2.4 environment working on Microsoft Edge and Google Chrome browsers:

Important Notes:

• Above list provides the minimum patch version for each Hyperion application you need to install, If you want, you can go for higher version too. 
• I assume your EPM/Hyperion 11.1.2.4 environment is up-to-date in terms of patches and you have all the prerequisite patches already applied.
• Its recommended to have EPM/Hyperion application schemas backup and if possible, backup of the existing installation/configuration directory to handle unforeseen critical issues.
• You should apply these patches in below given order. 
• I have all these Hyperion applications installed and configured on Windows server 2012 R2, therefore below given steps are applicable for Windows machines. For other operating systems, you can refer 'README' document of the respective patch number.
• Stop all the Hyperion services and WebLogic Admin server of your environment before proceeding further.

Lets start the patching now. 

Hyperion Workspace 11.1.2.4.825 (Patch 31124100)

----------------------------------------------------------------------

This patch needs to be applied on all your EPM Foundation/Workspace servers of the Hyperion environment. 

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch 

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\31124100 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

Hyperion Shared Services 11.1.2.4.100 (Patch 31319089)

-------------------------------------------------------------------------

This patch needs to be applied on all your EPM Foundation/Workspace servers of the Hyperion environment. 

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch 

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\31319089 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

Oracle JDeveloper / Application Development Framework 11.1.1.7.1 (Patch 31246831)

-----------------------------------------------------------------------------------------------------------------

This patch needs to be applied on all your EPM Foundation/Workspace servers of the Hyperion environment. 

After downloading the patch zip file, extract it to C:\Patch_Top. There should now be a 31246831 directory in the C:\Patch_Top directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\oracle_common\OPatch\ 

opatch.bat apply C:\Patch_Top\31246831\oui -oh E:\apps\OracleEPM\Middleware\oracle_common -jre E:\apps\OracleEPM\Middleware\jdk160_35

Hyperion Planning, Hyperion Capital Asset Planning, Hyperion Workforce Planning, Hyperion Project Financial Planning 11.1.2.4.009+ (Patch 29889455)

---------------------------------------------------------------------------

This patch needs to be applied on all your Hyperion Planning application servers and Web servers of the environment. 

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch 

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\29889455 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

Delete the tmp directory for the Planning server in: 

<MIDDLEWARE_HOME>/user_projects/domains/EPMSystem/servers/<server> 

where <server> is the Planning server (for example, Planning0).

The full path for tmp is: <MIDDLEWARE_HOME>/user_projects/domains/EPMSystem/servers/<server>/tmp.

Hyperion Calculation Manager 11.1.2.4.014  (Patch 28557058)

-------------------------------------------------------------------------------

This patch needs to be applied on all your Calculation Manager servers and Web servers of the Hyperion environment. 

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch 

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\28557058 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

On the server where Oracle Hyperion Calculation Manager is installed, delete the cached files in the following directories:

<Middleware_Home>/user_projects/domains/<DOMAIN_NAME>/servers/<CalcMgr0>/tmp 

<Middleware_Home>/user_projects/domains/<DOMAIN_NAME>/servers/<CalcMgr0>/cache

Hyperion Financial Reporting 11.1.2.4.712  (Patch 30670918)

----------------------------------------------------------------------------------

This patch needs to be applied on all your Financial Reporting servers, Web servers and also on HFM application server (if you use HFR for reporting) of the Hyperion environment. 

To apply this patch first Uninstall the Financial Reporting Studio by going to Control Panel, Add or Remove Programs, and then uninstall Financial Reporting Studio. 

On the Financial Reporting server machines, download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\30670918 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

Delete the Financial Reporting precompiled jsp files located at:

<ORACLE_HOME>\Middleware\user_projects\domains\EPMSystem\precompiled\hr

Use the EPM System configurator to redeploy the Financial Reporting Web Application.  If RA Framework, OHS, or IIS are installed on a separate host, it must be patched also.

In order to run the new Oracle Hyperion Financial Reporting Web Studio, you must enable proxying. I am sure it would have already been enabled in your environment too. If not, kindly refer 'README' document of the patch.

At the end, when you start your EPM services, remember to connect to the EPM Workspace machine on patched Financial Reporting server machine. Download the Financial Reporting Studio installer from the EPM Workspace menu: From the Tools menu, select Install and then select Financial Reporting Studio and then launch the installer. Follow the installation guide to install the Financial Reporting Studio to the location you want.

Hyperion Financial Management PSU 11.1.2.4.209 (Patch 29343616)

----------------------------------------------------------------------------------------

This patch needs to be applied on all your Hyperion Financial Management application servers, HFM Web servers and also on HFR application server (if you use HFR for reporting) of the Hyperion environment.  

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\29343616 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

If you are applying this patch to version 11.1.2.4.208 or an earlier version, also execute the following sql command to upgrade the HFM schema against the Financial Management database. Use appropriate sql as per your database type.

<EPM_ORACLE_HOME>\products\FinancialManagement\Server\conf\<DB Type>_from_11.1.2.4.208_to_11.1.2.4.209_SchemaUpgrade.sql

E:\apps\OracleEPM\Middleware\EPMSystem11R1\products\FinancialManagement\Server\conf\Oracle_from_11.1.2.4.208_to_11.1.2.4.209_SchemaUpgrade.sql

In addition, you must run below by login to HFM schema:

<EPM_ORACLE_HOME>\products\FinancialManagement\Server\conf\Oracle_Create_ImportApp.sql

<EPM_ORACLE_HOME>\products\FinancialManagement\Server\conf\Oracle_Create_InsightDetails.sql

This step is required for 'Importing Application' feature. 

If you are using the extract data to database functionality with Oracle database as the target database, you will need to reconfigure DSN. To reconfigure:

1. Select Navigate, then Administer, and then Consolidation Administration.
2. From Admin Tasks, click Configure DSN. 
3. Select any data source that has Oracle database as database type, and then click on Edit.
4. Enter the database connection information, including the database server network address as Host.
5. Click Test Connection. If successful, click on Save.

Hyperion Financial Close Management and Hyperion Tax Governance 11.1.2.4.253+ (Patch 29060830)

Hyperion Tax Provision 11.1.2.4.202+ (Patch 25316913)

---------------------------------------------------------------------------

We don't use these two components in our EPM setup. You can follow README document to apply these patches, if there components are there in your EPM/Hyperion environment.

Hyperion Data Relationship Management 11.1.2.4.350  (Patch 31420887)

---------------------------------------------------------------------------------------------

This patch needs to be applied on all your DRM application server and DRM Web services servers of the Hyperion environment.  

Make sure you have stopped the DRM services. 

Take a backup of DRM schema (Must!) before applying the DRM patch.

Save a backup copy of the drm-config.xml file found under directory

E:\apps\OracleEPM\Middleware\EPMSystem11R1\products\DataRelationshipManagement\server\config

Uninstall the existing 11.1.2.4 release:

a.    Stop the Data Relationship Management service.

b.    Verify all processes are shut down.

c.    Run the installer from that release and choose the Remove option.

Run the new setup.exe to install this patch release.

If a multiple-machine configuration is being used, repeat the uninstall and install steps above for each machine.

When using an Oracle repository and upgrading from a Data Relationship Management release prior to 11.1.2.4.330, run the following statement for the current schema owner for each application to be updated: GRANT CREATE TYPE, CREATE SEQUENCE TO <user>;

In the Configuration Console on the application controller machine, select Application, then 'Apply Updates' for all existing applications.

Make sure to save any configuration changes made in the Data Relationship Management Console. Click on save configuration to do so. 

Redeploy the Data Relationship Management Web service application (oracle-epm-drm-webservices.ear) to Oracle Weblogic. The oracle-epm-drm-webservices.ear file is located in the %EPM_ORACLE_HOME%\products\DataRelationshipManagement\api directory. This step is only required if applying this patch to an installation where the Web service application has been previously deployed. See the “Redeploying a Web Services Application” section in the Oracle® Fusion Middleware Security and Administrator's Guide for Web Services for instructions to redeploy using Oracle Enterprise Manager. 

FDMEE (Hyperion Financial Data Management) Patch Set Update 11.1.2.4.220 (Patch 25312033)

----------------------------------------------------------------------------------------------------------------------

This patch needs to be applied on all your Hyperion Financial Data Management application servers (FDMEE) of the Hyperion environment.  

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\25312033 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jre E:\apps\OracleEPM\Middleware\jdk160_35

Additional Installation Instructions:

1.    Copy the following script files.

For Windows, copy from:

EPM_ORACLE_HOME/FinancialDataQuality/bin/executescript.bat.template 

to:

EPM_INSTANCE_HOME/FinancialDataQuality/executescript.bat

2.    If your deployment is a distributed environment where Hyperion Financial Management and Planning are on different servers than FDMEE, copy the following jar files:

Copy files from the Hyperion Financial Management server folder EPM_ORACLE_HOME\common\hfm\11.1.2.0\lib to the same directory on the FDMEE server.

Copy files from the Planning server on the EPM_ORACLE_HOME\common\planning\11.1.2.0\lib to same directory on the FDMEE server.

3. Now, on the servers where FDMEE is installed, you must delete tmp and cache files in the following directories:

<MIDDLEWARE_HOME>/user_projects/domains/<DOMAIN_NAME>/servers/<MANAGED_SERVER_NAME/tmp/

<MIDDLEWARE_HOME>/user_projects/domains/<DOMAIN_NAME>/servers/<MANAGED_SERVER_NAME/cache

Hyperion Profitability and Cost Management (HPCM) 11.1.2.4.131 (Patch 31159372)

-----------------------------------------------------

This patch needs to be applied on all your Hyperion Profitability and Cost Management (HPCM) of the Hyperion environment. 

Perform a full system backup, including EPM Architect and the Profitability and Cost Management databases. If you have Management Ledger applications, you should back them up using Life Cycle Management (LCM) in Shared Services. 

This patch must automatically re-register your Profitability and Cost Management applications, which will delete native non-Admin user provisioning.  Use Life cycle Management (LCM) in the Shared Services console to export the Native Directory under Foundation, usually located at Foundation => Shared Services => Native Directory, so you can restore it later.  

 1)  From the Shared Services console, open the Application Groups folder.

 2)  Open the Foundation sub-folder and select Shared Services.  

 3)  Export the Native Directory artifact and all content under it.  

You will need the resulting LCM export file later to restore the user provisioning.

Download the patch and place it under apps\OracleEPM\Middleware\EPMSystem11R1\OPatch directory.

Open command prompt and apply the patch using below commands:

cd E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch

opatch.bat apply E:\apps\OracleEPM\Middleware\EPMSystem11R1\OPatch\31159372 -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35

Clear the WebLogic cache under

E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\Profitability0

When you will start the Profitability service (Hyperion Profitability - Java Web Application) it automatically performs any required database updates after applying a new patch - there is no need to run a manual SQL script.

Invoke Profitability and Cost Management, open an application, and check in the System Information tab under Model Summary to verify that the Schema Version is 11124.153. If instead you get a Schema Version Mismatch error, report this to Oracle Support.  You can also search the hpcm.log file for SQL errors following the first recent occurrence of the string '11124.153'. If you find an error that you can resolve, the database upgrade can be performed manually after resolving the error, to complete the installation (see the Performing Manual Database Updates section below).

From the Shared Services console, import the LCM file you created earlier, to restore the native user provisioning.

Note also that any changes made previously to the hpm_ws_client.properties file to customize either of the web services sample programs will be overwritten by the patch and must be reapplied.

Optional – Performing Manual Database Updates (only when there are errors in the automatic update):

--------------------------------------------------------------------------------------------------------

If errors occurred during the automatic database updates performed during service startup, and you have identified and corrected the problem, you can follow these instructions to complete the required database updates manually.

1.    Shut down the Profitability service (Hyperion Profitability – Web Application).

Run the appropriate database update patch script for your database type.  Run the script from the Profitability and Cost Management product database/schema/user.  Capture the SQL script log output and save it.  If there are any errors, report them to Oracle Support.

Database Type: MS SQL Server 

Database Patch Files: Run the following database update script using Microsoft SQL Server Management Studio against the Profitability and Cost Management product database: <EPM_ORACLE_HOME>\products\Profitability\database\Common\MSSQLServer\create_11.1.2.4.131.sql

Database Type: Oracle

Database Patch Files: Run the following database update script in the Profitability and Cost Management product schema/user using Oracle SQL Developer:<EPM_ORACLE_HOME>\products\Profitability\database\Common\Oracle\create_11.1.2.4.131.sql

2.    Start the Profitability service (Hyperion Profitability - Java Web Application).

3.    Invoke Profitability and Cost Management, open an application, and check in the System Information tab under Model Summary to verify that the Schema Version is 11124.142.  

4.    For each Detailed application, you must re-deploy the Reporting Tables and Views.

5.    Complete any remaining steps you have not performed in the Applying this Patch section above.

Once all the patching steps are completed, do the following:

--------------------------------------------------------------------------------------------

1.    Stop all EPM services.

2.    For each of the following folders, create a backup copy of the folder, then delete all the contained files within the original folders:

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices$\cache

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices$\tmp

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\RaFramework$\cache

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\RaFramework$\tmp

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\precompiled\raframework

apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\precompiled\workspace

[NOTE: The value of the above strings will actually be a digit in your environment (e.g., FoundationServices0). Depending on the deployment, the number could be 0, 1, 2, etc.]

3.    You need to clear browser's cache and temporary files on all the client/users machines.

4.    Start all EPM services and WebLogic Admin server of your EPM environment.

5.    Validate the Hyperion applications on Microsoft Edge and Google Chrome 80+ browsers.

Post patching, all EPM applications like Workspace, Essbase, HFM, HPCM, DRM, FDMEE are working fine in our environments (DEV/TEST/PROD). We haven't faced any showstopper issue/error in these applications as such. 

There is one issue observed in EPMA that you can read about in below article:

EPM 11.1.2.4: EPMA is not compatible with Google Chrome and Microsoft Edge browsers

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!!!

Read More