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.

Looking forward to Collaborate 2010

Collaborate 2010 will be held in Las Vegas April 18-April 22. Enclosed is a list of sessions not to be missed (although some not to be missed sessions do conflict, so be sure to split up coverage with your colleagues).

Sunday, April 18
10:00 – 11:50, Sessions 4656 and 4657, Database SIG, Michael Brown
10:00 – 11:50, Sessions 4660 and 4661, Fusion Middleware SIG, Raman Batra
3:00 – 4:50, Sessions 4679 and 4680, EBS Applications Technology SIG, Elke Phelps

• Enterprise Manager Change Management Pack, Angelo Rosado
• Oracle EBS Technology Stack Update and Roadmap, Steven Chan
• Oracle EBS Technology SIG Panel, Oracle EBS Technology SIG Board with Steven Chan

4:00 PM, Session 3597, Ten Steps to Success: Tips and Advice to Achieve Oracle EBS Certification, Mohan Dutt

Monday, April 19
8:00 AM, Session ID 3240, Thinking of Supporting or Extending Fusion Applications – An Update, Debra Liley
8:00 AM, Session 3656, Get Ready for EBS Release 12.1! Tasks to Complete Noew to Ease the R12.1 Upgrade Process, Sandra Vucinic
8:00 AM, Session 3299, Oracle E-Business Suite Technology Road Map and Vision: Release 12.1 and Beyond, Uma Prabhala
8:00 AM, Session 4360, Oracle BPEL 11g Training, Basheer Khan
10:45 AM, Session 3668, Migrations to Linux Panel, Sandra Vucinic
10:45 AM, Session 3842, 12.1.1 Architecture, Installation, and Cloning Best Practices, Max Arderius
10:45, Session 4408, Oracle Fusion Applications: Functional Overview, Basheer Khan
2:30 PM, Session 4777, The Latest on the E-Business Technology Roadmap, Steven Chan
2:30 PM, Session 4632, Oracle E-Business Suite Applications Strategy and General Manager Update, Cliff Godwin
3:45 PM, Session 4190, 11g Upgrade Essentials for Oracle E-Business Enviornments, Elke Phelps

Tuesday, April 20
8:00 AM, Session ID 3662, 10 Things You Can Do Today to Prepare for the Next Generation Applications, Nadia Bendjedou
10:30 AM, Session 4390, Oracle Fusion Applications Technical Overview, Basheer Khan
3:15 PM, Session 4698, Upgrade SIG Meeting, Sandra Vucinic
4:30 PM, Session 341, Tuning the Oracle Grid, Richard Niemiec

Wednesday, April 21
8:00 AM, Session 3634, Technology Essentials: Using the Latest Oracle Technologies with E-Business Suite, Lisa Parekh
9:15 AM, Session 346, The Best Oracle Database 11gR1 and 11gR2 New Features, Richard Niemiec
9:15 AM, Session 3663, Oracle E-Business Suite Cloning Panel, Sandra Vucinic
10:30 AM, Session ID 3323, How to Leverage the New E-Business Suite R12.1 Solutions without Upgrading your 11.5.10 Enviornment?, Nadia Bendjedou
4:00 PM, Session 70230, Understanding What Fusion Applications Means to You, Debra Liley
1:00 PM, Session 4180, Implementing an Advanced Architecture for Oracle E-Business Suite, Elke Phelps
2:15 PM, Session 4625, Advanced Technology Deployment Architectures for E-Business Suite, Steven Chan
4:00 PM, Session 3678, Leveraging Cloud Computing in an ERP Ecosystem, Michael Rulf
4:00 PM, Session 3594, Maximizing E-Business Suite Performance, Ahmed Alomari

Thursday, April 22
8:30 AM, Session 3445, Top Ten DBA Challenges in an EBS Environment, Srini Chavali
9:45 AM, Session 4232, Integrating OID/SSO with E-Business Suite and Third Party SSO Solutions, Norman Leach
12:15 PM, Session 3902, Successful Oracle Database 11g Upgrades with the E-Business Suite, Michael Brown

Table statistics being locked after exporting in 10g

Some unexpected activity was recently encountered while we were exporting data from a 10g version of a database into another database that was version 11g. We were moving data structures without moving the data. After doing so we were unable to analyze the tables in the target system. It turns out this is a common problem.

Table statistics get locked when exporting only the table structures with DataPump. This situation is identified as an issue that occurs with Oracle 10.2. Using DataPump data is not exported or imported if the option CONTENT = METADATA_ONLY is set.

To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.

2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.

REFERENCES

415081.1, DataPump Import Without Data Locks Table Statistics

Extra lines in controlfile to trace

The database command 'alter database backup controlfile to trace;' is a commonly used command for DBAs to make a backup of the database controlfile. This tracefile can be used in cloning or other activities to create a new controlfile as part of a fully automated process. Some users, however, have seen an issue with 'alter database backup controlfile to trace;' in an 11g (11.1.0.7 specifically) instance which can cause issues with any such automation.

ISSUE
'alter database backup controlfile to trace;' puts additional header lines in seemingly random locations in the trace file. An example of the line: *** 2010-03-06 14:24:42.720

SOLUTION
The reason for this issue is unknown. However, there is a pretty simple workaround. Rather than issuing only 'alter database backup controlfile to trace;', issue 'alter database backup controlfile to trace as ;' instead. This removes the header information and the issue has not been seen using the more exact syntax.

TIP
If you have any fully automated processes, such as cloning, make sure you fully test them out multiple times before rolling any changes, especially major ones such as database upgrades, to your production instance.

Subscribe to HRMS Notfications

APPS DBAs who support Oracle systems that run payroll are aware of numerous patching requirements for those instances. There are quarterly patches as well as several phases of year end patches which need to be applied in a timely manner.

Oracle provides an email list to notify customers when these patches are released. The email list is the best way to quickly receive information about these patches. An email will be sent from Oracle North American Payroll with a subject line similar to "ATTN: US & Canadian HRMS Customers: End of Year Phase 2 2009, US Q4 2009 and Year Begin 2010 Statutory Updates Released!" The body of the email will contain patch number information for different versions of the software. There will also be other sections in the email with important information for payroll customers.

To subscribe to the email distribution list described in this blog, send e-mail with the following:

To: cshrdev_uk@oracle.com
Subject: Oracle North American Payroll World Contact Update
Body: your contact name, CSI number, and company name

To ensure that information is received and acted on in a timely manner have multiple people subscribe to this distribution list. Functional users, lead developers, DBAs and managers should have subscriptions so that the information is available for the entire organization even if a key person is out of the office when the email is sent.

Don't forget to apply this patch when upgrading to 12.1.1 !

In a recent test upgrade from 11.5.10.2 (ATG RUP6) to R12.1.1, one of the issues we discovered is that users are unable to login after the upgrade due to an "invalid password" message. Resetting the password using FNDCPASS did not help. After logging an SR with Support and much troubleshooting, we discovered that an important pre-requisite patch had been overlooked.


The patch number is 8764069 and it needs to be applied in pre-install mode (see the patch README for detailed instructions). The cause of the issue and the fix that this patch delivers is found in these MOS Documents -


566521.1 - Oracle Application Object Library Release Notes, Release 12.1.1 (see Section 5)
457166.1 - FNDCPASS Utility New Feature: Enhance Security With Non-Reversible Hash Password (see highlighted Note #5 (in yellow) in the "Goal" section)


In essence, if you are on 11.5.10.2 ATG RUP6 or higher, and have migrated to using non-reversible hash passwords using the FNDCPASS USERMIGRATE functionality delivered in ATG RUP6, and are now migrating to R12.1.x, this patch needs to be applied before the upgrade.


Not doing so will, unfortunately, make the upgraded instance unusable - and there is no fix, other than to re-do the upgrade from scratch. MOS Docs 942600.1 (Post Rapid Wiz Install Check Fails On Login Page With RW-50016 After Upgrading From 11i or 12.0.x to 12.1.x) and 566521.1 indicate that this patch can be applied after the upgrade, but this did not remedy the situation in our case. We basically had to start with the upgrade process all over again.


This important pre-requisite is not currently documented in any of the R12.1.x upgrade guides.


REFERENCES


566521.1 - Oracle Application Object Library Release Notes, Release 12.1.1
457166.1 - FNDCPASS Utility New Feature: Enhance Security With Non-Reversible Hash Password
942600.1 - Post Rapid Wiz Install Check Fails On Login Page With RW-50016 After Upgrading From 11i or 12.0.x to 12.1.x)

EM Widget Review for EM Version 10.2.0.5

A cool feature has been developed by Oracle to run on top of Enterprise Manager. Desktop Widgets are available for download from Oracle.com. There are currently three widgets that can be downloaded. The widgets are developed on Adobe Air which allows them to run as lightweight internet applications.

The three widgets available are:
Target Search and Monitoring
High-Load Databases
Service Level Monitoring

Of these three I have found High-Load Database widget to be the most useful in my environment. This widget has a screen which can be flipped onto two sides. One side provides a bar graph summary of active sessions of the top five databases. The graph provided ties back to the performance screen in EM. The other side of the widget's screen shows recent ADDM findings. Using this widget will help the DBA develop a feel for the expected activity on the systems. When the load seems high or the ADDM findings show something odd, click on the database name to bring up a login screen in EM to direct to the performance tab of the target database.

Note that these widgets should be treated as a supplement to EM. They do not replace the metrics and automatic monitoring that EM provides. These are a secondary tool to assist with monitoring of the systems.

The widgets contain a customize menu option which will control refresh rate, display options, and other items. Download these widgets and try them out.

REFERENCES

OEM Widget Page

Advanced Patch Search Options With Oracle Support

With Oracle's Patch search there are several useful Advanced Search options which extend the flexibility of the support tool. This feature can be used to locate Oracle Application patches that meet a wide variety of criteria. I've used this feature several times to quickly find patches needed to resolve a specific problem.



This search feature is not available on the Patch Search section under the My Oracle Support Patches & Updates tab. The feature is listed in the Patching Quick Links section under the heading Advanced "Classic" Patch Search. In the Patching Quick Links section there are several other links that are useful for an APPS DBA. There are links for recommended patches and latest packs for both releases 11i and 12. Per Oracle's help screen the Classic Advanced Search feature will eventually be moved into the Patch Search section, but is currently unavailable from there.



Under Classic Advanced search the APPS DBA has the ability to search for patches that have specific file versions. This option has been useful to locate a patch which includes a certain version of a file. Sometimes Oracle Support notes will list file versions that resolve a known problem. If you do not have the latest file, the search feature will list the patches that contain that file.



Spend some time using the advanced patch search features and the Patching Quick Links. Developing an understanding of the options available will allow for quicker searches in the future. This can help lead to faster problem resolution.

Bug 8207550 Not Dropping FA_JOURNAL_INTERIM Tables

If you run the Create Journal Entries request set in 11.5.10.2, you may have some unneeded tables hanging around in your system. The code to drop the FA_JOURNAL_INTERIM table has been inadvertently commented out of FAXCJEB.pls, allowing the tables to remain after they are no longer needed.


ISSUE

BUG 8207550 FA_JOURNALS_INTERIM tables are not dropped after Create Journal Entry.


SYMPTOM

FA_JOURNALS_INTERIM tables remain present in the database after Create Journal Entries run.


SOLUTION

There is no patch currently available to resolve this issue. You may choose to allow the tables to remain in your system, or these tables can be safely removed after the completion of the Create Journal Entries run.


TIP

If you aren't currently doing so, you should consider implementing a check for newly created objects in your database. This can help you identify objects that are not properly cleaned up.


REFERENCES

757666.1, FA_JOURNALS_INTERIM Tables are Not Dropped after Successful Create Journal Entry and Journal Import

732925.1, Journal Import Fails After Depreciation Run. Depreciation Journal Is Unbalanced

Minimum Requirements for 11.5.10 Extended Support

While Premier Support for 11i ends in November 2010, Extended Support will pick up and be available for free until November 2011.

Current bug fixes will continue to be available for older patch levels. However, EBS customers must meet a minimum baseline patch level in order to receive any new bug fixes. The minimum baseline requirement is spelled out in:

883202.1 Minimum Baseline Patch Requirements for Extended Support on Oracle E-Business Suite 11.5.10

Please note that, per the doc, the baseline versions may change in the future and any such changes will be communicated in the document.

Bug 8819967 with JRE Plugin and R11i Oracle Sourcing

If you have migrated from JInitiator to the native SUN JRE Plugin and utlize the Oracle Sourcing module, you will need to apply this fix to your enviornment.

ISSUE
BUG 8819967 JINI 1.3.1.21 is hardcoded into applet

SYMPTOM
You've upgraded to use the SUN JRE Plugin, however, when using Oracle Sourcing, the application attempts to launch the JInitiator.

SOLTUION
Apply Patch 8819967
or
11.5.10 PRC RUP XIII

TIP
If you have not migrated to SUN JRE Plugin, please consider doing this. Note that JInitiator has been desupported.

R11i REFERENCE
290807.1, Deploying Sun JRE (Nativ Plug-in) for Windows Clients in Oracle E-Business Suite 11i

R12 REFERENCE
393931.1, Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12

Bug 8264899 with 11.1.0.7 and UTLPRP.SQL

If you have recently upgraded to Oracle RDBMS 11.1.0.7, you may have noticed issues while using UTLPRP.SQL. If you are considering an 11.1.0.7 upgrade, please add this patch to your deployment.

ISSUE
BUG 8264899

SYMPTOM
UTLPRP.SQL failes from ORA-00904: "FALSE"; INVALID IDENTIFIER

SOLUTION
Apply patch 8264899 to Oracle RDBMS 11.1.0.7

TIP
Details for using 11gR1 with Oracle R11i and R12 can be found on My Oracle Support in the enclosed articles:
R11i Reference
452783.1, Interoperability Notes, Oracle E-Business Suite Release 11i with Oracle Database 11g Release 1 (11.1.0)
R12 Reference
735276.1, Interoperability Notes, E-Business Suite R12 with Oracle Database 11gR1 (11.1.0)

APPS Schema security changes delivered with HR EOY Phase I patch 8947666 for R11i

As an APPS DBA, there are occurences where an application patch will do things you don't expect. That is why reviewing the readme document and performing regression testing is important.


Some odd behaviour was seen when applying the HR EOY Phase I patch (8947666) for 2009. A customization in place that required the APPS account to access a data dictionary table stopped working after the patch was applied. After a little research the issue was traced to some security changes Oracle bundled with the EOY Phase I patch.

The EOY Phase I patch includes a call to adrevdba.sql in the directory $AD_TOP/patch/115/sql. This script will revoke some grants from the APPS user. The specific commands run are:


revoke dba from apps;

revoke select any dictionary from apps;


These grants are not required for functioning of the application. The grants are being revoked by Oracle to help secure the application. If these grants used by any custom work, they will need to be granted after the patch has been applied. Future patches may also revoke the grants, so they will need to be monitored after patching to validate they still exist.

The custom code using these grants should be reviewed to determine if the grants are neccessary. Changes should be made to the code to work around the requirement for the grants. This will allow for a more secured application.