Oracle EBS 12.2.

7 Upgrade:
Lessons Learned

Presented by the City of St. Petersburg

Rob Sipko
Oracle Applications Developer
Procure to Pay Support: Purchasing (PO), Oracle iProcurement (ICX), Payables (SQLAP), Order Entry (OE), Inventory (INV), iSupplier Portal (POS), Sourcing (PON)
Takeaways from Presentation

For developers (and DBAs), the upgrade to 12.2 requires significant research and understanding of
new technologies and concepts that have not been present in past versions of EBS.

The upgrade from 11.5.10 to 12.1.3 required a lot of technical effort using the skills a developer /
DBA already possessed. The upgrade from 12.1.3 to 12.2.7 did not have as much effort, but
required significant research and learning of new technologies.

Even if you are months / year+ from beginning an to upgrade to 12.2, there are
steps you can take in your 12.1 environment today that will make life easier when
it is time to upgrade.
 A Problem with Older Versions of EBS…

Problem: The system is down during patching.

Solution: Online Patching.

The Price you Pay: A change in organizational

culture in your technology department.
Technology Stack Overview in 12.1
Where Oracle’s code & your customizations reside

PL/SQL
Forms,
Reports,
UNIX Scripts

OAF / Java Tables

Online Patching
ADOP: AD Online Patching

 ADOP replaces ADPATCH as the

12.2 patching tool.

 ADOP orchestrates the patching

cycle, steps the user through
each of the patching phases:
 – Prepare
 – Apply
 – Finalize
 – Cutover
 – Cleanup
Online Patching
ADOP: AD Online Patching
How is Online Patching Possible in the Code?
Application Tier  There are 2 file systems:
FS-1 FS-2
Application Tier
Just Kidding. There are 3 File Systems
 There are 2 3 file systems:
FS-1 FS-2 FS-NE
 FS-NE (Non Editioned file system)
 Stores data that is stored on
the file system.
- Data import and Export
files
- Report output
- Log files
Non Editioned File System - Etc.
 If you have custom code that writes data to a
directory under $APPL_TOP you will have to
modify it to write to the non-editioned file system.
Database
11G R2 Edition-Based Redefinition (EBR)
 Allows for online updating of database
objects
 Provides an isolation mechanism (“Edition”)
that allows pre-upgrade and post-upgrade
schemas to co-exist
 With EBR, you can have two objects with the
Package XXTEST
same name, as long as they are in different Procedure do_something;
Editions
 The following types of objects are
editionable: Package XXTEST
Function, Package, Package Body, Procedure do_something;
Procedure, Trigger, View, Synonym, Library, Procedure do_something_else;

Type, Type Body

 When you create a new edition, all
editionable objects are inherited from the
previous edition. When you alter one of the
objects in the new edition you will stop the
inheritance of that object ALTER SESSION SET EDITION V_20171129_1315;

Not all objects can be editioned: most notably,

ALTER DATABASE DEFAULT EDITION = V_20180108_0929

this includes transactional data, of which there

is only ever one copy.
Database
Edition-Based Redefinition (EBR) & Online
Patching
Oracle E-Business Suite Release 12.2
introduces a new concept, the logical
view of the data model. This is
implemented via synonyms and editioning
views, which isolate the running
application from changes to the data
model that may be introduced by a patch.
Database
Edition-Based Redefinition
(EBR) & Online Patching
Editioning views can be
thought of as providing
a cover layer, or logical
representation of the data,
on top of the physical
representation. All code
(Oracle E-Business Suite,
custom, or third-party) must
access Oracle E-Business
Suite data via the cover
layer: accessing the data
model via the physical
layer may result in obsolete
data been returned.

Crossedition Triggers sync the data during the online

patching process
1: Decision Point: Development Standards for Custom Code

Before you start the upgrade process. Determine if you want your customizations to
follow Minimal or Full levels.
2: Writing files to $APPL_TOP Directories

If you have custom code that writes data to a directory under $APPL_TOP you will
have to modify it to write to the non-editioned file system.

Non Editioned File System

3: Custom Code & Table Access

All of your custom code must reference EBS Tables through the APPS schema.
4: Code Naming Standards for Online Patching
In order for Online Patching to automatically manage derived objects the following
rules have to be in place:
5: Utilities are available for the analysis of your pre-upgrade
system

These Utilities are scripts and are available as standalone patches for 11i, 12.0, 12.1 and 12.2 –
Note:1531121.1 Using the Online Patching Readiness Reports in Oracle E-Business Suite Release 12.2
6: Online Patching Standards Compliance Report – Part 1
ADZDDBCC.sql for DATABASE

Note: If you run this in a 12.1 environment note that all seeded objects will
automatically be fixed in the upgrade to 12.2. You only need to focus on custom
objects.
7: Online Patching Standards Compliance Report – Part 2
gscc.pl for Application Tier

Note: We did not have much luck getting useful results from gscc.pl. Instead we
opened up every custom object on the file system looking for non APPS synonym
access to tables and fixed them accordingly.
8: Online Patching Readiness Reports
9: Custom Oracle Applications and Schemas – Part 1
Two types of custom schemas:

1. Tied to a custom EBS Application. XXSPAP

(Custom Oracle Payables)
2. Not tied to a custom EBS Application. XXCUSTOM

If you need to register a custom schema use

API: fnd_oracle_user_pkg.load_row

Bug: Needed a script (not API) to then make

install_group_num = 0 for custom schemas in
table fnd_oracle_userid
10: Custom Oracle Applications and Schemas – Part 2

New Applications need to be

registered using: ADSPLICE

Obsolete Form: Read Only in 12.2

 11: Preparedness Strategy Summary:
1. Research and drop obsolete schemas

2. Register custom EBS application schemas properly

3. Register custom Non-EBS application schemas properly that we wanted to become editioned schemas in
12.2

4. For each schema registered, reset password using FNDCPASS

5. Online Patching Enablement Readiness Report. This report should show us a list of schemas.
Run ADZDPSUM.sql as SYSTEM

6. Non-editioned objects that depend on editioned objects report. Required for

minimal compliance.

Run ADZDPMAN.sql as SYSTEM

7. Database Check Report. This should give us a list of database objects with references to the data model
that need to go through a synonym in the apps schema.
Run ADZDDBCC.sql

8. File System Check report. Run gscc.pl. Since this did not work for us we had to manually look at all custom
files on the system to fix for compliance.
8. ffsd
12: $OA_HTML Directory

The $OA_HTML directory is a part of the dual file system, but its contents are not
synced during the prepare phase of the ADOP patching cycle.

If you want to deploy any custom code in $OA_HTML then you

should move it to the RUN and PATCH file systems at the same
time.
13: Patch Wizard Inaccurate

Patch wizard results would show that a patch has 99% new code to apply.

Fixed using ADADMIN

1. Run adadmin as applmgr user.
2. Follow prompts to AD Administration Main Menu
3. Choose Option 2 Maintain Applications File Menu
4. Choose Option 4 Maintain Snapshot Information
5. Choose Option 2 Update Current View Snapshot
6. Then select: Update Complete APPL_TOP and re-test once completed.
14: Unable to view Value Set Values
Release 12.2.2 includes a new security feature, flexfield value set security, to control who
can create or modify flexfield values in the Flexfield Values. Because this is a security
feature, it is enabled by default.
15: EBS Proxies
16: Java Code
In the past if you wanted to deploy OAF / Java code you just needed to move the
file(s) to the correct $JAVA_TOP location and bounce the middle tier.

Due to the switch to Weblogic Server, this is no longer sufficient enough.

Custom JAVA Code: Customizing Seeded JAVA Code:

1. Move files to $JAVA_TOP 1. Move files to $JAVA_TOP
2. Run the adcgnjar utility 2. Run the adadmin utility
- No parameters, requires APPS password 3. Run the “Generate Product
- Creates a customall.jar file JAR files”
3. Bounce 4. Bounce
17: Custom Tables Owned by APPS
It is a bad practice to have custom tables owned by APPS. Over the years we
accumulated many custom tables in the APPS schema.

Some tables were built for custom applications and some were just TEMP tables to hold
backup versions of tables before running scripts.

Full Compliance requires these tables be moved, but even though we were doing
Minimal Compliance we decided to take this opportunity to do some housekeeping.

Created 2 new schemas: XXCUSTOM (for non product specific custom tables)
XXTEMP (for temporary tables such as backup tables)
18: Security Features
Examples of additional security features:

All JSP Pages need to be whitelisted in allowed_jsps.conf unless you alter the profile
option Allow Unrestricted JSP Access

Allowed Redirects.
Whitelist of allowed redirect locations located in allowed_redirects.conf
Can be overridden by profile option Allow Unrestricted Redirects

(necessary for our APEX – EBS integration)

19: Export to Excel Issue

New Feature converts most OAF data into Rich Tables. This feature has an Export to Excel option. In
testing we were able to crash the system with only a handful of users clicking this button simultaneously
on large datasets. Can disable the Excel button with profile option: FND: Enable BNE Table Integration

You can disable all of the Rich Table integration with profile option: FND: Enable Rich Table Interactions
(old look and feel)
20: Password Lock Issue

Oracle allows you to lock EBS users after a certain number of failed password attempts.

After upgrading to 12.2.7 we noticed a bug where the user’s failed password attempts did not reset
back to 0 after a successful login. This caused a record number of account lockouts. It was treating the
failed password attempts as a cumulative number and never resetting it.

Apply patch: 28292585:R12.FND.C to fix this issue.

21: Leaked Connections in OAF Pages
Oracle EBS it littered with code that doesn’t close its database connection when it is finished. This issue
wasn’t as bad in 12.1, but its exacerbated in 12.2 because of Weblogic Server. This will prevent users
from logging in and eventually require you to bounce the system.

There are a number of suggested patches. This is still an ongoing effort for us.

So far we have applied: 26965182, 26709462 & 26599059

22: Cascading Invalid Objects
We seem to have a higher frequency of database objects becoming invalid. This requires the
recompilation of all invalid objects along with a bounce of the Weblogic server. This is still an ongoing
issue we are researching.

Potential Culprits: Hot Patching

Modifications to seeded objects (packages) while the system is online.
23: Fast Formula String Limitation
24: Upgrade to version 12.2.6 or higher

12.2: Functionality Disabled During An Online Patching Cycle (ADZDPATCH) (Doc ID 1586609.1)
25: Weblogic Server

EBS 12.2.7 Requires EXPERT knowledge of Weblogic Server. It has endless configurations and will require
a significant amount of researching and tweaking for your environment.

Most issues won’t be reproduceable in your test environments since they are not as active as production.
Q&A
1: Decision Point: Development Standards for Custom Code 14: Unable to view Value Set Values

2: Writing files to $APPL_TOP Directories 15: EBS Proxies

3: Custom Code & Table Access 16: Java Code

4: Code Naming Standards for Online Patching 17: Custom Tables Owned by APPS

5: Utilities are available for the analysis of your pre-upgrade system 18: Security Features

6: Online Patching Standards Compliance Report – Part 1 19: Export to Excel Issue

7: Online Patching Standards Compliance Report – Part 2 20: Password Lock Issue

8: Online Patching Readiness Reports 21: Leaked Connections in OAF Pages

9: Custom Oracle Applications and Schemas – Part 1 22: Cascading Invalid Objects

10: Custom Oracle Applications and Schemas – Part 2 23: Fast Formula String Limitation

11: Preparedness Strategy Summary 24: Upgrade to version 12.2.6 or higher

12: $OA_HTML Directory 25: Weblogic Server

13: Patch Wizard Inaccurate