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