Oracle EBS Upgrade R12.2

In this post, explained an overview of the Oracle EBS upgrade paths and high level steps involved in Upgrading Oracle E-Business Suite 11i/R12.1 to R12.2.

Before Starting, review the below images to understand EBS Roadmap and Oracle support limits.

Overview: 

The process of upgrading Oracle E-Business Suite to Release 12 involves updating the middleware technology stack and a new APPL_TOP on the application tier. The primary utilities used in the R12 upgrade process are Rapid Install and AutoPatch. 

Rapid Install installs the new R12 technology stack and AutoPatch upgrades the Oracle E-Business Suite database to Release 12.

For 11i requirement for upgrading to Release 12.2 is that the Oracle E-Business Suite version be at 11.5.10.2 along with minimum baseline patches.

For R12, the requirement is that the Oracle E-Business Suite versions be at 12.0.4 (or higher) or 12.1.1 (or higher) prior to upgrading to 12.2.

11.5.10.2, 12.0.4+, 12.1.1+  ⇨  R12.2

Note: The database must be upgraded to the latest patchsets of 11gR2 or 12cR1 prior to the 12.2 upgrade.

Similarly to Oracle Application, Oracle Database releases also has direct upgrade paths, if direct path not available, we need to upgrade to intermediate release to reach the target release (Example: Target Release, 12cR1). 

Below listed Oracle Database releases which has direct upgrade paths to 12cR1.

10.2.0.5,11.1.0.7,11.2.0.2+  ⇨ 12cR1

High Level Steps involved in Upgrade Oracle EBS R12.2:

• Install Oracle Home 12c -- If rapidwiz not used, Oracle Examples should be installed on top of Oracle Home.
• Refer appropriate interoperability notes and upgrade Database to 12.1.0 
• Complete post upgrade scripts
• Install R12.2 File System (Core Tree Structure)
• Run the American English upgrade patch driver

Note: Run Gather Stats prior running american driver for better performance and add adequate space to the tablespace.

• Database Configuration -- eg: autoconfig
• Configure Release 12.2 EBS -- Run rapidwiz and provide the run file system contextfile during the configuration.
• Online Patching Enablement patch -- Enable ADOP 
• Apply AD/TXK Latest Patches
• Create Custom Top, if required
• Apply Release Update Pack (Example, R12.2.8)

Oracle Database Cloud Backup

Oracle Database Backup Service -- securely backup your database using RMAN interface for seamless backup and restore operations.

Step 1: Create a container in Oracle Cloud to save the backup.

Step 2: Install Oracle Cloud Backup Module 

[orauser@test opc_wallet]$ ls

cwallet.sso  cwallet.sso.lck

[orauser@test dbs]$ cat opcDEV.ora

OPC_HOST=https://storage_container_url

OPC_WALLET='LOCATION=file:/u01/orauser/dbtest/dbhome/dbs/opc_wallet CREDENTIAL_ALIAS=alias_opc'

Step 3: RMAN Configuration and backup

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='SBT_LIBRARY=/u01/orauser/dbtest/dbhome/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/u01/orauser/dbtest/dbhome/dbs/opcDEV.ora)';

RMAN> SET ENCRYPTION ON IDENTIFIED BY '<Password>' ONLY;

RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'sbt_tape';

$ rman target /

RMAN>  backup database plus archivelog;

Verify the backups are created in container.

Step 4: Using backup for restore operation.

Using the backup created in Oracle cloud, we can use it to restore/recovery during any failure of on-premises database and can also be used to create the database in Oracle cloud i.e. we can migrate Oracle Database from On-Premises to Oracle Cloud.

RMAN> SET DECRYPTION IDENTIFIED BY '<Password>';

RMAN> SET DBID=<Database_ID>;

RUN {

ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS='SBT_LIBRARY=/u01/orauser/dbtest/dbhome/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/u01/orauser/dbtest/dbhome/dbs/opcDEV.ora)';

RESTORE ...;

}

Creating Database service using cloud backup.

Create Oracle Database Service in Oracle Cloud

Process Flow:

 Step1: Create SSH key

Note: 

We can provide Passphrase to enhance security. 

To save public key use copy & paste, save it as .pub file.

Step2: Create the Database Cloud Service

Note: In Advanced setting, we can define the port and timestamp for the database.

Step 3: Apply security rules to access the cloud the service (Database).

Step 4: Use dbaas_monitor

Database Partitioning -- Oracle EBS (Part 1)

Database Partitioning -- process of logically and/or physically segmenting data (and it’s associated storage, tables and indexes) into more manageable pieces, in order to improve manageability and accessibility.

A good partitioning design lies the choice of a suitable partitioning key, typically be the column that is most frequently used as the predicate to access the data.

Partitioning Methods:

• Range Partitioning -- Example: partitioning the GL_BALANCES table as ranges by "Period Name".
• List Partitioning -- Example: Partitioning OE_ORDER_LINES_ALL table to have categories listed like open & closed Orders using OPEN_FLAG.
• Hash Partitioning -- Hash function to the partitioning key to stripe data into partitions.
• Composite Partitioning -- Data is partitioned by using both the range method and then sub-partitioned by the hash method. Example: WF_ITEM_ACTIVITY_STATUSES Partitioned with Range of Item Type and then sub partitioned using Hash Method.

Index Partitioning & Methods: 

Partitioned or non-partioned indexes can be used with partitioned or non-partitioned tables.

• Global Non-partitioned (prefixed)
• Global Partitioned (prefixed)
• Local Partitioned Prefixed
• Local Partitioned Non-Prefix

Advantages:

• It brings significant benefits in performance - Improves access path/scanning the current partition
• Easy to manage the table and index can be rebuild at partition level
• Improves purge performance
• Improves Upgrade performance
• No modifications to application queries are necessary when accessing a partitioned table.
• CBO is partition aware.

Note: If an incorrect partitioning design is chosen, partitioning can actually degrade rather than enhance performance.

Important Database Views:

DBA_TABLES

DBA_PART_TABLES

DBA_TAB_PARTITIONS

DBA_PART_KEY_COLUMNS

DBA_PART_HISTOGRAMS

DBA_PART_COL_STATISTICS

DBA_SUBPART_KEY_COLUMNS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_INDEXES