Automation of 'Maintain FDMEE Data Tables' System Maintenance Task

For those who are new to my blog, we have already covered all the Oracle Hyperion Financial Data Quality Management (FDMEE) System Maintenance Tasks with the step-by-step process to manually execute them. If you have not already gone through those articles, I would suggest you click on the below links and read them first to get a comprehensive understanding of all the 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
FDMEE: System Maintenance Tasks: Maintain EBS GL Balances Table

Running 'FDMEE System Maintenance Tasks' manually for multiple combinations of Target Applications and Categories requires a lot of manual effort causing it to be a tiresome process because for each maintenance task you need to manually select all the combinations of target applications and categories one by one in Data Management (FDMEE) console for a particular set of start and end period. So why not have a script which does all of these tasks for us and make life easier.

But now using below given automated script you no more need to do all these activities manually These automated jobs can be scheduled in Windows Task Scheduler to run at any chosen day/time. Also, these automated scripts don’t require any outage on FDMEE application and run smoothly using only the inbuilt functionality of FDMEE.

Note:

• The demonstrating Hyperion environment has the 'Windows Server 2012 R2 Standard' operating system. 
• The demonstrating Hyperion environment has 'Oracle database server 12.2.0.2 (18c)' as backend database. 
• This post has been written and associated activities have been demonstrated on Oracle FDMEE version 11.1.2.4.220.
• There is no outage required on the FDMEE application to execute these scripts. But it is advisable not to run any data loads during the activity.
• Don't forget to take complete FDMEE schema backup before attempting to run these scripts. 
• Different paths and folder names mentioned in this post may slightly vary for different Hyperion setups but at large it should be the same.  

In this blog series, we are going to learn how can we automate these 'FDMEE System Maintenance Tasks' one-by-one and schedule it via Windows Task Scheduler to run whenever we want to do FDMEE System maintenance activities.

FDMEE batch utility executescript.bat
------------------------------------------------------------
Oracle provides a utility called executescript.bat to run each of these System Maintenance Tasks as well as other custom scripts from the command line. This command-line utility allows us to execute custom scripts either from Windows or Unix/Linux systems. Unfortunately, this topic is not well documented in the FDMEE admin guide.

You will find the executescript.bat (among others) in the folder E:\apps\OracleEPM\Middleware\user_projects\epmsystem_fdm\FinancialDataQuality.

Using utility executescript.bat you can automate all the FDMEE System Maintenance Tasks.

Command syntax of FDMEE batch utility executescript.bat:
----------------------------------------------------------------------------------
executescript <username> <password> "<script name>" "<parameters>" <execution mode>

Where:

• <username>: is your FDMEE user running the script
• <password>: can be either the hard-coded password or a file with encoded password (-f:filename)
• <script name>: is the name of the script registered in Script Registration page (not the python script filename). You find the script name here: Navigate-Administer-Data Management-Setup tab-Script Registration
• <parameters>: are the script parameters in the form "Display Name=Parameter Value"
• <execution mode>: is either SYNC (synchronous) or ASYNC (asynchronous). SYNC—Process runs immediately and control returns when process completes. ASYNC—When the ODI job is submitted, control returns. The load process continues to execute in ODI.

Now the command syntax in more detailed form will look like below:

executescript <username> <password> "<script name>" "<parameterdisplay1=value1>" "<parameterdisplay2=value2>" <SYNC | ASYNC>

If you are going to use an encrypted password (which is the recommended best practice), following will be the command syntax:

executescript <username> <password | -f:password.txt> "<script name>" "<parameterdisplay1=value1>" "<parameterdisplay2=value2>" <SYNC | ASYNC>

You will find the configuration steps for password encryption in the FDMEE admin guide as shown below:

You need to ensure that your encrypted password.txt file is placed in the location defined in Navigate-Administer-Data Management-Setup tab-System Settings-Encrypted Password Folder.

Automating 'Maintain FDMEE Data Tables' System Maintenance Task:
--------------------------------------------------------------------------------------------
In this post, we will see how to automate 'Maintain FDMEE Data Tables' System Maintenance Task. I suggest you to first read this post FDMEE: System Maintenance Tasks: Maintain FDMEE Data Tables for theoretical understanding, manual implementation, and post-execution activities of 'Maintain FDMEE Data Tables' System Maintenance Task.

When you execute ‘Maintain FDMEE Data Tables’ purge script manually from Data Management console, it seeks the following input parameters:

• Target Application
• Start Period
• End Period
• Category

To run ‘Maintain FDMEE Data Tables’ purge script using executescript.bat command-line utility below is the command syntax:

executescript username password "MaintainFDMEEDataTables" "Target Application=TargetAppName" "Start Period=YYYY-MM-DD" "End Period=YYYY-MM-DD" "Select Category=CategoryKey" SYNC

Description:

• TargetAppName should be entered the same as what you get in the output by running below query against FDMEE schema:

          SELECT APPLICATION_NAME FROM AIF_TARGET_APPLICATIONS; 

Above query-output is same as what you see in Data Management console Target Applications list values as shown below:

• Start Period and End Period can be chosen from the values what you get in the output by running below query against FDMEE schema:

          SELECT PERIODDESC, PERIODKEY FROM TPOVPERIOD;

Above query-output is same as what you see in Data Management console Period list values as shown below:

If you want to quickly check what is the min and max value of period to help you choose your Start Period and End Period, you can run below query against FDMEE schema:

           SELECT MIN(PERIODKEY), MAX(PERIODKEY) FROM TPOVPERIOD; 

Note: Start Period and End Period values must be entered in the 'YYYY-MM-DD' format only.

• In Category segment, you need to enter the 'Category Key (number)' what you get in the output by running below query against FDMEE schema:

          SELECT CATNAME, CATKEY FROM TPOVCATEGORY;

Above query-output is same as what you see in Data Management console Category list values as shown below:

Most of the time, we need to run 'MaintainFDMEEDataTables' purge script for many combinations of various Target applications and Categories for a selected Start Period and End Period range.

Let's understand the automation script for 'MaintainFDMEEDataTables' with the help of an example.

Suppose using FDMEE Admin user, you want to run 'MaintainFDMEEDataTables' purge script for 4 Target applications name TargetApp1, TargetApp2, TargetApp3, TargetApp4, and 3 Categories keys name CategoryKey1, CategoryKey2, CategoryKey3 for Start Period Oct 2017 to End Period Apr 2018.

In order to achieve the above goal, create a batch script name 'MaintainFDMEEDataTables.bat' under folder E:\Admin\scripts having the following content:

MaintainFDMEEDataTables.bat:
--------------------------------------------------
@echo off

REM Set the value for the variables 
set "username=Admin"
set "password=YourAdminPassword"
set "startperiod=2017-10-01"
set "endperiod=2018-04-01"
set "logfile=E:\Admin\scripts\output.log"

REM Clear the content of existing logfile
rem.> %logfile%

REM Navigate the executescript.bat utility folder
cd "E:\apps\OracleEPM\Middleware\user_projects\epmsystem_fdm\FinancialDataQuality"

REM Run executescript to trigger "MaintainFDMEEDataTables" for all combinations of 4 Target applications and 3 Category Keys for the mentioned Start and End Period range

for %%f in (TargetApp1 TargetApp2 TargetApp3 TargetApp4) do (
    for %%i in (CategoryKey1 CategoryKey2 CategoryKey3) do (
      echo call executescript %username% %password% "MaintainFDMEEDataTables" "Target Application=%%f" "Start Period=%startperiod%" "End Period=%endperiod%" "Select  Category=%%i" SYNC
call executescript %username% %password% "MaintainFDMEEDataTables" "Target Application=%%f" "Start Period=%startperiod%" "End Period=%endperiod%" "Select Category=%%i" SYNC
)
echo ------------------------------------------------------------------------------
echo ------------------------------------------------------------------------------
) >>%logfile%

cd E:\Admin\scripts

Change the paths and variables mentioned in the script as per your requirement and FDMEE application setup.

You can put your Target Application names in the segment (TargetApp1 TargetApp2 TargetApp3 TargetApp4) and Category Keys in (CategoryKey1 CategoryKey2 CategoryKey3) separated by single space. 

Now login to your FDMEE server, open an Administrator Command prompt, and run 'MaintainFDMEEDataTables.bat'.

After completion of the script your output.log will be generated in below format:

To verify whether your process IDs 8237, 8238... have successfully completed or not, we can navigate to Process Details page (In Data Management console, click on the Process Details link under the Workflow tab—Monitor—Process Details):

We can see our process IDs 8237, 8238 have executed successfully. Click on the 'Show' button for Process IDs 8237 to open the corresponding log file to see more details. Similarly, you can verify other process IDs recorded in output.log file for various combinations of Target Applications and Categories.

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

How to Extract Oracle DRM (Data Relationship Management) Users List and Access Attributes

Tracking changes regarding application users and their accesses in the Production environment is a very important part of your Hyperion application auditing. DRM is one such application where the Business team performs regular user auditing because your critical Hyperion metadata is managed here.

Here we are going to explore some of the very handy and important Oracle database queries using which you can check your Data Relationship Management (DRM) users, their assigned roles, roles permissions, Property Categories access, Node Access Groups Access, Object Access Groups Access etc.

These queries are also helpful for your DRM Licensing based on the DRM user base.

Using these queries, you can automate Data Relationship Management (DRM) users tracking process in your Production environment. For automation, you just need to put your chosen query in a .sql file, add SQL output formatting options as per your need, call that .sql file from a batch script and send the formatted output to the recipients using an email script.

In DRM_DB schema/repository where your Oracle Data Relationship Management (DRM) application is configured, there are many tables that contain information regarding your DRM application users, their roles, etc. but in a distributed manner, i.e. there is no single table, which consists of everything. Therefore, you need to join multiple tables to fetch the required users' information.

What will you learn in this ebook?:

• 30+ very handy and important DRM database queries with outputs and actual screenshots that you can directly use not only to check your Oracle DRM Users and their access tracking but also to automate them. 
• Various DRM database tables, which consist of information regarding DRM users and their various access levels.
• DRM Security concept and different levels of  DRM security options under the following headings:

Queries to extract DRM Users and their assigned DRM Roles:  

• Query to list out all the DRM users including those users who do not have any DRM ROLE assigned.
• Query to list out all your DRM business users (Active Directory users) who are externally authenticated through Hyperion Shared Services.
• Query to list out all the DRM Roles and their full list of permissions.
• Query to list out all the DRM users with all of their assigned roles (concatenated in one row), who have been assigned at least one DRM role. 
• Query to list out only those DRM users with all their assigned roles who are externally authenticated through Hyperion Shared Services.
• Query to list out all those DRM users who have been assigned 'Application Administrator' role in your DRM application.
• Query to list out all those DRM users who have been assigned 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services.
• Query to list out all those DRM users who have been assigned EITHER 'Interactive User' OR 'Application Administrator' role in your DRM application. 
• Query to list out all those DRM users who have been assigned EITHER 'Interactive User' OR 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services. 
• Query to list out all those DRM users who have been assigned BOTH 'Access Manager' and 'Application Administrator' role in your DRM application. 
• Query to list out all those DRM users who have been assigned BOTH 'Access Manager' and 'Application Administrator' role in your DRM application and who are externally authenticated through Hyperion Shared Services. 

Queries to extract DRM Users and their assigned Property Categories Access:

• Query to list out all the DRM Users with their assigned Property Category names and Access Levels (Read/Edit).
• Query to list out only those DRM users with their assigned Property Category names who have READ level access.
• Query to list out only those DRM users with their assigned Property Category names who have EDIT level access.
• Query to list out only those DRM users with their assigned Property Category names and Access Levels (Read/Edit) who are externally authenticated through Hyperion Shared Services.
• Query to list out user detail with his/her assigned Property Category names and Access Levels (Read/Edit) for a particular DRM user. 

Queries to extract DRM Users and their assigned Node Access Groups:

• Query to list out all the DRM Users with their assigned Node Access Groups (in one row). 
• Query to list out only those DRM Users with their assigned Node Access Groups who are externally authenticated through Hyperion Shared Services.

Queries to extract DRM Users and their assigned Object Access Groups:

• Query to list out all the DRM Users with their assigned Object Access Group and Object access level.
• Query to list out only those DRM Users with their assigned Object Access Group and Object access level who are externally authenticated through Hyperion Shared Services.

To purchase the ebook, choose any of the below given payment options:

Option-1 : Buy on PayPal ( Price = $4 ):

Option-2 : Buy in INR on Instamojo ( Price = INR 295 ):

Option-3 : To pay in INR via UPI (GooglePay or Paytm), please use below given links. Post payment, drop an email to skp.1world@gmail.com with payment screenshot. After verifying the payment, we will share the pdf ebook with you on the same email.

Paytm Payment Link --> Paytm (INR 295)

# For any query/issue regarding the ebook purchase, please feel free to drop an email at skp.1world@gmail.com. Thank you!

Read More

Automation: Batch Script to delete Hyperion backup folders older than N months/days

Hi Friends,

Topic: How to delete Oracle Hyperion backup folders older than N months/days

Maintaining a backup of various applications is an important part of any Oracle Hyperion Production system. In most of the cases, we have a network drive connected and accessible from all the servers of a Hyperion Production environment where we save the backup of Hyperion application data, metadata, artifacts, LCM exports, etc. either manually or through automated scripts like Lifecycle Management (LCM) automation, FDMEE data load jobs, Maxl scripts, etc. on a daily or weekly basis.

Over the course of time, your network drive is filled with past many months’ backup folders consuming a lot of space and thus causing a requirement for network drive housekeeping. In most of the cases, maintaining last 3-6 months application backup folders actually serve the purpose.   

We too faced the similar case where we had many Hyperion application LCM backup folders over a network shared drive starting from the year 2017, which were consuming a huge amount of space raising critical space alerts. To housekeep the network drive when we tried to delete some of these LCM backup folders manually, we encountered the notorious ‘Source Path Too Long’ error to make matters worse.

Therefore, to fix this issue permanently, we have created a batch script called “Network_Housekeeper” which works perfectly fine and is based on the following logic:

1- We have a LCM automated scheduled job which adds two huge sized LCM backup folders every day to the network drive path: \\NetworkServerAddress\HyperionBackupFolder\AutomatedLCMExport with the following folder naming convention:

HYP_PROD_YYYYMMDDhhmm

Note: To learn how you can add current date and time in your backup folders' name, kindly refer this link: Batch script to add today's date and time in file/folder name

2- The script maps the network drive path as a local drive on the server from where we trigger this script.

3- It identifies the date 4 months (120 days) before today’s date.

4- It lists out all the LCM backup folders with the name HYP_PROD_* having the oldest date folder first and compares the date of each folder with the date 4 months before the current date.

5- If the folder date is less than the date 4 months before the current date, it passes that folder name to ROBOCOPY purge command section as an input to delete that folder.

Note: ROBOCOPY purge command is used to troubleshoot ‘Source Path Too Long’ error while deleting the folder.

6- This loop continues until all the folders older than 4 months (120 days) are deleted.

7- The script saves the names of the deleted folders in a log file for your record: D:\Data\Scripts\NETWORK_HOUSEKEEPER\Deleted_Folder.log 

Network_Housekeeper Batch Script:

@echo off

REM Connect to the network drive path

pushd \\NetworkServerAddress\HyperionBackupFolder\AutomatedLCMExport

REM Set the number of days to retain the folders for. You need to change the day=-120 to the relevant number of days you want. 

set day=-120

echo >"%temp%\%~n0.vbs" s=DateAdd("d",%day%,now) : d=weekday(s)

echo>>"%temp%\%~n0.vbs" WScript.Echo year(s)^& right(100+month(s),2)^& right(100+day(s),2)

for /f %%a in ('cscript /nologo "%temp%\%~n0.vbs"') do set "result=%%a"

del "%temp%\*%~n0.vbs"

set "yyyy=%result:~0,4%"

set "mm=%result:~4,2%"

set "dd=%result:~6,2%"

set "final=%yyyy%%mm%%dd%"

setlocal enabledelayedexpansion

REM List out all the folders with name HYP_PROD_* having the oldest folder first and then extract the date from each folder name 

for /f "delims=" %%a in ('dir "HYP_PROD_*" /a:d /o:d /b') do (

set "folder=%%a"

set folddate=!folder:~9,8!

REM If folder date is less than the date 4 months before the current date, trigger the 'work' section of the script

if !folddate! LSS !final! call :work

)

goto :EOF

REM Delete the folder using ROBOCOPY command route 

:work

echo !folder! >> D:\Data\Scripts\NETWORK_HOUSEKEEPER\Deleted_Folder.log

rmdir emptyfolder

mkdir emptyfolder

robocopy emptyfolder "!folder!" /purge                  

rmdir !folder!

rmdir emptyfolder

You can schedule this batch script through Windows Task Scheduler to run at any time of the day. I have tested this script on my system and it takes 80 seconds to delete a 2 GB LCM backup folder. That is quite ok! 

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

Shell Command/Script to extract Oracle EPM/Hyperion already applied patches in Linux/Unix server

Hi Friends,

Topic: How to extract Oracle Hyperion patches currently applied in your Hyperion environment using shell/bash script

In this post, we will see how you can extract all the Oracle Hyperion patches currently applied in your Hyperion environment Linux servers. I am sure you would have seen Oracle Hyperion Essbase application installed and configured on Linux/Unix server in many Hyperion setups. 

When you plan to apply any newly released Oracle 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 Windows Servers, to know how to extract Oracle Hyperion patches currently applied in your Hyperion environment using a batch script, you may like to read this post: Batch 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 Essbase application server has the 'Red Hat Enterprise Linux Server release 6.10' operating system.

Concept:

As you know, we all use ./opatch lsinventory command in order to see details of all the patches that are currently installed on a Hyperion Linux/Unix 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 Linux/Unix machine):

./opatch lsinventory -oh /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 shell/bash 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 Linux/Unix server's command prompt, run the below commands:

To see all installed patches:

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

cd /apps/oracle/epm/Middleware/EPMSystem11R1/OPatch

For EPMSystem11R1 patches:

./opatch lsinventory -oh /apps/oracle/epm/Middleware/EPMSystem11R1 -jdk /apps/oracle/epm/Middleware/jdk160_35 -invPtrLoc /apps/oracle/epm/Middleware/EPMSystem11R1/oraInst.loc | grep -i applied

For oracle_common patches:

./opatch lsinventory -oh /apps/oracle/epm/Middleware/oracle_common -jdk /apps/oracle/epm/Middleware/jdk160_35 -invPtrLoc /apps/oracle/epm/Middleware/oracle_common/oraInst.loc | grep -i applied

To see a particular installed patch:

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

cd /apps/oracle/epm/Middleware/EPMSystem11R1/OPatch

For EPMSystem11R1 patches:

./opatch lsinventory -oh /apps/oracle/epm/Middleware/EPMSystem11R1 -jdk /apps/oracle/epm/Middleware/jdk160_35 -invPtrLoc /apps/oracle/epm/Middleware/EPMSystem11R1/oraInst.loc | grep -i "applied" | grep -i "28314691"

For oracle_common patches:

./opatch lsinventory -oh /apps/oracle/epm/Middleware/oracle_common -jdk /apps/oracle/epm/Middleware/jdk160_35 -invPtrLoc /apps/oracle/epm/Middleware/oracle_common/oraInst.loc | grep -i "applied" | grep -i "18514458"

Shell/Bash Script:

Create a folder (/apps/Admin) in the Hyperion Linux/Unix server where you want to extract the installed Oracle patch list and then create a shell/bash script (/apps/Admin/test.sh) with the below codes under that folder.

#!/bin/bash

cd /apps/OracleEPM/Middleware/EPMSystem11R1/OPatch

./opatch lsinventory -oh /apps/OracleEPM/Middleware/EPMSystem11R1 > /apps/Admin/Patch_History.txt

cd /apps/Admin

truncate -s 0 /apps/Admin/Installed_Patches_$HOSTNAME.txt

grep -w "Interim patches" /apps/Admin/Patch_History.txt >>/apps/Admin/Installed_Patches_$HOSTNAME.txt

grep -w "applied" /apps/Admin/Patch_History.txt >>/apps/Admin/Installed_Patches_$HOSTNAME.txt

When you run this test.sh script via bash command line, 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 Linux/Unix server HOSTNAME appended in the log file name.

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

You can run this script across all the Linux/Unix 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

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