HFM Housekeeping in Oracle Hyperion (EPM) 11.1.2.4: PART-1

Hello Friends!

There are many applications where a lot of data transactions, movement, retrieval, refresh, update, etc. activities happen as a result of day-to-day business activities and thus it creates many log files, temporary files, audit files, database records, etc. If not house kept on time, these files can cause severe performance issues in that application. 

Oracle Hyperion Financial Management (HFM) is one of those apps where we need to regularly perform housekeeping in order to improve application performance, especially in the Production environment. 

In this blog series, we will explore in detail what are the various things, which need to be regularly house kept in Oracle Hyperion Financial Management (HFM) and how to do that.

NOTE: 

• This post has been written and associated activities have been demonstrated on Oracle Hyperion Financial Management (HFM) version 11.1.2.4.204.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 

The complete Hyperion Financial Management (HFM) Housekeeping activity will be covered in two parts.

In this first part, we will cover prerequisites, stopping Hyperion Financial Management (HFM) services and processes, killing Hyperion Financial Management (HFM) database sessions, Hyperion Financial Management (HFM) schema tables housekeeping, etc. You can find the second part of Hyperion Financial Management (HFM) Housekeeping blog series on below link:

HFM Housekeeping in Oracle Hyperion 11.1.2.4: PART-2

Prerequisites:

• As Hyperion Financial Management (HFM) Housekeeping activity requires complete HFM outage and involves some critical tasks in the database, it’s recommended to plan this activity over the weekend if you are doing in Production. Business users should be informed accordingly. 
• As housekeeping of Hyperion Financial Management (HFM) audit tables is also involved, you need to have login credentials of HFM relational database schema. Needless to say, your HFM schema will be having all the required privileges as recommended in the Oracle EPM guide. 
• It’s recommended to have a database user with DBA level privileges in order to check active, Inactive, Killed sessions on your Hyperion Financial Management (HFM) schema. If you can’t own such a user, you can ask your application DBA to do perform such activity which will be described later in this post. 
• Different paths and folders' names mentioned in this blog may slightly vary for different Oracle Hyperion setups but at large it should be the same.  
• Please make sure you take Hyperion Financial Management (HFM) database schema backup before attempting HFM housekeeping steps mentioned below.
• Never forget to take complete backup of EVERYTHING before deleting or changing anything. 

Step-by-step process to do Oracle Hyperion Financial Management (HFM) Housekeeping:

PART-A: Stopping HFM services and killing HFM processes

1- Stop following two HFM services in all your HFM application servers:

1. Oracle Hyperion Financial Management – Java Server
2. Oracle Hyperion Financial Management - Web Tier 

Note: Open task manager and make sure the following two processes are no more running:

1. HyS9FinancialManagementJavaServer.exe
2. HyS9FinancialManagementWeb.exe

2- Open Windows Task Manager and kill all XFMDataSource.exe processes running in all your Oracle HFM application servers. For each HFM application, there will be one XFMDataSource.exe process.

Note: When an HFM application is started the Java Server starts an application process with the name XFMDataSource.exe. 

PART-B: Killing any running “Active, Inactive, Killed” database sessions under HFM schema

1- Using SQL developer, login to your environment’s database with a user having DBA level privileges (user should have access on GV$SESSION view) and run the following query to list out any Active, Inactive or Killed sessions running under HFM schema:

select sid, serial#, inst_id, status from gv$session where username = 'HFM' and status in ('INACTIVE','KILLED','ACTIVE');

2- Now using below query, kill all those Active, Inactive or Killed sessions listed above. You can take the help of your DBA if the user you own, does not have sufficient privileges to do this deletion task.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Where sid and serial# are session ID and serial number of your Active, Inactive or Killed sessions listed above.

For example:

If your Oracle Hyperion environment has a RAC database setup, you can specify the INST_ID in your kill command. This way you will be able to kill the session on the respective RAC node.

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;

For example:

Note: The KILL SESSION command doesn't actually kill the session. It only asks the session to kill itself. In some cases, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete first. In such cases, the session will have a status of "marked for kill". It will then be killed as soon as possible.

Actually, adding the IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time, you should kill the process at the operating system level. Ask your DBA to kill that session at the OS level.

3- Once listed sessions are killed, re-run below query to ensure all sessions are gone:

select sid, serial#, inst_id, status from gv$session where username = 'HFM' and status in ('INACTIVE','KILLED','ACTIVE');

PART-C: HFM schema tables housekeeping

Here we will first archive following three Hyperion Financial Management (HFM) tables and then truncate table entries keeping only past few months data in live tables:

1. <appname>_DATA_AUDIT
2. <appname>_TASK_AUDIT 
3. HFM_ERRORLOG

The general recommendation to maintain good application performance is to archive and delete the content of these tables in the HFM repository database before it reaches 500,000 records.

If the Data Audit feature is not part of your business requirements then it is recommended to turn off auditing of data for Hyperion Financial Management (HFM) applications. There is degradation in performance observed for HFM applications with the Data Audit table having more than 10GB entries.

There is no built-in mechanism in Hyperion Financial Management (HFM) to monitor the size of these tables, so the Hyperion administrator should be tasked with it to do the regular maintenance of these tables.

For the above tables, it is recommended to implement the following housekeeping best practices:

• Quarterly - Business to review the Audit logs, archive and delete.
• Half-Yearly - Archive System Messages and truncate table.

1- Archiving Oracle Hyperion Financial Management (HFM) apps Audit tables

To perform this activity, log in to HFM schema using SQL Developer. Suppose you have the following two HFM applications in your Hyperion environment:

1. HFMAPP1
2. HFMAPP2

So to archive/backup the audit tables of these two HFM apps, run following queries:

Archive HFMAPP1_task_audit table with today's date (ddmmyyyy):

Create table HFMAPP1_task_audit_21032020 as select * from HFMAPP1_task_audit;

Archive HFMAPP1_data_audit table with today's date (ddmmyyyy):

Create table HFMAPP1_data_audit_21032020 as select * from HFMAPP1_data_audit;    

Archive HFMAPP2_task_audit table with today's date (ddmmyyyy):

  Create table HFMAPP2_task_audit_21032020 as select * from HFMAPP2_task_audit;  

Archive HFMAPP2_data_audit table with today's date (ddmmyyyy):

Create table HFMAPP2_data_audit_21032020 as select * from HFMAPP2_data_audit;  

2- Deleting Oracle Hyperion Financial Management (HFM) Audit tables entries keeping last 90 days entries (you can decide the no. of days data to be retained for by checking with your business/team):

Delete HFMAPP1_task_audit table's all entries leaving past 90 days entries in live table: 

Run below query to see how many records are going to be deleted:
select count(*)from HFMAPP1_task_audit where starttime < (select max(starttime)-90 from HFMAPP1_task_audit);     

Run below query to delete the records:                

delete from HFMAPP1_task_audit where starttime < (select max(starttime)-90 from HFMAPP1_task_audit);

Run below query again to see whether all the records have been deleted or not:

select count(*)from HFMAPP1_task_audit where starttime < (select max(starttime)-90 from HFMAPP1_task_audit);

Run below command to end your current transaction and make permanent all changes performed in the transaction:

commit;

Delete HFMAPP1_data_audit table's all entries leaving past 90 days entries in live table: 

Run below query to see how many records are going to be deleted:
select count(*)from HFMAPP1_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP1_data_audit);

Run below query to delete the records:

delete from HFMAPP1_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP1_data_audit);

Run below query again to see whether all the records have been deleted or not:

select count(*)from HFMAPP1_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP1_data_audit);

Run below command to end your current transaction and make permanent all changes performed in the transaction:

commit;

Delete HFMAPP2_task_audit table's all entries leaving past 90 days entries in live table: 

Run below query to see how many records are going to be deleted:
select count(*)from HFMAPP2_task_audit where starttime < (select max(starttime)-90 from HFMAPP2_task_audit);  

                   

Run below query to delete the records:
delete from HFMAPP2_task_audit where starttime < (select max(starttime)-90 from HFMAPP2_task_audit);

Run below query again to see whether all the records have been deleted or not:

select count(*)from HFMAPP2_task_audit where starttime < (select max(starttime)-90 from HFMAPP2_task_audit);

Run below command to end your current transaction and make permanent all changes performed in the transaction:
commit;

Delete HFMAPP2_data_audit table's all entries leaving past 90 days entries in live table: 

Run below query to see how many records are going to be deleted:
select count(*)from HFMAPP2_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP2_data_audit);

Run below query to delete the records:

delete from HFMAPP2_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP2_data_audit);

Run below query again to see whether all the records have been deleted or not:

select count(*)from HFMAPP2_data_audit where dtimestamp < (select max(dtimestamp)-90 from HFMAPP2_data_audit);

Run below command to end your current transaction and make permanent all changes performed in the transaction:

commit;

3- Deleting HFM ERRORLOG table entries keeping last 30 days entries/data (you can decide the no. of days data to be maintained for checking with your business/team):

Run below query to see how many records are going to be deleted:

select count(*)from HFM_ERRORLOG where dtimestamp < (select max(dtimestamp)-30 from HFM_ERRORLOG);  

                                 

Run below query to delete the records:
delete from HFM_ERRORLOG where dtimestamp < (select max(dtimestamp)-30 from HFM_ERRORLOG);

Run below query again to see whether all the records have been deleted or not:

select count(*)from HFM_ERRORLOG where dtimestamp < (select max(dtimestamp)-30 from HFM_ERRORLOG);  

Run below command to end your current transaction and make permanent all changes performed in the transaction:

commit;

4- Purging database Recyclebin:

purge recyclebin;

The 'Purge recyclebin' command removes the items and their objects from the database recyclebin and restores the used storage space. The purge command is used to remove the items which have no use in the future. The main purpose here is to reclaim space used by deleted objects laying in recyclebin.

Note: Keep an eye on the space utilization on the database server and Hyperion Financial Management (HFM) Schema.

That's all for this PART-1.

In the last PART-2 of this blog series, we will see Hyperion Financial Management (HFM) logs archiving, deleting HFM temp and cache files, etc.

I hope this article has helped you.
Your suggestions/feedback are most welcome.
Keep learning and Have a great day!!!

Read More

Oracle WebLogic Server patching in Oracle Hyperion 11.1.2.4

Hi Friends!

As part of Oracle recommendations and organization's IT security requirements, we need to keep patch levels of various Oracle Hyperion components like Hyperion Essbase, Hyperion Planning, Hyperion Financial Management (HFM), Hyperion Financial Data Quality Management (FDMEE) etc. and also Oracle WebLogic server up to date.

In this post, we will see a detailed description of how to patch (Patch Set Update 10.3.6.0.190716) Oracle WebLogic Server in EPM (Hyperion) 11.1.2.4 for both Windows and Linux platforms.

We know that Oracle WebLogic Server 10.3.6.0 is the default version which comes with Oracle Hyperion 11.1.2.4 package. In this article, we will see patching Oracle WebLogic Server 10.3.6.0 of Oracle Hyperion 11.1.2.4 environment with Patch Set Update 10.3.6.0.190716 (Patch 29633432).

Before proceeding further, below are some important points to note: 

1. We need to patch all Oracle WebLogic servers (Admin server and domain servers) of a particular Oracle Hyperion environment. 
2. If previous Oracle WebLogic PSUs were applied before, the PSUs need to be rolled back first. (In our case it is not required as we are patching the Oracle WebLogic Server base version 10.3.6.0).
3. You need to perform this patching activity in each and every server of your Oracle Hyperion environment.
4. This article is applicable for both Windows and Linux servers i.e. same steps will apply on both.

 Prerequisites:

1- Download Oracle WebLogic Server Patch Set Update 10.3.6.0.190716 from Oracle Support portal link given below (This patch is for Generic platform so the same patch is applicable for both Windows and Linux operating systems): 

https://support.oracle.com/rs?type=patch&id=29633432 

(Note: You need to have Oracle support login credentials in order to access the above page.)

You will see following patch download page after login on the above link:

2-The downloaded file name is p29633432_1036_Generic.zip. It is advisable to have this zip file placed in a common network path accessible from all the servers of your Oracle Hyperion environment for the easy movement of extracted files.

3-You, need to login on each Oracle Hyperion server with the same "USER/ACCOUNT" which was used to install & configure Oracle Hyperion 11.1.2.4 components on that server. 

4-Stop Oracle WebLogic servers (Admin server + domain servers) running on all servers of your Oracle Hyperion environment. Make sure there are no processes or services running which could be holding a lock on any jar file. If Node Manager is running, you should also stop that.

5-Stop all Oracle Hyperion services and any java processes used by Oracle Hyperion applications, running on all the servers of your Hyperion Environment.

6-In Task manager check any Oracle WebLogic or Oracle Hyperion service-related processes should not be running.

Installing Oracle WebLogic Server Patch Set Update 10.3.6.0.190716 (Patch 29633432)

1- On the server create directory cache_dir  under E:\apps\OracleEPM\Middleware\utils\bsu\ if it does not exist (E:\apps\OracleEPM\Middleware\utils\bsu\cache_dir).

2- Unzip p29633432_1036_Generic.zip to a common network path and then copy the extracted files to E:\apps\OracleEPM\Middleware\utils\bsu\cache_dir directory of your Oracle Hyperion servers (On both Windows server as well as Linux server. Path will be almost same).

After unzip, your cache_dir directory will have the following 3 files:

1. MXLE.jar
2. patch-catalog_26707.xml
3. README.txt

You must make sure that the target directory for unzip has required write and executable permissions for the "USER/ACCOUNT" which was used to install & configure Oracle Hyperion on that server.

3- Open E:\apps\OracleEPM\Middleware\utils\bsu folder and Do the following to prevent 'Java OutofMemory Error' during the installation of the patch:

• Take a backup of bsu.cmd/bsu.sh.
• Edit bsu.cmd/bsu.sh file and change MEM_ARGS settings to 4GB (4096m):

          set MEM_ARGS=-Xms4096m -Xmx4096m

• Now save the file bsu.cmd/bsu.sh.  

4- Open CMD and Goto E:\apps\OracleEPM\Middleware\utils\bsu and Execute the following command to install the path:

Command format:

bsu.cmd -install -patch_download_dir={MW_HOME}/utils/bsu/cache_dir -patchlist={PATCH_ID} -prod_dir={MW_HOME}/{WL_HOME}

Where WL_HOME is the path of the WebLogic home.

On Windows Server command will be:

bsu.cmd -install -patch_download_dir=E:\apps\OracleEPM\Middleware\utils\bsu\cache_dir -patchlist=MXLE -prod_dir=E:\apps\OracleEPM\Middleware\wlserver_10.3

On Linux Server command will be:

./bsu.sh -install -patch_download_dir=/apps/oracle/epm/Middleware/utils/bsu/cache_dir -patchlist=MXLE -prod_dir=/apps/oracle/epm/Middleware/wlserver_10.3

Once the command gets completed and the patch is successfully applied, you will see below SUCCESS message:

On Windows Server:

On Linux Server:

We see the patch has been successfully installed. Now its time to verify the same.

5- Post-Installation verification:

To check whether the Oracle WebLogic Server version has been updated or not do the following:

1-Goto this path:

E:\apps\OracleEPM\Middleware\wlserver_10.3\server\bin\ 

2- Run below command to set the Oracle WebLogic Environment variables:

setWLSEnv.cmd   (On Linux server: ./setWLSEnv.cmd)

3- Now run following command to check the version:

java weblogic.version (On Linux server too same command: java weblogic.version)

In the below output, 10.3.6.0.190716 is the currently installed Oracle WebLogic Server PSU. It confirms now our Oracle WebLogic Server has been upgraded from 10.3.6.0 to version 10.3.6.0.190716 :

Oracle WebLogic Server 10.3.6.0.190716 PSU Patch for BUG29633432

Do verify on each and every server of your Oracle Hyperion environment whether the Oracle WebLogic Server has been successfully upgraded or not.

6- Start Oracle WebLogic servers of your Oracle Hyperion environment.

7- Start all Oracle Hyperion services in your environment.

This completes the patching process.

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