Step by step upgrade process for R12.2 Upgrade Part -1

R12.2 Upgrade consists of following part

1. Pre Upgrade preparation steps
2. 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)
3. Online Patching Enablement
4. Applying the latest  R12.AD.C.Delta.n and R12.TXK.C.Delta.n
5. Applying 12.2.n Release Update Pack (RUP)

We will be taking care of step 1 in this post.

Step by step upgrade process for R12.2 Upgrade Part -1
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 11.5.10.2 to 12.2
• Oracle E-Business Suite Release 12.2 Technology Stack Documentation Roadmap (Doc ID 1934915.1)
• 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: 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)
• Document 2666934.1, Oracle E-Business Suite Release 12.2.10 Readme
• Document 2495027.1, Oracle E-Business Suite Release 12.2.9 Readme
• Document 2393248.1, Oracle E-Business Suite Release 12.2.8 Readme
• Document 2230783.1, Oracle E-Business Suite Release 12.2.7 Readme
• Document 2114016.1, Oracle E-Business Suite Release 12.2.6 Readme

PREPARATION  STEPS BEFORE STARTING THE UPGRADE

(1) OS level parameters

• Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (1330701.1)
• Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Oracle Solaris on SPARC (64-bit)(1330702.1)
• Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for IBM AIX on Power Systems (64-bit)(1330703.1)
• Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for HP-UX Itanium(1330704.1)
• Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Microsoft Windows (64-bit)(1330706.1)

Check out the below article for detailed read out

Solaris OS parameter required for R12.2 Upgrade

(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;'
 FROM (
 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

Oracle Applications tablespace model (OATM)

(4)  Apply the RDBMS patches

• We need to migrate to either 11.2.0.4 or 12.1.0.2  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 the articles for the Upgrades How to upgrade R12.1 Instance database to 12c
• Also we need to apply the database patches listed in the below oracle support 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:
• http://www.oracle.com/technetwork/java/javase/downloads/index.html.

(5)  Run Gather stats  and purge recycle bin

(i) Ran Gather schema stats for ALL schema

exec apps.fnd_stats.gather_schema_statistics(schemaname=> 'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,options => 'GATHER AUTO');

(ii) Gather SYS, Fixed Object and Dictionary Statistics (required)

Execute all the steps as ‘SYSDBA’ user.

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;

(iii) Purge recycle bin

purge dba_recyclebin;

(6)   Purge old AWR data

• Purge AWR using EXEC dbms_workload_repository.drop_snapshot_range:
• Take the snapshot range from DBA_HIST_SNAPSHOT

select dbid,min(snap_id),max(snap_id) from DBA_HIST_SNAPSHOT group by dbid;

(7)  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
• How to perform Installation of an EBS 12.2 Vision Instance

(9)  Perform compilation of invalids in the database  and Take the object count

(i) Compile Invalid

sqlplus / as sysdba
exec sys.utl_recomp.recomp_parallel(10);

(ii)) Take invalid count before Upgrade

sqlplus / as sysdba
Spool invalid_before_upgrade.log
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
from dba_objects
where status!='VALID'
order by 1,2,3;
Spool off

(10)  Disable Custom triggers and indexes, Crontabs, scheduler

• 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
• Please disable cron jobs if any that will impact upgrade process other than file system or tablespace alert
• Disable any schedule jobs in DBMS_scheduler

(11)  Apply functional patches

(i) 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 Responsibility for audit table and logs. For Example, you can run the scrip using the following username: SYSADMIN and responsibility: Collections Agent

(ii)Subledger Accounting:

• Perform this task if you are using Oracle Subledger Accounting.
• 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.

(iii)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

(iv) Supply Chain Management Tasks
The tasks in this section are required only if you are using Oracle Supply Chain Management products.
Order Management:
Perform these tasks if you are using Oracle Order Management.

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.

(v). Product Hub: –
Checking for Duplicate Records
Applies to: Release 12.0 and 12.1
Run the following query and verify the output:

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'; 
COUNT(1)
---------
0

If the above query returns ‘0’ as the output, then perform the following steps:

• Take backup of ego_mtl_sy_items_ext_b table
• 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.
• For R12.1 and above releases, use the below query to identify the duplicate records:

SELECT COUNT() AS v_count , inventory_item_id, organization_id, revision_id, attr_group_id, data_level_id, pk1_value, pk2_value 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, organization_id, revision_id, attr_group_id, data_level_id, pk1_value, pk2_value HAVING COUNT() > 1;
no rows selected
 

For next steps. please refer below articles

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

Related Articles

Top AWR useful queries for R12.2/R12.1 Upgrade
40 question you must know about R12.2
How to clone R12.2 environment
Important Changes in Autoconfig R12.2