TruTek’s Newest R12.2.5 Book is Ready: the little r12.2.5 upgrade essentials for managers and team members

littler1225upgradeessentialsguideFRONTcover 03-29-16wBorderPutting together the right team to tackle the upgrade, and understanding the issues that the team needs to consider to be successful, can be quite a challenge. _the little r12.2.5 upgrade essentials for managers and team members_ describes the big picture of what you need to consider before tackling the Release 12.2.5 E-Business Suite upgrade. Based on TruTek’s popular R12.1 to R12.2 Technical Upgrade classes, this book describes what managers, functional, and technical team members need to know to prepare to upgrade from Release 12.1 to Release 12.2 of Oracle’s E-Business Suite of Applications.

**You can order Mike Swing’s newest book, and his other Release 12.1.3 and 12.2 [books](http://www.lulu.com/spotlight/trutek) now.**

High Level Upgrade Plan to 12.2.5 with Database Migration to Linux

Aside

High Level Upgrade Plan to 12.2.5 with a Database Migration to Linux

        TASK NAME                                                             Duration     

  1. Procure Linux Hardware                                                     12d
  2. Install and configure OEL for EBS for DB                            2w
  3. Gather custom code                                                             1w
  4. Migrate Solaris database to Linux                                       1mo
  5. Develop Test Scripts                                                            1w
  6. Test Linux 64 bit DB server with Solaris Apps Tier              1w
  7. Freeze Patching of PROD                                                    0d
  8. Production Cutover to Linux Database servers                    2d
  9. Configure Linux Upgrade DB server – Install OEL                1w
  10. Clone Linux DB servers to Linux Upgrade server                 1w
  11. Configure Linux Apps Upgrade server- Install OEL              1w
  12. Install 12.2.0 on Linux 64 bit apps tier                                  1w
  13. Upgrade DB to 12.1.0.2 on Upgrade Server                        1w
  14. Test 12.1.0.2 DB with Solaris Apps Tier                               1w
  15. Upgrade PROD DB to 12.1.0.2                                             2d
  16. Clone PROD DB to Upgrade DB                                          1w
  17. Upgrade to 12.2.5                                                                 2w
  18. Develop and Apply Customizations to 12.2.5                       3w
  19. Test 12.2.5                                                                            3w
  20. Upgrade Pass #2 to 12.2.5                                                   2w
  21. Apply Customizations                                                           1w
  22. Test 12.2.5                                                                            1w
  23. Performance Upgrade to 12.2.5                                           1w
  24. Apply Customizations                                                           1w
  25. Test 12.2.5                                                                            1w
  26. Go Live                                                                                  4d

 

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.

 

 

 

 

Use New Password Utility AFPASSWD in R12.1 and 11i

Originally, FNDCPASS was the password utility for Release 11i and AFPASSWD is the new password utility for R12.1 of the E-Business Suite. AFPASSWD has been backported from R12.1 and is now available for 11i.  The 11i version for AFPASSWD is delivered as part of patch 13644154.

AFPASSWD only prompts for passwords required for the current operation,
allowing separation of duties between applications administrators and database administrators.

In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

AFPASSWD can be run from the database tier as well as the application tier, while FNDCPASS can only be run from the application tier.

Prereqs for patch 13644154:

5903765 – 11i.ATG_PF.H.RUP6
3219567 – Patch 11i.TXK.B Technology Stack Minipack B (also in 11.5.10)
3264822 – Patch 11i.CAC.B
3261254 – Patch 11i.ALR.G
5161676 – MINIPACK 11I.AD.I.5
3036401 – Mini-Pack 11i.HZ.L
3263588 – Patch 11i.XDO.H
3264818 – Patch 11i.UMX.H
3218526 – Patch 11i.BNE.D
3263645 – Patch 11i.AK.G
4206794 – Patch 11i.FRM.H
3262486 – 11i.JTA.F
3261243 – Patch 11i.EC.G
2614213 – AME PATCH :DELIVERY OF GA AND RULE PRIORITY FUNCTIONALITY
3262159 – Patch 11i.FND.H
2819091 – Patch 11i.BNE.C
3412795 – ADSPLICE PATCH FOR XDO

Find the Patches in a Merged Patch

Maintaining and patching the E-Business Suite is faster when using the AD Merge Patch utility or admrgpch as the applmgr user, to merge patches. AD Merge Patch merges separate patches together, and the Patch Name stored in the database is “merged” and the patch driver is u_merged.drv. Merging patches avoids the duplication of effort, during the application of the merged patch, if multiple patches are trying to copy and generate the same file. The biggest time savings is the ability to recompile invalid objects only once, instead of once for each patch.

The database stores information about the patch when each patch is applied. The patch name is usually the same as the patch number. However, for a merged patch, the patch name is “merged”. This makes it difficult to report the patches that have been applied to your instance. One approach is to keep the patches on the filesystem in a directory named after the patch session.

Another approach to find all patches within a merged patch is to understand the tables that contain this information and the relationships between these tables.

By joining  the following APPLSYS tables, we can relate Patches to Bugs and know when the patches were applied:

ad_applied_patches   ->  ad_patch_drivers   ->   ad_comprising_patches   ->  ad_bugs

We can find all the bugs associated with a patch, even a merged patch.

1.      Find the APPLIED_PATCH_ID for a merged patch

Query the AD_APPLIED_PATCHES table:

SELECT applied_patch_id, rapid_installed_flag, patch_name, patch_type, maint_pack_level

FROM   applsys.ad_applied_patches

merged_patch

 

  Figure 1

Problem: We need to find all the patches that were part of the merged patch.

2.      Find the PATCH_DRIVER_ID

295501 is the applied_patch_id for the merged patch.  In order to find the detail of the merged patch, we need to look in the AD_PATCH_DRIVERS table, using the APPLIED_PATCH_ID from the AD_APPLIED_PATCHES table.

From the AD_PATCH_DRIVERS table we find the APPLIED_PATCH_ID 295501 and the corresponding PATCH_DRIVER_ID (279993):

AD_PATCH_DRIVERS Table:

merged_patch2

 

Figure 2

3.      Find all the BUGS

Then, use the corresponding PATCH_DRIVER_ID 279993 in the following query:

SELECT *

FROM (SELECT ab.bug_number ||DECODE(ab.baseline_name,NULL,”,’.’)

|| ab.baseline_name,  acp.patch_abstract

FROM  ad_comprising_patches acp, ad_bugs ab

WHERE acp.bug_id = ab.bug_id AND acp.patch_driver_id =279993)

WHERE ROWNUM<201

This returns the original bugs/patches that were merged together:

merged_patch3

 

Figure 3

I use bugs/patches because sometimes a bug number will also be the patch name/number. This happens when the bug is not merged with other bugs into a bigger patch and assigned a surrogate number.  Patches with surrogate numbers are related to multiple bugs.

4.      Join the queries together

By joining the queries together, we get a list of all patches and the patch names, even patch names that were assigned by Oracle. Oracle also uses AD Merge Patch to merge patches.

SELECT ab.bug_number, ap.patch_name, ap.patch_type, ap.maint_pack_level,

ap.last_update_date

FROM     applsys.ad_comprising_patches acp,

applsys.ad_bugs ab,

applsys.ad_patch_drivers pd,

applsys.ad_applied_patches ap

WHERE    acp.bug_id = ab.bug_id

AND acp.patch_driver_id = pd.patch_driver_id

AND pd.applied_patch_id = ap.applied_patch_id

ORDER BY ap.last_update_date desc

merged_patch4

 

Figure 4