Batch Command/Script to extract Oracle EPM/Hyperion already applied patches in Windows server

Hi Friends,

Topic: How to extract Oracle Hyperion patches currently applied in your Hyperion environment using a batch script

In this post, we will see how you can extract all the Oracle Hyperion patches currently applied in your Hyperion environment Windows servers. 

When you plan to apply any newly released Oracle Hyperion patches to upgrade your Hyperion applications patch level, you may need to first know the currently applied Oracle patches for that particular Hyperion application. 

For the Linux/Unix Servers, to know how to extract Oracle Hyperion patches currently applied in your Hyperion environment using a shell/bash script, you may like to read this post: Shell script to extract Oracle Hyperion patches currently applied

Note: 

• This post has been written and associated activities have been demonstrated on Oracle Hyperion version 11.1.2.4.
• The demonstrating Hyperion environment has the 'Windows Server 2012 R2 Standard' operating system. 

Concept:

As you know, we all use opatch lsinventory command in order to see all the details of the patches that are currently installed on a particular Oracle Hyperion server.

The lsinventory command reports what has been installed on the system for a particular Oracle home directory, or for all installations. 

The following syntax is used for this command (on Windows machine):

opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1

where -oh option is your Oracle Home location. 

The result of opatch lsinventory command contains a lot of data like various versions, paths, bugs fixed, overlaying patch details, etc as shown in the below image. So in order to list out only the Oracle patch numbers and when they were applied you need to go through all the details generated as a result of opatch lsinventory command then extract the required details manually.

The below-mentioned batch script does all these things for you automatically by extracting out only the Oracle patch numbers with the date when they were applied. It saves the output into a log file for your record along with saving your manual effort. 

On Command prompt:

If you want to see all the applied patch list directly on your EPM/Hyperion Windows server's command prompt, run the below commands:

To see all installed patches:

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

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

For EPMSystem11R1 patches:

opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35 | findstr -i applied

For oracle_common patches:

opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\oracle_common -jdk E:\apps\OracleEPM\Middleware\jdk160_35 | findstr -i applied

To see a particular installed patch:

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

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

For EPMSystem11R1 patches:

opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 -jdk E:\apps\OracleEPM\Middleware\jdk160_35 | findstr "applied" | findstr "24738130"

For oracle_common patches:

opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\oracle_common -jdk E:\apps\OracleEPM\Middleware\jdk160_35 | findstr "applied" | findstr "16964825"

Through Batch Script:

Create a folder (E:\Admin) in the server where you want to extract the installed Oracle patch list and then create a batch script (E:\Admin\test.bat) with the below codes under that folder.

@echo off

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

call opatch.bat lsinventory -oh E:\apps\OracleEPM\Middleware\EPMSystem11R1 >"E:\Admin\Patch_History.txt"

cd E:\Admin

rem. > "E:\Admin\Installed_Patches_%COMPUTERNAME%.txt"

findstr /L /C:"Interim patches" "E:\Admin\Patch_History.txt" >>"E:\Admin\Installed_Patches_%COMPUTERNAME%.txt"

echo. >>"E:\Admin\Installed_Patches_%COMPUTERNAME%.txt"

FOR /F "tokens=*" %%A IN (
  'findstr /L "applied" "E:\Admin\Patch_History.txt"'
) DO (
  echo %%A >>"E:\Admin\Installed_Patches_%COMPUTERNAME%.txt"
)

When you run this test.bat script via command prompt, it will extract out the following things for you:

1. Total number of Oracle patches
2. Patch numbers of all the currently applied Oracle Hyperion patches
3. Date of applying these patches

It will also save the output in a log file with your SERVERNAME appended in the log file name.

Below is the folder structure and the output format of the above-given script:

You can run this script across all the Windows servers of your Hyperion environment to extract the list of applied Oracle Hyperion patches. 

In order to see which Oracle Hyperion applications your extracted patch numbers belong to, simply do the following:  

• Go to https://support.oracle.com.
• Select the Patches & Updates tab.
• In Patch Search, enter the patch number. 
• Make sure you are searching for a Patch Name or Number and select Search.

The corresponding Hyperion application name with patch details will be displayed. 

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

Automation: Batch script to add today's date and time in file/folder name

Topic: Batch script to create files/folders with current (today) date and time in their name

Hi Friends,

In this post, we will see a batch script to add the current date and time in Windows files or folders' names.

This batch script is very useful especially when you need to take periodic backups of Hyperion applications, LCM exports, Essbase Level 0 data exports, Oracle database schemas, creating log files, etc. This backup process is part and parcel of any administration work including Hyperion Administration.

In Windows system, manipulating date and time value is not that straight-forward compared to Linux/Unix systems.

Concept:

As you know, most of the time following commands are used to display the current date and time in Windows system when you quickly want to check the same:

date /t
time /t

But when it comes to batch scripting where you want to use date and time values, there is a problem with these commands. 

date /t command returns the current date using the windows local system settings for the "short date format" so it is Windows-version and region-dependent. This local system 'date and time setting' is fully customizable. Each language/region has its own settings and the users can change this too. One user may configure its system to show the short date as Tue060719; while another user (even in the same Windows system) may choose 07/06/2019. So it is not really a reliable way to get the date. It becomes difficult for you to script using date /t command as the output of this command varies with Windows machines with a different regional/country settings.

To check the above, I randomly tested these commands on two different Windows systems.

On Windows Server 2012 R2 server:

On Windows 10 system:

Alright! here is the solution.

In order to overcome this issue, you should use the WMIC localdatetime command to get the date and time as this will work independent of the region setting in your Windows system/server. WMIC is the WMI command-line interface to WMI. WMIC localdatetime command provides you regionally independent date-time parsing.

I tested WMIC localdatetime command on the same set of Windows systems and got the output as shown below where we can see the output format is exactly the same in both:

On Windows Server 2012 R2 server:

On Windows 10 system:

From the above output, you can easily see that this WMIC command output needs some formatting to put the current date and time in more readable format.

Below is the batch script that formats the WMIC command output for you. You can directly use this script to add current/today date and time in your Windows files/folders.

Batch Script: Version-1

@echo off

for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set "dt=%%a"

:: Format the WMIC command output in YY_MM_DD_hr_mn format

set "YY=%dt:~0,4%"

set "MM=%dt:~4,2%"

set "DD=%dt:~6,2%"

set "hr=%dt:~8,2%"

set "mn=%dt:~10,2%"

set "today_date_time=%YY%_%MM%_%DD%_%hr%_%mn%"

echo %today_date_time%

:: Create a folder in the current directory with name as today’s date and time as shown below

mkdir .\%today_date_time%

:: Append today’s date and time in your log file name created in the current directory as shown below

echo This information will be recorded in the following log file >> %today_date_time%_Output.log

Save the above code in a batch file (test.bat) as shown below:

Now let’s run this batch script (either from the command line or directly from the folder itself by double-clicking on test.bat).

Running this batch script will create the following two things:

1. A log file in current directory with name: %today_date_time%_Output.log
2. A folder in current directory with name: %today_date_time%

Batch Script: Version-2

If you want to display the month-name (with the first 3 characters like 'Feb') and not the month-number, use the below batch script code:

@echo off

for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set "dt=%%a"

:: Format the WMIC command output in YY_MM_DD_hr_mn format

set "YY=%dt:~0,4%"

set "MM=%dt:~4,2%"

set "DD=%dt:~6,2%"

set "hr=%dt:~8,2%"

set "mn=%dt:~10,2%"

:: Format the MM (month-number) to display the month-name

if %MM%==01 set MM=Jan

if %MM%==02 set MM=Feb

if %MM%==03 set MM=Mar

if %MM%==04 set MM=Apr

if %MM%==05 set MM=May

if %MM%==06 set MM=Jun

if %MM%==07 set MM=Jul

if %MM%==08 set MM=Aug

if %MM%==09 set MM=Sep

if %MM%==10 set MM=Oct

if %MM%==11 set MM=Nov

if %MM%==12 set MM=Dec

set "today_date_time=%YY%_%MM%_%DD%_%hr%_%mn%"

echo %today_date_time%

:: Create a folder in the current directory with name as today’s date and time as shown below

mkdir .\%today_date_time%

:: Append today’s date and time in your log file name created in the current directory as shown below

echo This information will be recorded in the following log file >> %today_date_time%_Output.log

Running above batch script will generate your output in below format (with month-name in place of month-number compared to the first batch script):

The purpose here is that you can use the above two batch script codes in any of your batch files where you either want to create a backup folder or a log file with the current date and timestamp added in the folder or file name.

WMIC localdatetime command can be run on any Windows platform and the result will be the same. You don’t need to worry about Windows system regional settings and the variation in date and time formats. The returned value of WMIC localdatetime is always in the same format and with time in the 24-hour format.

WMIC localdatetime command works universally on all locales or date-time formats that's why it is the most reliable way to get the date in batch scripting.

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: System Maintenance Tasks: Maintain EBS GL Balances Table

Topic: How to manually run FDMEE System Maintenance Tasks: Maintain EBS GL Balances Table

Hi Friends,

This is the fifth post under Oracle Hyperion Financial Data Quality Management (FDMEE): System Maintenance Tasks blog series where we are going to cover the Maintain EBS GL Balances Table script.

If you have not yet read the previous four posts of this blog series, I would suggest you to go through them on below links to get a comprehensive picture of Oracle Hyperion Financial Data Quality Management (FDMEE) System Maintenance Tasks:

FDMEE: System Maintenance Tasks: Maintain FDMEE Data Tables
FDMEE: System Maintenance Tasks: Maintain ODI Session Data

FDMEE: System Maintenance Tasks: Maintain Application Folder

FDMEE: System Maintenance Tasks: Maintain Process Tables  

In this fifth post, we will focus on the ‘Maintain EBS GL Balances Table’ task and will see how to manually execute ‘Maintain EBS GL Balances Table’ purge script.  

Important 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.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database.
• There is no outage required on the Oracle Hyperion Financial Data Quality Management (FDMEE) application to execute these purge scripts. But it is advisable not to run any data loads during the purge activity.
• Based on the data retention policy of your organization, you should pre-decide how many days of data you want to retain for Oracle Hyperion Financial Data Quality Management (FDMEE) EBS GL Balances Table.
• Don't forget to take complete Oracle Hyperion Financial Data Quality Management (FDMEE) schema backup before attempting to run these purge scripts

Maintain EBS GL Balances Table

FDMEE is the EPM tool for data integration. Using FDMEE, ERP applications like Oracle EBS, PeopleSoft, SAP, etc. can be directly connected to your target Oracle Hyperion applications for data loading.

You would have seen FDMEE integration system having Oracle EBS General Ledger as data source system and HFM or Hyperion Planning as target applications. Data (GL_Balances) is extracted from Oracle EBS source system using ODI (FDMEE), moved to staging tables (AIF_EBS_GL_BALANCES_STG), and then eventually loaded to your target applications after transformation.

‘Maintain EBS GL Balances Table’ purge script maintains the AIF_EBS_GL_BALANCES_STG table in the FDMEE schema. EBS General Ledger balances contain a snapshot of the General Ledger balances and are deleted based on the General Ledger period. ‘Maintain EBS GL Balances Table’ purge process determines the list of General Ledger periods between the start and end periods. 

Following are the input parameters which you need to enter while running ‘Maintain EBS GL Balances Table’ purge script:

• Source System
• Start Period
• End Period

Before proceeding further, with purging steps, I would suggest you to check and note down the pre-purge FDMEE schema size to see how much space has been freed up post purge activity.

Step-by-Step process to execute ‘Maintain EBS GL Balances Tables’ purge script

1- Log in to Workspace with Admin credentials.

2- Open Oracle Hyperion Financial Data Quality Management (FDMEE) console (Data Management) from Workspace and on the Workflow tab, under Other, select Script Execution:

3- In Script Execution, and then in Custom Script Group, select System Maintenance Tasks.

4- From the Scripts grid, select Maintain EBS GL Balances Table script and Click Execute.

5- It will prompt to enter the following parameter values on Execute Script screen.

• Source System
• Start Period
• End Period

6- Click on the search icon for Source System. 

Select any Source System name for which you want to purge data and click OK.

7- Source System will be populated. 

8- Now enter Start Period and End Period. 

For example: If you want to purge all data from Nov-2016 to Oct-2017, then select Start Period and End Period as shown below:

9- Keep Execution mode selected as Online. The online method processes the report immediately.

Execution modes:

online  — ODI processes the data in sync mode (immediate processing).

offline — ODI processes the data in async mode (runs in background).

10- Optionally, If you want to schedule the purge script run rather than running it instantly, click on Schedule option and enter the scheduling details as per your requirement.

11- If you want to instantly run the purge script, then directly click on OK (not on the Schedule option).

You will see a message informing the process ID of your script run as shown below:

12- To see the status of your script execution, click on the Process Details link under the Workflow tab—Monitor—Process Details. 

13- Above we can see our script with Process ID 8184 executed successfully. 

Click on the Show button for Process ID 8184 to open the corresponding log file.

In the log file, look for the message "Maintain EBS GL Table: Completed" to confirm that the script has run successfully without any error. If you encounter any errors, troubleshoot them based on the cause mentioned in the log file. 

You need to repeat all of the above steps for other Source System from where your FDMEE data extractions are running to target Hyperion applications. 

Once you are done with running the Maintain EBS GL Balances Table purge script, you can check your Hyperion Financial Data Quality Management (FDMEE) schema size to see how much space has been cleared.

Here it’s important to note that after running the Maintain EBS GL Balances Table purge script, your DBA needs to do FDMEE tables reorganization activity in order to reclaim freed space. 

Actually, when you delete the data from the Oracle tables, the data blocks will go onto the free list for that table. That free space will be used for the subsequent insert and update operations into the table. You need to perform table reorganization to reclaim that free space.

Note: Reorganization of tables requires outage in your Hyperion environment so you have to stop all your application services prior to the reorganization activity to be performed by your application DBA.

You may also need to ask your DBA team to 'Rebuild Indexes' in your Hyperion Financial Data Quality Management (FDMEE) database after performing the 'Tables Reorganisation' activity.

If you miss rebuilding indexes after tables reorganization activity, your FDMEE data load job may take longer than normal to complete.

Index rebuild becomes absolutely necessary especially when you delete millions of records from FDMEE tables as part of running FDMEE purge scripts because the corresponding indexes of those deleted records go in the unusable state after tables reorganization activity. Due to this bulk deletion of records, indexes become fragmented causing indexes taking more time to give the output and hence delaying your FDMEE data load job.

You will observe the following type of error during your FDMEE data load run if you do not rebuild database indexes after tables reorganization activity:

ORA-01502: index 'FDMEE_ODI.PK_AIF_EBS_GL_BALANCES_STG' or partition of such index is in unusable state.

Once both the DBA tasks (Tables reorganization + Rebuilding Indexes) are completed, compare pre and post purge size of your Hyperion Financial Data Quality Management (FDMEE) schema to see how much has the schema size reduced. 

Maintain EBS GL Balances Table purge script can also be automated to run using Executescript batch file located in the folder: 

<drive>\Oracle\Middleware\user_projects\epmsystem1_fdm\FinancialDataQuality.

We will cover the automation part in a separate post of this blog series. 

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