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.

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.

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

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.

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.