HFM 11.1.2.4 Support for Sqlnet Encryption

A while back I posted about an issue with HFM 11.1.2.4 where the product cannot support an Oracle database using Sqlnet (Advanced) Encryption. This means all database traffic between the database and HFM is in clear text for anyone sniffing/capturing network packets. The basic issue is that the Merant ODBC driver needs a specific flag set to support encryption. HFM does not provide any option to set this flag for the ODBC connection.

Troubleshooting HFM 11.1.2.4 on Windows

Looks like we have confirmation that a fix is coming in the HFM 11.1.2.4.250 PSU. The rough ETA on this PSU is July.

A Look Inside EPMVirt: Automation Scripts - EPM Installation

Finally, this post will get into the EPMVirt automation for EPM.

The installation is performed using a simple response file:

/uo/automation/epm/installAll.sh
which contains:

/u0/install/epm/installTool.sh -silent /u0/automation/epm/silentInstall.xml

Response files for the install tool are pretty simple. They just contain what products to install. To generate a response file simply go to the last page of the EPM installer after selecting the products and you sill see a Save button for saving the contents to a file.

<?xml version="1.0" encoding="UTF-8"?>
<HyperionInstall>
  <HyperionHome>/u0/Oracle/Middleware</HyperionHome>
  <UserLocale>en_US</UserLocale>
  <ActionType>0</ActionType>
  <SelectedProducts>
        <Product name="foundation">
            <ProductComponent name="foundationServices">
                <Component>hssWebApp</Component>
                <Component>staticContent</Component>
                <Component>weblogic</Component>
            </ProductComponent>
            <ProductComponent name="Calc">
                <Component>CalcWebApp</Component>
            </ProductComponent>
        </Product>
        <Product name="essbase">
            <Component>essbaseWebApp</Component>
            <Component>essbaseApsWebApp</Component>
            <Component>essbaseApsWebAppSamples</Component>
            <Component>essbaseStudioService</Component>
            <Component>essbaseStudioServiceSamples</Component>
            <Component>essbaseService</Component>
            <Component>essbaseServiceSamples</Component>
        </Product>
        <Product name="reportingAndAnalysis">
            <ProductComponent name="raFramework">
                <Component>raFrameworkWebApp</Component>
                <Component>raFrameworkService</Component>
            </ProductComponent>
            <ProductComponent name="fr">
                <Component>frWebApp</Component>
            </ProductComponent>
        </Product>
        <Product name="planning">
            <Component>planningWebApp</Component>
        </Product>
        <Product name="disclosure">
            <Component>disclosureWebApp</Component>
        </Product>
        <Product name="hfm">
            <Component>hfmAdmClient</Component>
            <Component>hfmWebApps</Component>
            <Component>hfmService</Component>
        </Product>
        <Product name="erpi">
            <Component>erpiWebApp</Component>
        </Product>
        <Product name="profitability">
            <Component>osloWebApp</Component>
            <Component>osloWebAppSamples</Component>
        </Product>
    </SelectedProducts>
  <ProductHomes/>
  <UpgradeCleanUp/>
  <UninstallCleanUp>false</UninstallCleanUp>
</HyperionInstall>

After the installer is run, the Config tool is also run using response files. My preference when running the config tool is to run through the Config for each product separately. Consequently, I have saved response files for each product and run through them separately. 

#!/bin/bash

echo "Status of database is...."

source ~/HYPDB.env

sqlplus EPM_HSS/PWD < /dev/null

echo "Fixing HFM scripts..."

cp /u0/automation/epm/boost_libs/hfm_post_install_steps.sh /u0/Oracle/Middleware/EPMSystem11R1/products/FinancialManagement/Server/

cp /u0/automation/epm/boost_libs/odbc.ini /u0/Oracle/Middleware/EPMSystem11R1/common/ODBC-64/Merant/7.1/odbc.ini

echo "Configuring..."

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_Foundation.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_CalcMgr.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_FDMEE.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_Essbase.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_EssbaseStudio.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_HFM.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_Plan.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_Profit.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_RA.xml

/u0/Oracle/Middleware/EPMSystem11R1/common/config/11.1.2.0/configtool.sh -silent /u0/automation/epm/EPMconfig_ReconfigWeb.xml

The basic flow of the file is:

1. Check that the database is running by trying to connect using SQLPlus
2. Run some customizations needed to get HFM 11.1.2.4 running on Oracle Linux 
3. Run the typical EPM configurations
4. Reconfigure the web server so it knows about all the products we've installed.
5. LCM Import some Financial Reports for HFM and Planning 
6. LCM Import the HFM-COMMA4DIM demo app

When complete, the basic EPM environment is ready. However, it doesn't contain any applications or data. Since EPMVirt is meant to be a working environment for demos, the next step will be to get the sample applications up and running. The sample applications do not come preinstalled. Again, like the OS install, database install, and EPM install - the sample applications config is all automated.

The automation is stored under /u0/automation/apps

I would like to mention again that this is just a test environment and the scripts and methods seen here are not meant for production. In addition, some of this requires crude, behind the scenes manipulations that I will leave to more advanced readers to take up on their own if interested. 

The script which creates the sample applications is /u0/automation/apps/createApps.sh.

The basic idea of the script is to:

• Do some post install tuning for HFM. The default HFM memory settings are much to high to run on a small VM environment. 
• LCM Import the Planning Sample Application
• Perform a planning refresh to Essbase
• Load the sample data for the Planning app into Essbase
• Calculate the Essbase cube to roll up the data
• LCM Import HFM-COMMA4DIM

Full contents of the createApps.sh script are below:

#!/bin/sh

echo "Post install steps for HFM..."

/u0/automation/epm/boost_libs/hfm_post_install_steps_helper.sh

/u0/automation/apps/HFMTuning.sh

echo LCM IMPORT OF CANNED APP

rm -rf /tmp/lcm 2> /dev/null

unzip /u0/automation/apps/PLANDEMO.zip -d /tmp/lcm

perl -p -i -e "s/name=\"\"/name=\"admin\"/" /tmp/lcm/HP-*/Import.xml

perl -p -i -e "s/password=\"\"/password=\"password\"/" /tmp/lcm/HP-*/Import.xml

/u0/Oracle/Middleware/user_projects/epmsystem1/bin/Utility.sh /tmp/lcm/HP-*/Import.xml

echo Planning Database Cube Refresh

echo password > /tmp/password

/u0/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1/CubeRefresh.sh /A:PLANDEMO /U:admin /C /D /FSV /L < /tmp/password

echo Unzip the Planning Data

rm -rf /tmp/sampapp 2> /dev/null

unzip /u0/Oracle/Middleware/EPMSystem11R1/products/Planning/bin/sampleapp/sampApp_data.zip -d /tmp/sampapp 

echo Load Data and Calc Planning Cube

/u0/Oracle/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1/bin/startMaxl.sh /u0/automation/apps/loadPlanData.maxl

echo LCM Import for Reporting and Analysis

rm -rf /tmp/lcm 2> /dev/null

unzip /u0/automation/apps/RA.zip -d /tmp/lcm

perl -p -i -e "s/name=\"\"/name=\"admin\"/" /tmp/lcm/Import.xml

perl -p -i -e "s/password=\"\"/password=\"password\"/" /tmp/lcm/Import.xml

/u0/Oracle/Middleware/user_projects/epmsystem1/bin/Utility.sh /tmp/lcm/Import.xml

echo LCM Import Part 1 for HFM

rm -rf /tmp/lcm 2> /dev/null

unzip /u0/automation/apps/HFM-COMMA4DIM.zip -d /tmp/lcm

cp /u0/automation/apps/ImportHFM1.xml /tmp/lcm

perl -p -i -e "s/name=\"\"/name=\"admin\"/" /tmp/lcm/ImportHFM1.xml

perl -p -i -e "s/password=\"\"/password=\"password\"/" /tmp/lcm/ImportHFM1.xml

/u0/Oracle/Middleware/user_projects/epmsystem1/bin/Utility.sh /tmp/lcm/ImportHFM1.xml

echo Ignore errors on import above ^^^

echo LCM Import Part 2 for HFM

perl -p -i -e "s/name=\"\"/name=\"admin\"/" /tmp/lcm/Import.xml

perl -p -i -e "s/password=\"\"/password=\"password\"/" /tmp/lcm/Import.xml

perl -p -i -e 's/^.*Application Snapshot.*$//' /tmp/lcm/Import.xml

/u0/Oracle/Middleware/user_projects/epmsystem1/bin/Utility.sh /tmp/lcm/Import.xml

Sample HFM Tuning SQL script for demo app:

/u0/automation/apps/HFMTuning.sh

update EPM_HFM.XFM_PARAMETERS set VALUE=500 where parametername = 'MaxDataCacheSizeInMB';
update EPM_HFM.XFM_PARAMETERS set VALUE=1 where parametername = 'MaxNumConcurrentConsolidations';
update EPM_HFM.XFM_PARAMETERS set VALUE=100 where parametername = 'MaxNumCubesInRAM';
update EPM_HFM.XFM_PARAMETERS set VALUE=10000 where parametername = 'MaxNumDataRecordsInRAM';
update EPM_HFM.XFM_PARAMETERS set VALUE=100 where parametername = 'MinDataCacheSizeInMB';
update EPM_HFM.XFM_PARAMETERS set VALUE=1 where parametername = 'NumConsolidationThreads';
update EPM_HFM.XFM_PARAMETERS set VALUE=1 where parametername = 'NumThreadsToUseWhenUpdatingCalcStatusSystemWasChanged';
update EPM_HFM.XFM_PARAMETERS set VALUE=1 where parametername = 'NumEAThreads';
update EPM_HFM.XFM_PARAMETERS set VALUE=180 where parametername = 'SQLCommandTimeout';

LoadPlanData.maxl

login admin password;
import database PLANDEMO.Consol data from data_file "/tmp/sampapp/SampleApp_data.txt" on error abort;
execute calculation default on PLANDEMO.Consol;
quit;

This concludes the discussion on "A Look Inside EPMVirt". It seems like a fairly simple task to put together an auto-installing EPM Demo environment, but as you can see, there are a number of automation scripts required. It boils down to automating the OS Install, Database, and EPM environment, then bundling into a handy EPMVirt RPM.
Enjoy!

A Look Inside EPMVirt: Automation Scripts - Oracle Database

Continuing on with the EPMVirt deep dive I will now discuss the automated installation and configuration of the Oracle Database for EPM.

The simple method for configuring a new Oracle database is to use the Database Creation Utility (dbca), a GUI interface to help run the proper SQL for creating a new database. For EPMVirt we will run the database creation manually using SQL scripts.

The script to perform the install is:
/u0/automation/database/installDB.sh which calls installDB_main.sh

Let's break down this script,

The first part lays out the Oracle binaries by running a silent installer via response file for the Oracle Database:

/u0/install/oracle_db/database/runInstaller -silent -waitforcompletion -showProgress -responseFile /u0/automation/database/db.rsp

Next, we the required root.sh scripts using sudo

sudo /u0/app/oracle/product/12.1.0/dbhome_1/root.sh
sudo /u0/app/oraInventory/orainstRoot.sh

 Then make some necessary directories, setup the environment file, and listener using templates that come in the EPMVirt rpm:

mkdir -p /u0/app/oracle/admin/$SID/arch
mkdir -p /u0/app/oracle/admin/$SID/flash_recovery_area
mkdir -p /u0/app/oracle/oradata/$SID/
mkdir -p /u0/app/oracle/admin/$SID/adump
mkdir -p $TNS_ADMIN
#Set up listener:
# Not supported by HFM in 11.1.2.4
#cp /u0/automation/database/sqlnet.ora $TNS_ADMIN
cp /u0/automation/database/listener.ora $TNS_ADMIN
perl -p -i -e s/__SID__/$SID/g $TNS_ADMIN/listener.ora
cp /u0/automation/database/tnsnames.ora $TNS_ADMIN
perl -p -i -e s/__SID__/$SID/g $TNS_ADMIN/tnsnames.ora

The perl -p -i -e does a regular expression global string replace within the template file.

Setup the init.ora for the initial database configuration.  Most of the configuration comes from a template file that is prepackaged in the EPMVirt RPM. This file specifies the memory/RAM allocation for the database and many other important features. This is important because we need to specify enough RAM for the database to operate properly, but not too much since it is a virtual environment running in a small footprint.

cp /u0/automation/database/init.ora $ORACLE_HOME/dbs/init$SID.ora
perl -p -i -e s/__SID__/$SID/g $ORACLE_HOME/dbs/init$SID.ora

convert the init.ora into a spfile:

sqlplus / as sysdba < /u0/automation/database/installDB_1.sql

Next we run the create database command:

# create the DB:
cp /u0/automation/database/install_DB_create_db.sql /tmp/
perl -p -i -e s/__SID__/$SID/g /tmp/install_DB_create_db.sql
sqlplus / as sysdba < /tmp/install_DB_create_db.sql

The basic database creation sql is:

 CREATE DATABASE __SID__

   USER SYS IDENTIFIED BY orcl

   USER SYSTEM IDENTIFIED BY orcl

   LOGFILE GROUP 1 ('/u0/app/oracle/oradata/__SID__/Online-Redo-Log-01a.rdo') SIZE 50M,

           GROUP 2 ('/u0/app/oracle/oradata/__SID__/Online-Redo-Log-02a.rdo') SIZE 50M,

           GROUP 3 ('/u0/app/oracle/oradata/__SID__/Online-Redo-Log-03a.rdo') SIZE 50M

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 4090

   MAXDATAFILES 100

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   DATAFILE '/u0/app/oracle/oradata/__SID__/system01.dbf' SIZE 50M REUSE autoextend on

   SYSAUX DATAFILE '/u0/app/oracle/oradata/__SID__/sysaux01.dbf' SIZE 50M REUSE autoextend on

   DEFAULT TABLESPACE users

      DATAFILE '/u0/app/oracle/oradata/__SID__/users01.dbf'

      SIZE 100M REUSE

   DEFAULT TEMPORARY TABLESPACE TEMP

      TEMPFILE '/u0/app/oracle/oradata/__SID__/temp01.dbf'

      SIZE 50M REUSE autoextend on

   UNDO TABLESPACE undotbs1

      DATAFILE '/u0/app/oracle/oradata/__SID__/undotbs01.dbf'

      SIZE 50M REUSE autoextend on;

@/u0/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql

@/u0/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catproc.sql

Note the two scripts at the end, catalog.sql and catproc.sql manually run the database scripts to setup the Oracle metadata typically found in the database such as V$ views and dba_ tables. These scripts take quite a bit of time to run and you will see a lot of SQL commands running as the internals of the database get created.

Another script to populate metadata:

 sqlplus system/orcl < /u0/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/pupbld.sql

Finally, create the EPM database objects:

# Create the objects:
cp /u0/automation/database/install_DB_create_objects.sql /tmp/
perl -p -i -e s/__SID__/$SID/g /tmp/install_DB_create_objects.sql
sqlplus / as sysdba < /tmp/install_DB_create_objects.sql

And startup the listener for use:

# start listener
lsnrctl start $SID

The contents of install_DB_create_objects.sql is:

create tablespace EPM_OBJECTS        datafile '/u0/app/oracle/oradata/__SID__/EPM_OBJECTS_01.dbf'        size 50M autoextend on,                                              '/u0/app/oracle/oradata/__SID__/EPM_OBJECTS_02.dbf'        size 50M autoextend on   

; 

CREATE USER EPM_HSS       IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_RA        IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_CALC      IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_HPSYS     IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_HPAPP1    IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_ESTUDIO   IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_EAS       IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_PROFIT    IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_FDMEE     IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_DISC      IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

CREATE USER EPM_HFM       IDENTIFIED BY PWD DEFAULT TABLESPACE EPM_OBJECTS temporary tablespace TEMP profile DEFAULT;

ALTER USER EPM_HSS      QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_RA       QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_CALC     QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_HPSYS    QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_HPAPP1   QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_ESTUDIO  QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_EAS      QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_PROFIT   QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_FDMEE    QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_DISC     QUOTA UNLIMITED ON EPM_OBJECTS;

ALTER USER EPM_HFM      QUOTA UNLIMITED ON EPM_OBJECTS;

create role EPM_ROLE;

grant CREATE TABLE to EPM_ROLE;

grant CREATE SEQUENCE to EPM_ROLE;

grant CREATE VIEW to EPM_ROLE;

grant CREATE SESSION to EPM_ROLE;

grant CREATE PROCEDURE to EPM_ROLE;

grant CREATE TRIGGER to EPM_ROLE;

grant CREATE TYPE to EPM_ROLE;

grant EPM_ROLE to EPM_HSS, EPM_RA, EPM_CALC, EPM_HPSYS, EPM_HPAPP1, EPM_ESTUDIO, EPM_EAS, EPM_PROFIT, EPM_FDMEE, EPM_DISC, EPM_HFM;

What happens here is we create a single tablespace for EPM, create a myriad of schema/users for each product. For each schema we grant access to the EPM_OBJECTS tablespace, then finally give the schema the EPM_ROLE so it can login and create objects.

This concludes the database setup. The system is ready for the EPM installation. Keep in mind that this is just a test instance. These steps do not represent a production database configuration. Check the /u0/automation/database folder for full listing of the scripts discussed.

A Look Inside EPMVirt: Automation Scripts - buildEPMVirt

Finally, in this installment, we will look at the automation scripts. They control the actual installation and configuration of the Oracle EPM environment.

The automation scripts get deployed into /u0/automation.
There are three main automation areas:

1. Installation and configuration of the Oracle Database (RDBMS)
2. Installation and configuration of Hyperion/EPM
3. Post install configuration and setup of EPM sample applications

They are denoted by the directories:

  /u0/automation/{apps, database, epm}

We will look at each of these in more detail below and expand on the areas. 

buildEPMVirt

buildEPMVirt the main script that is run when setting up EPMVirt. buildEPMVirt is a wrapper script that calls buildEPMVirt.py in /u0/automation. buildEPMVirt.py is the script that ties all these automation areas together. Let's take a look in more detail.

There is one note to bring up first... The scripts in EPMVirt are in a rather unpolished format. They are not intended to be examples of high quality python code. Rather, they are quick and dirty scripts created in my spare time to help get EPMVirt working. Please excuse the ugly code and hacks.

buildEPMVirt can be broken down simply by this script flow:

1. extractFiles.sh - Unzips all the downloaded files
2. database/installDB.sh - Installes the Oracle Database and configures tablespaces/schemas for EPM
3. epm/installAll.sh - Runs the EPM installer using response files
4. epm/configAll.sh - Runs the EPM configuration using response files
5. apps/insertPlanDS.sh - Creates a planning datasource for the sample planning app. 
6. start all EPM services - Runs EPM start.sh
7. apps/createApps.sh - LCM Imports some sample apps

Lastly, the buildEPMVirt script checks each step for expected output and tries to detect any errors along the way. Ideally, if errors crop up, the script will fail and let you fix the issue. If the script completes, it is a fairly good indication that the install was successful.

The full listing of the buildEPMVirt.py script is below:

from subprocess import call

import os

import sys

import glob

import time

EXPECTED_EPM_INSTALL_PASS_COUNT=72

STEP_NUM = 0

STOP_NUM = 99999

if len(sys.argv) == 2:

  STEP_NUM = int(sys.argv[1])

elif len(sys.argv) == 3:

  STEP_NUM = int(sys.argv[1])

  STOP_NUM = int(sys.argv[2])

if STEP_NUM:

  print "Starting at %s, stopping at %s" % (STEP_NUM, STOP_NUM)

def IsInFile(str, file):

  if not os.path.exists(file):

    return False

  with open(file, 'r') as f:

    return str in f.read()

def CheckDatabase():

    if IsInFile("Connected to:", "/tmp/DBResults"):

      print "DB Connection OK!"

    else:

      print "Db connection failed. Exiting..."

      sys.exit(-1)

def CheckEPMInstall():

  fname = glob.glob("/u0/Oracle/Middleware/EPMSystem11R1/diagnostics/logs/install/installTool-summary*.log")[0]

  with open(fname, 'r') as f:

    contents = f.read()

  pass_count = contents.count("Pass")

  if pass_count == EXPECTED_EPM_INSTALL_PASS_COUNT:

    print "EPM Install Component Count OK."

  else:

    print "EPM Installer failed Pass check (%s vs %s). Exiting..." % (pass_count, EXPECTED_EPM_INSTALL_PASS_COUNT)

    sys.exit(-1)

def CheckEPMConfig():

  if IsInFile("Fail", "/u0/Oracle/Middleware/user_projects/epmsystem1/diagnostics/logs/config/configtool_summary.log"):

    print "EPM Config failed. Exiting..."

    sys.exit(-1)

  else:

    print "EPM Config OK..."

print "Starting at %s, stopping at %s" % (STEP_NUM, STOP_NUM)

my_step = 0

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Extracting files..."

  time.sleep(10)

  call(["/u0/automation/extractFiles.sh", ])

  if not os.path.exists("/u0/install/epm/jre"):

    print "Error Exacting Files..."

    sys.exit(-1)

my_step = 1

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Installing Database..."

  time.sleep(10)

  call(["/u0/automation/database/installDB.sh", ])

  CheckDatabase()

my_step = 2

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Installing EPM..."

  time.sleep(10)

  call(["/u0/automation/epm/installAll.sh", ])

  CheckEPMInstall()

my_step = 3

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Configuring EPM..."

  time.sleep(10)

  call(["/u0/automation/epm/configAll.sh", ])

  CheckEPMConfig()

my_step = 4

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Configuring APPS (preinstall)..."

  time.sleep(10)

  call(["/u0/automation/apps/insertPlanDS.sh", ])

my_step = 5

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Starting EPM..."

  call(["/u0/Oracle/Middleware/user_projects/epmsystem1/bin/start.sh", ])

my_step = 6

if my_step >= STEP_NUM and my_step < STOP_NUM:

  print "Running step number %s" % my_step

  print "Installing APPS..."

  time.sleep(10)

  call(["/u0/automation/apps/createApps.sh", ])

print """

Installation Complete!

"""

Essentially buildEPMVirt is just a wrapper script that ties together a few other automation scripts and does some quick error checking. To get more details we need to look at all the automation scripts listed above. Let's start with extractFiles.sh.

extractFiles.py

The extractFiles.sh script simply calls extractFiles.py while redirecting the output for logging via the "tee" command. Most of the scripts in EPMVirt use the tee command like this to capture output to a log file while displaying the output on the screen.

#!/bin/sh
python /u0/automation/extractFiles.py  2>&1 | tee /u0/automation/extractFiles.log

The basic flow of extractFiles.py is:

Loop through a list of zip files and run the unzip command. 

If a file is not found it should throw an error. This  ensures you have all the files needed to run the EPM install. Finally, there is some ugly code to extract the Oracle DB files into a separate directory than the EPM files. Specifically, EPM is extracted to /u0/install/epm while the database installer is extracted to /u0/install/oracle_db.

 Full contents of extractFiles.py

import os
from subprocess import call
import sys
import getpass 

ISTESTING = False
required = ("linuxamd64_12102_database_se2_1of2.zip",
            "linuxamd64_12102_database_se2_2of2.zip",
            "Foundation-11124-linux64-Part1.zip",
            "Foundation-11124-linux64-Part2.zip",
            "Foundation-11124-Part3.zip",
            "Foundation-11124-linux64-Part4.zip",
            "Essbase-11124-linux64.zip",
            "Apps-11124-linux64.zip",
           )
base_dir = "/u0/install/downloads"
def check_downloads():
  for file in required:
    fpath = "%s/%s" % (base_dir, file)
    if not os.path.exists(fpath):
      if not ISTESTING:
        print "Error, required file not not found, %s" % fpath
        sys.exit()
    else:
      print "Found %s" % fpath
def extract(archive, dest):
  call(["unzip", "-o", "-d", dest, archive])

if not getpass.getuser() == "oracle":
  print "Error, you must be oracle user to run this script."
  sys.exit(-1)

check_downloads()
for file in required:
  fpath = "%s/%s" % (base_dir, file)
  if not os.path.exists(fpath):
    print "Not found, skipping... %s" % fpath
    continue
  epm_path = "/u0/install/epm"
  db_path = "/u0/install/oracle_db"
  if "-111" in os.path.basename(fpath):
    extract(fpath, epm_path)
  else:
    extract(fpath, db_path)
  # get rid of the original download to save space
  if not ISTESTING:
    os.remove(fpath);

Now that we have discussed the wrapper script, buildEPMVirt, and the extractFiles.py (step 1 from above) the next two blog posts will focus on the automated database install (step 2 from above) and the automation scripts for EPM (steps 3-7 from above). 

A Look Inside EPMVirt: The EPMVirt RPM Package

The EPMVirt RPM is what deploys all the custom automation scripts and OS configurations needed to perform the EPM installation.

An RPM consists of a .spec file, which contains the configuration details for the RPM. I

The rpm can  be built with the following command:

cd ~/rpmbuild/$VERSION/ 

rpmbuild -ba --define "_topdir $PWD" --clean SPECS/epmvirt.spec

Building will generate a new RPM package such as:

epmvirt-11.1.2.4.0-4.x86_64.rpm

The spec file is split into a few basic sections. The top contains basic info on the version and prerequisites for the package. 

The %pre section setups up the oracle user and dba group for the Oracle database installation. 

The main purpose of the RPM is to lay out the automation scripts needed to build EPM. The %install section lays out the files onto the user's system. The %files section defines the files that are part of the rpm. 

Finally, the post section kicks off the first automation script, which is desribed in more detail below.

The spec file contents look like this:

[root@localhost SPECS]# cat epmvirt.spec

Summary: Virtual Environment

Name: epmvirt

Version: 11.1.2.4.0

Release: 4

License: GPL

Group: Oracle EPM

URL: epm-errors.blogspot.com

Source0: %{name}-%{version}.tar.gz

BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-root

Requires: unixODBC, openssl098e, numactl, zip, emacs, unzip, xauth, xdpyinfo, compat-libcap1, libstdc++-devel, sysstat, gcc, gcc-c++, ksh, libaio, libaio-devel, lsof, /usr/lib/libg.a, /lib/libgcc_s.so.1, /usr/lib/libnss_compat.so, /usr/lib/libstdc++.so.5, /usr/lib/libstdc++.so.6, /usr/lib64/libnsl.so, /usr/lib64/libstdc++.so.5

%description

%prep

rm -rf $RPM_BUILD_ROOT

%setup -q 

%pre

groupadd -f dba

groupadd -f oinstall

id -u oracle &>/dev/null || useradd -G dba oracle

%build

%install 

# dirs

install -m 0755 -d $RPM_BUILD_ROOT/u0

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation

install -m 0755 -d $RPM_BUILD_ROOT/u0/install

install -m 0755 -d $RPM_BUILD_ROOT/u0/install/downloads

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation/root

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 -d $RPM_BUILD_ROOT/u0/automation/database

# files

install -m 0755 u0/automation/startAll.sh $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/startDB.sh $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/startDB.sql $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/root/start.sh $RPM_BUILD_ROOT/u0/automation/root

install -m 0755 u0/automation/root/start_main.sh $RPM_BUILD_ROOT/u0/automation/root

install -m 0755 u0/automation/root/startOver.sh $RPM_BUILD_ROOT/u0/automation/root

install -m 0755 u0/automation/database/db.rsp $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/ENV.env $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/init.ora $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/installDB_1.sql $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/install_DB_create_db.sql $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/install_DB_create_objects.sql $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/listener.ora $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/sqlnet.ora $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/tnsnames.ora $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/installDB.sh $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/installDB_main.sh $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/dropAll.sql $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/database/recreateDB.sh $RPM_BUILD_ROOT/u0/automation/database

install -m 0755 u0/automation/epm/EPMconfig_Essbase.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm//EPMconfig_Disc.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_CalcMgr.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_ReconfigWeb.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_RA.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_Profit.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_Plan.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_HFM.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_Foundation.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_FDMEE.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/EPMconfig_EssbaseStudio.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/silentInstall.xml $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/installAll.sh $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/installAll_main.sh $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/configAll.sh $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/configAll_main.sh $RPM_BUILD_ROOT/u0/automation/epm

install -m 0755 u0/automation/epm/boost_libs/libboost_date_time.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/libboost_filesystem.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/libboost_locale.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/libboost_regex.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/libboost_system.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/libboost_thread.so.1.48.0 $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/hfm_post_install_steps.sh $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/hfm_post_install_steps_helper.sh $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/epm/boost_libs/odbc.ini $RPM_BUILD_ROOT/u0/automation/epm/boost_libs

install -m 0755 u0/automation/apps/createApps.sh $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/insertPlanDS.sh $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/insertPlanDS.sql $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/loadPlanData.maxl $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/PLANDEMO.zip $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/RA.zip $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/pwgen.java $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/ImportHFM1.xml $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/HFM-COMMA4DIM.zip $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/HFMTuning.sh $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/apps/HFMTuning.sql $RPM_BUILD_ROOT/u0/automation/apps

install -m 0755 u0/automation/extractFiles.py  $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/extractFiles.sh  $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/buildEPMvirt.sh  $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/buildEPMvirt.py  $RPM_BUILD_ROOT/u0/automation

install -m 0755 u0/automation/restartEPM.sh  $RPM_BUILD_ROOT/u0/automation

%clean

%files

%defattr(-,oracle,dba,-)

%dir /u0

%dir /u0/automation

%dir /u0/install

%dir /u0/install/downloads

%dir /u0/automation/root

%dir /u0/automation/database

%dir /u0/automation/epm

%dir /u0/automation/epm/boost_libs

/u0/automation/startAll.sh

/u0/automation/startDB.sh

/u0/automation/startDB.sql

/u0/automation/root/start.sh

/u0/automation/root/start_main.sh

/u0/automation/root/startOver.sh

/u0/automation/database/db.rsp

/u0/automation/database/ENV.env

/u0/automation/database/init.ora

/u0/automation/database/installDB_1.sql

/u0/automation/database/install_DB_create_db.sql

/u0/automation/database/install_DB_create_objects.sql

/u0/automation/database/listener.ora

/u0/automation/database/sqlnet.ora

/u0/automation/database/tnsnames.ora

/u0/automation/database/installDB.sh

/u0/automation/database/installDB_main.sh

/u0/automation/database/dropAll.sql

/u0/automation/database/recreateDB.sh

/u0/automation/epm/EPMconfig_Essbase.xml

/u0/automation/epm/EPMconfig_Disc.xml

/u0/automation/epm/EPMconfig_CalcMgr.xml

/u0/automation/epm/EPMconfig_ReconfigWeb.xml

/u0/automation/epm/EPMconfig_RA.xml

/u0/automation/epm/EPMconfig_Profit.xml

/u0/automation/epm/EPMconfig_Plan.xml

/u0/automation/epm/EPMconfig_HFM.xml

/u0/automation/epm/EPMconfig_Foundation.xml

/u0/automation/epm/EPMconfig_FDMEE.xml

/u0/automation/epm/EPMconfig_EssbaseStudio.xml

/u0/automation/epm/silentInstall.xml

/u0/automation/epm/installAll.sh

/u0/automation/epm/installAll_main.sh

/u0/automation/epm/configAll.sh

/u0/automation/epm/configAll_main.sh

/u0/automation/epm/boost_libs/libboost_date_time.so.1.48.0 

/u0/automation/epm/boost_libs/libboost_filesystem.so.1.48.0

/u0/automation/epm/boost_libs/libboost_locale.so.1.48.0

/u0/automation/epm/boost_libs/libboost_regex.so.1.48.0 

/u0/automation/epm/boost_libs/libboost_system.so.1.48.0 

/u0/automation/epm/boost_libs/libboost_thread.so.1.48.0 

/u0/automation/epm/boost_libs/hfm_post_install_steps.sh

/u0/automation/epm/boost_libs/hfm_post_install_steps_helper.sh

/u0/automation/epm/boost_libs/odbc.ini

/u0/automation/apps/createApps.sh

/u0/automation/apps/insertPlanDS.sh

/u0/automation/apps/insertPlanDS.sql

/u0/automation/apps/loadPlanData.maxl

/u0/automation/apps/PLANDEMO.zip

/u0/automation/apps/RA.zip

/u0/automation/apps/pwgen.java

/u0/automation/apps/ImportHFM1.xml

/u0/automation/apps/HFM-COMMA4DIM.zip

/u0/automation/apps/HFMTuning.sh

/u0/automation/apps/HFMTuning.sql

/u0/automation/extractFiles.py

/u0/automation/extractFiles.sh

/u0/automation/buildEPMvirt.sh

/u0/automation/buildEPMvirt.py

/u0/automation/restartEPM.sh

%doc

%post

cd /u0/automation/root

./start.sh

%changelog

* Thu Oct 23 2014 root <root@localhost.localdomain> - 

- Initial build.

# Don't try fancy stuff like debuginfo, which is useless on binary-only

# packages. Don't strip binary too

# Be sure buildpolicy set to do nothing

%define        __spec_install_post %{nil}

%define          debug_package %{nil}

%define        __os_install_post %{_dbpath}/brp-compress

Finally,

cd /u0/automation/root

./start.sh

        kicks off start_main.sh with the log going to /u0/automation/root/start.log

#!/bin/bash
echo oracle | passwd oracle --stdin

mkdir /u0
mkdir /u0/install
chown -R oracle:dba /u0

# add sudo ability for oracle root.sh scripts
chmod 700 /etc/sudoers
echo "oracle ALL=NOPASSWD: /u0/app/oracle/product/12.1.0/dbhome_1/root.sh" >> /etc/sudoers
echo "oracle ALL=NOPASSWD: /u0/app/oraInventory/orainstRoot.sh" >> /etc/sudoers
# allow non-tty sudo for startup of oracle services as root in rc.local
perl -p -i -e 's/Defaults\s+requiretty/\#Defaults requiretty/g' /etc/sudoers
chmod 440 /etc/sudoers



echo "oracle soft nproc 2047" >> /etc/security/limits.conf
echo "oracle hard nproc 16384" >> /etc/security/limits.conf
echo "oracle soft nofile 1024" >> /etc/security/limits.conf
echo "oracle hard nofile 65536" >> /etc/security/limits.conf



echo "session required pam_limits.so" >> /etc/pam.d/login



echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmax = 2684329984" >> /etc/sysctl.conf

echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048576" >> /etc/sysctl.conf

/sbin/sysctl -p

echo "oracle soft nproc 2047" >> /etc/security/limits.conf
echo "oracle hard nproc 16384" >> /etc/security/limits.conf
echo "oracle soft nofile 4096" >> /etc/security/limits.conf
echo "oracle hard nofile 65536" >> /etc/security/limits.conf
echo "oracle soft stack 10240" >> /etc/security/limits.conf

echo "source /home/oracle/*.env" >> /home/oracle/.bashrc
echo "ulimit -n 10000" >> /home/oracle/.bashrc

echo "nohup sudo -u oracle -i /u0/automation/startAll.sh &" >> /etc/rc.local

# needed for WL admin server.
echo "127.0.0.1 epmvirt" >> /etc/hosts

chkconfig iptables off
service iptables stop
ln -s /u0/install/downloads/ /home/oracle/downloads

ln -s /u0/automation/buildEPMvirt.sh /bin/buildEPMvirt

What's going on? 

1. Give oracle user the ability to do sudo for running "root.sh" during the Oracle installation
2. Setup kernel parameters for Oracle and EPM
3. Add startAll.sh to rc.local file to start EPM on boot

The other important thing the RPM does is setup the file system structure under /u0 and give rights to the oracle user. At this point, the root user has given all the necessary security and rights to the oracle user to perform the remaining installs. The rest of the installation is done as the oracle user.

Now that we understand how the OS was installed, and the EPMVirt RPM, the next thing to understand is the actual automation scripts. These scripts perform all of the actual installation and configuration tasks. Details will come in the next post...