This is the latest edition of Mike Swing’s book on upgrading to Oracle E-Business Suite R12.2.11. The book covers the nuances and lessons learned from many upgrades performed since Release 12 was first released.
Putting together the right team to tackle the Oracle E-Business Suite R12.2.11 upgrade, and understanding the issues that the team needs to consider to be successful, can be quite a challenge. “the little r12.2.11 upgrade essentials for managers and team members” describes the big picture of what you need to consider before tackling the Release 12.2.11 upgrade. Based on TruTek’s popular R11i to R12 Technical Upgrade training classes, this book describes what managers, functional, and technical team members need to know to prepare to upgrade from Release 11i to Release 12.2.11 of Oracle’s E-Business Suite of Applications. Enhanced topics with this edition include: Online Patching, the ADOP Patching Cycle, Materialized Views, Customizations, Development Standards for Edition Based Redefinition, How Cross-Edition Triggers Work, and Understanding the Release 12.2 Architecture.
1. The EBS Release 12.2.11 RUP (Patch 31856789) was released on November 9 2021, in MOS Doc. ID: 2758997.1, Oracle E-Business Suite Release 12.2.11 Readme New Features in R12.AD.C.Delta.7 and R12.TXK.C.Delta.7. It includes suite-wide and family-level granularity (unlike Release 12.2.3 and Release 12.2.2). You should apply the Release 12.2.11 RUP using Downtime Mode:
Grant Flexfield Value Set Access to specific users (required). Release12.2 includes a new security feature, Flexfield Value Set Security. It controls who can create or modify flexfield values in the Flexfield Values Setup window (FNDFFMSV). These are mandatory setup steps before any users can create or update values in the Values window.
However, if you choose not to implement Flexfield Value Set Security upon upgrading to or installing Release 12.2, you have two options to give users access to all Value Sets for backwards compatibility:
Assign the seeded unlimited-access role (“Flexfield and Report Values: All privileges”) directly to users, responsibilities, or roles. With this option, users who have function security access to the Segment Values form and have this role either directly or indirectly can see, insert, and update values for any value set.
Create an “all-value-sets, all-privileges, all-users”. With this option, any users who have function security access to the Segment Values form can view, insert, and update values for any value set.
If you are an E-Business Suite Information Discovery customer, see MOS Doc. ID: 2214431.1, Installing Oracle E-Business Suite Information Discovery, Release 12.2 V8.
If you support languages other than American English, perform NLS-related Step (conditional) from MOS Doc. ID: 2495027.1, Oracle E-Business Suite Release 12.2.11 Readme.
Run Patch Wizard for current recommended and high priority product patches. See MOS Doc. ID: 1400757.1, How to Find E-Business Suite Recommended Patches.
Patch Wizard is a tool included with Oracle Application Manager. Patch Wizard reviews recommended and high priority patches against those that have been applied on your system. Patches can be downloaded and merged using the tool. You can review patch impact to determine how a specific patch or several patches will affect your system. Review MOS Doc. ID: 976188.1, Patch Wizard Utility.
If your environment does not allow the Patch Wizard utility, review MOS Doc. ID: 1400757.1, How to Find Oracle E-Business Suite Recommended Patches.
Perform the following three checks to verify the directory service assignments:
Log in to Oracle E-Business Suite as the SYSADMIN user and check whether the Application Diagnostics responsibility appears for this user in the navigator. If not, then run the Workflow Directory Services User/Role Validation Concurrent Program with the parameters listed below.
Run the following SQL query:
SQL> SELECT ASSIGNING_ROLE, LAST_UPDATE_DATE, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID FROM WF_USER_ROLE_ASSIGNMENTS WHERE USER_NAME = ‘SYSADMIN’ AND ROLE_NAME = ‘FND_RESP|FND|APPLICATION_DIAGNOSTICS|STANDARD’;
If the originating system or originating system ID is null for either the user or the role in any of the records returned by this query, then run the Workflow Directory Services User/Role Validation Concurrent Program with the parameters listed below.
Run the following SQL query to obtain the originating system ID of the Application Diagnostics responsibility in your Oracle E-Business Suite instance:
SQL> SELECT ORIG_SYSTEM_ID FROM WF_LOCAL_ROLES WHERE NAME = ‘FND_RESP|FND|APPLICATION_DIAGNOSTICS|STANDARD’;
Next, run the following SQL query, replacing <RESP_ID> with the originating system ID returned by the previous query:
SQL> SELECT * FROM FND_USER_RESP_GROUPS WHERE RESPONSIBILITY_ID = <RESP_ID> AND USER_ID = 0;
If this query does not return any rows, then run the Workflow Directory Services User/Role Validation concurrent program with the parameters listed below.
To update the assignment records for the SYSADMIN user, run the Workflow Directory Services User/Role Validation concurrent program with the following parameters:
Batch size: 10000 (the default value)
User name: SYSADMIN
Role name: FND_RESP|FND|APPLICATION_DIAGNOSTICS|STANDARD
Fix dangling users: No
Add missing user/role assignments: Yes
Update WHO columns in WF tables: Yes
Number of Parallel Processes: Optionally specify the number of parallel processes you want to use when running the program.
See MOS Doc. ID: 1583092.1, E-Business Suite RUP, AD and TXK RUP Information and MOS Doc. ID: 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2.
This AD-TXK codelevel has a dependency on Oracle Fusion Middleware:
For FMW 184.108.40.206, see Patch 20642039
For FMW 220.127.116.11, see Patch 20756887
However, if you are using startCD51, the version of FMW should be 18.104.22.168, and there should be no need to apply these patches. See MOS Doc. ID: 2073977.1, How To Find The Version Of Oracle Fusion Middleware Used By E-Business Suite Release 12.2.
Ensure all the required ETCC database objects will be found by the Delta 10 RUP installation process. This means that you must still run the latest version of ETCC on your database, even if you already have all the required database patches installed.
Use the checkMTpatch.sh script supplied in Patch 17537119 to check your Fusion Middleware version and identify the patches required by ETCC. For the database use checkDBpatch.sh.
You should check to see if there is a new version of the ETCC patch about every 30 days. The xml file that contains the list of recent patches is released about every 30 days. The ETCC scripts check to see if the xml file is older than 30 days, if so, they terminate. In the upgrade environment, you can query the contents of the TXK_TCC_RESULTS table to make sure the dates are current.
[WARNING] DB-ETCC: Bugfix XML file (./db/onprem/txk_R1220_DB_base_bugs.xml) is more than 30 days old. Check if a newer version is available in Patch 17537119.
In our case, on February 5th, the base_bugs.xml is more than 30 days old:
30465 Nov 1 09:47 txk_R1220_DB_base_bugs.xml, but the patch says it was updated 29 days ago:
There may be an error depending on the case of your servers listed in the TXK_TCC_RESULTS table. Check the case of the servers in the TXK_TCC_RESULTS table to make sure they are valid if you get an error on the TXK_TCC_RESULTS table.
select count(table_name) from all_tables where table_name=’TXK_TCC_RESULTS’;
select DATABASE_NAME, COMPONENT_NAME, CHECK_DATE, CHECK_RESULT, CHECK_MESSAGE from TXK_TCC_RESULTS;
Applying the latest AD/TXK Delta patches is highly recommended. AD/TXK Delta 11 was released in June 2019. One of the major changes in AD-TXK Delta 10 was the addition of a new context variable s_srvctl_thread_count. You can now tune the thread count with this new context variable, s_srvctl_thread_count to optimize startup and shutdown.
AD/TXK Delta 9 was released in March 2017. AD/TXK Delta 9 introduced full support for the Oracle Grid Listener used by ASM. The Grid Listener requires all registered service names to be unique. In AD/TXK Delta 8 and earlier, the service name for connections to the Patch Edition of the database was ebs_patch. In AD/TXK Delta.9, the service name to connect to the Patch Edition has been changed to <instance_name>_ebs_patch.
Apply the latest application tier technology patches to the run file system. Refer to MOS Doc.ID: 1355068.1, Oracle E-Business Suite 12.2 Patching Technology Components Guide for instructions on how to do this.
Start only the WebLogic Admin Server on the application tier.
$ sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start
Download and unzip the AD/TXK Delta 13 patches and run adgrants:
Execute adgrants.sql (currently version 89, by following the instructions in the readme of Patch 32394134, R12.AD.C.Delta.13.
The Release 12.2 database architecture has been modernized by adoption of the Oracle E-Business Suite System Schema, EBS_SYSTEM. Prior to the introduction of the EBS_SYSTEM schema, Oracle E-Business Suite installed application objects in the Oracle Database SYS and SYSTEM schemas. Migration to the EBS SYSTEM Schema obviates the need for any EBS-owned objects to reside in the SYS or SYSTEM schemas.
Key characteristics of the EBS_SYSTEM Schema include:
Creation of the EBS_SYSTEM schema and associated grant management is performed as follows:
Creation of the EBS_SYSTEM schema and is performed by SYS running the adgrants.sql script (supplying the APPS account as the parameter) before applying the AD-TXK Delta 13 RUPs.
Grants required by the APPS account are given by the apps_adgrants.sql script being run automatically by the AD-TXK Delta 13 RUP installation process. This script does not need to be run manually as part of normal patching operations.
All EBS database objects that currently reside in the SYS or SYSTEM schemas are migrated to appropriate Oracle E-Business Suite schemas. Depending upon the EBS object type and function, the object is migrated to EBS_SYSTEM, APPS, or APPS_NE.
All Oracle E-Business Suite administration actions (such as running adop, adadmin and other utilities) are now performed by EBS_SYSTEM.
Access to the Oracle database SYS and SYSTEM and the Oracle database server operating system is no longer required for Oracle E-Business Suite system administrative functions.
Oracle E-Business Suite is focused on revoking unnecessary privileges from the core EBS accounts as part of the implementation of the Defense in Depth and Least Privileges Model. This may impact custom code that runs in the EBS schemas and relies on these privileges. In these cases, you should consider separating the custom code into a separate schema, or, if that is not possible, adding the minimal required privileges back to the necessary account. For more information on interacting with EBS objects and code from a separate schema, refer to MOS Doc. ID: 2327836.1, Guidance for Providing Access to the Oracle E-Business Suite Database for Extensions and Third-Party Products.
To use the EBS System Schema, you need to be on Oracle E-Business Suite Release 12.2.3 or later.
Mandatory Patches and Steps
You must have available the following release update packs (RUPs) and other patches, ready to apply below in Section 3, in the order shown here.
AD Delta 13 (or later) and TXK Delta 13 (or later) — Support for EBS System Schema Migration was introduced in the Oracle Applications DBA (AD) and Oracle E-Business Suite Technology Stack (TXK) Delta 13 release update packs. Previous releases of AD and TXK included database objects owned by AD or TXK that resided in the Oracle Database SYS or SYSTEM schemas, and also included references to private Oracle database SYS or SYSTEM objects.
With AD Delta 13 and TXK Delta 13, EBS database objects are migrated from SYS and SYSTEM into the EBS_SYSTEM, APPS, or APPS_NE schemas. All relevant object references are updated to point to the migrated objects in the APPS or APPS_NE schemas.
AD Delta 13 and TXK Delta 13 also update all code that previously referenced private Oracle Database SYS and SYSTEM objects, to reference Oracle Database approved public objects or public APIs instead.
EBS 12.2.11 RUP, or EBS Release 12.2.3—12.2.10 and EBS System Schema Migration Consolidated Patch — Oracle E-Business Suite Releases 12.2.3—12.2.10 include EBS-owned database objects that reside in the database SYS or SYSTEM schemas, or reference database SYS or SYSTEM objects. The EBS System Schema Migration Consolidated Patch (also referred to as the Consolidated Patch) migrates these objects to the appropriate EBS schemas. The EBS 12.2.11 RUP includes the Consolidated Patch.
Note: Oracle E-Business Suite Release 12.2.11 and later do not install EBS objects in the SYS or SYSTEM schemas, and so do not require the EBS System Schema Migration Consolidated Patch.
Before proceeding with application of the final patch in Step 4, you must update any customizations that have been identified with mandatory compliance requirements. For guidance, refer to MOS Doc. ID: 1577661.1, Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2.
EBS System Schema Migration Completion Patch — Although it is possible to apply AD-TXK Delta 13 and the EBS System Schema Migration Consolidated Patch without applying the EBS System Schema Migration Completion Patch immediately afterwards, the migration is not finished until the Completion Patch has been applied. This patch drops AD, TXK, and EBS product code in the database SYS or SYSTEM schemas, and removes any final restricted references to database SYS or SYSTEM objects.
Prior to applying the Completion Patch, you must apply the following prerequisite patches:AD-TXK Delta 13
Either the EBS 12.2.11 RUP or the EBS System Schema Migration Consolidated Patch
The Completion Patch will fail to apply if you do not apply these patches first.
You should also download and unzip any applicable patches from Section 4. Apply Additional Critical Patches of MOS Doc ID: 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2, which introduces R12.AD.C.Delta.12 and R12.TXK.C.Delta.12. The following are the critical patches as of February 6, 2020. Run adgrants.sql, from Patch 32394134, which delivers a newer version of adgrants (adgrants.sql.65). This is the proper version of adgrants.sql. Then, apply this critical AD patch:
From MOS Doc. ID: 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2: On the Run Edition, apply Patch 32394134 (R12.AD.C.Delta.13) and Patch 32392507:R12.AD.C in Hotpatch Mode. Do not merge any other patches in this step.
It is mandatory that you have already installed Oracle E-Business Suite Release 12.2 using the latest startCD (22.214.171.124 – Patch 22066363) along with Patch 32947483 (Rapid Install consolidated one-off bundle on top of startCD51).
Run the Online Patching Enablement Reports. From MOS Doc. ID: 1531121.1, Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2:
ADZDPSUM.sql Lists schemas containing objects that reference EBS objects that are not editioned. If the schemas are registered with the application, the Online Patching Enablement patch will enable editioning on those schemas avoiding many EBR rule violations (Non-Editioned objects referencing Editioned objects).
sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
mv adzdpsum.txt adzdpsum_pre_dbprep.txt
From ADZDPSUM.sql, see the full report in the attachments:
DOC> DOC> Schemas that will be editioned upon Online Patching Enablement.
ETL_USER EBS Product — registered before the 12.2 upgrade
HYPERION_USER Custom — not registered before the upgrade
ADZDPMAN.sql Lists objects that violate EBR standards and that require manual intervention in order to address the violations. It groups the objects per violation type and provides instructions on how to address those violations.
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
mv adzdpman.txt adzdpman_pre_dbprep.txt
ADZDPAUT.sql Lists objects that violate Online Patching Enablement standards. The objects in this report will be adjusted automatically by the Online Patching Enablement patch. This report is provided for information purposes only, no action is required on the output of this report.
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql
mv adzdpaut.txt adzdpaut_pre_dbprep.txt Fix any violations -Oracle recommends that you perform the chosen fix by customizing the template file
Go to edelivery.oracle.com, navigate to the Oracle E-Business Suite 12.2.0 Current
Choose Oracle E-Business Suite 12.2.0 Current
Agree to the Terms and Conditions
Agree to the license agreement and Continue, then download these files.
Unzip and build the rapidwiz stage directory
Use startCD51 for the upgrade, This is the latest startCD. (R12.AD.C.Delta.10 startcd51 – Patch 22066363).
Unzip the following patch using the following command:
Change to the 32947483 directory.
Patch the Rapid Install stage area using the following command:
For Oracle Solaris on SPARC (64-bit) and IBM AIX on Power Systems (64-bit): after executing patchRIStage.sh to patch the existing Rapid Install stage area, you must re-run buildStage.sh with the option “Copy patches to existing stage area”. This is to incorporate additional platform-specific fixes into the Rapid Install Stage area for these platforms. The script should be run with the same user that was used to create the stage area.
Premier Support for Oracle E-Business Suite Release 12.2 through at least Dec 2032.
Premier Support is available for Oracle E-Business Suite Release 12.1 through Dec 2021. After Dec 2021, Market Driven Support (MDS) will be available for 2022 and 2023. Additional details regarding MDS for Oracle E-Business Suite 12.1.3 will be published soon.
Follow the process documented in the following to determine eligibility for the waiver to database Extended Support fees: MOS Doc. ID: 2522948.1, Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite.
Due to the certification timing of Oracle E-Business Suite with Oracle Database versions beyond 12.1, Oracle has waived Extended Support license uplift fees for
Oracle Database 12.1 through 31 July 2022 licenses used for Oracle E-Business Suite and Oracle Database 11.2 Extended Support ended December 2020
Extended Support license uplift fees ended Extended Support December 31, 2020, for 126.96.36.199 databases used for Oracle E-Business Suite
ACS Market Driven Support (MDS) available for 188.8.131.52 databases after December 2020
This Extended Support license uplift fee waiver will be specific to the Oracle Database licenses, within your company’s footprint, that you use for running Oracle E-Business Suite software.
If you have already paid your Oracle Database Extended Support license uplift fees, you may be eligible to receive a credit that we will apply against your Oracle account.
Per MOS Doc. ID: 2580629.1, Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c:
When upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will be converted to the multitenant architecture, with a Container Database (CDB) and a single Pluggable Database. Only multitenant architecture databases are certified for Oracle E-Business Suite with Oracle Database 19c.
During the upgrade, you will also perform steps to migrate directories defined for PL/SQL File I/O to database directory objects. This requirement is due to the desupport in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.
Oracle 19c Database Release Update Information for Oracle E-Business Suite:
Oracle Database Release Update 19.3 as well as Release Update 19.5 and later are certified.
Oracle recommends that you upgrade to the latest Oracle Database Release Update that is certified with Oracle E-Business Suite. Applying the latest Release Update will ensure that you have the security-related fixes and high-priority non-security fixes. See MOS Doc. ID: 2285040.1, Release Update Introduction and FAQ, for more details.
You can upgrade directly from an earlier Oracle Database version to either:
Oracle Database 19c Release Update 19.3
Oracle Database 19c Release Update 19.6 or a later Oracle E-Business Suite certified RU.
You can upgrade to Oracle Database 19c Release Update 19.5 indirectly:
Upgrade from your earlier Oracle Database version to Oracle Database 19c Release Update 19.3
EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT)
The EBS Technology Patch Automation Tool for Application Tier automates patching of technology one-offs for the following components of the Oracle E-Business Suite Release (EBS) Release 12.2.0 application tier.
– FMW WebTier 184.108.40.206
– Oracle WebLogic Server 10.3.6 Smart Update Patch
– Oracle WebLogic Server 10.3.6.0
– FMW oracle_common 220.127.116.11
– Forms and Reports 10.1.2.3
ETPAT-AT can be run on an EBS Release 12.2.0 application tier (run file system) for new installations or upgrades. This tool cannot be used on EBS systems that are already at Release 12.2.3 or later.
Run the following command to start ETPAT-AT:
$ perl etpat_at.pl
EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT) (Doc ID 2749774.1)