HFM Patch Set Update 11.1.2.4.209 (Patch 29343616)

In April 2020 CPU (Critical Patch Update) alert, Oracle had released HFM (Hyperion Financial Management) PSU 11.1.2.4.209 (Patch 29343616). You can find the patch details on this link: https://blogs.oracle.com/proactivesupportepm/psu_hfm_11124209

I thought let's upgrade my Hyperion Financial Management (HFM) application from the existing version 11.1.2.4.204 to this latest patch level to be up to date with HFM bug fixes. So let's see how it goes.

Note: The demonstrating Hyperion Financial Management (HFM) environment has the 'Windows Server 2012 R2 Standard' operating system. 

As usual, the first thing to check was the prerequisites. Going through the README document of the patch, we come to know that the following prerequisites patches need to be installed before installing HFM PSU 11.1.2.4.209 (Patch 29343616):

As I was already having HFM 11.1.2.4.204 version running in my Hyperion environment so I was fully confident that following 2 prerequisites patches must have been already installed on my HFM servers as part of HFM 11.1.2.4.204 (Patch 25192566) patching:

1. Fusion Middleware (FMW) - WebServices  Patch 13866584
2. Fusion Middleware (FMW) - WebLogic  Patch 16810628

Why is it so? because these two patches are required to be installed as prerequisite patches even for HFM 11.1.2.4.204 (Patch 25192566) patching. You can check the same on this link: https://blogs.oracle.com/proactivesupportepm/patch-set-update-for-oracle-hyperion-financial-management-11124204-is-available

Let's cross-check from the HFM server itself. 

As Fusion Middleware (FMW) - WebServices  Patch 13866584 is a file-replacement kind of patch, so it will not be registered under home E:\apps\OracleEPM\Middleware\oracle_common. But if you know your last HFM patching date, you can match it with the modification date of the file E:\apps\OracleEPM\Middleware\oracle_common\modules\oracle.webservices_11.1.1\oracle.webservices.standalone.client.jar to confirm the Patch 13866584 applied.

Fusion Middleware (FMW) - WebLogic  Patch 16810628 can be checked using a command as shown below:

Now coming to the third prerequisite patch Fusion Middleware (FMW) Patch 30378046, its README document suggests:

Ensure the patch for bug 16964825 has already been applied before you proceed with the current patch.

If you remember, Oracle JDeveloper ADF Patch 16964825 is already included in HFM 11.1.2.4.100 installation itself. When upgrading Hyperion environment from 11.1.2.4 to 11.1.2.4.100 one of the prerequisites is to “Install the ADF patch 21240419”. The prerequisite to 21240419 is “patch 16964825(mandatory)”. 

So it must be there on my HFM server. Let's verify with opatch lsinventory in E:\apps\OracleEPM\Middleware\oracle_common that patch 16964825 is already present.

Now its turn to install Fusion Middleware (FMW) Patch 30378046.

When I ran the OPatch command to install Fusion Middleware (FMW) Patch 30378046, it was throwing the following issue:

Actually, Patch 30378046 was conflicting with the patch 20326778.  

In such cases Oracle recommendation is:

Obtain a copy of each patch and compare the 'inventory' file or the 'readme' file. These files contain a list of bugs fixed by each patch. Compare the bugs listed. Make sure that the patch being applied has all the bugs mentioned that are fixed in the existing patch.

If all the patches listed in the existing patch are included in the patch being applied you can safely continue the patch.

In some cases, the patch being applied may not contain all the bug fixes that are included with the existing patch. If you do not require the missing patches you can safely continue the new patch installation.

If you do require the missing patches then you should stop the patch installation and log a service request with Oracle Support requesting a "Merge Label Request, MLR" of the existing patch and the patch being applied.

Following the recommendation, I compared the two patches and noticed that following three bugs fixed in EXISTING Patch 20326778, are not included in the NEW Patch 30378046:

• 17890124: BLR BACKPORT 17776065 TO 11.1.1.7.1: FF25, ALL RTL TEXT IS MISALIGNED FOR DVT HT
• 18601128: BLR BACKPORT OF BUG 18233049 ON TOP OF 11.1.1.7.1 (BLR #3572140)
• 18954287: BLR BACKPORT OF BUG 18820382 ON TOP OF 11.1.1.7.1 (BLR #3789756)

But, if you dig deep you will notice that these are BACKPORT Bugs and their sources (17776065, 18233049, 18820382) are included in the new Patch 30378046. So it's safe to move with installing over Patch 20326778. It was also making sense as Patch 30378046 is the latest patch available for JDeveloper ADF and the other Patch 20326778 was outdated by 5 years at least.

I did the same as shown below:

As you can see OPatch itself will first uninstall the Patch 20326778 and then install the new Patch 30378046. 

For the fourth prerequisite ADF Patch 25113405, it was self-explanatory:

If you are applying this HFM PSU 11.1.2.4.209 Patch 29343616 to version 11.1.2.4.203 or an earlier version, this Financial Management patch requires Oracle Application Development Framework (ADF)  Patch 25113405.

Finally, its time to install the main Hyperion Financial Management PSU 11.1.2.4.209 Patch 29343616.  As all the prerequisites were clear, It got installed successfully without any issue:

After applying the HFM Patch 29343616, don't forget to execute the sql commands as per your database type mentioned in the README document of HFM PSU 11.1.2.4.209 Patch 29343616. 

As I have HFM version 11.1.2.4.204 and Oracle Database 18c, so I ran the below commands by logging to HFM schema (Refer the README document of Patch 29343616 for more details):

1- Executed the following sql command to upgrade the schema against the Financial Management database:

<EPM_ORACLE_HOME>\products\FinancialManagement\Server\conf\Oracle_from_11.1.2.4.208_to_11.1.2.4.209_SchemaUpgrade.sql

2- For the 'Importing Applications' feature, executed the following sql commands:

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

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

Make sure these sql commands are executed without any error message.

Post Hyperion services restart, all users should clear cached files from their browsers before login to Workspace.

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

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

Read More

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

Hello Friends!

This is PART-2 of our 'Oracle Hyperion Financial Management (HFM) Housekeeping' blog series. If you haven't read PART-1, I would suggest you to first go through that post to have a clear understanding of this PART-2.

In PART-1, we had covered stopping Oracle Hyperion Financial Management (HFM) services and processes, killing HFM database sessions, housekeeping of HFM schema tables, etc.

Now we will see Oracle Hyperion Financial Management (HFM) logs archiving, deleting HFM temp and cache files, etc. activities as part of Oracle Hyperion Financial Management (HFM) Housekeeping.

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. 

Oracle Hyperion Financial Management (HFM) logs archiving

To have good Hyperion Financial Management (HFM) application performance, it's recommended to archive HFM application logs on a regular basis as if it's not done on time it keeps growing and can consume your server's resources apart from downgrading your application performance.

1- Login to all your Hyperion Financial Management (HFM) application servers and perform below steps on each one of them.

2- Goto the path E:\apps\Oracle\Middleware\user_projects\epmsystem_hfm\diagnostics\logs\hfm

3- In the above folder, you will find the following log files:

• xfm.odl.<APPLICATION_NAME>.log
• oracle-epm-fm-hsx-server.log
• oracle-epm-fm-bi-publisher.log
• oracle-epm-fm-hsx-registry.log
• oracle-epm-fm-lcm-client.log
• SharedServices_Security.log

4- Archive all the LOG files under \hfm folder by zipping them to a separate backup folder. 

5- Now goto the path E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\HFMWeb0\logs

6- Archive all the LOG files under \logs folder by zipping them to a separate backup folder. 

7- Goto the path E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\HFMWeb0\logs\hfm

 

8- Archive all the LOG files under \hfm folder by zipping them to a separate backup folder. 

9- Now login to your HFM Webservers and perform below steps on each one of them.

10- Goto the path E:\apps\OracleEPM\Middleware\EPMSystem11R1\logs\hfm

11- Archive all the LOG files under \hfm folder by zipping them to a separate backup folder. 

This completes the Hyperion Financial Management (HFM) logs archiving part. 

Deleting Oracle Hyperion Financial Management (HFM) temp and cache files

Note: The purpose to include 'deleting tmp and cache folders on Hyperion Financial Management (HFM) app servers' is that it should be done once in a quarter or probably once in a 6 months period (not weekly/monthly) like truncation of HFM DATA_AUDIT, TASK_AUDIT, HFM_ERRORLOG tables or do it when you need to troubleshoot any particular technical issue where Oracle recommends to perform it. Deleting tmp and cache folders is mainly required during issues in Hyperion Financial Management (HFM) Web pages/interfaces or after Hyperion Financial Management (HFM) patching. On some occasions where frequent and intense data loads are running from FDMEE to HFM apps, it has been observed that truncating overgrown HFM audit and data tables along with clearing tmp and cache folders have worked as a temporary fix to resolve the issue like data is not getting loaded into Hyperion Financial Management (HFM) applications even though FDMEE DLR is getting successfully completed. 

Tmp folder on Hyperion Financial Management (HFM) application managed server is used to store temporary files related to your HFM applications. A cache is a set of temporary files used by HFM application server. 

Oracle recommends that users should periodically clear the tmp and cache directories to help your system run faster and reclaim disk space. Without damaging your applications, you can delete tmp and cache easily.

1- Login to all your Hyperion Financial Management (HFM) application servers and perform the below tasks.

2- Goto path E:\apps\OracleEPM\Middleware\user_projects\domains\EPMSystem\servers\HFMWeb0

3- Rename tmp and cache folders as shown below:

Note: Make sure you keep clearing these backup folders on every next Housekeeping activity. 

Rebooting Oracle Hyperion Financial Management (HFM) Application servers

Rebooting Hyperion Financial Management (HFM) application servers are important in order to clear locking, blocking, hanged, orphan processes/sessions running at the Operating system level. Sometimes these sessions can cause interruptions in HFM application normal functioning. 

Login to all your Hyperion Financial Management (HFM) application servers and reboot them. 

Note: It's also recommended to regularly monitor Hyperion Financial Management (HFM) application servers' space and especially memory utilization. 

Starting Oracle Hyperion Financial Management (HFM) application services

Once you complete all of the above tasks, you can go ahead and start HFM services on all Hyperion Financial Management (HFM) application servers.

Now login to Workspace and open your Hyperion Financial Management (HFM) applications and thoroughly validate them ensuring everything is working fine.

This completes your Hyperion Financial Management (HFM) Housekeeping 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

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