Log in

Menu

By Vivek Puri, The Sherwin Williams Company

This article focuses on Sherwin Williams’ experience of upgrading the Oracle E- Business Suite (EBS) Planning Application to R12.2. The Value Chain Planning (VCP) application includes globally available Advanced Supply Chain Planning (ASCP) and Demantra applications running on Oracle Engineered Systems.

We’ll examine the drivers for the adoption of EBS R12.2, our experience upgrading ASCP and Demantra to R12.2, the challenges faced with Demantra customizations, key tasks involved in the EBS 12.2 upgrade, lessons learned and the performance improvements of Advanced Supply Chain and Demand Planning processes. This article emphasizes leveraging the new capabilities of Online Patching in R12.2 and running the Demantra Analytical Engine and Collaborator Workbench on Linux using WebLogic.

The Sherwin Williams Company, founded in 1866 in Cleveland, OH, is the largest producer of paint and coatings in the U.S. and is among the top three worldwide. The company has businesses in more than 120 countries, with more than 90 manufacturing facilities, more than 4100 company stores and more than 47,000 employees. Sherwin Williams uses a wide range of Oracle technologies for different applications. Sherwin Williams uses EBS Financials, HR and Benefits, EAM, Property Manager, Supply Chain, Manufacturing and Planning modules.

About EBS R12.2

EBS R12.2, the latest release from Oracle, came with a breakthrough in application patching with the Online Patching technology, which improves the high availability of business applications by reducing the maintenance downtimes significantly. This release also has a lot of other product features, including mobility, which helps enhance user experience. There are a number of application performance improvements built in to this release, helping business processes to improve productivity.

Application Architecture on Oracle Engineered Systems Platform

Oracle’s Engineered System is a complete hardware and software platform for enterprise applications and databases. It is pre-integrated (integrated compute, storage and network) and built as a single, factory assembled unit. The hardware and software are tested, tuned and optimized, making it a reliable, scalable and well performing platform.

Engineered Systems are purpose-built systems for enhanced database and application performance. Engineered Systems provide a centralized platform to consolidate Oracle Apps, Fusion Middleware and databases. Customers can benefit from the single-vendor support, standard configuration and patches tested on identical configuration. Oracle provides Platinum Services for these systems, which includes monitoring and patching services. These systems are tested comprehensively for high reliability to run large workloads, including Oracle Enterprise Monitoring (OEM) monitoring for all layers (hardware and software) and Oracle Virtual Machine (OVM) templates. Patching is comprehensive, involving firmware, OS, Clusterware, Databases and Virtualization.

The EBS R12.2 application architecture is based on Maximum Availability Architecture (MAA) guidelines, where each tier is highly available and provides the fault tolerance to the business application. Our architecture has a two-node RAC database, two-node internal Web/Forms tier, two-node Concurrent/Admin tier, two-node Collaborator Workbench tier and two-node Analytical Engine tier. All the application tiers are front ended with hardware load balancer, whereas the database tier uses the scan listener approach for load balancing. The EBS architecture involves single Appl Top for internal and external application tiers so that maintenance efforts and time can be kept to a minimum.

E-Business Suite Technical Challenges

Managing a large EBS implementation has its own set of needs and challenges:

  • Conducting regular patching with minimal downtime impact, which gets difficult with tight maintenance windows.
  • Backing up applications and database regularly without impacting performance.
  • Accommodating frequent clone requests to support development and project initiatives.
  • Scaling with increase in workload (users/functionality/data volume).
  • Creating new instances with a quick turnaround (integration challenges with SOA, BI, custom/third-party applications).
  • Running mixed workloads, Reports, Forms, Web Pages, Interfaces, which involves different performance characteristics of different workloads.
  • Managing performance tuning that involves several moving parts in a complex architecture with multiple services running on multiple tiers.
  • Maintaining high availability of the architecture for business continuity and the ability to handle disaster and component failure.
  • Managing instance/infrastructure sprawl, including several supporting instances to support project and development lifecycle.

Handling these Challenges

A probable Solution to these challenges is multifold:

  • Technical folks need to know the workload and architect a system that is performing, scalable and stable.
  • Design the solution to meet the availability and performance objectives set by the business.
  • Develop and standardize business and technical processes.

At Sherwin Williams, we handled these challenges by deploying EBS (ERP, ASCP, Demantra) and BI databases on Exadata; strategized the database platform; and virtualized the Application Tier for EBS on Exalogic platform. We also developed and standardized various business and technical processes.

We were able to leverage the rolling patching capabilities of Engineered Systems to reduce the patching time. The performance of these systems enabled us to run the mixed workload seamlessly. The system was able to scale very well and was able to absorb the increasing demand for processing capacity for business processes resulting from new rollouts. Engineered Systems significantly improved the provisioning time for new environments, including build, cloning, backup and recovery.

R12.2 Upgrade Drivers and Approach

Our strategic direction was to minimize maintenance downtime for the EBS application with improvements in the technology platform, application architecture and processes. This was one of the important drivers for our upgrade to R12.2. With the upgrade, we were able to leverage the R12.2 Online Patching feature to cut patching down to a very small window, which remained the same irrespective of the patch size, including large patches for the upgrades.

Another compelling reason to go for the R12.2 upgrade was the performance improvements in R12.2. Getting to R12.2 enabled us to utilize new features, e.g., Demantra Calendar Month in Weekly Systems, Import from File, Multi-Language Support and Demantra AnyWhere Improvements – Embedded Worksheet, etc.

On the ERP side, the application of RPC 1 for Manufacturing and the Oracle Warehouse Management System (WMS) warrants VCP to be upgraded to 7.3.1.5 or R12.2. The R12.2 upgrade was combined with a platform migration to Engineered Systems, Exadata and Exalogic. To handle the limitation with Demantra schema editioning, customization was rewritten to align with the new R12.2 framework. As a part of this upgrade, the database was migrated from Exadata X2 to Exadata X4, and AppsTier was migrated from Commodity H/W to Virtualized Exalogic X3. We upgraded the one VCP to R12.2.3 in Aug 2014 and other to R12.2.4 in Sept 2015.

Drivers for R12.2

  • Strategy to minimize maintenance downtime; leverage R12.2 Online Patching.
  • RPC1 on ERP environment for Manufacturing and WMS, warrants VCP to be upgraded to 7.3.1.5 or R12.2.
  • Performance improvements in R12.2.
  • Getting to R12.2 enables us to utilize new features:
    • Calendar Month in Weekly Systems.
    • Import from File.
    • Multi-Language Support – Demantra.
    • Demantra AnyWhere Improvements – Embedded Worksheet.

VCP R12.2 Upgrade

Preparation of the database tier for the R12.2 upgrade involved applying the Exadata bundle patch and other pre-requisite patches and the creation of the upgrade file system. The other prerequisites included applying the consolidated upgrade patch, compiling apps schema and applying other pre-upgrade patches. The next steps involved generating configuration files, running autoconfig, configuring the upgraded R12.2.0 instance and applying the Online Patching reporting patch. The next phase of upgrade was Online Patching enablement, which included application of Online Patching enablement, AD and TXK patches.

Online Patching Enablement

  • Grant execute on sys.XDB_MIGRATESCHEMA to apps.
  • GRANT SELECT, UPDATE, DELETE INSERT on sys.xdb$moveSchemaTab to apps.
  • Apply Online Patching enablement patch.
  • Revoke EXEMPT ACCESS POLICY from system.
  • Apply latest AD patches.
    • adop phase=apply patches=18283295,20034256,20208298 hotpatch=yes merge=yes
  • Apply latest TXK patches.
    • adop phase=apply patches=18288881,20043910,19803392,20158287 hotpatch= yes merge=yes

After the application upgrade to R12.2 base release (R12.2.0), the upgrade driver for R12.2.4 was started using adop utility. As the VCP has been implemented in a distributed architecture, we had to perform some specific tasks, including applying VCP cumulative patch (12.2.4.1), applying the VCP 12.1.3.9.1 cumulative patch to the R12.1.3 ERP source instance and performing other functional configurations.

The post 12.2.4 upgrade tasks included applying patches to 10.2.3 Forms and Reports, 11.1.6 FMW WebTier and NLS configuration. The other finishing tasks were to update and verify custom responsibilities, migrate the CUSTOM library, grant flexfield value set access to specific users, review user responsibility assignments and complete product-specific tasks for supply chain management and system maintenance.

Demantra R12.2 Upgrade

The most important part of the Demantra upgrade was to convert Demantra schema to non-editioned schema, which involved exporting Demantra schema using datapump, dropping DB user (msdem) and creating the user with editioning disabled, and importing the Demantra schema using datapump.

Before the Demantra install on the Windows server, which hosts Admin utilities, database connectivity was tested from Admin Server and the Registry setting for database host, instance details and Demantra install were verified. Demantra 12.2.4 was installed with options for Demantra Web Application on Unix, Demantra Analytical Engine on Linux, DB Schema on Exadata, Middle Tier on Exalogic and multi-language support.

Demantra R12.2 Upgrade

Convert Demantra Shema to Non-Editioned Schema

  • Backup DB user (msdem) creation script and remove ‘enable editioning.’
  • Export the demantra schema using datapump.
  • Drop DB user (msdem) and create the user with editioning disabled.
  • Import the demantra schema using datapump.
  • Verify tablespaces with 16k block size for demantra.

Prep Tasks for Admin Server

  • Test database connectivity from Admin Server
  • Verify the Registry setting for database host, instance details and demantra install.

Demantra Collaborator Workbench and Analytical Engine were installed and configured using Weblogic Servers running on Oracle Linux. Demantra patching works a little differently in comparison to the core EBS application patching; it works as an installer on Windows and requires the Collaborator Workbench and Analytical Engine to be redeployed.

Oracle Apps HA Architecture

Upgrading Arch Diagram

Benefits from Upgrading EBS to R12.2

The R12.2 upgrade has enabled the new features, like import/export from Excel to Demantra (which assists with new product introduction) and allows sales force visibility to forecast/objectives in Demantra Anywhere. The flexibility with Sales Calendar improvements allows us to move to weekly buckets. The overall business user experience has been enhanced with the new release.

Upgrade Benefits

  • Import/Export from Excel to Demantra
    • Assists with new product introduction.
  • Performance Improvements
    • Collection, Engine Run and Worksheets.
  • Demantra AnyWhere
    • Allows sales force visibility to forecast/objectives.
  • Multi-Language Support for Demantra
  • Sales Calendar
    • Moved to monthly buckets – Earlier; Plan is to move to weekly.
  • End-User Experience – Performance.

There are performance improvements with the upgraded VCP application to R12.2 and migrating the Apps Tier to Engineered Systems. The performance improvements include reduced runtime for collection processes and Analytical Engine, improvements in Memory Based Planner and Demantra Worksheets processing.

Performance Comparison Matrix

Test Case
Old Env.
Engineered Systems
Collections (Planning Data Collection Report Set) 2 hours 45 mins
Collect Booking and Shipping History 2 hours 15 mins 1 hour 50 mins
EBS FullDownload 1 hour 7 mins 45 mins
Demantra: Collect Supplementary Data (Report Set) 2 hours 15 mins 3.5 hours
Analytical Engine Run 5 hours 37 mins 1 hour
Memory Based Planner    
  • Country-1 ASCP Plan
2 hours 11 mins 25 mins
  • Country-2 ASCP Plan
2 hours 15 mins 25 mins
  • Country-3 ASCP Plan
62 mins 11 mins
  • Country-4 ASCP Plan – I
40 mins 7 mins
  • Country-4 ASCP Plan — II
38 mins 7 mins

Performance Comparison Matrix

Test Case
Old Env.
Engineered Systems
Worksheet Performance    
  • Forecast Cycle week 1
3 hours 40 mins
  • OU Level Data
6:22 min 7 secs
  • Worksheet 1 – Brand Level data
7:10 min 45 sec
  • Worksheet 2 – Brand Level data
5:36 min 34 sec
  • Worksheet 3 – Brand Level data
4:44 min 5 sec
  • Worksheet 4 – Sub-brand Level data
4:58 min 4 sec
Publish Forecast to ASCP/Forecast upload 5 hours 2 ½ hours

Lessons Learned

Enterprises should allow reasonable time for implementing an R12.2 upgrade. There are some limitations with R12.2 release on the Demantra application side:

  • It does not support Database Editioning for Demantra schema.
  • Clone issues with R12.2, specifically on the WebLogic stack, are a big concern.
  • The application is very stable and performing very well after the upgrade, but the cloning process has bugs.
  • R12.2 upgrade patches were constantly changing during our upgrade cycle, which was very inconvenient.
  • Lack of guidelines for developers for VCP customization brought in some challenges; a white paper on it would have been nice.

Issues Faced

  • Online Patching enablement issues.
  • Autoconfig failing on 12.2.3 instance.
  • Issues with patches for upgrade and one-off fixes.
  • Disable editioning on MSDEM schema in the VCP instance.
  • Error when trying to configure second app tier for 12.2.3.
  • Patching using adop on remote node fails.
  • After the clone, adstpall fails and then adstrtal cannot be use to startup the services.
  • When attempting to clone patch filesystem, rapidclone fails.
  • After the upgrade the EBS environment to 12.2.3, the login page personalization does not take effect.
  • adcfgclone fails when source and target instances are on the same servers.

Lessons Learned / Takeaways

  • Lack of Database Editioning for Demantra Schema.
  • Clone issues with R12.2 (WebLogic stack).
  • Lack of R12.2 experience in Oracle Support team (Product team helped with a lot of things).
  • R12.2 Upgrade patches were constantly changing.
  • Lack of guidelines for developers for VCP customization; a white paper would be nice.
  • Demantra – Icon issue with Export/Import, Demantra AnyWhere.
  • Give yourself reasonable time while taking R12.2 Upgrade.

Conclusion

The Sherwin Williams Company has made a commitment to the Oracle Engineered Systems platform. Adopting EBS R12.2 is a significant step toward meeting the strategic goal of minimizing maintenance downtime for business applications.

We had success with our upgrade, and Oracle has worked with us for the successful implementation. A careful evaluation and architecture design led to formulating the strategy of using Engineered Systems as a platform for Database Tier and Application Tier for EBS.

Author’s Bio

Vivek Puri, Manager – Database Administration and Engineered Systems at Sherwin-Williams, has more than 20 years of experience working with Oracle technologies, including Oracle Databases and E-Business Suite and related technologies. Vivek is a member of the Oracle EBS ATG and Exologic Customer Advisory Boards. He has a Master’s degree in Computer Science. Contact Vivek at .

VivekPuri Profile Pic2