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



Last updated on April 14th, 2017 at 02:21 pm

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.

 

R12.2 Upgrade

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)

http://docs.oracle.com/cd/E26401_01/doc.122/e48834/T527285T528070.htm

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.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

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

OATM Lesson -1

OATM Tutorial Part 2 Migration Sizing report

OATM tutorial Part 3 : Create New Tablespaces

OATM Tutorial part 4 Generate Migration commands

OATM Tutorial part 5 Execute Migration Commands

OATM tutorial part 6 Migration Status Reports

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 below 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 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:

http://www.oracle.com/technetwork/java/javase/downloads/index.html.

 

 

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:

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;

/

2. 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;

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

How to perform Installation of an EBS 12.2 Vision Instance

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

from dba_objects

where status!=’VALID’

order by 1,2,3;

Spool invalid_before_upgrade.log

/

Spool off

 

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.

Order Management:

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

COUNT(1)

———-

0

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 ,

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

 

Hope you like this post. We will be submitting the other parts of the R12.2 Upgrade soon

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


Leave a Reply