About mswing

Software Engineer using Pascal, C, and Ada to develop DSP code. Ingres DBA and Developer Informix DBA and Developer Oracle DBA and Object Modeler Oracle Parallel Server DBA Oracle Applications DBA EBS Upgrade Expert Author and Instructor

What is the new view ad_objects and how is it different from dba_objects?

  • Developers often use the user_objects or all_objects data dictionary view to confirm that there are no unexpected invalid objects.
  • Due to a database limitation these dictionary views only return correct object status after running a full compilation procedure (utl_recomp.recomp_parallel or ad_zd.compile).
  • As a workaround, you can check object status using the ad_objects view included with online patching.

select * from ad_objects where status=’INVALID’;

If we compare the total number of objects in ad_objects to dba_objects, we find that ad_objects has one more object than dba_objects:

SQL> select count(*) from ad_objects

COUNT(*)
———-
394774

SQL>  select count(*) from dba_objects

COUNT(*)
———-
394773

So, what’s the difference between ad_objects and dba_objects?
SQL> select object_name, object_type, owner from ad_objects
2  minus
3  select object_name, object_type, owner from dba_objects;
OBJECT_NAME
——————————————————————————–
OBJECT_TYPE        OWNER
——————- ——————————
_default_auditing_options_
TABLE            SYS
How are the columns different between ad_objects  and dba_objects? Compare the following table structures below:

SQL> desc ad_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                   NOT NULL VARCHAR2(30)
OBJECT_TYPE                        VARCHAR2(18)
ACTUAL                         VARCHAR2(1)
STATUS                         VARCHAR2(7)
CTIME                            DATE
MTIME                            DATE
STIME                            DATE

SQL> desc dba_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                        VARCHAR2(128)
SUBOBJECT_NAME                     VARCHAR2(30)
OBJECT_ID                        NUMBER
DATA_OBJECT_ID                     NUMBER
OBJECT_TYPE                        VARCHAR2(19)
CREATED                        DATE
LAST_DDL_TIME                        DATE
TIMESTAMP                        VARCHAR2(19)
STATUS                         VARCHAR2(7)
TEMPORARY                        VARCHAR2(1)
GENERATED                        VARCHAR2(1)
SECONDARY                        VARCHAR2(1)
NAMESPACE                        NUMBER
EDITION_NAME                        VARCHAR2(30)

How to Determine that all Tables and Materialized Views have an Editioning View

After applying 13543062, we ran ADZDSHOWDDLS.sql to see if all tables and materialized views had editioning views. Below, an excerpt of ADZDSHOWDDLS.out shows the editioning results for tables and materialized views:

Phase                               Count Status

UPGRADE_TABLE          18355 S – Successfully Executed

UPGRADE_MVIEW              1 N – Not Executed                            1 R – Running                          113 S – Successfully Executed                           11 E – Error

Run the following SQL statement to check the count of editioning views versus the count of tables and materialized views:

select * from dba_objects where (object_type in (‘VIEW’) and object_name like ‘%#’)

union

select * from dba_objects where object_type in (‘MATERIALIZED VIEW’, ‘TABLE’);

Of course, we can do it an easier way and compare VIEW_NAME and TABLE_NAME in DBA_EDITIONING_VIEWS and do a “count” to see if the result agrees with the results from above.

describe dba_editioning_views

OWNER                 NOT NULL VARCHAR(128)

VIEW_NAME         NOT NULL VARCHAR(128)

TABLE_NAME       NOT NULL VARCHAR(128)

Or, select table_name where the view_name doesn’t exist.

Or, use the Section 20 SQL from ADZDDBCC.sql to find tables that don’t have an editioning view. In order to patch custom tables using online patching, custom tables require an editioning view and APPS synonym. The following SQL will find tables that may not have an editioning view.

**********************************************************************

SECTION-20  [full]

**********************************************************************

“Table must have an editioning view.”

– P2: These tables may not be patched using online patching.

– Fix:  Execute the table upgrade procedure.

SQL> exec ad_zd_table.upgrade(table_owner, new_table_name)

Note: Tables that are dynamically created by application runtime can be ignored.

Note: Tables that end with “_A” are typically audit tables by the Audit Trail feature, and can be ignored.

Note: This check is only active after Online Patching Enablement.

#

select tab.owner owner, tab.table_name table_name

from dba_tables tab

where tab.owner in

( select oracle_username from system.fnd_oracle_userid

where  read_only_flag in (‘A’, ‘B’, ‘E’) )

and tab.temporary = ‘N’

and tab.secondary = ‘N’

and tab.iot_type is null

and tab.tablespace_name not in (‘APPS_TS_NOLOGGING’, ‘APPS_TS_QUEUES’)

and tab.table_name not like ‘%$’

and not regexp_like(tab.table_name, ‘^AQ\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^AW\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^MLOG\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^BSC_DI_[0-9_]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^BSC_D_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_ADJUSTMENT_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_DETAIL_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_SUMMARY_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_DAILY_POST_INT_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_INTERCO_BSV_INT_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_BAL_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_INTERIM_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^XLA_GLT_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ICX_POR_C[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ICX_POR_UPLOAD_[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^IGI_SLS_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^JTF_TAE_[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^JTY_[0-9]+_.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ZPBDATA[0-9]+_EXCPT_T$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ZX_DATA_UPLOAD_.*$’, ‘c’)

/* Note: Exclusion list for AD varies by release */

and ( ( tab.table_name not in

(‘TXK_TCC_RESULTS’,

‘AD_DEFERRED_JOBS’,

‘AD_TABLE_INDEX_INFO’,

‘FND_INSTALL_PROCESSES’,

‘AD_UTIL_PARAMS’,

‘AD_PATCHED_TABLES’,

‘AD_ZD_DDL_HANDLER’,

‘AD_OBSOLETE_OBJECTS’,

‘FND_PRODUCT_INSTALLATIONS’)

and

( select codelevel from ad_trackable_entities

where upper(abbreviation)=’AD’ ) < ‘C.1’ )

or

( tab.table_name not in

(‘TXK_TCC_RESULTS’,

‘AD_DEFERRED_JOBS’,

‘AD_TABLE_INDEX_INFO’,

‘FND_INSTALL_PROCESSES’,

‘AD_UTIL_PARAMS’ )

and

( select codelevel from ad_trackable_entities

where upper(abbreviation)=’AD’ ) >= ‘C.1′ )

)

and (tab.owner, tab.table_name) not in

( select qt.owner, qt.queue_table

from   dba_queue_tables qt )

and (tab.owner, tab.table_name) not in

( select mv.owner, mv.container_name

from   dba_mviews mv )

and (tab.owner, tab.table_name) in

( select syn.table_owner, syn.table_name

from   dba_synonyms syn

where  syn.owner  in

( select oracle_username from system.fnd_oracle_userid

where  read_only_flag =’U’ ) )

and not exists

( select ev.owner, ev.view_name

from dba_editioning_views ev

where ev.owner      = tab.owner

and ev.table_name = tab.table_name

and ev.view_name  = substrb(tab.table_name, 1, 29)||’#’ )

and exists

( select null

from fnd_oracle_userid au, dba_users du

where au.oracle_username = du.username

and au.read_only_flag  = ‘U’

and du.editions_enabled = ‘Y’ )

and not exists

( select 1

from system.fnd_oracle_userid fou

, fnd_product_installations fpi

, ad_obsolete_objects aoo

where fpi.application_id = aoo.application_id

and fou.oracle_id = fpi.oracle_id

and fou.oracle_username = tab.owner

and aoo.object_name = tab.table_name

and aoo.object_type = ‘TABLE’ )

order by tab.owner, tab.table_name

/

 

Overview of Online Patching Phases

Prepare

Use adop phase=prepare sync_mode=delta. Sync_mode=delta is a new feature delivered in AD-TXK Delta 8. It is much faster than the previous sync_mode = patch. The new sync_mode=delta takes about 4-10 minutes on TruTek test servers. If the previous patch cycle was aborted, then FS_CLONE is run during the next prepare. The prepare phase can fail due to long running concurrent requests that are incompatible with the ADZDPATCH concurrent program.

FS_CLONE

Will copy all files including customizations and configurations – takes about 1 hour 40 minutes.Typiically, fs_clone only needs to be run after an abort. The usual sequence would be:

abort

cleanup (full)

fs_clone

Apply

We tested three apply modes: normal (default), hotpatch and downtime.

We found that downtime patching works, but the system must be down and the patch needs to be tested first in a test environment. Using hotpatch when the patch is not a hotpatch can result in a hung patch that can’t be aborted and can also cause an abandoned node. Neither downtime or hotpatch mode use an adop patch cycle and therefore cannot be aborted. Hotpatch mode requires the WebLogic Admin server to be running.

Finalize

Finalize prepares for the cutover by compiling invalid objects. This helps reduce the time cutover may need.

We found that if developers are applying hot custom code during finalize, that the compilation of objects can take a very long time (19 hours in one test)

Cutover

This is the most sensitive phase in ADOP for several reasons:

Users are not able to work during this phase, so the downtime needs to be very predictable

Any underlying code changes that affect tables, materialized views, and other objects may require the regeneration of logical views for tables, and regeneration of materialized views. The regeneration of materialized views from logical views is accomplished fairly quickly, however, repopulating the materialized view can take hours. I don’t think it is possible to prevent the regeneration of MVs, but we need to be able to predict the time it will take to regenerate MVs and other objects, so we can accurately advise the users/business of the downtime associated with each cutover.

The running concurrent requests need to be documented before we shutdown the concurrent managers. The ICM must also be shutdown. Otherwise, the cutover can take several hours to complete. This is because cutover waits for running concurrent requests to complete. It’s better to work with the business users and ask them to put long running requests on hold, before cutover starts.

We tested the cm_wait parameter (units are minutes). We found that no new requests were started, but the Internal Concurrent Manager is not shutdown/stopped. It took a long time for associated database sessions to be terminated.

Cleanup If a patch cycle has failed, it is possible to abort the patching cycle and return to normal runtime operation. Abort can be run for Prepare, Apply, Finalize, and Cutover phases. The abort command can only be used before successful completion of the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle. An abort of prepare, apply or finalize, if complete on the master node and it fails on a slave node, will cause an abandoned node. There are two solutions: first, detach and re-attach the abandoned node. This can be very time consuming. If you have a “pairsfile” created ahead of time this can be done fairly quickly. There is also a  solution (undocumented on MOS) that has worked in all test cases, that requires shared disk on the apps tier. This solution is an update to the AD_ADOP_SESSIONS table, followed by an FS_CLONE.

Abort

This phase seems to be the easiest, and the patch session will determine if the cleanup needs to be standard or full. You can specify quick, but we’ve found it’s safest to let the cleanup determine what mode to use. If the proper mode is not used, then cleanup may be run during the prepare( followed by a fs_clone), extending the time for prepare.

Brief Comparison of R12.1.3 and R12.2.6 Upgrades

The table below shows the major comparable steps in the 11i to 12.1.3 upgrade and the 12.1.3 to 12.2.6. The highlighted sections in the 12.2.6 upgrade indicate the new steps relative to past upgrades. These new sections, while they don’t take very long to complete during the downtime window, require extensive effort to prepare the instance for the upgrade.

For example, the more customizations you have, the longer it will take to make sure your customizations are minimally compliant to the Release 12.2 development standards. The process of updating your customizations to match new table structures is still a major part of fixing your customizations, but now you must also make sure your customizations are at least minimally compliant.

The second new step highlighted below requires your database (DB) and middle tier (MT) to be patched to a level specified in the ETCC scripts, checkDBpatch.sh and checkMTpatch.sh. While these scripts are easy to run, it’s not always straightforward to resolve patch conflicts in the database or the middle tier. This step is complicated because the list of required patches is continuously changing.

It’s not so much that these new steps are difficult, but they are new and different from all previous upgrades and take some time to understand and implement.

Upgrade to 12.1.3                               Upgrade to 12.2.6

11i  -> 12.1.3                                       12.1.3  -> 12.2.6

Major Functional Changes                  Major Technical Changes

11i pre-upgrade patches                     12.1.3 pre-upgrade patches

Upgrade to 12.1.1 (16 hours)              Upgrade to 12.2.0 (8 hours)

1) Online Enablement Patch –

Requires minimal compliance

for customizations

2) DB and MT to have all patches

specified by ETCC

Upgrade to 12.1.3                                Upgrade to 12.2.6

 

Using ADOP in Release 12.2 – Prepare phase details

Prepare

When the prepare phase is run, files are copied/synchronized from the Run filesystem to the Patch filesystem and code stubs are created in the patch edition of the database. These stubs are instantiated when they are called the first time or the actualize_all parameter is used.

The second file system contains a copy of all the components that make up an application tier file system, including:

    • APPL_TOP – Oracle E-Business Suite code
    • INST_TOP- Instance Configuration Home
    • FMW_HOME – Oracle Weblogic Server Home and Oracle E-Business Suite Domain
    • ORACLE_HOME – Oracle Application Server Home, Forms, Reports
    • IAS_ORACLE_HOME – Oracle OHS Home
    • COMMON_TOP – Oracle E-Business Suite Java code, third-party libraries

During the prepare phase, adop performs the following steps:

  • Checks whether to perform a cleanup.
  • Validates the system configuration to ensure that the system is ready to start an online patching cycle.
  • Checks to see if the database is prepared for online patching:
  • Checks if the database user is edition-enabled.
  • Checks to see if the patch service has been created. adop requires that a special database service exists for the purpose of connecting to the patch edition. Its existence is validated when the prepare phase is run.
  • Checks to see if logon trigger exists and is enabled.
  • Checks the integrity of the database data dictionary, refer to MOS Note 1531121.1, Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2.
  • Checks that the E-Business Suite Technology Codelevel Checker (ETCC) has has been run, to verify that all required patches have been applied to the database.
  • Checks system configuration on each application tier node to make sure that each application tier node is correctly registered, configured, and ready for patching.
  • Checks for the existence of the “Online Patching In Progress” (ADZDPATCH) concurrent program. This program prevents certain predefined concurrent programs from being started, and as such needs to be active while a patching cycle is in progress (that is, while a database patch edition exists).

ADZDPATCH

If the ADZDPATCH program has not yet been requested to run, a request is submitted. If the request status is pending, it may be waiting for an incompatible program to finish. After incompatible programs finish, the ADZDPATCH status will change to running, and it will allow the prepare phase to proceed.

The next stage depends on whether the concurrent managers are running:

If the concurrent managers are all down, the prepare phase continues, with ADZDPATCH entering a status of pending (with the highest priority) until the managers are started.

If the concurrent managers are partially up, but there is no manager defined that can run ADZDPATCH, then the prepare phase will exit with an error.

If the concurrent managers are up, and there is one defined that can run ADZDPATCH, processing will loop until ADZDPATCH changes status from pending to running (that is to say, as noted in Step 2, no incompatible programs are found). The prepare phase then continues.

Note: ADZDPATCH is cancelled when the cutover phase is complete.

  • Invokes the TXK script $AD_TOP/patch/115/bin/txkADOPPreparePhaseSynchronize.pl to synchronize the patches which have been applied to the run APPL_TOP, but not the patch APPL_TOP. The script depends on the adop repository for patches that have been applied on the run APPL_TOP but not the patch APPL_TOP.
  • Checks the database for the existence of a patch edition, and creates one if it does not find one. An example from the log file that shows the prepare checking for the patch edition:

EVENT      Prepare System

 700202704 09:00:10 00:00:00 ad.plsql.ad_zd.create_edition

STATEMENT  SQL: create edition V_20170316_0900

    EVENT      Patch edition not yet created by master node.

    EVENT      Will wait for another minute and retry.

  • Calls the $AD_TOP/patch/115/bin/txkADOPPreparePhaseSanityCheck.pl script again to confirm that the database connection to the patch edition is working.
  • Prepare does not run FS_CLONE. Prepare does run:

                    FsCloneStage

                    FsCloneApply

  • Prepare has the old default sync_mode of “patch”. This can be thought of as the file sync mode, because it synchronizes the files of the Run edition to the Patch edition. The new value for sync_mode that is introduced with AD-TXK Delta 8, is “delta”. Delta mode can use rsync to significantly speed up the prepare phase.

There are two values for sync_mode:

sync_mode = patch, and

sync_mode = delta, (uses rsync) – this is new in AD-TXK delta 8

The benefit besides being much faster, is that sync_mode=deltat also copies all customizations and ignores the adop_sync.drv.

Much faster than the other two methods, this delta synchronization method uses your choice of third-party utility to synchronize the file systems by copying files as applicable from the source directory to the destination directory, optionally ignoring any files and directories you may decide to specify in an exclusion file.

 To use this method, specify the parameter/value pair sync_mode=delta on the adop command line:

$ adop phase=prepare sync_mode=delta

The delta_sync_drv.txt file includes examples for setting up synchronization using rsync on UNIX.

Step 2 to Upgrade to R12.2.0 – Preinstall Patches, CUP6 and Helpful MOS Notes

This post walks you through the preinstall patches that you’ll need to prepare to upgrade to R12.2.0. Here’s a list of important My Oracle Support Notes associated with this post:

■Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes Doc ID 1594274.1

■R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] Doc ID 1448102.2 

■Oracle E-Business Suite Release 12.2 Technology Stack Documentation Roadmap Doc ID 1934915.1 

Oracle E-Business Suite Release Notes, Release 12.2  Doc ID 1320300.1

For instructions on applying Oracle E-Business Suite Consolidated Upgrade Patch 6 (CUP6) for Release 12.2 (patch 19796566:12.2.0), refer to step, ‘Apply all Consolidated Upgrade Patches (CUPs)’,  in Chapter 3, Section 10 of the Oracle E-Business Suite Upgrade Guide, Release 12.0 and 12.1 to 12.2, Part No. E48839-09. Also refer to My Oracle Support Knowledge Document 1320300.1, for more current details and the README.txt from patch 19796566. All downloads for patch 19796566 – 418, as of Feb 8 2016. When the Upgrade Guide says to apply the Consolidated Upgrade Patch (CUP) for Release 12.2.0 (Patch 18007406:12.2.0) in pre-installation mode on the Run Edition File System, please apply patch 19796566.

In section 3.1 of My Oracle Support Knowledge Document 1320300.1, it says:

“Prerequisite: Apply the AD Upgrade Patch for Release 12.2 (Patch 10117518) by merging it with the latest Consolidated Upgrade Patch (CUP) for AD (Patch 19796564:R12.AD.C).

  1. The AD Upgrade Patch for Release 12.2 (Patch 10117518) readme contains information about the latest Consolidated Upgrade Patch (CUP) for AD (Patch 19796564:R12.AD.C).

You must follow the Patch 10117518 readme and apply it by merging it with the latest Consolidated Upgrade Patch for AD, along with any other patches mentioned in the Readme, as instructed in the readme and by referring to the Upgrade Guides.

2. Apply the Consolidated Upgrade Patch (CUP) for Release  12.2.0 (Patch 19796566) in pre-installation mode on the Run Edition File System.”

Use adpatch preinstall=y, to apply Patch 19796566.

3.  Apply the patches that you need from MOS Note 1448102.2. Below in this post is an example of patches we needed.

Note: Before applying the latest CUP in pre-installation mode: If you have applied any other patch previously in pre-installation mode and do not intend to merge it with 12.2.0 upgrade driver u10124646.drv, clean up the directory <APPL_TOP>/admin/<TWO_TASK>/preinstall on run file system after taking the backup of the current directory.

4.  Merge the patch drivers in $APPL_TOP/admin/$TWO_TASK/preinstall directory with the 12.2.0 upgrade driver $AU_TOP/patch/115/driver/u10124646.drv:

All of the patch driver files located under $APPL_TOP/admin/<TWO_TASK>/preinstall are merged with the 12.2.0 upgrade driver <AU_TOP>/patch/115/driver/u10124646.drv. Therefore, carefully evaluate the content of the directory – <APPL_TOP>/admin/<TWO_TASK>/preinstall and retain only those patch drivers including u19796566.drv ; it is intended to be merged with 12.2.0 upgrade driver – u10124646.drv.

Merge the patch drivers in run file system’s <APPL_TOP>/admin/<TWO_TASK>/preinstall with <AU_TOP>/patch/115/driver/u10124646.drv:

    • Change directory to <AU_TOP>/patch/115/driver
$ cd $AU_TOP/patch/115/driver
$ admrgpch -d . -preinstall -master u10124646.drv

5.  Apply the newly merged 12.2.0 upgrade driver (For example, – <AU_TOP>/patch/115/driver/u_merged.drv) with the command:

adpatch options=nocopyportion,nogenerateportion

You don’t need the copy portion or the generate portion because you installed the upgrade file system and this copied all the files and they were all ready generated.

Note:

  • If you are upgrading your E-Business Suite environment from Release 12.0.4 or Release 12.0.6, then you must apply the following AD patch as the very last patch on the Application Tier, before upgrading to 12.2.0.
  • Patch 11939659:R12.AD.A
  • Important: If there are any other patches to be applied on your E-Business Suite Release 12.0.4 or Release 12.0.6 environment, then you must complete those patches and then apply AD patch 11939659:R12.AD.A.

An example of applying the preinstall patches from MOS Note 1448102.2

The upgrade file system for 12.2.0 is created once. When bugs are found, they are fixed by applying pre-install patches to the upgrade file system, including the Consolidated Upgrade Patch, 19796566 and patches from MOS Note 1448102.2.

Preinstall patches, including the CUP, can optionally be merged together in a single patch and driver to ease installation. Oracle Support has provided a wget* shell script, which can be used to download the entire set of preinstall patches, including the latest CUP, directly to your server from My Oracle Support. See the “Downloading Patches Using wget.sh” section of this document.

*Requires the gnu wget utility

Introduction to the 12.2 Oracle E-Business Suite Preinstall Patches Report

Installing the Oracle E-Business Suite Preinstall Patches Report

  1. Download the appropriate preinstall report for your upgrade to a local folder
  2. Unzip the file, extracting the entire contents, which creates the “EBS_12.2_Preinstall” folder
  3. Navigate to the “EBS_12.2_Preinstall” folder, open “index.html” in a browser
  4. Please note that renaming or modifying any files in the report may break the report

Download the Latest 12.2 Preinstall Patch Report: EBS_R12.2_Preinstall_2015_Dec_21.zip

The following is a summary from the 12.2 Preinstall Patch Report, SUMMARY_EBS.html:

ATG Upgrade script failure Data corruption Performance issue Upgrade integrity fix TOTAL
FND 1 1 2
TOTAL 1 0 0 1 2

 

FIN Upgrade script failure Data corruption Performance issue Upgrade integrity fix TOTAL
AP 1 1
JAI 2 2
JE 2 1 3
XLE 1 1
ZX 2 1 3
TOTAL 5 4 1 0 10
CRM Upgrade script failure Data corruption Performance issue Upgrade integrity fix TOTAL
DPP 1 1
OZF 1 1
TOTAL 1 1 0 0 2

Apply the pre-upgrade patches that were released after the Consolidated Upgrade Patch 19796566:12.2.0. Refer to My Oracle Support Knowledge Document 1448102.2 for the Oracle E-Business Suite Release 12.2 Pre-install Patches Report; it provides a list of essential patches that you must apply in pre-install mode before upgrading from Release 11i/12.0/12.1 to Release 12.2. Follow the recommendations within My Oracle Support Knowledge Document 1448102.2 and apply the additional pre-install patches.

The following is from the EBS.html file, from the 12.2 Preinstall Patch Report:

Upgrade script failure – EBS
SL.NO PRODUCT PATCH RELEASED_DATE PATCH_ABSTRACT
01 JAI 20526934 2015-03-13 QRE1225.3:FIN:JA.SQL:SKIPPED JOB: B17042034_SEC_MAP.SQL ON JAJAI_TDS_SECTION_M
02 JAI 20160778 2015-04-02 FWDPORT:13705714:PATCH 13532321 IS ERRORING OUT
03 FND 21632403 2015-08-14 QRE1225.10:FND:CP: FND_CONC_CLONE.SETUP_CLEAN FAILURE AT 12.2.0 LEVEL
04 XLE 21107018 2015-06-24 11I2R12 UPG: XLEUPG01.SQL FAILED DURING R12.2.0 UPGRADE
05 DPP 22246031 2015-12-14 QREP1225RP.1:DPP.DPPMOACA.SQL FAILED DURING 11IMBL->12.2.0 UPGRADE
06 JE 22268101 2015-12-08 INVALID JG_ZZ_AUDIT_AP_PKG
07 JE 22007691 2015-11-11 ADZDPCUST.SQL INCORRECT COLUMN NAME FOR JE TABLES

Data corruption – EBS
SL.NO PRODUCT PATCH RELEASED_DATE PATCH_ABSTRACT
01 AP 19971946 2015-09-11 RCA: AP_PAYMENT_HISTORY_ALL.RELATED_EVENT_ID IS NULL FOR 11I UPG PAY CR EVENT
02 ZX 21224981 2015-07-01 TAX RATE DESCRIPTION NOT POPULATED DURING MIGRATION TO 12.2
03 JE 21183729 2015-10-09 ROOT CAUSE BUG FOR BUG#20912379 TO NULLIFY THE GLOBAL_ATTRIBUTE1
04 ZX 21512930 2015-08-19 DATA TYPE OF TAX REPORTING TYPE CODE EMEA VAT REPORTING TYPE NOT VISIBLE IN UI
05 OZF 21248593 2015-07-29 Migration scripts creating duplicate data in ozf_object_fund_summary table

Performance issue – EBS
SL.NO PRODUCT PATCH RELEASED_DATE PATCH_ABSTRACT
01 ZX 20971794 2015-06-11 ZXPOTRXMIGUPD.SQL RUNS FOR UP TO 30 HOURS DURING UPGRADE FROM 11I

Upgrade integrity fix – EBS
SL.NO PRODUCT PATCH RELEASED_DATE PATCH_ABSTRACT
01 FND 22227761 2015-12-08 QRE1225RP1225.1:FND: INVALID FND_FLEX_SERVER CAUSING FAILURES (12.1.3RPC4->12.2)

We used the patches in BOLD.

You don’t need to apply pre-install patches for modules you don’t use or that aren’t set up.  The patches you use for your instance will depend on which modules you use.

 

 

Step1 to Upgrade to R12.2.0 – Install the R12.2.0 Upgrade Filesystem with startCD50

These are the steps to preparing to upgrade to R12.2.0, beginning with preparing your upgrade filesystem by downloading the appropriate files.

Install Linux 64 bit. I used OEL 6.5 and Red Hat 6.7 and both worked fine. This post uses examples from OEL6.5.

Download all the 12.2.0 zip files from edelivery.oracle.com:

V29856-01.zip       V35231-01_5of5.zip  V35805-01_1of2.zip  V35809-01.zip

V35230-01_1of2.zip  V35802-01.zip       V35805-01_2of2.zip  V35810-01.zip

V35230-01_2of2.zip  V35803-01_1of3.zip  V35806-01_1of3.zip  V35811-01.zip V35231-01_1of5.zip  V35803-01_2of3.zip  V35806-01_2of3.zip  V35812-01.zip V35231-01_2of5.zip  V35803-01_3of3.zip  V35806-01_3of3.zip  V35813-01.zip V35231-01_3of5.zip  V35804-01_1of2.zip  V35807-01.zip

V37384-01.zip V35231-01_4of5.zip  V35804-01_2of2.zip  V35808-01.zip

Download the startCD 50 patches, from support.oracle.com:

p21055525_R12_GENERIC_1of2.zip p21055525_R12_GENERIC_2of2.zip

Use MOS Note: Troubleshooting Rapid Install for E-Business Suite Release 12.2 (Doc ID 1378579.1) to build the stage directory.

Create /StageR122 directory, or whatever directory you want to use to stage the software.

Unzip the startCD patches to the startCD directory and copy/move to /StageR122:

cp startCD /StageR122/.

run buildstage.sh from /StageR122/startCD/Disk1/rapidwiz/bin

You should now have the following directories in your stage directory, as well as all the zip files you downloaded from edelivery.oracle.com:

The staging area should consist of a top-level directory with subdirectories startCD, EBSInstallMedia, TechInstallMedia, and TechPatches.

The startCD directory contains Rapid Install itself (in a subdirectory called Disk1), plus supporting files and documentation.

The EBSInstallMedia directory should contain the following subdirectories:

  • AppDB (Oracle E-Business Suite Database)
  • Apps (Oracle E-Business Suite products)
  • AS10.1.2 (Oracle Application Server 10.1.2)

The TechInstallMedia directory should contain the following subdirectories:

  • database (Oracle11gR2 ORACLE_HOME)
  • ohs11116 (Oracle HTTP Server)
  • wls1036_generic (Oracle WebLogic Server, part of Oracle Fusion Middleware)

The TechPatches directory should contain the following subdirectories:

  • MiddleTier (Application tier patches)
  • DB (Database Tier patches)

If you have any problems installing the R12.2.0 software, run md5sum:

md5sum V77282-01.zip
md5sum V77283-01.zip
md5sum V77837-01_1of4.zip
md5sum V77837-01_2of4.zip
md5sum V77837-01_3of4.zip
md5sum V77837-01_4of4.zip
md5sum V77838-01_1of2.zip
md5sum V77838-01_2of2.zip
md5sum V77839-01_1of5.zip
md5sum V77839-01_2of5.zip
md5sum V77839-01_3of5.zip
md5sum V77839-01_4of5.zip
md5sum V77839-01_5of5.zip
md5sum V77840-01_1of9.zip
md5sum V77840-01_2of9.zip
md5sum V77840-01_3of9.zip
md5sum V77840-01_4of9.zip
md5sum V77840-01_5of9.zip
md5sum V77840-01_6of9.zip
md5sum V77840-01_7of9.zip
md5sum V77840-01_8of9.zip
md5sum V77840-01_9of9.zip
md5sum V77972-01.zip

and manually check the checksums’s:

md5sum p21055525_R12_GENERIC_1of2.zip
echo “—-> MD5 5C6683B8998EB95DE646F4191EF57459″
echo ” ”
md5sum p21055525_R12_GENERIC_2of2.zip
echo “—-> MD5 861B06405E682997BFBB8FB6B0CBB7D9″
echo ” ”
md5sum V35230-01_1of2.zip
echo “—-> MD5 BDBF8E263663214DC60B0FDEF5A30B0A”
echo ” ”
md5sum V35230-01_2of2.zip
echo “—-> MD5 E56B3D9C6BC54B7717E14B6C549CEF9E”
echo ” ”
md5sum V35231-01_1of5.zip
echo “—-> MD5 695CBAD744752239C76487E324F7B1AB”
echo ” ”
md5sum V35231-01_2of5.zip
echo “—-> MD5 281A124E45C9DE60314478074330E92B”
echo ” ”
md5sum V35231-01_3of5.zip
echo “—-> MD5 4C0C62B6B005FE784E5EDFAD4CAE6F87″
echo ” ”
md5sum V35231-01_4of5.zip
echo “—-> MD5 285EDC5DCCB14C26249D8274A02F9179″
echo ” ”
md5sum V35231-01_5of5.zip
echo “—-> MD5 D78C75A453B57F23A01A6234A29BFD3B”
echo ” ”
md5sum V35802-01.zip
echo “—-> MD5 9D53A6D61670E8F73DEA48833EB52D4F”
echo ” ”
md5sum V35803-01_1of3.zip
echo “—-> MD5 24392A84184F4D1A77549D4341BA869A”
echo ” ”
md5sum V35803-01_2of3.zip
echo “—-> MD5 10049B7C7430114CD73315203A4696D4″
echo ” ”
md5sum V35803-01_3of3.zip
echo “—-> MD5 CD82A23B3847A77D1B0842F778150ECA”
echo ” ”
md5sum V35804-01_1of2.zip
echo “—-> MD5 4D7E3F455F0BF935DA6DD6A5BBA9B742″
echo ” ”
md5sum V35804-01_2of2.zip
echo “—-> MD5 67753C94F26AC3A75E289E811C5DC86D”
echo ” ”
md5sum V35805-01_1of2.zip
echo “—-> MD5 AEC1C5D25ADC4C4BC69F1F8FD09921F1″
echo ” ”
md5sum V35805-01_2of2.zip
echo “—-> MD5 4C4109F0888A94D3208E0127013FE63C”
echo ” ”
md5sum V35806-01_1of3.zip
echo “—-> MD5 9796D35E5F7358D0487248A838F43DF4″
echo ” ”
md5sum V35806-01_2of3.zip
echo “—-> MD5 F7CB441A5BC40EB61658876F41AA3943″
echo ” ”
md5sum V35806-01_3of3.zip
echo “—-> MD5 B4B846DF7C03F533C64B8CACE90C6813″
echo ” ”
md5sum V35807-01.zip
echo “—-> MD5 5FBC610482B8E4FEF1B28A76139E2CF5″
echo ” ”
md5sum V35808-01.zip
echo “—-> MD5 E652E5A7E1C4599F073DDE972C22A98F”
echo ” ”
md5sum V35809-01.zip
echo “—-> MD5 2391C7E6CC1563B0B7B23D42F08D6686″
echo ” ”
md5sum V35810-01.zip
echo “—-> MD5 5AB98E963318300606EF21EE8640956C”
echo ” ”
md5sum V35811-01.zip
echo “—-> MD5 1F809A52E9E45A9B034A6AA65F00D003″
echo ” ”
md5sum V35812-01.zip
echo “—-> MD5 13782F45691FB684ED1891E4CA87FC32″
echo ” ”
md5sum V35813-01.zip
echo “—-> MD5 5992BDF739960CD87E1DB7FF4B264084″
echo ” ”
md5sum V37384-01.zip
echo “—-> MD5 DB0B98DA532917CAECE48B6AF6D4DE0D”
echo ” ”
md5sum V29856-01.zip
echo “—-> MD5 8914CE641B06DAB53F3DCC1EEECD2AF9″
echo ” ”

Or, download the md5sum_Linux64.txt file from note: MD5 Checksums for R12.2 Rapid Install Media ( Doc ID 1505510.1 ) and run the following:

$ cd /StageR122
$ md5sum –check md5sum_Linux64.txt > md5sum_result.txt

In my case, I had a corrupt file listed in md5sum_result.txt:

startCD/Disk1/rapidwiz/template/restore-single2.sql: FAILED

I re-downloaded the startCD50 patches and the install worked.

Additionally, you can follow Metalink note 250262.1 ,to install the RDA diagnostic test and then run option 4 to check the database or option 21 to check the application tier.
Make sure you run it using the following syntax:

rda.sh -T hcve

Test Results
~~~~~~~~~~~~ID NAME RESULT VALUE
====== ==================== ======= ==========================================
A00010 OS Certified? WARNING 11.2.0.3+
A00020 User in /etc/passwd? PASSED userOK
A00040 Group in /etc/group? PASSED GroupOK
A00050 Enter ORACLE_HOME RECORD /d03/oracle/VIS
A00060 ORACLE_HOME Valid? PASSED OHexists
A00070 O_H Permissions OK? PASSED CorrectPerms
A00080 oraInventory Permiss PASSED oraInventoryOK
A00090 Got Software Tools? PASSED ld_nm_ar_make_found
A00100 Umask Set to 022? PASSED UmaskOK
A00120 Limits Processes PASSED Adequate
A00125 Limits Stacksize FAILED SoftLow HardHigh
A00130 Limits Descriptors FAILED HardLow
A00140 LDLIBRARYPATH Unset? PASSED UnSet
A00180 JAVA_HOME Unset? PASSED UnSet
A00190 Enter JDK Home RECORD /d05/oracle/VIS/12.1.0/jdk
A00200 JDK Version FAILED JDK home is missing
A00210 Other O_Hs in PATH? PASSED NoneFound
A00220 Other OUI Up? PASSED NoOtherOUI
A00230 Temp Adequate? PASSED TempSpaceOK
A00240 Disk Space OK? PASSED DiskSpaceOK
A00250 Swap (in MB) RECORD 32767
A00260 RAM (in MB) PASSED 32050
A00270 Swap OK? PASSED SwapToRamOK
A00280 Network PASSED Connected
A00290 IP Address RECORD 192.168.1.112
A00300 Domain Name RECORD trutek.com
A00310 DNS Lookup FAILED nslookup host.domain
A00320 /etc/hosts Format PASSED Adequate IPv4 entry
A00330 Kernel Parameters OK PASSED KernelOK
A00380 Tainted Kernel? PASSED NotVerifiable
A00400 ip_local_port_range PASSED RangeOK
A00480 OL4 RPMs OK? SKIPPED NotOL4
A00490 OL5 RPMs OK? SKIPPED NotOL5
A00500 OL6 RPMs OK? PASSED OL6rpmsOK
A00510 OL7 RPMs OK? SKIPPED NotOL7
A00530 RHEL4 RPMs OK? SKIPPED NotRedHat
A00540 RHEL5 RPMs OK? SKIPPED NotRedHat
A00550 RHEL6 RPMs OK? SKIPPED NotRedHat
A00560 RHEL7 RPMs OK? SKIPPED NotRedHat
A00570 SLES10 RPMs OK? SKIPPED NotSuSE
A00580 SLES11 RPMs OK? SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB11R2_lin_res.htm

From MOS Note 1330701.1,

  1. disable selinux
  2. check swap space is at least 16 GB or equal to your physical memory, whichever is greater.
  3. Make sure the following are in your PATH:
    • ar
    • gcc
    • g++
    • ksh
    • ld
    • linux32
    • make
    • X Display Server
  4. Add or update the following entries to these minimum settings in the /etc/resolv.conf file on each server node:
    options attempts:5
    options timeout:15

5.  Verify that the /etc/hosts file is formatted as follows:

127.0.0.1 localhost.localdomain localhost
[ip_address] [node_name].[domain_name] [node_name]

6. Verify that the /etc/sysconfig/network file is formatted as follows:

HOSTNAME=[node_name].[domain_name]
 or as follows:
HOSTNAME=[node_name]
NISDOMAIN=[domain_name]

7. The kernel parameters and ulimits should be changed when you run:

yum install oracle-rdbms-server-11gR2-preinstall.x86_64

yum install oracle-ebs-server-R12-preinstall

as shown below.

Before running Rapidwiz:

# cd /etc/yum.repos.d

# wget http://public-yum.oracle.com/public-yum-ol6.repo

I altered the /etc/yum.repos.d directory to add the ol6_addons, public_ol6_u5_base and ran the yum commands:

from MOS Note 1330701.1

[ol6_addons]

name=Oracle Linux $releasever Add ons ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1

[public_ol6_u5_base]

name=Oracle Linux $releasever Update 5 installation media copy ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/5/base/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1

 

Then run:

yum install oracle-rdbms-server-11gR2-preinstall.x86_64

yum install oracle-ebs-server-R12-preinstall

This section should not be necessary, but you can check /usr/lib for the correct symbolic links:

Then relink the motif libraries:

unlink /usr/lib/libXtst.so.6

ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6

++++++++++++++++++++++++++++++++++++++++

before running Rapidwiz:

run as root, set directory base to 777:

xhost + as root in the window you will run Rapid Install

cd /StageR122/startCD/Disk1/rapidwiz

./rapidwiz

Screenshots of rapidwiz.

The figure below shows the components that are installed. In our case, we have already upgraded the database to 12.1.0.2, so we won’t use the 11.2.0.3 database that comes with rapid install. Also, notice the Fusion Middleware is 11.1.1.7. We will upgrade this to 11.1.1.9 after the upgrade to 12.2.5. Refer to MOS Notes:

Announcing Oracle Fusion Middleware 11g Release 1 (11.1.1.9.0) (Doc ID 2003468.1)

Fs_clone Is Failing After Applying AD/TXK Delta-7 Patches And FMW Upgrade To 11.1.1.9 (Doc ID 2082408.1)

Oracle® Fusion Middleware Download, Installation, and Configuration ReadMe
11g Release 1 (11.1.1.9.0)

After the install, these are the Rapid Install Configuration Logs:

Configuration file written to: /d05/oracle/VIS/fs1/inst/apps/VIS_rh14/conf_VIS.txt Configuration file written to: /d05/oracle/VIS/fs2/inst/apps/VIS_rh14/conf_VIS.txt Configuration file written to: /d03/oracle/VIS/12.1.0/appsutil/conf_VIS.txt Configuration file written to: /d03/oracle/VIS/12.1.0/appsutil/conf_VIS.txt Configuration file written to: /d03/oracle/VIS/12.1.0/appsutil/conf_VIS.txt Configuration file written to: /d03/oracle/VIS/12.1.0/appsutil/conf_VIS.txt Database logfile – /d03/oracle/VIS/12.1.0/appsutil/log/VIS_rh14/01071923.log Second File System logfile – /d05/oracle/VIS/fs2/inst/apps/VIS_rh14/logs/01071923.log First File System logfile – /d05/oracle/VIS/fs1/inst/apps/VIS_rh14/logs/01071923.log

rapidwiz12204

Database node configuration – since we already have upgraded the database to 12.1.0.2, we check the box for Use Existing Oracle Home, and specify the location of the new Oracle Home.

rapidwiz12206

Check your existing Database Character Set and set this accordingly.

rapidwiz122011

These four checks won’t work, because the database connectivity doesn’t yet exist. Even though the database may be up and running, the port is not defined and the upgrade file systems doesn’t know how to connect to the database. Therefore, these should be the only acceptable failures during the install of the 12.2.0 upgrade file system.

rapidwiz12201 rapidwiz12202 rapidwiz12203 rapidwiz12205 rapidwiz12207 rapidwiz12208 rapidwiz12209 rapidwiz122010 rapidwiz122012 rapidwiz122014 rapidwiz122015 rapidwiz122017 rapidwiz122018 rapidwiz122019 rapidwiz122020 rapidwiz122021 rapidwiz122022

10 Ways to Leave your Upgrade, Faster

This blog is based on a RadiOAUG broadcast on April 9, 2013 at Collaborate in Denver.

10 Ways to Speed up your Upgrade.

1) Fast IO – Solid State Drive for the database files

2) Don’t do an in-place upgrade. This allows you to measure the upgrade on the actual machine you’ll use for the production upgrade.

3) Lots of disk space to stage files. Stage the 11i files so you don’t have to clone them before the upgrade. Stage the R1211 files and the post 1213 files, with all the customizations.

4) Try increasing parallel_max_servers to see if this allows greater parallelization.

5) Don’t use a threaded CPU architecture for the database server.

6) If you do use a threaded CPU server, use Automatic Degree of Parallelism (ADOP) to increase the parallelism. There are three modes: Manual, Limited and Auto. We tested Manual and Auto, and had the best results with Auto.

7) XDOLoader – entropy issue. Create a link to /dev/urandom called /dev/random. Note: 1065393.1.

8) Disable ARCHIVELOG Mode and Auditing

9) Use trace to diagnose performance issues:

oradebug setospid OSPID-number
oradebug unlimit
oradebug event 10046 trace name context forever, level 12
oradebug tracefile_name

Run tkprof on the tracefile and examine the tkprof output.

10) There are lots of functional scripts and datafixes that can speed your upgrade: facpupg.sql and glrsgup2.sql are two examples that allow you to run depreciation in advance and prepare posted journals before the upgrade.

Read more about it in my book, the little r12.1.3 tuning and troubleshooting guide for Oracle E-Business Suite

littler1213tuningFRONT200x155bueborder

 

ATG Genie – AD Wish List Recommendations

At OOW12, the ATG CAB reviewed the existing wish lists for R12.2 for each ATG module and asked for new recommendations for the wish list. I requested 6 or 7 new wishes, and I was granted 4 wishes.

ATG CAB Wish list itemPlease send me your ideas for your wish list and I’ll forward them to the ATG CAB. Anything you really need can be requested. If it makes sense to the development groups they will submit a bug and you’ll be granted your wish.