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.