CALL

Efficient Oracle EBS Upgrade Guide: Transitioning from R12.1.3 to R12.2.10

Oracle Database,Oracle E-Business Suite - August 15, 2023

by Mahendra Naik - Sr. Oracle Apps DBA

Step by Step instructions on Oracle EBS R12.1.3 upgrade to R12.2.0

blog3.jpeg


Introduction:

This document explains in detail all the steps required to migrate and upgrade from 12.1.3 to R12.2.10 Oracle E-Business Suite with Oracle Database Enterprise version 19c (CDB) database.

This is a Single Node Upgradation and Installation.

 

Environment Details:

Operating System Oracle Linux Enterprise Edition Server Release 5.8 Server
Memory 34GB
Number of CPUs 32
Database Release  19.9.0.0
Oracle E-business Suite 12.1.3

Note: The database and application tiers are on the same machine.

 

Database Configuration:

SGA 5 GB
Shared Pool Size 1 GB
PGA  3 GB
Log Buffer   30 MB
job_queue_processes 32

 

Application Tier Size:

Oracle E-Business Suite Release 12.2 introduces Online Patching. This requires three application-tier file systems:

 fs1 (Production file system) – Used by the current users of the system.

 fs2 (Copy of production file system) – Used by the patching tools.

 fs_ne (Non-editioned file system) – Stores files containing data that is needed across all file systems (for example, data import and export files, report output files, and log files).

 Inst (INST_TOP) - Oracle E-Business Suite Release 12 Instance Home, contains all the config files, log files, SSL certificates, etc.

 

All three file systems serve a single database. The file system that is currently being used by the running application is never patched: all patches are applied to the file system that is not currently in use.

 Component  Before Upgrade Size  After Upgrade Size
 ORACLE_HOME  3.6 GB  3.6 GB
 APPL_TOP  28 GB  Not Applicable
 INST_TOP  20 MB  Not Applicable
 fsi1 (APPL_TOP + INST_TOP)  Not Applicable  30 GB
 fsi2 (APPL_TOP + INST_TOP)  Not Applicable  29 GB
 fs_ne  Not Applicable  660 KB

Recommended initialization (init.ora) parameter settings for the upgrade:

  • It is recommended to follow AWR pool advisories for optimizing the SGA and PGA size in the Release 12.2 upgrade.
  • An upgrade involves tens of concurrent sessions. Suitable starting values are:
  1. log buffer = 30 to 100 Mb
  2. shared pool = 1 to 4 GB
  3. pga target = 3 to 20 GB
  4. buffer cache = 5-50 GB (multi-GB)
  • You should ensure that SGA and PGA are allocated appropriately within the system's available memory. If set higher it may cause excessive paging, or even swapping.
  • Set “job_queue_processes = No. of CPUs” – adobjcmp.sql (Phases: plb+90 and last+63)
  • This parameter is set at the beginning of the upgrade; it will be most useful in the phases mentioned above.
  • Setting parallel_max_servers to 2 x no. of CPUs will help with large index creation, statistics gathering, and some large upg+ phase jobs.
  • In order to maximize multiblock I/O sizes, you should remove "db_file_multiblock_read_count" (if specified).
  • Ensure you reset the following init.ora parameters after completion of the Release 12.2 upgrade.
  1. recyclebin
  2. parallel_max_servers
  3. job_queue_processes

 

Run the RDA report on the server and fix an error:

Oracle E-Business Suite 12.2.10 is certified on Linux x86-64 Red Hat Enterprise Linux 8

 

Download Oracle EBS R12.2 Software:

Document: R12.2: How to Create the Stage in Preparation for Installation (Doc ID 1596433.1)

Steps to download Oracle E-Business Suite Release 12.2.0 installation files from Oracle E-Delivery Portal.

 

p22066363_R12_GENERIC.zip V100059-01_1of2.zip  V35802-01.zip V999625-01_10of11.zip  
V100052-01_1of3.zip V100059-01_2of2.zip V35803-01_1of3.zip V999625-01_11of11.zip
V100052-01_2of3.zip V100060-01_1of3.zip V35803-01_2of3.zip V999625-01_1of11.zip
V100052-01_3of3.zip V100060-01_2of3.zip V35803-01_3of3.zip V999625-01_2of11.zip
V100053-01_1of2.zip V100060-01_3of3.zip V35804-01_1of2.zip V999625-01_3of11.zip
V100053-01_2of2.zip  V100061-01.zip V35804-01_2of2.zip V999625-01_4of11.zip
V100054-01.zip V100102-01.zip   V46095-01_1of2.zip V999625-01_5of11.zip
V100055-01.zip V1006971-01.zip V46095-01_2of2.zip   V999625-01_6of11.zip
V100056-01.zip    V1006972-01_1of2.zip V75792-01.zip  V999625-01_7of11.zip
V100057-01.zip V1006972-01_2of2.zip V998895-01_1of2.zip V999625-01_8of11.zip
V100058-01.zip   V29856-01.zip V998895-01_2of2.zip V999625-01_9of11.zip
  • Unzip the latest startCD 12.2.0.51 patch 22066363:
  • Run buildStage.sh script:

$ cd /u01/StageR122/startCD/Disk1/rapidwiz/bin

$ buildStage.sh

 

Create new stage area  ⇒  Linux x86-64  ⇒ (Please enter the directory containing the zipped installation media)  ⇒ Copy new patches to current stage area  ⇒  Linux x86-64  ⇒ Display existing files in stage TechPatches  ⇒   Exit menu.

  • Patching the Stage Area

Unzip the patch p32947483_R12_GENERIC.zip

./patchRIStage.sh

 

PreUpgrade Task:

  • Run preclone on the database and application sides.
  • Run autoconfig on the database and application sides.
  • Validate the passwords for the guest user and pub user.
  • Capture the count of invalid objects before commencing the process.

 

  • Start the RapidWiz – TECHSTACK Installation

If you have VNC access, open the VNC session. Otherwise, use the Xming method to launch the GUI.

  • Start VNC Session as Root owner

[root@datapatroltech ~] # xhost +

[root@datapatroltech ~]su – applmgr

[applmgr@datapatroltech ~]# export DISPLAY=localhost:11.0

[root@datapatroltech ~]# ./u01/StageR122/startCD/Disk1/rapidwiz/rapidwiz

 

  • After running Rapidwiz, we will be shown the "Welcome" window. In the Welcome window, expand all the components to view more details.

  • In the "Wizard Operation" window, click on "Upgrade to Oracle E-Business Suite Release 12.2.0". Then click “Next" to continue.
  • In the “Oracle Configuration Manager Details” window Click on the checkbox 'I wish to receive security updates via my Oracle Support'. Then Click “Next” to continue.       
  • In the "Select Upgrade Action" window, click on "Create Upgrade File System". Then, click "Next" to continue.
  • In the "Global System Settings" window, select the appropriate port for "File System 1" and "File System 2" based on your environment. Then click "Next" to continue

  • In the “Database Node Configuration” window provide the required database information. Then click “Next” to continue.
  • In the "Review Application User Information" window, please enter the passwords for "apps" . Then, click "Next" to continue.

  • In the “Primary Application Node Configuration” window provide the required application information. Then click “Next” to continue.
  • In the "Application User Information" window, please enter the passwords for "Weblogic" and "applmgr". Then, click "Next" to continue.
  • In the "Node Information" window, please verify the database and application nodes. Then, click "Next" to continue.

  • In the “Validate System Configuration” window, Please verify the precheck. Then click “Next” to continue.          
  • In the “Component Installation Review” window, Please verify the components. Then click Next to continue.
  • After clicking on the "Component Installation Review" window, click on "Yes". Then click "Next" to continue.

  •   Click Next to continue.

Click Finish to exit Rapid Install

 

Preparing the System for Upgrade:

  • Apply Required Application Tier Patches
  • Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID: 1594274.1)
  • Check for missing application tier patches by executing the Oracle E-Business Suite Technology Codelevel Checker (ETCC) - checkMTpatch.sh.

Generating Patch Recommendation Summary.

=================================================================

PATCH RECOMMENDATION SUMMARY

=================================================================

One or more products have bugfixes missing.

The default patch recommendations to install these missing bugfixes are:

-----------------------------------------------------------------------------------------------------------------

Oracle Forms and Reports 10.1.2.3.0

-----------------------------------------------------------------------------------------------------------------

Patch 32922089

- Filename: p32922089_101232_LINUX.zip

 

Patch 25342269

- Filename: p25342269_101232_Generic.zip

 

Patch 27491934

- Filename: p27491934_101232_LINUX.zip

-----------------------------------------------------------------------------------------------------------------

Oracle Fusion Middleware (FMW) - Web Tier 11.1.1.9.0

-----------------------------------------------------------------------------------------------------------------

  Patch 22288381

    - Filename: p22288381_111190_Generic.zip

Patch 33311587

- Filename: p33311587_111190_Linux-x86-64.zip

 

Patch 32287205 [IMPORTANT: Follow Note 2555355.1 before applying.]

 - Filename: p32287205_111190_Linux-x86-64.zip

 

  Patch 23716938

    - Filename: p23716938_111190_Generic.zip

 

  Patch 32928416

  - Filename: p32928416_111190_Linux-x86-64.zip

 

  Patch 33144848

 - Filename: p33144848_111190_Linux-x86-64.zip

-----------------------------------------------------------------------------------------------------------------

RSF within FMW Web tier 11.1.0.7.0

-----------------------------------------------------------------------------------------------------------------

  Patch 22290164

 - Filename: p22290164_111070_Linux-x86-64.zip

-----------------------------------------------------------------------------------------------------------------

Oracle Fusion Middleware (FMW) - oracle_common 11.1.1.9.0

-----------------------------------------------------------------------------------------------------------------

  Patch 9905685

    - Filename: p9905685_111190_Generic.zip

 

  Patch 17428617

    - Filename: p17428617_111190_Generic.zip

 

  Patch 21366277

    - Filename: p21366277_111190_Generic.zip

 

  Patch 21628307

    - Filename: p21628307_111190_Generic.zip

 

  Patch 24606362

    - Filename: p24606362_111190_Generic.zip

 

  Patch 24843064

    - Filename: p24843064_111190_Generic.zip

 

  Patch 13544814

    - Filename: p13544814_111190_Generic.zip

 

  Patch 16089637

    - Filename: p16089637_111190_Generic.zip

 

  Patch 20141119

    - Filename: p20141119_111190_Generic.zip

 

  Patch 30368663

    - Filename: p30368663_111190_Generic.zip

 

  Patch 27212806

    - Filename: p27212806_111190_Generic.zip

 

  Patch 28708563

    - Filename: p28708563_111190_Generic.zip

 

  Patch 26933408

    - Filename: p26933408_111190_Generic.zip

-----------------------------------------------------------------------------------------------------------------

Oracle WebLogic Server (WLS) 10.3.6.0.7

-----------------------------------------------------------------------------------------------------------------

  Patch 32728642 [SU Patch [I37G]: WLS PSU 10.3.6.0.200714]

  - Filename: p32728642_R12_GENERIC.zip 

  • Apply the aforementioned patches to the newly created file system.
  • Once the patches have been applied, Rerun the etcc and check if there are any pending patches remaining.
  • Drop Event Alert Triggers in Custom Schemas (conditional):

To drop all event alert database triggers in custom schemas, run the alrdtrig.sql script, located in $ALR_TOP/patch/115/sql. Re-create the triggers after the upgrade is complete.

  • Validate GUEST Account:

SQL> select fnd_web_sec.validate_login('GUEST','ORACLE') Valid from dual;

VALID

------------------------------------------------------------------------------------------------------------------------------

Y

 

Financials and Procurement Tasks:

  • Subledger Accounting:

           Perform this task if you are using Oracle Subledger Accounting.

  • Clean up Advance Queues from prior Create Accounting processes before enabling Edition-Based Redefinition (EBR).

          Apply patch p13420532_R12.XLA.B_R12_GENERIC.zip

 

  • Human Resource Management (HRMS):

SQL> SELECT

       package_name

       ,object_name

       ,argument_name

       ,data_type

FROM    all_arguments

WHERE   (package_name,object_name) IN

        (

        SELECT

           call_package

           ,call_procedure

        FROM    hr_api_hook_calls

        )

AND data_type = 'LONG';

 

Please run the query and check the data_type value as LONG. Afterward, change the data type from LONG to CLOB.

The LONG data type was deemed obsolete by the database group in release 8i and was subsequently replaced by the CLOB data type.

 

  • Order Management:

Perform these tasks if you are utilizing Oracle Order Management.

  • Check for the Nullable Hold Entity ID

If you upgrade from release 12.1, you should check for the Nullable Hold Entity ID and fix it to NOT NULL. Carefully follow the instructions in the readme of Patch 14191792:R12.ONT.B.

 

Database and System Administration Tasks:

  • Reset init.ora parameters (required)

          Follow the instructions in Database Initialization Parameters for Oracle E-Business Suite Release 12.2 (Doc ID: 396009.1) and reset the init.ora parameters as required.

  • Take a backup of the init.ora file.
  • Add the following parameters to the init.ora file.

optimizer_adaptive_plans=TRUE #MP

optimizer_adaptive_statistics=FALSE #MP

temp_undo_enabled=FALSE

_optimizer_gather_stats_on_conventional_dml=FALSE #MP

_optimizer_use_stats_on_conventional_dml=FALSE #MP

#optimizer_real_time_statistics=FALSE #MP
  • Comment the following parameters to the init.ora file.

#*.optimizer_features_enable='11.2.0.4'

#*.plsql_optimize_level=2
  • Gather SYS, Fixed Object and Dictionary Statistics (required)
  • Gather SYS schema statistics

begin

  dbms_stats.gather_schema_stats(

  'SYS',

  options=>'GATHER STALE',

  estimate_percent =>

DBMS_STATS.AUTO_SAMPLE_SIZE,

  method_opt => 'FOR ALL COLUMNS SIZE AUTO',

  cascade => TRUE);

  end;

/
  • Fixed Object and Dictionary Statistics
  • These should have been previously gathered, correct and up-to-date on the pre-upgrade environment.

exec dbms_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_dictionary_stats;
  • Gather schema statistics (required)

SQL> show parameter parallel_max_servers

NAME                                                                                                                                                TYPE         VALUE

------------------------------------                                                                                                           -----------       -------------

parallel_max_servers                                                                                                            integer             8

 

SQL> conn apps/apps

Connected.

SQL> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 8, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');

  • Install JRE on the database tier (conditional)

cp $ORACLE_HOME/jdk/jre $ORACLE_HOME/appsutil

cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext
  • Synchronize values of APPLPTMP with Database Directories for PL/SQL File I/O based Concurrent Requests (required)

SQL> select value from apps.v$parameter where name='utl_file_dir';

VALUE

-------------------------------------------------------------------

/oradb/oracle/db/tech_st/temp/ERP,/oradb/oracle/db/tech_st/

temp/ERP,/oradb/oracle/db/tech_st/19chome/appsutil/

outbound/ERP_datapatroltech,/oradb/oracle/db/tech_st/temp/ERP

  • Perform a system backup

Make a cold backup of the Oracle E-Business Suite database. Back up the Oracle E-Business Suite database. If you encounter problems during the upgrade process, you can use this backup to restore your system to the state before you began the upgrade.

 

Performing the Upgrade to Release 12.2.0:

  • Shut down application tier listeners and concurrent managers (required)
stop the APP: sh $ADMIN_SCRIPTS_HOME/adstpall.sh
  • Set FAILED_LOGIN_ATTEMPTS to UNLIMITED for Oracle E-Business Suite schema
  • Compile invalid objects

If there are any invalid objects, you must connect to SQL*Plus using the ‘apps’ account to login and execute the following commands.

$ exec sys.utl_recomp.recomp_parallel
  • Ensure that Maintenance Mode is enabled (required)
  • Apply AD 12.2 upgrade driver (required)
  • Download and unzip the AD Upgrade Patch for 12.2 (patch 10117518). Follow the instructions in the patch readme to use AutoPatch to run it from the 12.2 Run File System.

[applmgr@datapatroltech]$cd /oraapp/patches/PREPROD_upg_patches/Preupgrade_patches

[applmgr@datapatroltech]$ mkdir AD_source

  • Download and unzip R12.AD.C (patch#10117518), AD (Patch 31856771:R12.AD.C) under /oraapp/patches/PREPROD_upg_patches/Preupgrade_patches/AD_source

[applmgr@datapatroltech]$ unzip p31856771_R12.AD.C_R12_LINUX.zip

[applmgr@datapatroltech]$ unzip p10117518_R12_LINUX.zip

[applmgr@datapatroltech]$ mkdir 10117518_CUP12

[applmgr@datapatroltech]$ chmod 777 10117518_CUP12
  • Run adgrant.sql according to the instructions provided in the readme file for patch 10117518.
  • Merge R12.AD.C (patch#10117518) with AD CUP12 patch (patch#31856771:R12.AD.C)

admrgpch -s /oraapp/patches/PREPROD_upg_patches/Preupgrade_patches

/AD_source -d /oraapp/patches/PREPROD_upg_patches/Preupgrade_patches

/10117518_CUP12 -merge_name 10117518_cup12 –admode

  • Apply the merged patch using the following command.

[applmgr@datapatroltech10117518_CUP12]$adpatch patchtop=/

oraapp/patches/PREPROD_upg_patches/Preupgrade_patches/10117518

_CUP12 driver=u_10117518_cup12.drv logfile=u_10117518_cup12.log

 

Post-install Tasks:

Run adadmin Compile/Reload Applications Database Entities menu  Compile APPS schema.

Type "No" when prompt for Run Invoker Rights processing in incremental mode [No] ?

 

  • Apply the merged patch according to the provided instructions.

Apply the Consolidated Upgrade Patch (CUP) for Release 12.2.0 (Patch 31856773:12.2.0) in  

pre-installation mode on the run edition file system following the provided instructions.

[applmgr@datapatroltech Preupgrade_patches]$ cd /oraapp/patches/PREPROD_upg_patches/Preupgrade_patches

[applmgr@datapatroltech Preupgrade_patches]$ unzip p31856773_12.2.0_R12_LINUX.zip

 

[applmgr@datapatroltech Preupgrade_patches]$ cd 31856773

[applmgr@datapatroltech 31856773]$ time adpatch preinstall=y

 

[applmgr@datapatroltech 31856773]$ cd $AU_TOP/patch/115/driver

[applmgr@datapatroltech driver]$ admrgpch -d . -preinstall -master u10124646.drv

  • Apply the newly merged 12.2.0 upgrade driver

[applmgr@datapatroltech ~]$ cd $AU_TOP/patch/115/driver

[applmgr@datapatroltech ~]$ adpatch options=nocopyportion,nogenerateportion

  • Disable maintenance mode
  • Back up Oracle E-Business Suite (recommended)

Perform a full Oracle E-Business Suite backup of the application and database.

 

Finishing the 12.2.0 Upgrade:

  • Configure Release 12.2 Oracle E-Business Suite instance (required)
  • Update the DB ORACLE_HOME file system with AutoConfig and Clone files (conditional)

On the application tier as the APPLMGR user, log on to the APPL_TOP environment source the environment file, and run this perl script to create appsutil.zip in $INST_TOP/admin/out.

perl AD_TOP/bin/admkappsutil.pl

On the database tier as the ORACLE user, copy or FTP the appsutil.zip file to and unzip the file

$ cd $ORACLE_HOME

$ unzip -o appsutil.zip
  • Install JRE on the database tier (conditional)

           For Oracle Database 19c:

           Install JRE 8 (on the database tier) using the following instructions:

cp -r $ORACLE_HOME/jdk/jre /appsutil

cp$ORACLE_HOME/jlib/orai18n.jar ORACLE_HOME/appsutil/jre/lib/ext
  • Create the Context Name directory in the DB oracle home Note:

           This step is not applicable for multitenant architecture.

  • Set and export the following environment variables

export ORACLE_HOME=

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

export ORACLE_SID=

export TNS_ADMIN=$ORACLE_HOME/network/admin/

export DB_LISTENER=

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:/usr/bin:/usr/sbin:$ORACLE_HOME/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1:$ORACLE_HOME/appsutil/perl

  • Generate a new database context file as follows:

          Note: This step applies only to customers using their existing DB home during the upgrade

$ cd /appsutil/bin

$ perl adbldxml.pl

  • Clean up old node information prior to configuring the Oracle E-Business Suite Release 12.2 application tier
$ exec fnd_conc_clone.setup_clean ;
  • Synchronize values of APPLPTMP with Database Directories for PL/SQL File I/O based Concurrent Requests (required)
SQL> select value from v$parameter where name='utl_file_dir';
  • Run AutoConfig on the database tier nodes.

 For Database version Oracle 12c Release 1 (12.1.0.2) and Database version Oracle 19c:

$ /appsutil/bin/adconfig.sh contextfile=file created>

  • Source the Run file system environment file

On the application tier, source the environment file for the run file system. Run the following command to confirm that the environment is properly set:

    $ echo $FILE_EDITION

 Verify that the command returns 'run'.

  • Drop table ADX_PRE_AUTOCONFIG from APPS schema

Prior to running Rapid Install to configure the Oracle E-Business Suite Release 12 instance, check if table - ADX_PRE_AUTOCONFIG exists in APPS schema:

select object_name, object_type, owner, status from dba_objects where

upper(object_name)='ADX_PRE_AUTOCONFIG' and object_type='TABLE'

and upper(owner)='';

Execute the following commands to drop table ADX_PRE_AUTOCONFIG from APPS schema:

$ cd /patch/115/sql

$sqlplus APPS/ @txkDropAdxPreAutoConfig.sql
  • Configuring the Oracle E-Business Suite Release 12.2 Application Tier

You will now run Rapid Install to configure the application tier services.

 

Configure an Existing Instance:

 1. Ensure that the database and listeners have been started.

 2. Start the Rapid Install Wizard by entering the command rapidwiz.

      [root@datapatroltech ~]# xhost +

   [applmgr@datapatroltech ~]# export DISPLAY=localhost:10.0

   [applmgr@datapatroltech ~]# cd /oraapp/patches/StageR122/startCD/Disk1/rapidwiz

   [applmgr@datapatroltech rapidwiz]# ./rapidwiz

 3. On the Welcome Screen, click Next.

 4. Select the option Upgrade to Oracle E-Business Suite Release 12.2.0

 5. Specify Oracle Configuration Manager Details

     

You will now perform the relevant upgrade actions.

Configure the upgraded instance

On the Select Wizard Operation screen, choose Upgrade to Oracle Applications

12.2.0 option to indicate you are performing an upgrade.

 

This displays the Select  Upgrade Action screen.