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)