Exalytics, DR, EPM Multi-Instance Over Bare Metal, and

Tying it All Together

Mike Paladino Doug Fiedler

About Us

Business Intelligence

• Scorecards & Dashboards; Mobile Delivery

• Prepackaged Analytic Applications (OBIA)

• BI Foundation (OBIEE / Essbase)

• Financial & Operational Analytics

• Reporting & Publishing

• Visual Exploration

• Information Discovery (Endeca)

• Business Intelligence Cloud Service (BICS)

Financial Close & Consolidations

• Legal, Segment, & Mgmt Reporting

• Financial Performance & Close

• Optimization & Performance Lab

• Tax Provisioning

• Regulatory Requirements

Data Integration Services

• ERP Integration

• Financial Data Management

• Data Warehousing

• MDM & DRM

• ETL Services and Automation

 Infrastructure

• Hardware Sizing, Installation, & Configuration

• Exalytics Benchmarking

• System Monitoring, Backup, & Disaster Recovery

• Upgrade & Migrations 

Enterprise Planning & Analytics

• Strategic Planning (HSF) & Modeling

• Planning, Budgeting & Forecasting

• Workforce, Capital, & Project Financial Planning

• Public Sector Planning

• Cloud Services

Costing & Profitability Management

• HPCM Standard & Detail Models

• Waterfall Allocations

• Shared Service Charges

• Activity Based Costing

• Customer, Product, Segment & LOB Profitability

Advisory Services

• Project/Program Management & Process Change

• EPM & BI Road Maps

Training & Support Services

• Standard & Custom Training

• Virtual Classroom

• Multimedia and UPK

• Service Level Agreements

• Managed Services & Hosting

• Performance Engineering

Financial Close & Consolidations

BusinessIntelligence

EnterprisePlanning & Analytics

Data IntegrationServices

Training &Support Services

Advisory ServicesInfrastructure

Costing &Profitability

Mgmt

Our Services

The Sherwin-Williams Company

The Sherwin-Williams Company was founded by

Henry Sherwin and Edward Williams in 1866.

Today, we are global leader in the manufacture,

development, distribution and sale of coatings and related

products to professional, industrial, commercial and retail

customers.

The Company manufactures products under well-known brands such as Sherwin-Williams®, Dutch Boy®, Krylon®, Minwax®, Thompson’s® Water Seal® and many more. With global headquarters in Cleveland, Ohio, Sherwin-Williams® branded products are sold exclusively through more than 4,000 company-operated stores and facilities, while the Company’s other brands are sold through leading mass merchandisers, home centers, hardware stores, automotive retailers and industrial distributors.

www.sherwin-williams.com

The Sherwin-Williams Company Largest Producer of Paint & Coatings in US; among Top 3 worldwide

Founded in 1866 in Cleveland, OH

2016 marks 150th Anniversary

2013: $10.19 billion in sales

Business in 120+ Countries

34,000+ employees

4,000+ Company-Owned Stores

90+ Manufacturing Facilities Globally

A fully functional DR system● EPMA Deployments● Essbase Studio deployments● Planning● Essbase● FRS

RTO – 8 hours RPO – 24 Hours Use existing infrastructure No VMs

The Challenge

Backups● Obviously to support Production issues● To keep DR application in sync● Failsafe when switching to DR● Need

● RDBMS● Rman and datapump export

● Essbase● Export (level 0 or full of all apps)● Application metadata● Full ARBORPATH and MIDDLEWARE_HOME (in case of media failure)

● LCM● EPMA● Great for one off artifacts● Good to have after DR failover for insurance

Important Prerequisites

DR Solution Architecture

If you want to connect to Oracle RAC via infiniband, you cannot use OVM

You want to save the resource overhead required by the OVM hypervisor

Prevent oversubscription and performance issues associated with OVM

More consistent disk and network I/O performance It is an extra skill set and technology that needs to be

accounted for One less point of failure Allow more direct control over the environment (i.e. DR

Process)

Why use bare metal over Oracle VM?

Here are the standard ports and what we set for the ports for the 2nd instance:

How to change the ports for the 2nd instance

Most of the ports are weblogic-based and can be changed in the EPM config tool or in the weblogic admin console

R & A RMI ports are changed via the config tool Planning RMI port is tricky; it needs to be changed in the EPM registry and in the

following files: /oracle/Middleware/EPMSystem11R1/common/RMI/11.1.2.0/HyperionRMIRegistry

And in this file as well : /oracle/Middleware/user_projects/epmsystem1/RMI/HyperionRMIRegistry

Essbase studio ports are changed via the EPM registry OPMN ports are changed via the following file:

How to change the ports

Essbase Studio & EPMA Create EPM REG scripts to change hostname &

ports as part of the failover process

How to handle products that are not HA capable to work in an automated DR fashion

Run a registry report to find Essbase Studio values

Sample Script to change EPM registry for Essbase Studio

#Essbase Studio

#PRD

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@url "http://PROD_SERVER_NAME:12080/eas"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@transport.port "5300"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@server.httpPort "12080"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@ESSBASE_STUDIO_INSTANCE "/u01/app/oracle/product/EPM/Middleware/user_projects/epmsystem1/BPMS"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@server.datafile.dir "/u04/app/oracle/product/EPM/Middleware/prod/datafiles"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@instance_home "/u01/app/oracle/product/EPM/Middleware/user_projects/epmsystem1"

epmsys_registry updateproperty #a5963aff3ec60eda7cc94dbc148d6bb51baS789e/@localhost_name "PROD_SERVER_NAME"

How to Handle the RDBMS Oracle Data Guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for the Oracle DBMS through the creation and maintenance of a standby database which is a copy of the production database. If the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the production role.

Oracle Data Guard Setup

1. Configure all EPM Products to use the Data Guard URL1. Allows transparent connection to the Standby in a Fail over situation

2. JDBC URL for Hyperion when using Oracle DataGuard (Doc ID 1359130.1)

3. Bug 20729245 - ESSBASE DOES NOT START WITH DATA GUARD URL 

2. Create a Physical Standby1. Provides a physically identical copy of the primary database, with on disk database structures that are identical to the

primary database

2. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

1. http://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB00200

3. If using 12c apply this patch https://updates.oracle.com/Orion/Services/download?type=readme&aru=17704007

1. https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=3939865576206499&id=17877323&_afrWindowMode=0&_adf.ctrl-state=obgnx2qnt_224

1. ORA-600 [KRBI_INIT_FRA_METADATA_NOT_BACKUP_CTL] RMAN DUPLICATE FROM ACTIVE DATABASE with STANDBY as TARGET (Doc ID 1618470.1)

3. Create a Data Guard Broker Configuration1. Provides a management system to maintain and monitor the configuration and interactions between the primary

database and the standby

4. Monitor1. We use Grid control to alert us when there are issues with the standby.

Oracle Data Guard Switchover

Oracle Data Guard Switchover

We switchover to the standby database instance using Data Guard

● The instances switch roles in which the DR database becomes the primary and updates the standby database (which was PROD)

● In a true DR situation, this would be done as a failover because the PROD database would not be available

● Use dgmgrl

Oracle Data Guard Switchover

● In our case, we have to manually mount the standby instance; this is due to a bug with the local_listener parameter in the database which needs to be fixed

● srvctl start database -d <dbname> -o mount● Once that is done, archive log shipping resumes to

PROD (which is the standby now)

1. Nightly backups that: a) Back up essbase data

b) Back up essbase artifacts

c) Back up outline

2. Copy the files to the DR server using rsync

3. Sync the RM1/LCM directories using rsync

How to setup Essbase replication scripting

1. Restore the backups to the essbase servera) If a new database/application create it

b) Copy outline to the application

c) Copy the artifacts

d) Load data

We are using an rsync process to sync up a backup area and the RM1/LCM directories

rsync -avz source directory> user@server:<target directory>

A replicated NAS (such as NetApp) could use be used instead of rsync

How to configure replication of EPM shared directories

Create a friendly name in DNS like “hyperion.company.com”

This can then be controlled via changing the DNS pointer or by pointing it to a VIP in a load balancer

When the environment fails over to DR, it will be transparent to the end users

How to setup a dynamic host naming convention for failover & failback

We got it down to 8 easy steps!

1. Run stop.sh/.bat on Exa01, Exa02 2. Fail Data Guard instance to DR 3. Execute “sq_script_prod_to_DR.sql.bat” (changes Planning data

sources for Oracle & Essbase Host names) 4. Execute “switch_to_dr.bat” (makes necessary EPM registry changes for

non HA aware products like EPMA & Essbase Studio) 5. Flip Rsync Job from PROD -> DR to DR -> PROD 6. Fail Data Guard back to PROD 7. Run start.sh/.bat Exa01, Exa02 8. Manually change FR data source properties in EPM workspace (this is

stored in a binary file in the RM1 folder and thus cannot be scripted)

Failover & Failback Process

We had to manually reconfigure Shared Services connection using a registry edit command on failover and back

● In versions past, you can use a Data Guard URL● JDBC URL for Hyperion when using Oracle Data Guard (Doc ID

1359130.1)● This does not work with a RAC database and Data Guard and

the note above needs to be updated

Challenges Encountered

● Readme for 11.1.2.3.0 states:

● 16618139, 16515442 -- High Availability. When the Oracle Hyperion Foundation Services database is configured on an Oracle Real Application Clusters (RAC) database in which the JDBC URL connection string specifies more than one node of the RAC database, Essbase fails to start in failover mode.

● The SCAN listener work around also does not work if you want to include the Data Guard instance in the URL also, even though the note states you cannot configure 2 nodes in the URL (which is why you use SCAN), it also seems to apply to 2 different databases (which was allowed in prior releases)

Challenges Encountered

● Readme for 11.1.2.3.0 states:

● This will not work● jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_TIMEOUT=3)

(ADDRESS_LIST=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=PROD-SCAN)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=DR-SCAN)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=EPM)))

Challenges Encountered

● Issues making the LWAs work in DR● Had to update some ports not to use LWA for Calc

manager, Planning● EPMA Planning Application Deployment Hangs at 10%

Without Errors (Doc ID 1334738.1)● The hardware load balancer has not been configured to

accept loopback requests that come from the internal Oracle processes

● Issues making Essbase Studio start● startServer.sh & stopServer.sh scripts did not have

execute permissions; once I chmoded them, they worked. This appears to be a bug with the Linux installer/configurator

Challenges Encountered

● Issues making Essbase Studio start● Need to modify

$EPM_ORACLE_INSTANCE/config/starter/EssbaseStudio.properties

● > start.script/BPMS/bin/startServer.sh● env=JAVA_HOME\=/u01/app/oracle/product/EPM/

Middleware/jdk160_35/jre,JAVA_OPTIONS\=-DESSBASE_STUDIO_INSTANCE\=/u01/app/oracle/product/EPM/Middleware/user_projects/epmsystem1/BPMS –DsuppressAPSProductInf

● stop.script/BPMS/bin/stopServer.sh

Challenges Encountered

● Issues making Essbase Studio start● added the following highlighted lines to the

“server.properties” on xlytwv01; this caused Essbase Studio to override what is the EPM registry.

● transport.port=5410● catalog.url=oracle://scan-listener:1521/SERVICE● catalog.username=studio_schema● catalog.password=<encrypted value>● url=http://host:12190/eas● server.httpPort=12190

● Make sure to not use server.enableNativeAdministrator=true in server.properties; if you do, you won’t be able to login to the Essbase Studio Console.

Challenges Encountered

● Updating Hyperion Shared Services (HSS) Registry with Host Name for SHARED_SERVICES_PRODUCT/LOGICAL_WEB_APP Fails with Error "Multiple host components exist for host name" (Doc ID 825974.1)

Challenges Encountered

● RMI Port issue● check the HyperionRMI_Port.properties and the

registry; both are set to the custom RMI port of 11443● Also check:

$EPM_ORACLE_HOME/common/RMI/11.1.2.0/HyperionRMIRegistry

● Change port there also

● F5 LWA port and port 10080 must be opened to use LWA for EAS

● F5 timeout settings

Challenges Encountered

● Essbase subvars need to be loaded manually● This can be done by using the LCM backups from

production that are carried over to DR● Manually load through EAS export/import

● EPMA Applications can be deployed to DR System if needed after failover

● This can account for any inconsistencies in RDBMS to Essbase backup timings

● Aggregate Data● Test

Some things that may have to be manually done after failover

Questions?

Doug Fiedlerdfiedler@ranzal.comwww.ranzal.com

Mike PaladinoMichael.E.Paladino@sherwin.comhttp://www.sherwin-williams.com/

Come see us @ Booth 500!