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]);

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)

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.

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

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.