Oracle Applications – 11i / R12 – Missing Patches Analysis

Every organisation has more than one oracle apps instance for different purpose viz., PROD, TEST, DEV etc. and generating a report on missing apps patches on the available instances is a pain if it has to be done manually.

Often, the TEST instances will have more patches when compared to PROD, reason being first the patch will have to be tested on a TEST instance for resolving an issue on PROD or DEV, then it will be moved to DEV and by the time the patch gets applied to PROD it will be a month or so, or may be even more than that depending on the management policies and severity of the issue for which the patch is applied on TEST or DEV.

Now lets say you want to compare DEV instance based on the patches applied to TEST instance.

1. Login to TEST instance as apps user
2. SQL Command: create table patch_list as (select distinct patch_name from ad_applied_patches);
3. Now PATCH_LIST table has all the patches that are applied to TEST instance.
4. Generate an insert script for the available patches.

SQL Command: spool patch_numbers.sql
SQL Command: select ‘insert into patch_list values(”’ || patch_name ||”’);’ from patch_list;
SQL Command: spool off

5. Login to the DEV instance as apps user
6. SQL Command: create table patch_list (patch_name varchar2(120));
7. Run patch_numbers.sql in this instance. This will populate the PATCH_LIST table with the TEST instance patches.
8. Run below sql to get the missing patches in DEV instance.

SQL Command: set heading off pages 0

SQL Command: select patch_list.* from patch_list LEFT JOIN ad_applied_patches ON patch_list.patch_name = ad_applied_patches.patch_name where ad_applied_patches.patch_name IS NULL;

The resulting output is the missing patches on DEV instance.

Above information can also be achieved without creating the PATCH_LIST table if DB_LINK is used. If using DB_LINK dont forget to modify the above query with correct column and table names.