SQL Generating SQL to Remove EBS Online Patching Codelevel Checker Violations

The EBS Online Patching Database Compliance Checker, ADZDDBCC.sql, currently has 50 sections, section 1 through section 47, with sections 5.0 and 5.1, and sections 91 and 92. New sections are added as Oracle finds violations that can be checked reliably. There are some violations that are very difficult to check, such as user defined types, because there are almost unlimited variations of user defined types.

Oracle states in the ADZDDBCC.lst:

“Please do not attempt to correct violations in objects owned by Oracle.

P1 violations must be fixed before using the system or object.

P2 violations might not pose an immediate problem, but may cause later    issues if the affected objects are changed in a future online patch.

P3 violations are minor issues that can be deferred or ignored”

There are two different groups of violations, priority and compliance: P1, P2 and P3 priority violations and Minimal, Full and Internal compliance violations. You should fix all P1 and P2 compliance violations and all minimal priority violations. I usually fix both the minimal and full compliance violations, because in the future, as your development team better understands how to implement custom code, they may chose to create online patches for custom code.

“Minimal Compliance [minimal]

These checks represent the minimum requirement for correct operation of E-Business Suite Release 12.2.  Do not attempt to operate the system if there are P1 minimal compliance violations.  Custom code should pass the minimal compliance checks before being used in a Release 12.2 system.

Full Compliance [full]

These checks indicate whether an object can be patched using Online Patching.  Objects which do not meet full compliance may have limitations in how they can be patched, or may need to be patched using downtime patching.  Full compliance also requires that all minimal compliance checks are passed.  Custom code that will only be patched using downtime patching does not need to meet the full compliance level.”

“This report also implements a number of internal checks which verify that the upgrade and Online Patching Enablement process executed correctly. Violations of internal checks indicate upgrade failure and should be reviewed with support if the cause is not understood.”

The following is the header from section-15 from the ADZDDBCC.lst output from AADZDDBCC.sql. Even though this is a P3 priority violation, I usually fix this because of “clutter and confusion”.

**********************************************************************

DOC>  SECTION-15  [minimal]

DOC>  **********************************************************************

DOC>  “Synonym must point to an object.”

DOC>

DOC>   – P3: broken synonyms cause clutter and confusion.

DOC>   – Fix: Correct or drop these synonyms.

DOC>#

OWNER       SYNONYM_NAME           TABLE_OWNER      TABLE_NAME

However, there can be thousands of these broken synonyms. Most violations can be fixed with minor changes. However, because there are so many of these violations I chose to write SQL generating SQL that can be run to fix all the violations in one script. The following SQL script is one I created to fix this section:

select ‘drop synonym ‘||syn.owner||’.’||syn.synonym_name|| ‘;’

from  dba_synonyms syn

where syn.table_owner in

        ( select oracle_username from fnd_oracle_userid

          where  read_only_flag in (‘A’, ‘B’, ‘C’, ‘E’, ‘U’) )

  and not exists

        ( select obj.object_name

          from   dba_objects obj

          where  obj.owner       = syn.table_owner

            and  obj.object_name = syn.table_name )

  and not exists

        ( select 1

          from system.fnd_oracle_userid fou

             , fnd_product_installations fpi

             , ad_obsolete_objects aoo

          where fpi.application_id = aoo.application_id

            and fou.oracle_id = fpi.oracle_id

            and fou.oracle_username = syn.owner

            and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or

                 (aoo.object_name = syn.table_name   and aoo.object_type = ‘TABLE’)) )

order by 1, 2

I also create a script that will create all the synonyms, just in case:

select ‘create synonym ‘ ||syn.owner|| ‘.’ ||syn.synonym_name|| ‘ for  ‘ ||syn.table_owner|| ‘.’ ||syn.table_name || ‘;’

from  dba_synonyms syn

where syn.table_owner in

        ( select oracle_username from fnd_oracle_userid

          where  read_only_flag in (‘A’, ‘B’, ‘C’, ‘E’, ‘U’) )

  and not exists

        ( select obj.object_name

          from   dba_objects obj

          where  obj.owner       = syn.table_owner

            and  obj.object_name = syn.table_name )

  and not exists

        ( select 1

          from system.fnd_oracle_userid fou

             , fnd_product_installations fpi

             , ad_obsolete_objects aoo

          where fpi.application_id = aoo.application_id

            and fou.oracle_id = fpi.oracle_id

            and fou.oracle_username = syn.owner

            and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or

                 (aoo.object_name = syn.table_name   and aoo.object_type = ‘TABLE’)) )

order by syn.owner, syn.synonym_name;

Section-26 contains objects that are mostly Oracle seeded  objects, so no action is necessary. However, some objects are owned by custom schemas and these typically need to be fixed by your development team.

**********************************************************************

SECTION-26  [minimal]

**********************************************************************

“Query/DML statements must access tables via the APPS table synonym.”

– P2: These objects may operate incorrectly after the referenced table has been patched.

Fix:  Change the object to reference tables via the APPS table synonym.

Recent email asks, “Why not wait for 12.2 to upgrade from 11.5.10.2”

One of the important things to consider with 11.5.10.2 is support. I think Extended Support ends Nov 2013 and without support I’m sure your business will want to upgrade.

You could wait for 12.2, but I wouldn’t want to implement a product with a brand new application server, until a few bugs settle out. 12.2 will use Weblogics.

The 12.1 upgrade is a functional upgrade and the 12.2 is a tech stack upgrade, probably with 11.2.0.3 for the DB. The big deal is WLS and the administration of possibly much more complicated configurations.

At OOW, a development manager at Oracle said that all the platforms, Peoplesoft, JDEdwards all have to run on WLS before they can attempt a “fusion” of the products. This seems a bit obvious, but illustrates the fact that Oracle will be really pushing us hard to 12.2.

If you really want to take on the complexity of educating your users in a completely new way of accounting and trying to resolve new undocumented AS issues at the same time by doing a 12.2 upgrade, good luck.

I think the more sensible approach would be to upgrade to 12.1.3 and then 2 years later upgrade to 12.2. Since, it’s primarily a tech stack upgrade with bug fixes, the users should not need to be retrained.

The Big Picture of the R12.1.3 Upgrade

The Big Picture is an overview of the Release 12.1.3 upgrade process.
The number #1 reason to upgrade is to keep you’re My Oracle Support in compliance, followed by new feature adoption, and plans to use new business processes.

Oracle Support Compliance
Release 11.5.10.2 Support Milestones
Premium Support for E-Business Suite 11.5.10.2 ended November 30, 2010.
• First year of 11.5.10.2 EBS Extended Support fees are waived.
• Mandatory Patches – Minimum Baseline Patches for Extended Support 11.5.10.2 – After December 1, 2010 (Note 883202.1)
Release 12.0 Support Milestones
• February 1, 2012 – Premium Support Ends (R12.0.6) (845809.1)
Release 12.1 Support Milestones
• July 1, 2011 – You must apply R12.ATG_PF.B.delta.2 (R12.1.2 ATG RUP) (845809.1)
• February 1, 2012 – You must apply R12.ATG_PF.B.delta3 (R12.1.3 ATG RUP) (1066312.1)
• February 1, 2013 – You must upgrade to EBS R12.1.3

Why Not Wait for Fusion?
Nadia Benedjedou lays out short and long term goals for migrating to fusion:

The steps Nadia lays out to migrate to Fusion include:
  Implement OBIEE for Analytical Reporting
  Implement Identity Management
  Start using BI Publisher
  Use Enterprise Manager, if you’re not already, to manage more of the enterprise.
  Move your customizations to a Fusion compatible framework, JDeveloper and ADF.
  Centralize and Consolidate your data using Oracle’s Master Data Management.

The Upgrade is an Iterative Process, until all the issues are resolved and new functionality is working.

The Technical Upgrade is like Evolution – It’s an Iterative Approach
The faster you can iterate, the faster you can resolve problems and proceed to the production upgrade.
In general, the iterative cycle is as follows:
1. Follow the steps in the upgrade guide
2. Run patchsets.sh
3. Run Patch Wizard – • Patch Wizard Update for 11i (Patch 9803629)
4. Apply any new patches
5. Iterate back to Step 3 and run Patch Wizard, until there are no new patches.
The 12.0.4 and 12.0.6 Upgrade
We started with the upgrade to 12.0.4 from 11.5.10.2 and learned with each book we wrote. Our initial upgrades were based on VISION upgrades with base 11.5.10.2 patches. The same basic way Oracle initially tests the upgrade. We’ve done many technical upgrade assessments, functional assessments and customization assessments. The technical assessment is an important step to establish your patch levels for each module. If you want to continue receiving support from Oracle, then you’ll need to be patched with the Mandatory patches.

First Pass Upgrade to 12.1.1
Real customer data introduces new combinations of patch levels and possible data issues. This gave us greater insight into the upgrade issues. We then introduced the “First Pass” upgrade. We come on-site and upgrade your instance in 2 weeks. The whole assessment -1st pass upgrade usually takes 4-6 weeks, nt including customizations. This gives us many data points in our analysis of what patches are required. The 1st pass upgrade gives your functional analysts an instance to perform gap analysis and the developers an instance to see what customizations are broken.
The combination of assessments and 1st Pass upgrades help define the P3 Upgrade Methodology.
From the assessments and 1st pass upgrades we gathered new issues /solutions and developed an upgrade methodology that we use at every customer. The following picture shows the book covers for the project plan, the overview for managers and team members and the detailed, step-by-step instructions to complete the upgrade to R12.1.3.
With another release and more clients patch levels/issues, we’ve identified even more issues. The following are the pictures for the covers for the project plan, the overview and detailed upgrade guide.

Plan
Decision: re-implement or upgrade
Understand the hardware requirements and the upgrade path
Procure upgrade hardware
Purge unnecessary data
Train the functional super users in the new features of Release 12.1
Create an upgraded instance for gap analysis
Start with an assessment and a 1st pass upgrade.
Technical Assessment
Determine future capacity requirements, tech stack version compatibility and patch levels, including CPUs and PSUs. We review current issues from log files, unresolved service requests, and identify potential issues with the R12.1 Upgrade.

Architecture – Hardware Assessment
We review hardware configurations including options like: RAC vs SMP, Shared Application Tier with Distributed Processing, Parallel Concurrent Processing, SAN vs JBOD, RMAN vs Snapshots.
If the plan includes buying new hardware, consider migrating from the current 32-bit platform to a 64-bit platform.

Functional Assessment
The R12 Upgrade is not just a technical upgrade
The functional upgrade consists of mapping new business requirements with new functional features in R12.
Identify AS-IS Processes
Determine TO-BE Processes
Evaluate Potential Data Issues
Which New Features may replace customizations?
Estimate R12 New Features Training Needs
Recommend “Best Practices”

Example of Functional Issues
SQL> select count(*), country from ap_bank_branches group by country;
COUNT(*) COUNTRY
1081 (null)
112 US
3 NZ
226 CA
1 BB
2 GB
1 DE

The best solution is to have the AP functional superusers update the banks to their proper value. However, the following SQL statement can be used temporarily:
update ap_bank_branches set country=’US’ where country is null;
There are many other functional checks that are provided as a part of the functional assessment. We also provide a summary of functional new features for your installed modules.

Customization Assessment
CEMLI = Configurations, Extensions, Modifications, Localizations, and Integrations
The CEMLI Upgrade Assessment includes determining technical impact of Oracle E-Business Suite Release 12.1 on CEMLIs, upgrading CEMLIs to the new technology stack, retrofit of CEMLIs for compatibility and usability on Oracle E-Business Suite Release 12.1.
  Identify all customizations
  Check-in all customizations into configuration management
  Determine customizations that are replaced by new R12.1 functionality
  Re-Code customizations
  Prepare Customization Upgrade Implementation Plan
  Customization Configuration Management

Start by identifying all customizations. In some environments the customizations aren’t well documented and some customizations may have been lost due to previous patching. Check-in all customizations into configuration management. Customizations are easier to customize if you can find them and have some version control.
Determine the customizations that are replaced by new R12.1 functionality. This requires an analyst that knows the new functionality of R12.1 and understands the customizations.
Lastly, determine the customizations that need to be fixed or added to the upgrade to preserve or extend the process alignment.

Prepare
Train the Super Users and Technical Staff
Buy Hardware
Create Upgraded Instance for detailed gap analysis
Practice Testing
Practice Upgrades
Use the Maintenance Wizard (215527.1)
Step-by-step, graphical user interface for performing upgrade tasks
Consolidates instructions from multiple sources to present a comprehensive upgrade picture
Reduces upgrade tasks by filtering out those that do not apply to you (using TUMS)
Indicates critical patches that your system requires
Can automatically execute upgrade tasks for you
Run patchsets.sh

Use Patch Wizard from Oracle Application Manager (OAM)
We recommend that when you finish your upgrade to what you believe is the latest version of 12.1.3, with all the patches and Family Packs identified from patchsets.sh, this book, and your own research, you should run Patch Wizard again to see if additional patches are found.

Note that Patch Wizard may require patches for both Release 11i and Release 12 (9643141, 10629956).
If possible complete the following prior to the R12.1 upgrade weekend:
  Upgrade the Database to 11.2.0.2
  Migrate to OATM
  Install the R12.1.1 software
  Run Downtime Reducing steps
  Run pre-upgrade verification steps

Technical Upgrade – Details
R12.1 Upgrade Paths
Path A DB 9iR2, 10gR2 Apps 11.5.7 or 11.5.8
DB Upgrade & Apps Upgrade need to be completed during the same downtime window.
Path B If the DB already at 11gR1, Apps 11.5.9.2 or 11.5.10.2
Only upgrade the Apps Stack
Path C Upgrade the DB & Apps in different phases
If upgrading from a release prior to 11.5.7, the upgrade path may require an interim upgrade to Release 11.5.10.2. Because of the significant downtime required to upgrade from Release 11.0 to Release 12, it may be more feasible to first upgrade to Release 11.5.10.2 and then some time later upgrade to Release 12. This requires the functional users to learn Release 11.5.10.2, and perform all the testing for another upgrade. The amount of work necessary to perform two rounds of system acceptance testing may justify another day or two of downtime, so that the upgrade from Release 11.0 to Release 12.1 can be completed in one longer period of downtime.

Upgrade Paths
The light green circles indicate the most documented upgrade path from 11.5.10.2 to 12.1.1 and 12.1.3.

These bubbles show the upgrade paths. If your initial release is 11.0.3, you will need to upgrade to an interim release, 11.5.10.2, before you can upgrade to 12.1.1. The following chart lists the initial release, interim release and final release, with the associated patch number,
Initial Release                                Interim Release                  Final Release   R12 Patch
11.0, 11.5.1 – 11.5.6                       11.5.10 CU2                         12.0.0               4440000
11.5.7. 11.5.8, 11.5.9*, 11.5.10*                                                  12.0.0              4440000
11.5.7, 11.5.8, 11.5.9.2, 11.5.10.2                                                12.0.4              6394500
11.5.9*, 11.5.10*                                                                           12.1                  6678700
12.1.1                                                                                              12.1.2               7303033
12.1.1                                                                                              12.1.3               9239090
* includes CU1 and CU2 (consolidated update)
Figure 4 indicates that a direct upgrade path exists from Release 11.5.7 to Release 12.0.0.

The Applications Upgrade path is constrained by the database release. The following chart shows the application release and the database versions that are certified on Solaris. If the application release is 11.5.10.2, then you can upgrade the database to 11gR2 before the application upgrade, saving downtime during upgrade weekend, if you plan you use 11gR2, and you should always try to use the latest certified version of the database.
EBS Release                Certified Database Versions on Solaris
12.1                               10gR2, 11gR2 and the 64 bit versions
12.0                               10gR2, 11gR2 and the 64 bit versions
11.5.10.2                       10gR2 or 11gR2 and the 64 bit versions
11.5.9.2                         10gR2 and the 64 bit versions
11.5.7                             8.1.7, 9.0.1 9.2, and 9.2-64 bit

We can see that there is no certified database version that is certified with both Release 11.5.7 and Release 12.1. Therefore, we can’t do the database upgrade before the downtime window.
Overview of the R12.1 Technical Upgrade

The database upgrade is a bit more complicated if you’re running 9.2.0.6, because of Daylight Savings Time.
• The database installed by the 11.5.10.2 RapidWiz is Version 9.2.0.6. This database version does not support Daylight Savings Time (DST). Therefore, we have two choices:
• Upgrade the database to Version 9.2.0.8, which has support for DST, and then upgrade to Version 11.2.0.1, or

Perform the Upgrade
11i pre-reqs for Release 12.1.1
11870353, 5880762, 7477784, 7721754, 7828862, 8579398, 8757781, 8761881, 8798855, 8845395, 8908907, 8990356, 8991381, 9003549, 9053932, 9109247, 9128838, 9187813, 9288021, 9304675, 9442701, 9446543, 9476923, 9535311, 9685457, 9725579, 9747572, 9871422, 9889680, 3865683, 6408117, 8242248, 6024690, 4619025, 5368595, 5357791, 5970422, 59105548, 5194357, 5230979, 4396821, 5377946, 6741394, 6505416, 7418579, 4963569, 5259121, 4551977, 4607647, 6027561, 5760729, 5382135, 4699061, 6696828, 4582937, 4507073, 8340090, 4350832, 4563075, 4582937, 4607647, 4699061, 4939444, 4963569, 4969938, 5259121, 5382135, 6349338, 6351946, 6694260, 6696828, 8340090, 8487779, 10258309, 6264601, 3153717, 4252634
This list of patches continually changes. You should run Patch Wizard to determine any missing patches for your environment. When you’ve determined your patch list, cut and paste the patch list into the Patch Search form, on My Oracle Support.

Mike’s OOW Schedule

Here’s my schedule during Open World. During the breaks I could talk with R12.1 Upgrade customers and help answer R12.1 Upgrade questions. Otherwise, I’ll spend some time at “Closed World”, sponsored by Miracle AS and have some fun with the “Oakies”.

Oracle Business Intelligence Enterprise Edition 11g Architecture 10/2/2011 10:15 AM 10:45 AM Moscone West 2003 Mark Rittman
28360 — Upgrade SIG 10/2/2011 10:45 AM 11:45 AM Moscone West – 3004
08949 — Introduction to Oracle Buffer Cache Internals 10/2/2011 12:15 PM 1:15 PM Moscone West – 2006
28224 — Demantra SIG 10/2/2011 1:45 PM 2:45 PM Moscone West – 3002
06787 — Oracle Recovery Manager (Oracle RMAN) 11g New Features 10/2/2011 2:45 PM 3:45 PM Moscone West – 2006
28240 — Atlanta OAUG 10/2/2011 4:15 PM 5:00 PM Moscone West – 3007
32180 — Oracle OpenWorld Welcome Keynote 10/2/2011 5:30 PM 7:00 PM Moscone North – Hall D
32181 — Oracle OpenWorld Keynote: Oracle and EMC 10/3/2011 8:00 AM 9:45 AM Moscone North – Hall D

16800 — General Session: Oracle E-Business Suite: Vision, Strategy, and Roadmap 10/3/2011 11:00 AM 12:00 PM Moscone West – 3002/3004
08637 — Do I Upgrade or Go Straight to Oracle Fusion Applications? 10/3/2011 12:30 PM 1:30 PM Westin San Francisco – Stanford
08709 — Deployment Patterns for Oracle Fusion Middleware 11g 10/3/2011 2:00 PM 3:00 PM Moscone South – 310
06788 — Thinking Clearly About Skew 10/3/2011 3:30 PM 4:30 PM Moscone South – 302
18022 — Oracle Fusion Financials’ Coexistence with Oracle E-Business Suite 10/3/2011 5:00 PM 6:00 PM Moscone West – 3018
31800 — Oracle WebLogic Server JMS Support: Architecture/Performance/Tuning Essentials/Best Practices 10/3/2011 6:30 PM 7:15 PM Marriott Marquis – Salon 7
32980 — High-Performance Traffic Management with Oracle Fusion Middleware on Oracle Exalogic 10/3/2011 7:30 PM 8:15 PM Marriott Marquis – Salon 9

30000 — CERN Achieves Oracle Database Scalability and Performance by Building on NetApp 10/4/2011 10:15 AM 11:15 AM Novellus Theater
18582 — General Session: Oracle Fusion Applications—Overview, Strategy, and Roa… 10/4/2011 11:45 AM 12:45 PM Moscone West – 2002/2004
13643 — Real-World Performance: How Oracle Does It 10/4/2011 1:15 PM 2:15 PM Moscone South – 104
34180 — Can an ERP Upgrade Now Help Transform Your Business? 10/4/2011 4:00 PM 5:00 PM Novellus Theater
Personal Meeting — Arjen’s party DBVisit
Time: 4:30pm to 6:30pm
Location: Hotel Nikko
222 Mason St, San Francisco 10/4/2011 5:00 PM 5:30 PM
17244 — Best Practices for Oracle E-Business Suite Performance Tuning 10/4/2011 5:30 PM 6:30 PM Moscone West – 2014

17220 — Oracle Fusion Applications: Technology Essentials Overview 10/5/2011 10:00 AM 11:00 AM Moscone West – 2005
14862 — Best Practices for Maintaining Oracle Fusion Middleware and Oracle Exalogic 10/5/2011 11:30 AM 12:30 PM Moscone West – 2020
15940 — Oracle Identity Manager 11g: Lessons from a Customer Deployment 10/5/2011 1:00 PM 2:00 PM Moscone West – 3022

17680 — Secrets of Demand and Trade Management with Oracle’s Demantra Applications 10/6/2011 12:00 PM 1:00 PM Moscone West – 2008