Upgrade E-Business Suite from 12.1.3 to 12.2.9
For instruction on how to upgrade E-Business Suite (EBS) from release 12.1.3 to 12.2.9, see Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2.
Note:
While following the instructions in the Oracle E-Business Suite Upgrade Guide, the following are a few exceptions you must perform as these are required specifically for Oracle DMW and Oracle LSH.
- Instead of instructions in chapter 4 Performing the Upgrade > section
"Performing the Upgrade to Release 12.2.0" > step 1 "Disable AOL Audit Trail
(conditional)", perform the following steps to back up the Oracle DMW and Oracle LSH audit tables and clean up the audit tables to prepare for the upgrade:
- Connect to the database as SYSDBA.
- Execute the following query to determine the space required by audit tables:
select segment_name,segment_type,bytes/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name IN ('CDR_BA_DB_PRIVILEGES_A', 'CDR_CONNECTIONS_A', 'CDR_NAMINGS_A', 'CDR_NAMING_VERSIONS_A', 'CDR_OBJECT_SUBTYPES_B_A', 'CDR_OBJECT_SUBTYPES_TL_A', 'CDR_OBJ_CLA_MAPPINGS_A', 'CDR_OBJ_UG_A', 'CDR_OUTPUTS_A', 'CDR_REMOTE_LOCATIONS_A', 'CDR_ROLES_B_A', 'CDR_ROLES_TL_A', 'CDR_SECURITY_DBACCOUNT_A', 'CDR_SOURCE_CODES_A', 'CDR_SUBTYPE_OPR_ROLES_A', 'CDR_TABLE_REFS_A', 'CDR_TABLE_REF_JOBS_A', 'CDR_UG_ROLES_A', 'CDR_USER_GROUPS_B_A', 'CDR_USER_GROUPS_TL_A', 'CDR_USER_UG_ROLES_A', 'DME_FLAG_DATA_A'); - Execute the following query to determine the total space (in GB) required in
the APPS_TS_TX_DATA tablespace to back up all the audit tables:
select sum(GB) from (select segment_name,segment_type,bytes/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name IN ('CDR_BA_DB_PRIVILEGES_A', 'CDR_CONNECTIONS_A', 'CDR_NAMINGS_A', 'CDR_NAMING_VERSIONS_A', 'CDR_OBJECT_SUBTYPES_B_A', 'CDR_OBJECT_SUBTYPES_TL_A', 'CDR_OBJ_CLA_MAPPINGS_A', 'CDR_OBJ_UG_A', 'CDR_OUTPUTS_A', 'CDR_REMOTE_LOCATIONS_A', 'CDR_ROLES_B_A', 'CDR_ROLES_TL_A', 'CDR_SECURITY_DBACCOUNT_A', 'CDR_SOURCE_CODES_A', 'CDR_SUBTYPE_OPR_ROLES_A', 'CDR_TABLE_REFS_A', 'CDR_TABLE_REF_JOBS_A', 'CDR_UG_ROLES_A', 'CDR_USER_GROUPS_B_A', 'CDR_USER_GROUPS_TL_A', 'CDR_USER_UG_ROLES_A', 'DME_FLAG_DATA_A') ) t ; - Allocate the required space (in GB) from the pervious step to the APPS_TS_TX_DATA tablespace.
- Download Oracle LSH 3.1 patch p32078417_R12_GENERIC.zip.
- Unzip patch p32078417_R12_GENERIC.zip (DO NOT APPLY PATCH).
- Go to
PATCH_Folder/32078417/cdr/patch/115/sql. - Log in to SQL*Plus as the APPS user.
- Execute the script
cdr31ugppreauditscript.sqlto back up the tables. - Set the audit groups (LSH_Audit and DMW_AUDIT) status to "Disable-Purge Table".
- Run the Audit Table Update Request program. This will drop all triggers/views and data from _A tables.
- Go to
PATCH_Folder/32078417/cdr/patch/115/sql. - Log in to SQL*Plus as the APPS user.
- Execute the script
cdr31ugppostauditscript.sqlto drop the audit (_A) tables and the corresponding synonyms. - Connect as APPS user and back up the CDR_REMOTE_LOCATIONS table
data:
Create table CDR.CDR_REMOTE_LOCATIONS_BK31 as select * from CDR.CDR_REMOTE_LOCATIONS;
-
Before you disable the custom triggers in chapter 4 Performing the Upgrade > section "Performing the Upgrade to Release 12.2.0" > step 5 Disable custom triggers, constraints, indexes, business events, and VPD (conditional), drop the trigger "OWBSYS.CDR_RTREPOS_TG". Then, execute the following command to find the custom triggers that you must disable:
select trigger_name from dba_triggers where trigger_name like '%CDR%';Follow the remaining instructions in step 5 Disable custom triggers, constraints, indexes, business events, and VPD (conditional).
-
Instead of instructions in chapter 4 Performing the Upgrade > section "Database and System Administration Tasks" > step 3 "Gather schema statistics (required)", perform the instructions in Gathering Schema Statistics for DMW and LSH (Doc ID 2220975.1). For all other EBS schemas, execute the following command:
Note:
To save some upgrade time, perform this task at least 1 or 2 weeks before upgrading to Oracle LSH release 3.1.FND_STATS.GATHER_SCHEMA_STATISTICS (<schema name>, <estimate percent>, <degree of parallelism>, <backup flag>, <restart request ID, if applicable>, <history mode>, <gather options>, <modificationsthreshold>, <invalidate dependent cursors>);For example:
FND_STATS.GATHER_SCHEMA_STATISTICS('AK', 10, 8, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');You can get the schema details by executing the following query:
set lines 1000 set pages 1000 select 'FND_STATS.GATHER_SCHEMA_STATISTICS('''|| ORACLE_USERNAME ||''', 10, 8, ''NOBACKUP'', NULL, ''LASTRUN'', ''GATHER AUTO'', 10, ''N'')' prep_statement from fnd_oracle_userid where ORACLE_USERNAME not in ('APPS','CDR','APPLSYS'); ; - In chapter 4 Performing the Upgrade > section "Performing the Upgrade to Release 12.2.0" > step 13 "Apply AD 12.2 upgrade driver (required)", if any error occurs while applying the patch 10117518 as a prerequisite to run the adgrants.sql, see R12 E-Business Suite On Oracle Cloud Administration Error "ORA-20000: ORA-01917: user or role 'APPS' does not exist"When Running Adgrants.sql Against A Cloud Multi Tenant Database (Doc ID 2447786.1).
- In chapter 4 Performing the Upgrade > section "Performing the
Upgrade to Release 12.2.0" > step 3 "Update init.ora with upgrade parameters
(required)", along with the instructions in See Database
Initialization Parameters for Oracle Applications Release 12.2 (Doc ID:
396009.1), execute the following query to update the "container_data" parameter (in
both CDB and PDB) and revoke the administer database trigger:
alter system set audit_trail=NONE scope=spfile Revoke ADMINISTER DATABASE TRIGGER from APPS ; alter system set container_data=ALL scope=spfile;After the EBS upgrade is complete, you must revert all the changes done in the above query. For instruction on how to rever the changes, see Revert Database Init Parameters and Grant Access to APPS.
- In chapter 4 Performing the Upgrade > section "Enabling Online Patching", while reviewing the output of the script ADZDPSUM.sql, do not register any custom schemas using the script ADZDREG.sql.
- Instead of instructions in chapter 4 Performing the Upgrade > section "Upgrading to the Latest Code Level" > sub-section "Apply the Latest AD and TXK for Release 12.2" > step 3.1 "Running adsplice", apply the AD Splicer patch for Oracle LSH, patch number 6114439, which is in the Oracle LSH media pack. For more information, see Oracle Life Sciences Data Hub Installation Guide Release 3.0.
-
Instead of instructions in chapter 4 Performing the Upgrade > section "Upgrading to the Latest Code Level" > sub-section "Apply the Latest Oracle E-Business Suite Release Update Pack for Release 12.2", perform instructions in Oracle E-Business Suite Release 12.2.9 Readme (Doc ID 2495027.1) on My Oracle Support > section "Section 8: Apply Oracle E-Business Suite 12.2.9 Release Update Pack" > sub-section "8.1 Path A — Upgrade and New Installation Customers upgrading to Oracle E-Business Suite 12.2.9 Release Update Pack"
After performing the instructions in sub-section "8.1 Path A — Upgrade and New Installation Customers upgrading to Oracle E-Business Suite 12.2.9 Release Update Pack", see Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2 > chapter 5 "Performing Post-Upgrade Tasks" > perform instructions in sections "Database and System Administration TasksDatabase and System Administration Tasks" through "Oracle XML Publisher Tasks". After that, instead of performing the post-upgrade steps in chapter 5 "Performing Post-Upgrade Tasks", perform instructions in Oracle E-Business Suite Release 12.2.9 Readme (Doc ID 2495027.1) on My Oracle Support > section "Section 9: Post-Update Steps".
Parent topic: Upgrading E-Business Suite from 12.1.3 to 12.2.9