My Top 10 DBA Challenges in an EBS Environment

Over the past 12+ years of being an Apps DBA, I have had the pleasure of working with many versions of Apps, from 10.4 character to 10.7 SC, 11.0.3, and 11.5.5 thru 12.1.1. During this time, I faced many challenges in working with and rolling out these varied version of EBS. Some were relatively easy to overcome, while I still struggle to resolves others. Here is my "Top 10" list of these challenges so far. This list is in no particular order.

1. Keeping up with Apps patching - the one constant in the life of an Apps DBA is patches. It is best to first "test" apply a patch to a patch test instance that resembles production as closely as possible to verify that the patch applies cleanly and that it resolves the issue that required its application in the first place. If both the conditions are met, then it can be applied to other instances (development, test, QA/UAT, production etc) either manually, or by home grown scripts, or by Oracle tools such as OEM or 3rd party tools, after following the necessary change management process in your organization. If the patch does not apply cleanly, or it does not resolve the issue that required its application, further follow up with Support will be necessary.

2. Query access to instances - this includes both read-only access to the database as well as query only access to the application. This is a requirement that comes up very frequently in project implementations as well as production support scenarios. Oracle does not provide an out-of-the-box solution for EBS for either situation, and custom solutions need to be created in order to give this ability, whilst not jeopardizing any sensitive data in the database.

3. Learning continuously - One of the biggest challenges in the EBS arena is that the landscape is changing continuously. New tools and features are constantly being added, some without much warning or notice at all. Some examples include database features such as VPD, partitioning and compression, personalization features in Forms and self-service pages, new developer tools like JDeveloper/BPEL, XML Publisher Desktop and Workflow Builder, integrations like OID/Discoverer/SSO/OBIEE, user features like Web ADI etc. There is a constant challenge to understand these tools/features in-depth, to understand what advantages/drawbacks they have from a technical/DBA perspective and how they can be implemented in an efficient manner.

4. Customization standards - The one good thing about EBS is that it is very flexible and customization is very easy. The one bad thing about EBS is that it is very flexible and customization is very easy. The sword cuts both ways. There are a plethora of tools and features in EBS, and there are many ways in which these tools and features can be used. Oracle provides easy methods to personalize, customize and extend EBS in many ways. Organizations need standards (such as naming standards for custom objects etc) in these various arenas for long-term supportability and uniformity, and to ensure that these customizations survive upgrades. Oracle provides sparse to no standards at all, and it is up to customers to determine how best to define standards and use these tools/features. Defining standards is relatively simple and easy, but developing processes to enforce these standards can be tricky and daunting, given the wide variety of tools and features and the lack of a common framework in which to enforce the standards.

5. Cloning - This is another constant in an Apps DBAs life. There are constant requests to clone various environments. Cloning essentially is used to either create a new instance or refresh a target instance from a source instance – typically to troubleshoot an issue or to test a new feature using a copy of production data. Most challenges in this area relate to the volume/frequency of cloning and not to the clone process itself, which is very robust. Cloning is made somewhat challenging by the fact that instances are integrated with other technologies like OID/SSO/Discoverer and also by the fact that not all Apps modules/features are clone-friendly. The standard Oracle process used to perform cloning is RapidClone –this has evolved over the years to be a reliable and tested process. The majority of the time spent in this process is on copying the database files and the Apps binaries. There are some known caveats with this tool which are documented in the My Oracle Support RapidClone documents. These include – RapidClone only updates Site level profile options, it does not update some workflow information and instances with SSO/SSL configurations require extra manual steps as part of this process.

6. Keeping up with RUPs/PSUs/CPUs - Somewhat in line with the first challenge of patching is this one - trying to keep up with ATG RUPs and CPUs/PSUs that Oracle releases at regular intervals. These patches upgrade techstack features and/or fix security holes and/or provide other bug fixes. Very little, if any, of the end-user EBS functionality is affected. In a large shop like ours where there are many production instances that are expected to run 24x7, it is often difficult to justify downtime up to 4 times a year to apply these patches. All database CPU fixes are included in the PSU patches, which are safe to apply to any EBS instance. Database CPU patches are cumulative. Application CPU patches are cumulative for R12 but, until recently, were not cumulative for 11i, which was somewhat of a dis-incentive for those of us on 11i. Effective as of Jan 2010, 11i CPU patches are also cumulative going forward. The database CPU/PSU patches require little, if any, testing. Testing of the critical pieces of your functionality is recommended for ATG RUP patches to ensure that previous features continue to function as needed. It is best to combine application of the database and application patches in one bundle so that end users can test and verify that these patches do not affect functionality. It is best to apply ATG RUP patches and keep as current as possible – Support has published documents on My Oracle Support requiring customers to be either on the current or previous (N and N-1) release of ATG RUP level patches. Being on an older version of ATG may force an unexpected upgrade if an issue is found that is fixed in a later ATG RUP patch or if Support requires an upgrade to the N or N-1 release of the ATG RUP patch in order to troubleshoot your issue.

7. Performance tuning - The EBS landscape is vast and tuning opportunities abound. This challenge encompasses all types of tuning – database query tuning, forms and apache tuning, multi-tier tuning where apps services are distributed among multiple servers, integration tuning where Apps is integrated with other software like OID/SSO and Discoverer, tuning for custom code etc. Each of the various scenarios require different skills and tuning techniques, all of which the Apps DBA must be knowledgeable in. The solutions in each of these cases are as varied as the problems themselves.

8. Integrations - As the EBS application grows and matures, we are seeing that EBS is not a silo as it was in the past. Integrations with both Oracle and non-Oracle technologies and solutions are becoming a big part of the daily DBA life. Examples of such integrations are Discoverer, OID/SSO/OAM, Discoverer/OBIEE, SOA/Middlware, AIA/PIP/MDM, other ERP or legacy databases, third party software like Noetix, Loftware and Vertex etc. Apps DBAs need to have a good understanding of these technologies, their architectures, how they integrate with EBS and how to troubleshoot issues with these integrations – the nature and scope of Apps DBA duties is ever expanding. Given that all of these technologies have a release cycle independent of the EBS release cycle, DBAs need to keep up with the various integration certifications and de-certifications that are updated almost on a weekly basis to ensure that they keep current and understand the implications of these certifications and de-certifications.

9. Concurrent manager and workflow issues - Given the ubiquity of concurrent requests and workflows in today’s EBS environments, DBAs are constantly challenged to determine what the current status of a concurrent request or a workflow is, or why a concurrent request is not running, or why a workflow is “stuck”, along with trying to load balance and tune concurrent requests and queues, and figuring out why workflow notifications are note being sent/received. Out of the box, Oracle provides some standard managers and queues to run various concurrent requests. The settings on these queues like the number of threads, sleep time, workshifts etc will need to be fine tuned based on requirements in order to maximize batch requests throughput. Creating custom managers/queues to handle custom concurrent programs is also common. Incompatibility settings at the concurrent program level also need to be considered.
At the workflow level, Oracle also provides various queues out of the box to process various workflow transactions. These also can be tuned as per requirements. The notification mailers play a key role in sending out workflow notifications and need to be monitored and toubleshot constantly.

10. Managing expectations - This is another challenge that we face more often than not. It is somewhat related to keeping up with technology and patches, as Oracle Marketing does a good job of selling features that are either not completely available or do not work as expected. The reality of the features in the application or technology sometimes does not live up to the marketing fanfare. Users then have high expectations of the product or technology based on a demonstration that makes it look easy by hiding all of the complexity underlying that feature. The DBA team then has the unenvious job of leveling expectations and managing/tempering enthusiasm by painting a more realistic picture of the challenges that the product/technology/feature brings. The DBA team almost plays a referee-like role between the users and Oracle. Having a long and symbiotic relationship with Oracle helps alleviate some of these issues. Treating Oracle as a partner in your IT goals/challenges gives them a buy-in to help solve your company’s issues. When either of these is not possible, it is best to obtain very detailed technical/functional information about the product/technology/feature that your company is interested in – then conduct a proof-of-concept (POC) with specific product goals/features in mind, so as to document and verify that the product/feature truly meets your company needs and provides value.

Feedback on this post is most welcome. What are some of the challenges you have faced as an Apps DBA ?

Manage Workflow Notification Mailer in Test R11i Instances

Following a clone, be sure to validate the a test email address has been set for the Workflow Notification Mailer. Failing to do so, could result in issues where users receive emails that they may assume are from the production system. This will cause a lot of confusion amoung the users.

The Workflow Test Email address can be set outside of OAM. This is useful for automating the change. Oracle Support ID 459932.1. 'How To Set A Different "Test Email Address" For The Workflow Notification Without Connecting To OAM.' Describes the process. Be sure to run through the process manually in a test system to validate the steps. Add details on performing any such steps to your cloning documentation.

From within OAM you can perform the following steps to change the Test Email Address:

• At the OAM Dashboard, navigate to Workflow Manager.
• At Workflow Manager, select the Service Components link.
• At Service Components page, select the Workflow Notification Mailer link.
• From here, edit the mailer to set the Test Email Address to a valid email address for your organization.

When the steps have been completed, be sure to test the mailer to validate the email has been changed.

You may want to assign each test instance it's own email address. Having all instances send information to one addess will result in confusion if workflow related testing needs to be performed. Also, don't forget to regularly purge the emails from the test address. Failure to remove old emails can result in a large amount of wasted space on your email servers.

NEWS FLASH: Oracle R12 Applications DBA Field Guide Now Available

As readers of this blog, you may have been wondering if we were just a flash in the pan....I am here to state that we are not!  Our lack of posts in May - July were due to our activities with our latest project, the second edition of a book titled Oracle R12 Applications DBA Field Guide.

The OAUG will publish a press release this week about the guide.  I am enclosing the full press release in this post. 

Thanks for your patience with the blog. We will once again work towards providing you great tips to help you survive in the field.

Best Regards,
Elke


FOR IMMEDIATE RELEASE

NOW AVAILABLE: Oracle R12 Applications DBA Field Guide
CONTACT: Elke Phelps in partnership with the Oracle Applications Users Group (OAUG)
WEB SITE: See publications at www.coquitechandpress.com
EMAIL: services@coquitechandpress.com or Cindy Force, cforce@oaug.com

Dateline (September 1, 2010, Louisville, KY) --

Is your organization considering an upgrade to Oracle E-Business Suite (EBS) Release 12, or have you recently performed a new installation or upgrade to Oracle EBS R12? As an Oracle Applications DBA, you may find that managing Oracle EBS R12 is substantially different from its R11i predecessor. Administering Oracle EBS R12 can be made easier with the Oracle R12 Applications DBA Field Guide, a portable reference manual that is now available. The guide is brought to via the partnership between author Elke Phelps and the Oracle Applications Users Group (OAUG).

The Oracle R12 Applications DBA Field Guide is the second edition book authored by Elke Phelps. The new edition provides essentials for administering the updated Oracle EBS Applications tier and technology stack, including Oracle 10gAS and Oracle Database 11g. The book includes guidelines and references to guide you through the management and deployment of Oracle EBS R12. Included in the guide are topics ranging from architecture, configuration, installation, cloning, patching, monitoring, troubleshooting to performance tuning.

Excerpt from the foreword by Steven Chan, Oracle E-Business Suite Development, Oracle Corporation, http://blogs.oracle.com/stevenChan/

“In the first edition of the guide, the authors and reviewers managed to distill hundreds -- if not thousands -- of pages of the relevant Oracle manuals, release notes, ReadMes, Knowledge Base documents, and their own hard-won expertise, into a dense but remarkably-compact volume.

It wasn't comprehensive by any means, and didn't claim to be. That was its strength. It was able to see the forest for the trees. It touched on nearly everything important that a DBA would really need to know about administering an Oracle E-Business Suite environment without drowning the reader in unnecessary details…

…and it was small enough to fit into a laptop bag.

They have done an excellent job with this second edition, which has been refreshed and expanded without losing its concise and practical focus. You hold the product of many years of experience in your hands, one that I expect will save you countless hours of arduous research and painful experimentation.”

Even for the experienced database administrator, Oracle Applications are complicated to administer, and most documentation that is available is difficult to find and understand. Whether you're an experienced Oracle Applications DBA or new to Oracle Applications, (perhaps experienced with PeopleSoft, JD Edwards, or Siebel), this book will enable and ease the administration and efficiency of day-to-day tasks. For more information about the Oracle R12 Applications DBA Field Guide by Elke Phelps or to purchase the guide, go to www.coquitechandpress.com

About the Oracle Applications Users Group (OAUG®)
Founded in 1990, the Oracle Applications Users Group (OAUG) is the world’s largest knowledgebase for Oracle Applications users. The organization serves as an advocate to Oracle Corp. for companies worldwide. The OAUG provides users with education, networking and support via a wide range of activities and forums including conferences, publications, special interest groups and online communities. For more information about the OAUG, visit the website at oaug.org.

About the Author
Elke Phelps is an Oracle Certified Professional and Technical Architect focusing on Oracle Applications deployments. Elke's experience with Oracle Technology began in 1993. Her primary areas of expertise include Oracle Database and E-Business Suite deployments and upgrades, platform migrations and infrastructure design. Elke's advocacy of Oracle Technology through the publication of the first edition of the Oracle Applications DBA Field Guide (Apress, 2006), the publication of white papers, leadership of the Oracle E-Business Applications Technology Special Interest Group (SIG) and presentations at Oracle conferences since 2004 has earned her the designation of Oracle ACE in 2007 and Oracle ACE Director in 2009. She is a contributor to the blog hosted at http://www.fieldappsdba.com/.

Running Multiple Versions of JRE Plugin

It is possible to run multiple versions of the JRE plugin on different clients. The EBS is set to use one default value for the plugin for all clients. However, in some situations it may be required to run different versions for different clients. Maybe one part of the organization requires a laster version of the plugin for security purposes. Or some users could be testing a different version of the plugin before rolling it out to the rest of the users.

To allow some users to use a different version of the client, you need to complete a couple of steps.

First, you need to update the appsweb_${CONTEXT_FILE}.cfg file in $COMMON_TOP/html/bin to add a section for the JRE additional version you want to support.

There is also an autoconfig template file, appsweb.cfg, in $FND_TOP/admin/template/custom directory. This needs to be updated usig context variables in order to preserve your additions following AutoConfig runs.

Finally, for users who need to run the additional version of JRE, their ICX_FORMS_LAUNCHER profile value needs to be customized.

An example of these steps to update some users to JRE 1.6.0_17 is listed below:
1. Update appsweb_${CONTEXT_FILE}.cfg
[J16017]
connectMode=socket
serverPort=[your forms port]
sun_plugin_classid=clsid:CAFEEFAC-0016-0000-017-ABCDEFEDCBA
sun_plugin_version=1.6.0_17
sun_plugin_url=https://[your url]/OA_HTML/j2se16017.exe
sun_plugin_mimetype=application/x-java-applet;jpi-version=1.6.0_17
sun_plugin_legacy_lifecycle=false
plugin=jdk

2. In the template file, add the following.
[J16017]
connectMode=socket
serverPort=%s_formsport%
sun_plugin_classid=clsid:CAFEEFAC-0016-0000-017-ABCDEFEDCBA
sun_plugin_version=1.6.0_17
sun_plugin_url=%s_webentryurlprotocol%://%s_webentryhost%.%s_webentrydomain%:%s_active_webport%/OA_HTML/j2se16017.exe
sun_plugin_mimetype=application/x-java-applet;jpi-version=1.6.0_17
sun_plugin_legacy_lifecycle=false
plugin=jdk

3. Update user's profile value
fnd_profile.save('ICX_FORMS_LAUNCHER','https://[your url]/dev60cgi/f60cgi?config=J16017','USER',[userid]);

Download VM Templates for Oracle E-Business Suite

The VM Templates can be a little tricky to find on the Oracle web site. If you go to the Oracle E-Delivery web site, the VM Templates cannot be found directly on that site. If you need to download the VM Templates for E-Business Suite you can follow these procedures.

To download the templates you have to navigate to the Oracle Virtualization Downloads web site.
This is http://www.oracle.com/technetwork/topics/virtualization/downloads/index.html

From the Downloads tab, select the Oracle VM link.

This link will direct you to the Oracle E-Delivery Web site for Enterprise Linux and Oracle VM. In Media Pack Search screen, select the Oracle VM Templates Product Pack.

Choose the appropriate templates from this screen. For example, select the Oracle VM Templates for Oracle E-Business Suite Release 12.1.1 Vision Media Pack for x86 (32 bit) product that has eleven parts and 38G worth of data. Click Continue to progress to the screen with the 11 parts listed out. Select each part to download it.

Depending on your connection speed this can take quite a while. Be sure to track which parts you've downloaded so that you don't miss any of the parts. The last thing you want to do is unzip all of the files and begin the install only to realize that you did not download one of the files.

Enable Native Compiled PL/SQL with EBS

Performance benefits can be seen in some areas of the E-Business Suite by using Native Compilation of PL/SQL. With the 11g version of the database, setting up Native Compilation has become even easier

High level overview of steps to enable Native Compilation:
1) Set init parameter plsql_code_type='NATIVE'
2) restart DB in upgrade mode
3) As sys, run $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script with TRUE parameter (excludes package specifications) (NOTE this doesn't take much time)
4) restart DB in normal mode
5) run utlrp to recompile invalid objects (you will have a lot of them following #3)

You can run this script before #1 and after #6 to see that a lot of package bodies are now Native.
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;

REFERENCES

"Compiling PL/SQL Program Units for Native Execution" section of Chapter 12 of Oracle Database PL/SQL Language Reference 11g Release 1 (11.1). 

1086845.1, PLSQL Native Compilation (NCOMP) in 11g

757593.1, Oracle E-Business Suite Release 11i with Oracle Database 11g Release 1 (11.1.0.6)

802875.1, Interoperability Notes Oracle EBS Release 12.1 with Oracle Database 11gR1 (11.1.0)

Link to OID/SSO Presentation from COLLABORATE

Download or view the presentation Integrating OID/SSO with E-Business Suite and Third-Party SSP Solutions from COLLABORATE10.