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 ?