Online Patching Readiness Reports in 12.2

  1. Run the Online Patching Enablement Reports. From MOS Doc. ID: 1531121.1, Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2:
    1. ADZDPSUM.sql
      Lists schemas containing objects that reference EBS objects that are not editioned. If the schemas are registered with the application, the Online Patching Enablement patch will enable editioning on those schemas avoiding many EBR rule violations (Non-Editioned objects referencing Editioned objects).

sqlplus system @$AD_TOP/sql/ADZDPSUM.sql

mv adzdpsum.txt adzdpsum_pre_dbprep.txt

From ADZDPSUM.sql, see the full report in the attachments:

DOC> ********************************

DOC> SECTION-1
DOC> ***********************************

DOC>
DOC>  Schemas that will be editioned upon Online Patching Enablement.

ETL_USER               EBS Product   — registered before the 12.2 upgrade

HYPERION_USER  Custom      — not registered before the upgrade

  • ADZDPMAN.sql
    Lists objects that violate EBR standards and that require manual intervention in order to address the violations. It groups the objects per violation type and provides instructions on how to address those violations.

sqlplus system @$AD_TOP/sql/ADZDPMAN.sql

mv adzdpman.txt adzdpman_pre_dbprep.txt

  • ADZDPAUT.sql
    Lists objects that violate Online Patching Enablement standards. The objects in this report will be adjusted automatically by the Online Patching Enablement patch. This report is provided for information purposes only, no action is required on the output of this report.

sqlplus system @$AD_TOP/sql/ADZDPAUT.sql

mv adzdpaut.txt adzdpaut_pre_dbprep.txt Fix any violations -Oracle recommends that you perform the chosen fix by customizing the template file

Major Steps in the R12.2 Upgrade

Preparing the Database for the Upgrade

              Back Up the Oracle E-Business Suite Database and Customizations

              Migrate Existing Objects to New Tablespace Model

Upgrade Your Database and Apply Mandatory Oracle E-Business Suite 12.2 Database Patches (required):

Ensure that the required database patches for Oracle E-Business Release 12.2 have been applied

Preparing the Oracle E-Business Suite Release 12.2 File System

Performing Mandatory Preparatory Tasks

Setting Up the Stage Area

Obtaining Installation Software

              Select only the following releases:

• Oracle E-Business Suite 12.2.0 Current

• Oracle WebLogic Server 10.3.6.0.0

• Oracle Web Tier 11.1.1.9.0

• Oracle Database 12.1.0.2.0

Creating the Stage Area

              Run the buildStage Script

              Patch the Stage Area

Creating the Upgrade File System

DB Node Configuration

Preparing the System for Upgrade

              Apply Required Application Tier Patches

                            • FMW WebTier 11.1.1.9

• Oracle WebLogic Server 10.3.6 Smart Update Patch

• Oracle WebLogic Server 10.3.6.0

• FMW oracle_common 11.1.1.9

• Forms and Reports 10.1.2.3

              Validate the GUEST user account

Customer Relationship Management Tasks

Financials and Procurement Tasks

Human Resource Management (HRMS) Tasks

Supply Chain Management Tasks

Database and System Administration Tasks

              Reset init.ora parameters (required)

Gather SYS, Fixed Object and Dictionary Statistics (required)

Gather schema statistics (required)

Install JRE on the database tier (conditional)

Synchronize values of APPLPTMP with Database Directories for PL/SQL File I/O based Concurrent Requests (required)

Perform a system backup

Performing the Upgrade to Release 12.2.0

              Shut down application tier listeners and concurrent managers (required)

Update init.ora with upgrade parameters (required) 

Set FAILED_LOGIN_ATTEMPTS to UNLIMITED for Oracle E-Business Suite schema

Disable custom triggers, constraints, indexes, business events, and VPD (conditional)

Check for data dictionary corruption and time stamp mismatch

Compile invalid objects

Rebuild unusable indices

Refresh materialized view (conditional)

Back up the database (recommended)

Disable database auditing (recommended)

Ensure that Maintenance Mode is enabled (required)

Apply AD 12.2 upgrade driver (required)

Apply the consolidated upgrade patch and run the 12.2.0 Upgrade (required)

Disable maintenance mode (required)

Back up Oracle E-Business Suite

Finishing the 12.2.0 Upgrade

              Configure Release 12.2 Oracle E-Business Suite instance (required)

                            Update the DB ORACLE_HOME file system with AutoConfig and Clone files (conditional)

                            Install JRE on the database tier (conditional)

                            Create the Context Name directory in the DB oracle home

                            Set and export the following environment variables

                            Generate a new database context file

                            Clean up old node information prior to configuring the Oracle E-Business

Synchronize values of APPLPTMP with Database Directories for PL/SQL File I/O based Concurrent Requests (required)

Run AutoConfig on the database tier nodes

Source the Run file system environment file

Drop table ADX_PRE_AUTOCONFIG from APPS schema

Configuring the Oracle E-Business Suite Release 12.2 Application Tier

Enabling Online Patching

              Verify the database version

              Verify that the latest patches have been applied to the database and application tier

              Gather SYS schema statistics

              Apply the latest Oracle E-Business Suite Online Patching Readiness and GSCC Report Patch

              Run the Online Patching Enablement – Readiness Reports

              Fix Violations Listed in the Online Patching Readiness Report that Require Manual Intervention

              Verify database tablespace free space

              Run the Online Patching Enablement – Status Report

              Ensure that all Application Tier Oracle E-Business Suite services are shut down

              Download and apply the Online Patching Enablement patch

              Compile Invalid Objects (if any)

              Re-run the Online Patching Enablement Status Report after the Online Patching

Re-run the Online Patching Enablement Readiness Report after the Online Patching Enablement patch has been applied

Run the Online Patching Database Compliance Checker report to check for coding standards violations

Database Initialization Parameters

Upgrading to the Latest Code Level

              Apply the Latest AD and TXK for Release 12.2

              Execute txkSavePDBState.pl (conditional)

For the custom products that already exist, you must run adsplice on the Run File system to lay down the file system.

Apply the Latest Oracle E-Business Suite Release Update Pack for Release 12.2

The Big Picture of the R12.1.3 Upgrade

The Big Picture is an overview of the Release 12.1.3 upgrade process.
The number #1 reason to upgrade is to keep you’re My Oracle Support in compliance, followed by new feature adoption, and plans to use new business processes.
Oracle Support Compliance
Release 11.5.10.2 Support Milestones
Premium Support for E-Business Suite 11.5.10.2 ended November 30, 2010.
• First year of 11.5.10.2 EBS Extended Support fees are waived.
• Mandatory Patches – Minimum Baseline Patches for Extended Support 11.5.10.2 – After December 1, 2010 (Note 883202.1)
Release 12.0 Support Milestones
• February 1, 2012 – Premium Support Ends (R12.0.6) (845809.1)
Release 12.1 Support Milestones
• July 1, 2011 – You must apply R12.ATG_PF.B.delta.2 (R12.1.2 ATG RUP) (845809.1)
• February 1, 2012 – You must apply R12.ATG_PF.B.delta3 (R12.1.3 ATG RUP) (1066312.1)
• February 1, 2013 – You must upgrade to EBS R12.1.3

Why Not Wait for Fusion?
Nadia Benedjedou lays out short and long term goals for migrating to fusion:

The steps Nadia lays out to migrate to Fusion include:
Implement OBIEE
Implement Identity Management
Start using BI Publisher
Use Enterprise Manager, if you’re not already, to manage more of the enterprise.
Move your customizations to a Fusion compatible framework, JDeveloper and ADF.
Centralize and Consolidate your data using Oracle’s Master Data Management.

The Upgrade is an Iterative Process, until all the issues are resolved and new functionality is working.

The Technical Upgrade is like Evolution – It’s an Iterative Approach
The faster you can iterate, the faster you can resolve problems and proceed to the production upgrade.
In general, the iterative cycle is as follows:
1. Follow the steps in the upgrade guide
2. Run patchsets.sh
3. Run Patch Wizard – • Patch Wizard Update for 11i (Patch 9803629)
4. Apply any new patches
5. Iterate back to Step 3 and run Patch Wizard, until there are no new patches
The 12.0.4 and 12.0.6 Upgrade
We started with the upgrade to 12.0.4 from 11.5.10.2 and learned with each book we wrote. Our initial upgrades were based on VISION upgrades with base 11.5.10.2 patches. The same basic way Oracle initially tests the upgrade. We’ve done many technical upgrade assessments, functional assessments and customization assessments. The technical assessment is an important step to establish your patch levels for each module. If you want to continue receiving support from Oracle, then you’ll need to be patched with the Mandatory patches.
First Pass Upgrade to 12.1.1
Real customer data introduces new combinations of patch levels and possible data issues. This gave us greater insight into the upgrade issues. We then introduced the “First Pass” upgrade. We come on-site and upgrade your instance in 2 weeks. The whole assessment -1st pass upgrade usually takes 4-6 weeks, nt including customizations. This gives us many data points in our analysis of what patches are required. The 1st pass upgrade gives your functional analysts an instance to perform gap analysis and the developers an instance to see what customizations are broken.
The combination of assessments and 1st Pass upgrades help define the P3 Upgrade Methodology.
From the assessments and 1st pass upgrades we gathered new issues /solutions and developed an upgrade methodology that we use at every customer. The following picture shows the book covers for the project plan, the overview for managers and team members and the detailed, step-by-step instructions to complete the upgrade to R12.1.3.
With another release and more clients patch levels/issues, we’ve identified even more issues. The following are the pictures for the covers for the project plan, the overview and detailed upgrade guide.

Plan
Decision: re-implement or upgrade
Understand the hardware requirements and the upgrade path
Procure upgrade hardware
Purge unnecessary data
Train the functional super users in the new features of Release 12.1
Create an upgraded instance for gap analysis
Start with an assessment and a 1st pass upgrade.
Technical Assessment
Determine future capacity requirements, tech stack version compatibility and patch levels, including CPUs and PSUs. We review current issues from log files, unresolved service requests, and identify potential issues with the R12.1 Upgrade.
Architecture – Hardware Assessment
We review hardware configurations including options like: RAC vs SMP, Shared Application Tier with Distributed Processing, Parallel Concurrent Processing, SAN vs JBOD, RMAN vs Snapshots.
If the plan includes buying new hardware, consider migrating from the current 32-bit platform to a 64-bit platform.
Functional Assessment
The R12 Upgrade is not just a technical upgrade
The functional upgrade consists of mapping new business requirements with new functional features in R12.
Identify AS-IS Processes
Determine TO-BE Processes
Evaluate Potential Data Issues
Which New Features may replace customizations?
Estimate R12 New Features Training Needs
Recommend “Best Practices”

Example of Functional Issues
SQL> select count(*), country from ap_bank_branches group by country;
COUNT(*) COUNTRY
1081 (null)
112 US
3 NZ
226 CA
1 BB
2 GB
1 DE

The best solution is to have the AP functional superusers update the banks to their proper value. However, the following SQL statement can be used temporarily:
update ap_bank_branches set country=’US’ where country is null;
There are many other functional checks that are provided as a part of the functional assessment. We also provide a summary of functional new features for your installed modules.

Customization Assessment
CEMLI = Configurations, Extensions, Modifications, Localizations, and Integrations
The CEMLI Upgrade Assessment includes determining technical impact of Oracle E-Business Suite Release 12.1 on CEMLIs, upgrading CEMLIs to the new technology stack, retrofit of CEMLIs for compatibility and usability on Oracle E-Business Suite Release 12.1.
Identify all customizations
Check-in all customizations into configuration management
Determine customizations that are replaced by new R12.1 functionality
Re-Code customizations
Prepare Customization Upgrade Implementation Plan
Customization Configuration Management

Start by identifying all customizations. In some environments the customizations aren’t well documented and some customizations may have been lost due to previous patching. Check-in all customizations into configuration management. Customizations are easier to customize if you can find them and have some version control.
Determine the customizations that are replaced by new R12.1 functionality. This requires an analyst that knows the new functionality of R12.1 and understands the customizations.
Lastly, determine the customizations that need to be fixed or added to the upgrade to preserve or extend the process alignment.
Prepare
Train the Super Users and Technical Staff
Buy Hardware
Create Upgraded Instance for detailed gap analysis
Practice Testing
Practice Upgrades
Use the Maintenance Wizard (215527.1)
Step-by-step, graphical user interface for performing upgrade tasks
Consolidates instructions from multiple sources to present a comprehensive upgrade picture
Reduces upgrade tasks by filtering out those that do not apply to you (using TUMS)
Indicates critical patches that your system requires
Can automatically execute upgrade tasks for you
Run patchsets.sh

Use Patch Wizard from Oracle Application Manager (OAM)
We recommend that when you finish your upgrade to what you believe is the latest version of 12.1.3, with all the patches and Family Packs identified from patchsets.sh, this book, and your own research, you should run Patch Wizard again to see if additional patches are found.

Note that Patch Wizard may require patches for both Release 11i and Release 12 (9643141, 10629956).
If possible complete the following prior to the R12.1 upgrade weekend:
Upgrade the Database to 11.2.0.2
Migrate to OATM
Install the R12.1.1 software
Run Downtime Reducing steps
Run pre-upgrade verification steps
Technical Upgrade – Details
R12.1 Upgrade Paths
Path A DB 9iR2, 10gR2 Apps 11.5.7 or 11.5.8
DB Upgrade & Apps Upgrade need to be completed during the same downtime window.
Path B If the DB already at 11gR1, Apps 11.5.9.2 or 11.5.10.2
Only upgrade the Apps Stack
Path C Upgrade the DB & Apps in different phases
If upgrading from a release prior to 11.5.7, the upgrade path may require an interim upgrade to Release 11.5.10.2. Because of the significant downtime required to upgrade from Release 11.0 to Release 12, it may be more feasible to first upgrade to Release 11.5.10.2 and then some time later upgrade to Release 12. This requires the functional users to learn Release 11.5.10.2, and perform all the testing for another upgrade. The amount of work necessary to perform two rounds of system acceptance testing may justify another day or two of downtime, so that the upgrade from Release 11.0 to Release 12.1 can be completed in one longer period of downtime.
Upgrade Paths
The light green circles indicate the most documented upgrade path from 11.5.10.2 to 12.1.1 and 12.1.3.

These bubbles show the upgrade paths. If your initial release is 11.0.3, you will need to upgrade to an interim release, 11.5.10.2, before you can upgrade to 12.1.1. The following chart lists the initial release, interim release and final release, with the associated patch number,
Initial Release Interim Release Final Release R12 Patch
11.0, 11.5.1 – 11.5.6 11.5.10 CU2 12.0.0 4440000
11.5.7. 11.5.8, 11.5.9*, 11.5.10* Release 12.0.0 4440000
11.5.7, 11.5.8, 11.5.9.2, 11.5.10.2 Release 12.0.4 6394500
11.5.9*, 11.5.10* Release 12.1 6678700
12.1.1 Release 12.1.2 7303033
12.1.1 Release 12.1.3 9239090
* includes CU1 and CU2 (consolidated update)
Figure 4 indicates that a direct upgrade path exists from Release 11.5.7 to Release 12.0.0.

The Applications Upgrade path is constrained by the database release. The following chart shows the application release and the database versions that are certified on Solaris. If the application release is 11.5.10.2, then you can upgrade the database to 11gR2 before the application upgrade, saving downtime during upgrade weekend, if you plan you use 11gR2, and you should always try to use the latest certified version of the database.
Release Certified Database Versions on Solaris
12.1 10gR2, 11gR2 and the 64 bit versions
12.0 10gR2, 11gR2 and the 64 bit versions
11.5.10.2 10gR2 or 11gR2 and the 64 bit versions
11.5.9.2 10gR2 and the 64 bit versions
11.5.7 8.1.7, 9.0.1 9.2, and 9.2-64 bit

We can see that there is no certified database version that is certified with both Release 11.5.7 and Release 12.1. Therefore, we can’t do the database upgrade before the downtime window.
Overview of the R12.1 Technical Upgrade

The database upgrade is a bit more complicated if you’re running 9.2.0.6, because of Daylight Savings Time.
• The database installed by the 11.5.10.2 RapidWiz is Version 9.2.0.6. This database version does not support Daylight Savings Time (DST). Therefore, we have two choices:
• Upgrade the database to Version 9.2.0.8, which has support for DST, and then upgrade to Version 11.2.0.1, or

Perform the Upgrade
11i pre-reqs for Release 12.1.1
11870353, 5880762, 7477784, 7721754, 7828862, 8579398, 8757781, 8761881, 8798855, 8845395, 8908907, 8990356, 8991381, 9003549, 9053932, 9109247, 9128838, 9187813, 9288021, 9304675, 9442701, 9446543, 9476923, 9535311, 9685457, 9725579, 9747572, 9871422, 9889680, 3865683, 6408117, 8242248, 6024690, 4619025, 5368595, 5357791, 5970422, 59105548, 5194357, 5230979, 4396821, 5377946, 6741394, 6505416, 7418579, 4963569, 5259121, 4551977, 4607647, 6027561, 5760729, 5382135, 4699061, 6696828, 4582937, 4507073, 8340090, 4350832, 4563075, 4582937, 4607647, 4699061, 4939444, 4963569, 4969938, 5259121, 5382135, 6349338, 6351946, 6694260, 6696828, 8340090, 8487779, 10258309, 6264601, 3153717, 4252634
This list of patches continually changes. You should run Patch Wizard to determine any missing patches for your environment. When you’ve determined your patch list, cut and paste the patch list into the Patch Search form, on My Oracle Support.

Purging Workflow – Updated version

Purging Workflow Item Type and Notifications

In the past, I’ve blogged about how to set workflow item_types so they can be purged using scripts in $FND_TOP. One such script is wf_purge.total. Below, I give an example of using wf_purge.total to item_types where the PARENT_ITEM_TYPE is null. I also write about two methods to purge notifications, wf_purge.notifications and manually updating the wf_notifications table and set status=’CLOSED’, mail_status=’SENT’, and END_DATE=sysdate-1.

This blog also  introduces using the WF_ITEM_ACTIVITY_STATUS.Create_Status procedure to set the status to ‘COMPLETE’ from ‘NOTIFIED’. This can be used to change the status.

— When STATUS=’NOTIFIED’ it can’t be purged. Use API to set the STATUS to ‘COMPLETE’

 

BEGIN

WF_ITEM_ACTIVITY_STATUS.Create_Status(itemtype  => ‘WFERROR’,

itemkey=>’10037187′,

actid=> 255020,

status=> ‘COMPLETE’,

result=>null,

beginning=> null,

ending=> sysdate,

suspended=> FALSE,

newStatus=> FALSE);

END;

 

Queries used to find WF_ITEMS that can’t be purged. It turns out the biggest offender was FAFLEXWF (41 million rows), because of a custom portion of the workflow for cash management had an error.

 

The next item_type with the most issues was WFERROR. This is very common. The problem with resolving WFERROR item_types, is you need to know the item_key, based on some criteria.

 

First, find the item_types that have issues, in this case the PARENT_ITEM_TYPE is null:

 

select count(*) as cnt, item_type from apps.wf_items where  PARENT_ITEM_TYPE is null group by item_type

 

Or, use the following to order by descending count:

select count(1) from apps.wf_items where item_type=’WFERROR’

group by PARENT_ITEM_TYPE order by 2 desc

 

Second, find the item_keys associated with the issue:

 

select item_type, item_key from apps.wf_items where item_type=’WFERROR’ and PARENT_ITEM_TYPE is null

 

Third, purge WFERROR item types where the parent_item_type is null using wf_purge.total, by looping through all the item_keys:

 

BEGIN

FOR c1_rec IN (select item_type, item_key from apps.wf_items where item_type=’WFERROR’and PARENT_ITEM_TYPE is null)

LOOP

apps.wf_purge.total(c1_rec.item_type, c1_rec.item_key)   ;

END LOOP;

END;

 

Helpful Views

select * from all_views where view_name like ‘%WF%ITEM%’

select * from apps.WF_ITEMS_V where item_type=’FAFLEXWF’

 

WF_ITEM_ATTRIBUTES_VL

select * from apps.WF_ITEM_TYPES_VL where

name=’WFERROR’

WF_ITEM_ACTIVITY_STATUSES_V

 

wf_purge.notifications

In this example, we will purge notifications, in this case, CREATEPO and passing it yesterday’s date as the END_DATE:

BEGIN

wf_purge.notifications(‘CREATEPO’, sysdate-1);

END;

For example, we had nine wf_notifications where the message_type=’CREATEPO’ and the status was not ‘CLOSED’ or the mail_status was not ‘SENT’ or there was no end_date.

select * from apps.wf_notifications where message_type=’CREATEPO’

update apps.wf_notifications set status=’CLOSED’, mail_status=’SENT’, END_DATE=sysdate-1 where message_type=’CREATEPO’

Major Oracle E-business suite upgrade coming in 2019. Or maybe 2020

If you can’t wait, Big Red says its new SaaS apps will behave just like software modules

Oracle is working on a big upgrade to its e-business suite in 2019. Or maybe 2020.

That’s what senior veep for application development Cliff Godwin told the Red Rock Oracle Leadership Forum* in Sydney, Australia, today.

The suite is currently on version 12.2.6 and Godwin said Oracle plans just-about-annual double-point releases for the next couple of years to bring it to to version 12.2.7 and 12.2.8. The release after that will be 12.3 and while Godwin couldn’t say what would be in it, he did say that Oracle has already guaranteed support for it until the year 2030 and beyond.

Big Red’s made that pledge to ensure users don’t feel their on-premises applications are in peril, or that they’re being herded to the cloud. Godwin said Oracle knows it has plenty of customers, many of them in military or other sensitive government fields, that are likely years or decades away from being comfortable in the public cloud.

The roadmap to version 12.3 and long support plan are designed to give them comfort that the suite has a future and that “if you are contemplating moving to cloud, do it for merits of the business not because you are afraid the suite is going to expire out from under you.”

Oracle’s double-point updates, however, don’t add a lot of functionality to the suite and the company’s current plans see most of its innovation focused on software-as-a-service (SaaS). Godwin said on-premises users will be able to access new functions by piping in those new SaaS offerings.

“They will work as if we built them as new modules,” he said.

Among the new SaaS offerings Oracle is working on is predictive analytics it will bake into its asset management modules. A new in-memory cost management cloud service replaces an on-premises product that Godwin said was “very computationally intensive” and required users to acquire an Oracle Engineered system. The new SaaS version does away with the need for the tin, but gives you access to an evolving piece of software.

Godwin said that ongoing work on the suite will include more emphasis on mobile, so that core functions can be poured into apps from Oracle or cooked up by customers. He added that the company wants to build more migration tools to help users of the suite move into the cloud, but not as a lift-and-shift. Instead he said Oracle hopes users can move test and development or disaster recovery rigs into the cloud, while retaining integration with on-premises production systems

How to perform Patch Impact Analysis in 12.1 and 12.2

In 11i, R12.0 and R12.1, we can use adpatch apply=no to apply a patch in test mode.

Test Mode

In test mode, AutoPatch does not apply the patch. Instead, it lists each file it would have copied, relinked, executed, or generated and shows exactly what actions it would have performed had it applied the patch. It also runs AutoConfig in test mode to determine any impending changes to the configuration files. This allows you to see the effects of the patch on your production system before you apply it.

To run AutoPatch in test mode, include apply=no on the AutoPatch command line. This runs as if AutoPatch is applying the patch, except it does not.

In R12.2, we can use adop with apply=no, since most of the options and flags that are valid for adpatch are valid for adop.

To run adop in test mode, add the apply=no parameter to the adop command you would use if you were actually going to apply the patch. In test mode, adop will go through the process of applying the patch but will not perform any of the following actions:

  • Copy files from the patch directory to the Oracle E-Business Suite file system
  • Archive object modules into the product libraries
  • Relink executables
  • Generate forms, reports, PL/SQL libraries, or menu files
  • Run SQL or EXEC commands (commands that change the database)
  • Instantiate new configuration files
  • Update the patch information files
  • Update patch information and release version in the database 

    Reference: Oracle E-Business Suite Maintenance Guide

Another way to do Patch Impact analysis is to apply the patch to the patch edition but don’t perform a cutover. Then, use the SQL provided by Oracle in $AD_TOP/sql,

ADZDCMPED.sql

This script is used to display the differences between RUN and PATCH edition

Displays the New/Changed objects

This script should only be run in PATCH edition

This script will compare editions and will report differences in all objects including tables, materialized views, indexes and cross-edition triggers. This can be very useful when trying to identify objects that may take a long time to regenerate during cutover, especially objects that are related to materialized views

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

SET FEEDBACK OFF;

SET ECHO OFF;

 

exec ad_zd.set_edition(‘PATCH’);

 

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

 

REM

REM Spool results to adzdcmped.out file.

REM

spool adzdcmped.out

set pages 1000

set lines 200

prompt

prompt Database Information:

prompt =====================

prompt

 

column “Edition Name” format a15

column “Type” format a8

column “Status” format a8

column “Current?” format a8

 

REM Bug 15889193 – JWSMITH, Display same as ADZDSHOWED.sql

prompt =========================================================================

prompt = Editions

prompt =========================================================================

select

aed.edition_name “Edition Name”

, ad_zd.get_edition_type(aed.edition_name) “Type”

, decode(use.privilege, ‘USE’, ‘ACTIVE’, ‘RETIRED’) “Status”

, decode(aed.edition_name, sys_context(‘userenv’, ‘current_edition_name’),

‘CURRENT’, ”) “Current?”

from

all_editions aed

, database_properties prop

, dba_tab_privs use

where prop.property_name = ‘DEFAULT_EDITION’

and use.privilege(+) = ‘USE’

and use.owner(+) = ‘SYS’

and use.grantee(+) = ‘PUBLIC’

and use.table_name(+) = aed.edition_name

order by 1

/

 

variable patch_edition VARCHAR2(30);

variable run_edition VARCHAR2(30);

 

begin

:patch_edition := ad_zd.get_edition(‘PATCH’);

:run_edition := ad_zd.get_edition(‘RUN’);

end;

/

 

column “Owner” format a30

column “Object_Name” format a35

column “Object_Type” format a20

column “Status” format a7

column “InValid?” format a9

prompt ===========================================================================

prompt = Differences between RUN and PATCH edition (Editionable Objects)

prompt ===========================================================================

prompt

 

SELECT

eusr.user_name “Owner”,

dobjs.name “Object_Name”,

decode(dobjs.type#,

4, ‘VIEW’,

5, ‘SYNONYM’,

7, ‘PROCEDURE’,

8, ‘FUNCTION’,

9, ‘PACKAGE’,

11, ‘PACKAGE BODY’,

12, ‘TRIGGER’,

13, ‘TYPE’,

14, ‘TYPE BODY’,

‘ERROR’) “Object_Type”,

‘NEW’ “Status” ,

Decode(dobjs.status,’INVALID’,’INVALID’,NULL) “InValid?”

FROM sys.obj$ dobjs,

(

select

xusr.user#

, xusr.ext_username user_name

, ed.name edition_name

from

(select * from sys.user$ where type# = 2) xusr

, (select * from sys.obj$

where owner# = 0 and type# = 57) ed

where xusr.spare2 = ed.obj#

and ed.name=Sys_Context(‘Userenv’, ‘Current_Edition_Name’)

union all

select

busr.user#

, busr.name user_name

, ed.name edition_name

from

(select * from sys.user$ where type#=1 or user#=1) busr

, (select * from sys.obj$ where owner#=0 and type#=57) ed

where ed.name = ‘ORA$BASE’

and ed.name = Sys_Context(‘Userenv’, ‘Current_Edition_Name’)

) eusr

WHERE dobjs.owner#=eusr.user#

AND dobjs.type# in (4,5,7,8,9,11,12,13,14)

AND eusr.user_name in (select username from dba_users where

editions_enabled=’Y’)

AND dobjs.name NOT IN ( SELECT object_name

FROM dba_objects_ae

WHERE edition_name <> Sys_Context(‘Userenv’,

‘Current_Edition_Name’)

AND owner = eusr.user_name

AND object_type = decode(dobjs.type#,

4, ‘VIEW’,

5, ‘SYNONYM’,

7, ‘PROCEDURE’,

8, ‘FUNCTION’,

9, ‘PACKAGE’,

11, ‘PACKAGE BODY’,

12, ‘TRIGGER’,

13, ‘TYPE’,

14, ‘TYPE BODY’,

‘ERROR’))

UNION ALL

SELECT

eusr.user_name “Owner”,

dobjs.name “Object_Name”,

decode(dobjs.type#,

4, ‘VIEW’,

5, ‘SYNONYM’,

7, ‘PROCEDURE’,

8, ‘FUNCTION’,

9, ‘PACKAGE’,

11, ‘PACKAGE BODY’,

12, ‘TRIGGER’,

13, ‘TYPE’,

14, ‘TYPE BODY’,

‘ERROR’) “Object_Type”,

‘CHANGED’ “Status” ,

Decode(dobjs.status,’INVALID’,’INVALID’,NULL) “InValid?”

FROM sys.obj$ dobjs,

(

select

xusr.user#

, xusr.ext_username user_name

, ed.name edition_name

from

(select * from sys.user$ where type# = 2) xusr

, (select * from sys.obj$

where owner# = 0 and type# = 57) ed

where xusr.spare2 = ed.obj#

and ed.name=Sys_Context(‘Userenv’, ‘Current_Edition_Name’)

union all

select

busr.user#

, busr.name user_name

, ed.name edition_name

from

(select * from sys.user$ where type#=1 or user#=1) busr

, (select * from sys.obj$ where owner#=0 and type#=57) ed

where ed.name = ‘ORA$BASE’

and ed.name = Sys_Context(‘Userenv’, ‘Current_Edition_Name’)

) eusr

WHERE dobjs.owner#=eusr.user#

AND dobjs.type# in (4,5,7,8,9,11,12,13,14)

AND eusr.user_name in (select username from dba_users where

editions_enabled=’Y’)

AND dobjs.name IN ( SELECT object_name

FROM dba_objects_ae

WHERE edition_name <> Sys_Context(‘Userenv’,

‘Current_Edition_Name’)

AND owner = eusr.user_name

AND object_type = decode(dobjs.type#,

4, ‘VIEW’,

5, ‘SYNONYM’,

7, ‘PROCEDURE’,

8, ‘FUNCTION’,

9, ‘PACKAGE’,

11, ‘PACKAGE BODY’,

12, ‘TRIGGER’,

13, ‘TYPE’,

14, ‘TYPE BODY’,

‘ERROR’))

ORDER BY “Owner”, “Status”, “Object_Name”;

 

REM Merged in ADZDSHOWOBJS.sql with slight modifications

column edition_name format A15

 

prompt

prompt

prompt =========================================================================

prompt = Editioned Objects Per Edition

prompt =========================================================================

prompt

ttitle COL 27 “Actualized Referenced” skip 1 –

COL 17 “——————————– ——————————-”

 

select * from

(

select

eusr.edition_name edition_name

, count(decode(obj.type#,88,NULL,decode(obj.status,1,1,NULL))) a_valid

, count(decode(obj.type#,88,NULL,decode(obj.status,1,NULL,1))) a_invalid

, count(decode(obj.type#,88,NULL,1)) a_total

, count(decode(obj.type#,88,decode(obj.status,1,1,NULL),NULL)) s_valid

, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),NULL)) s_invalid

, count(decode(obj.type#,88,1,NULL)) s_total

, count(1) total

from

sys.obj$ obj

, (

select

xusr.user#

, xusr.ext_username user_name

, ed.name edition_name

from

(select * from sys.user$ where type# = 2) xusr

, (select * from sys.obj$ where owner# = 0 and type# = 57) ed

where xusr.spare2 = ed.obj#

union

select

busr.user#

, busr.name user_name

, ed.name edition_name

from

(select * from sys.user$ where type# = 1 or user# = 1) busr

, (select * from sys.obj$ where owner# = 0 and type# = 57) ed

where ed.name = ‘ORA$BASE’

) eusr

where obj.owner# = eusr.user#

and obj.type# in (4,5,7,8,9,11,12,13,14,22,87,88)

and obj.remoteowner is null

and eusr.user_name in

( select username from dba_users where editions_enabled=’Y’ )

group by eusr.edition_name

) x

order by 1, 2;

 

prompt

prompt Note :

prompt (A_) – Actualized objects

prompt (S_) – Stubs

prompt

TTITLE OFF

 

 

column owner format a20

column table_name format a30

column sync format a7

 

prompt

prompt

prompt

prompt =========================================================================

prompt = Seed Data Tables Prepared in the Current Edition

prompt =========================================================================

select

col.owner owner

, col.table_name table_name

, trg.crossedition sync

from

dba_tab_columns col

, dba_objects obj

, dba_source src

, dba_triggers trg

where col.owner in

( select oracle_username

from fnd_oracle_userid

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

and col.table_name not like ‘%#’

and col.column_name = ‘ZD_EDITION_NAME’

and obj.owner = ad_zd.apps_schema

and obj.object_name = ad_zd_seed.eds_function(col.table_name)

and obj.object_type = ‘FUNCTION’

and obj.edition_name = sys_context(‘userenv’, ‘current_edition_name’)

and src.owner = obj.owner

and src.name = obj.object_name

and src.type = obj.object_type

and src.text like ‘%’||sys_context(‘userenv’,

‘current_edition_name’)||’%’

and trg.owner(+) = ad_zd.apps_schema

and trg.trigger_name(+) = ad_zd_seed.eds_fcet(col.table_name)

order by owner, table_name

/

 

prompt

prompt

prompt

prompt =========================================================================

prompt = New Tables

prompt =========================================================================

column Table_Name format a30

 

SELECT

substr(v.view_name,1,Length(v.view_name)-1) Table_Name

FROM dba_views_ae v

WHERE v.editioning_view = ‘Y’

AND v.edition_name = :patch_edition

AND v.view_name NOT IN (SELECT vi.view_name

FROM dba_views_ae vi

WHERE vi.editioning_view = ‘Y’

AND vi.view_name = v.view_name

AND vi.owner = v.owner

AND vi.edition_name = :run_edition)

/

 

prompt

prompt

prompt

prompt =========================================================================

prompt = New/Changed Columns

prompt =========================================================================

 

declare

procedure exec_force(stmt in varchar2) is

no_such_table exception;

pragma exception_init(no_such_table, -942);

begin

execute immediate stmt;

exception

when no_such_table then

null;

end;

begin

exec_force(‘drop table ad_zd_report_table_columns’);

end;

/

 

declare

procedure exec_force(stmt in varchar2) is

begin

execute immediate stmt;

end;

begin

exec_force(‘create ‘ || ‘table ‘ || ‘ad_zd_report_table_columns

(

p_owner varchar2(30) not null,

p_view_name varchar2(30) not null,

p_view_column_name varchar2(30) not null,

p_table_column_name varchar2(30) not null,

p_status varchar2(30) not null

)’);

end;

/

 

DECLARE

CURSOR c_changed_evs IS

SELECT

v.view_name,

v.owner

FROM dba_views_ae v

WHERE v.editioning_view = ‘Y’

AND v.edition_name = :patch_edition

AND v.view_name IN (SELECT vi.view_name

FROM dba_views_ae vi

WHERE vi.editioning_view = ‘Y’

AND vi.view_name = v.view_name

AND vi.owner = v.owner

AND vi.edition_name = :run_edition);

cursor new_columns(x_owner varchar2,x_view_name varchar2) is

select c.view_column_name,

c.table_column_name

from dba_editioning_view_cols_ae c

where c.view_name = x_view_name

and c.owner = x_owner

and c.edition_name = :patch_edition

and c.view_column_name not in (select ci.view_column_name

from dba_editioning_view_cols_ae ci

where ci.view_name = c.view_name

and ci.owner = c.owner

and ci.edition_name = :run_edition);

 

cursor revised_columns(x_owner varchar2,x_view_name varchar2) is

select c.view_column_name,

c.table_column_name

from dba_editioning_view_cols_ae c

where c.view_name = x_view_name

and c.owner = x_owner

and c.edition_name = :patch_edition

and exists (select ci.view_column_name

from dba_editioning_view_cols_ae ci

where ci.view_name = c.view_name

and ci.owner = c.owner

and ci.edition_name = :run_edition

and ci.view_column_name = c.view_column_name

and ci.table_column_name <> c.table_column_name);

 

 

l_view_name varchar2(30);

l_owner varchar2(30);

l_view_col varchar2(30);

l_table_col varchar2(30);

BEGIN

OPEN c_changed_evs;

FETCH c_changed_evs into l_view_name, l_owner;

 

while (c_changed_evs%found) loop

 

open new_columns(l_owner,l_view_name);

fetch new_columns into l_view_col,l_table_col;

while (new_columns%found) loop

insert into ad_zd_report_table_columns (p_owner,p_view_name,p_view_column_name,p_table_column_name,p_status)

values(l_owner, l_view_name, l_view_col, l_table_col,’NEW’);

fetch new_columns into l_view_col,l_table_col;

end loop;

close new_columns;

 

 

open revised_columns(l_owner,l_view_name);

fetch revised_columns into l_view_col,l_table_col;

while (revised_columns%found) loop

insert into ad_zd_report_table_columns (p_owner,p_view_name,p_view_column_name,p_table_column_name,p_status)

values(l_owner, l_view_name, l_view_col, l_table_col,’CHANGED’);

fetch revised_columns into l_view_col,l_table_col;

end loop;

close revised_columns;

 

 

fetch c_changed_evs into l_view_name, l_owner;

end loop;

close c_changed_evs;

commit;

END;

/

 

column TableName format a30

column ColumnName format a30

column status format a10

 

 

select substr(p_view_name,1,length(p_view_name)-1) TableName,

p_view_column_name ColumnName,

p_status STATUS

from ad_zd_report_table_columns

order by TableName,status desc,ColumnName

/

 

 

declare

procedure exec_force(stmt in varchar2) is

no_such_table exception;

pragma exception_init(no_such_table, -942);

begin

execute immediate stmt;

exception

when no_such_table then

null;

end;

begin

exec_force(‘drop table ad_zd_report_table_columns’);

end;

/

 

 

prompt

prompt

prompt

prompt =========================================================================

prompt = New Indexes

prompt =========================================================================

 

column owner format a30

column table_name format a30

column index_name format a30

 

select

idx.owner owner

, idx.table_name

, ad_zd_table.original_index_name(idx.index_name) index_name

from dba_indexes idx

where idx.owner in

( select oracle_username

from fnd_oracle_userid

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

and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, ‘c’)

and not exists ( SELECT inner.index_name

FROM dba_indexes inner

WHERE inner.index_name = ad_zd_table.original_index_name(idx.index_name)

AND inner.owner = idx.owner)

order by 1, 2, 3

/

 

 

prompt

prompt

prompt

prompt =========================================================================

prompt = Revised Indexes

prompt =========================================================================

 

column owner format a30

column table_name format a30

column index_name format a30

 

select

idx.owner owner

, idx.table_name

, idx.index_name patched_index

, oidx.index_name original_index

from dba_indexes idx, dba_indexes oidx

where idx.owner in

( select oracle_username

from fnd_oracle_userid

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

and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, ‘c’)

and oidx.owner(+) = idx.owner

and oidx.index_name = ad_zd_table.original_index_name(idx.index_name)

order by 1, 2, 3

/

 

 

prompt

prompt

prompt

prompt =========================================================================

prompt = Cross-edition Triggers

prompt =========================================================================

 

column owner format a30

column trigger_name format a30

column table_owner format a30

column table_name format a30

column crossedition format a10

column status format a10

 

select trg.owner,

trg.trigger_name,

trg.table_owner,

trg.table_name,

crossedition,

status

from dba_triggers trg

where crossedition in (‘FORWARD’, ‘REVERSE’)

order by 1

/

 

 

prompt

prompt

prompt

prompt =========================================================================

prompt = New/Revised Materialized views

prompt =========================================================================

 

column “Owner” format a30

column “MView Name” format a30

column “Status” format a7

 

SELECT mvq.owner “Owner”,

ad_zd_mview.get_mv_name(mvq.view_name) “MView Name”,

‘NEW’ “Status”

FROM dba_views_ae mvq,

fnd_oracle_userid fou

WHERE fou.read_only_flag in (‘A’,’B’, ‘E’, ‘U’)

AND mvq.owner = fou.oracle_username

AND mvq.editioning_view = ‘N’

AND mvq.view_name LIKE ‘%#’

AND mvq.edition_name = :patch_edition

AND NOT EXISTS (SELECT mv.mview_name

FROM dba_mviews mv

WHERE mv.owner = mvq.owner

AND mv.mview_name = ad_zd_mview.get_mv_name(mvq.view_name))

UNION

SELECT mvq.owner “Owner”,

ad_zd_mview.get_mv_name(mvq.view_name) “MView Name”,

‘CHANGED’ “Status”

FROM dba_views_ae mvq,

fnd_oracle_userid fou

WHERE fou.read_only_flag in (‘A’,’B’, ‘E’, ‘U’)

AND mvq.owner = fou.oracle_username

AND mvq.editioning_view = ‘N’

AND mvq.view_name LIKE ‘%#’

AND mvq.edition_name = :patch_edition

AND EXISTS (SELECT mv.mview_name

FROM dba_mviews mv

WHERE mv.owner = mvq.owner

AND mv.mview_name = ad_zd_mview.get_mv_name(mvq.view_name))

ORDER BY “Owner”, “Status”, “MView Name”

/

prompt

spool off;

exit;

/

 

 

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

/

 

Finding the Patches inside a Merged Patch

SELECT applied_patch_id, rapid_installed_flag, patch_name, patch_type, maint_pack_level
FROM   applsys.ad_applied_patches
order by 1 desc;
select * from ad_patch_drivers
where applied_patch_id = 102974;
Notice the ORIG_PATCH_NAME is a surrogate number instead of the old convention of “merged”.
PATCH_DRIVER_ID     APPLIED_PATCH_ID     ORIG_PATCH_NAME

                       99582                            102974                     2123079401

SELECT ab.bug_number, acp.patch_abstract
FROM  ad_comprising_patches acp, ad_bugs ab
WHERE acp.bug_id = ab.bug_id AND acp.patch_driver_id = 99582;

BUG_NUMBER    PATCH_ABSTRACT

 21830810            R12.TXK.C.DELTA.8
 23569114            INVALID APPS DATABASE USER CREDENTIALS – TXKGENADOPWRAPPER_PL – 12.2.4
23705992             TNSNAMES.ORA GENERATED WITH SCAN NAME INSTEAD OF SCAN IPS FOR CUSTOM RACE DB CLONE
18525466             REVERT CHANGES MADE TO VALIDATE MANAGED SERVER PORTS OF ALL NODES