R12.2 Upgrade consists of following part
- Pre Upgrade preparation steps
- Main upgrade using Main Upgrade Driver for R12.2.0 (merged with pre-installed Oracle E-Business Suite Consolidated Upgrade Patch for R12.2.0)
- Online Patching Enablement
- Applying the latest R12.AD.C.Delta.n and R12.TXK.C.Delta.n
- Applying 12.2.n Release Update Pack (RUP)
We will be taking care of step 1 in this post.
Step by step upgrade process to R12.2 Upgrade part -2
Step by step upgrade process to R12.2 Upgrade part -3
Step by step upgrade process for R12.2 Upgrade Part -4
DOCUMENT REFERENCE for R12.2 Upgrade
Database Preparation Guidelines for an Oracle E-Business Suite Release 12.2 Upgrade (Doc ID: 1349240.1).
Oracle E-Business Suite Documentation Resources, Release 12.2 document (Doc ID: 1498411.1)
Oracle® E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2 Part No E48839-02
Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)
Oracle E-Business Suite Release 12.2.2 Readme (Doc ID 1506669.1)
Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Doc ID 1531121.1)
Applying the 12.2.2 AD and TXK Release Update Pack (Doc ID 1560906.1)
12.2 Information Center: Doc ID: 1581299.1
Oracle E-Business Suite Release 12.2: Upgrade Sizing and Best Practices Doc ID: 1597531.1
Best Practices for Minimizing Oracle E-Business Suite Release 12 Upgrade Downtime Doc ID: 1581549.1
R11i / R12 : Planning Your Oracle E-Business Suite (EBS) Upgrade from Release 11i to Release 12 Doc ID:1406960.1
http://docs.oracle.com/cd/E26401_01/doc.122/e48839.pdf 12.0 – 12.1 to 12.2
http://docs.oracle.com/cd/E26401_01/doc.122/e48834.pdf 18.104.22.168 to 12.2
Oracle E-Business Suite Release 12.2.5 Readme (Doc ID 1983050.1)
Oracle E-Business Suite Release Notes, Release 12.2 (Doc 1320300.1)
Oracle E-Business Suite Release 12.2.4 Readme (Doc 1617458.1)
Oracle E-Business Suite Release 12.2.5 Readme (Doc 1983050.1)
Oracle E-Business Suite Release 12.2: Online Patching FAQ (Doc 1583902.1)
Related Articles on Oracle E-Business Technology Blog- https://blogs.oracle.com/stevenChan
Oracle E-Business Suite Release 12.2.5 Now Available
Oracle E-Business Suite Release 12.2.4 Now Available
Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Cloning Oracle E-Business Suite Release 12.2 with Rapid Clone (Doc ID 1383621.1)
PREPARATION STEPS BEFORE STARTING THE UPGRADE
1) OS level parameters
|Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Oracle Solaris on SPARC (64-bit) (Doc ID 1330702.1)|
Check out the below article for detailed read out
2) Drop event alert triggers in custom schemas
|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.
There were no Custom Triggers so did not run the alrdtrig.sql. Run the below query and make sure you don’t have any Custom Trigger ALR%
SQL> select distinct owner, TRIGGER_NAME, TRIGGER_TYPE, STATUS from dba_triggers where trigger_name like ‘ALR%’;
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
—————————— —————————— ——————————————
APPS ALR_ALR_DISTRIBUTION_LISTS_IAR AFTER EACH ROW DISABLED
APPS ALR_ALR_DISTRIBUTION_LISTS_UAR AFTER EACH ROW DISABLED
3) Review sizes of tablespaces and Migrate to OATM
|Read Oracle E-Business Suite Release 12.2: Upgrade Sizing and Best Practices (Doc ID: 1597531.1).
And appropriately resized the tablespace
It is good to have the last datafile for each tablespace set to unlimited autoextent. Please use the following SQL to make the last datafile autoextend on:
SELECT ‘ALTER database DATAFILE ‘||FILEID||’ autoextend on maxsize 50 g;’
SELECT MAX(FILE_ID) FILEID FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
Migrate Existing Objects to New Tablespace Model (recommended):
Oracle E-Business Suite Release 12 environments contain the new Oracle Applications Tablespace Model (OATM). This model is based on database object type rather than product affiliation, making it simpler to manage and requiring far fewer tablespaces and operating system files.
If you previously upgraded your environment from Release 11i to Release 12, then the upgrade process created tablespaces for all new products, configured the database for the new tablespace model, and created new objects. However, it did not automatically migrate your existing objects. If you have not already done so, Oracle strongly recommends that you use the Tablespace Migration Utility to perform this migration now. Note that this utility is not supported for use after you enable Online Patching, so you cannot perform the migration after your environment is upgraded to Release 12.2. If you choose not to migrate to OATM now, then you must continue to manage your tablespaces separately.
Check out below article for OATM migration
4) Apply the RDBMS patches
|We need to migrate to either 22.214.171.124 or 126.96.36.199 database version in order to migrate to R12.2
Note: We can do this is separate downtime also.
We need to follow the interoperability doc for the upgrade
Check below articles for the Upgrades
Also we need to apply the database patches listed in the below metalink doc.
Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Install JRE on the database tier (conditional)
If you are planning to run Rapid Install in Upgrade Mode by using the Use Existing ORACLE HOME option, then you must install JRE in the Database ORACLE_HOME/appsutil as follows:
Download the latest JRE 7 Update. For optimum stability, performance, scalability, and OS vendor support, use the latest available update of JRE for the Oracle
E-Business Suite database tier. The JRE download location is:
5) Run Gather stats and purge recycle bin
|1) Ran Gather schema stats for ALL schema
2) Gather SYS, Fixed Object and Dictionary Statistics (required)
Execute all the steps as ‘SYSDBA’ user.
1. Gather SYS schema statistics:
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
cascade => TRUE);
2. Fixed Object and Dictionary Statistics
These should have been previously gathered, correct and up-to-date on the
3. Purge recycle bin
6) Purge old AWR data
|Purge AWR using EXEC dbms_workload_repository.drop_snapshot_range:
Take the snapshot range from DBA_HIST_SNAPSHOT(<from_snap>,<to_snap>,<dbsid>):
select dbid,min(snap_id),max(snap_id) from DBA_HIST_SNAPSHOT group by dbid;
7) Disable Audit trail
|Disable Audit trail
Login as sysadmin, navigate to system administrator responsibility -> Security -> Audit Trail -> Groups
Enable Query window by pressing F11 and choose ENABLED option for Group State and press Ctrl+F11 to get the results.
Except for Application Name value – “Application Object Library” remaining all “Enabled” status should be made to “Disable Purge Table” and save the record.
Once the upgrade one we have to re-enable these by setting it to “Enabled” status and saving record
8) Install R12.2 rapid install
|Install R12.2 rapid install using rapidwiz and choosing upgrade file system. Input will be similar to New installation only
Check out below articles on detailed steps
9) Perform compilation of invalids in the database and Take the object count
|1) Exec sys.utl_recomp.recomp_parallel(10);
2) col object_name for a30
col owner for a20
col status for a10
col cnt for 999999
break on report on owner
compute sum of cnt on report owner
set lines 120
set pages 5000
select owner,object_name,status, 1 cnt
order by 1,2,3;
10) Disable Custom triggers and indexes
|1) Need to Disable Custom Triggers & custom Indexes. Keep a script to disable and enable the triggers and indexes. As we will need to enable them after the upgrade is completed
2) Please disable cron jobs if any that will impact upgrade process other than file system or tablespace alert
3) Disable any schedule jobs in DBMS_scheduler
11) Apply functional patches
1) Perform this task only if you are using Oracle Advanced Collections with Strategies.
Strategy Workflow checks Applies to 12.0 and 12.1 release levels.
Apply the diagnostic scripts patch 13027498:R12.IEX.A when upgrading from R12.0.X and apply 13027498:R12.IEX.B when upgrading from 12.1.X. This patch contains scripts for Collection Strategy Workflow.
From the Collections Agent responsibility using the user name ‘SYSADMIN’, stop any scheduled Workflow Background Process concurrent program request for the following Item Types:
• IEXSTRY – IEX: Collection Strategy Work Flow
• IEXSTFFM – IEX: Strategy Fulfilment Mailer
• IEXSTRCM – IEX: Strategy Custom Work Flow
Run the script $IEX_TOP/patch/115/sql/iexswowf.sql to check and clean the strategies running without workflow. This script requires two parameters: FND username and Reponsibility for audit table and logs. For Example, you can run the script
using the following:
• username: SYSADMIN
• responsibility: Collections Agent
2. Subledger Accounting:
Perform this task if you are using Oracle Subledger Accounting.
1. Clean up Advance Queues from prior Create Accounting processes before enabling EBR.
Applies to 12.0 and 12.1 release levels.
Apply patch 13420532:R12.XLA.A when upgrading from Release 12.0.x and apply Patch 13420532:R12.XLA.B when upgrading from Release 12.1.x to clean up temporary advance queues that were created by prior Create Accounting program processes. Pending Advance Queues of completed Create Accounting processes do not create upgrade issues, but must be cleaned up for EBR.
Note: You can perform this pre-upgrade step while the pre-upgrade system is online.
This patch delivers script xlaqclen.sql to STOP and DROP the Advance Queues from prior completed runs of Create Accounting program. Advance Queues of Create Accounting program were not dropped after program completion, if there were no accounting events processed by the program. These pending Advance Queues of already completed Create Accounting programs do not cause any corruption, however need to be cleaned up for EBR. This patch needs to be applied only by customers preparing for Oracle Applications release 12.2 upgrade. This patch is essential before enabling EBR in 12.2. Please refer to Oracle Applications release 12.2 release notes for more details on EBR.
3. Human Resource Management (HRMS)
All custom database objects that are mapped to seeded APIs/Row Handlers must be
changed from LONG/LONG RAW to CLOB.
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’;
PACKAGE_NAME OBJECT_NAME ARGUMENT_NAME DATA_TYPE
—————————— —————————— —————————— ——————————
PSB_HR_POPULATE_DATA_PVT INSERT_POSITION_TXN_INFO P_COMMENTS LONG
select object_name, object_type, owner from dba_objects where object_name = ‘PSB_HR_POPULATE_DATA_PVT’;
OBJECT_NAME OBJECT_TYPE OWNER
—————————— ——————- ——————————
PSB_HR_POPULATE_DATA_PVT PACKAGE APPS
PSB_HR_POPULATE_DATA_PVT PACKAGE BODY APPS
4. Supply Chain Management Tasks
The tasks in this section are required only if you are using Oracle Supply Chain Management products.
Perform these tasks if you are using Oracle Order Management.
1. Check for the Nullable Hold Entity ID.
Applies to: Release 12.1
If you are upgrading from release 12.1, then 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.
5. Product Hub: –
Checking for Duplicate Records
Applies to: Release 12.0 and 12.1
Run the following query and verify the output:
SELECT COUNT(1) FROM SYS.ALL_IND_COLUMNS WHERE INDEX_NAME = ‘EGO_MTL_SY_ITEMS_EXT_B_U2’ AND COLUMN_NAME = ‘UNIQUE_VALUE’
AND INDEX_OWNER = ‘EGO’;
SQL> SELECT COUNT(1) FROM SYS.ALL_IND_COLUMNS WHERE INDEX_NAME = ‘EGO_MTL_SY_ITEMS_EXT_B_U2’ AND COLUMN_NAME = ‘UNIQUE_VALUE’
AND INDEX_OWNER = ‘EGO’; 2 3 4 5
If the above query returns ‘0’ as the output, then perform the following steps:
1. Take backup of ego_mtl_sy_items_ext_b table
2. Refer to Note ID: 953449.1 and run the script provided in the ‘Identification Script’ column within the ‘Solution – Datafix’ section to identify duplicate records.
If there are duplicate records, then run the scripts provided in the ‘Fix’ column within the ‘Solution – Datafix’ section for the appropriate base version to delete duplicate records.
Note: You must eliminate duplicate records before upgrading to Release 12.2. If you require help eliminating duplicate records, then contact Oracle Support.
REFER Duplicate User Defined Attribute (UDA) Records Created in EGO_MTL_SY_ITEMS_EXT_B (Doc ID 953449.1)
b. For R12.1 and above releases, use the below query to identify the duplicate records:
SELECT COUNT(*) AS v_count ,
FROM apps.ego_mtl_sy_items_ext_b a
WHERE EXISTS (SELECT 1
FROM apps.ego_fnd_dsc_flx_ctx_ext b
WHERE a.attr_group_id = b.attr_group_id
AND application_id = 431
AND multi_row <> ‘Y’)
GROUP BY inventory_item_id,
HAVING COUNT(*) > 1;
no rows selected
Hope you like this post. We will be submitting the other parts of the R12.2 Upgrade soon