Oracle Patch Impact analysis

Objective:

Oracle always provides patch to fix bugs, add more features, improvements and so on.
So while implementing patch in development environment everyone wants to know the impact of the patch, what objects are impacted, how many files are added or changed etc.

Various method:

There are 2 methods this can be achieved.

1. adpatch
2. Patch wizard

1. adpatch:

You can install patch with apply mode=no (by default it is yes).
apply=no mode will provide a snap shot of what the patch will do (like introducing new file, changing exisitng files and so on) but it will not apply the patch.

The syntax for using this mode is: 

$ adpatch apply=no 

Problem with this method:

The major issue with this method is lets say if you are applying patch set which involves 500+ objects then reading log file (log file which generated from adpatch) will be a big hassle because you will have to go through each object one by one and get the complete list of impacted objects. Also it will not tell you whether it has modified or creating.

2. Patch wizard:

This method is easy to use and efficient way to get impact analyzed.

Steps to follow:

a. You can analyze patch either having Patch wizard itself download patch from metalink.oracle.com for you and then do the analysis [or] you can download patch manually and then have patch wizard do analyze for you.

We will talk more about how to analyze patch after downloading manually patch from metallic.oracle.com

a. Login as SYSADMIN and navigate to patch wizard

b. Naviage to site map tab --> maintenance (sub tab) -->  patch wizard



c. Change preference (this is one time setup)

d. Provide staging directory in the preference.
Remember this location should have full access.

e. create ad directory within staging location. <staging directory>/ad
Move patch.zip file to <staging directory>/ad
e.g.: /patches12/ad

f. Move InfoBundleR12.zip file to staging directory. Patch wizard requires this file because this stores the meta data for analyzes.

g. Give patch # and perform the analysis.

h. Once analyse is done you can click on the details to find more about the impacts like
       a. No of file changed
       b. No of files newly added
       c. Object type
       e. Impacted modules, etc...

Using below query you can get the list of patches analyzed and other details:

select
adp.bug_number patch_name,
adp.product_family_abbreviation ,
ad_pa_validate_criteriaset.get_cs_prod_fam_abbr(adp.product_abbreviation) product_family_abbreviation,
nvl(adp.product_abbreviation, ' ') ,
adp.bug_description ,
INITCAP(NVL(adp.patch_type,'User Request')) Patch_Type ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_prereqs aparbp
WHERE adb.analysis_run_bug_id = aparbp.analysis_run_bug_id ) prereq_cnt,
DECODE (adh.bug_number, NULL, 'N', 'Y'),
adt.name ,
adb.analysis_run_id ,
analysis_run_bug_id ,
ads.snapshot_id ,
ad_pa_validate_criteriaset.get_cs_prod_fam_name(adp.product_abbreviation) product_family_name ,
appi.product_name ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_codelevels aparbc
WHERE adb.analysis_run_bug_id = aparbc.analysis_run_bug_id ) level_cnt ,
decode (adb.analysis_status,'MISSING','Missing','READY','Unapplied','APPLIED','Applied',adb.analysis_status) patch_status ,
nvl(decode(adp.has_msi_steps,'Y','Yes','N','No',adp.has_msi_steps), ' ') hasMsiSteps ,
adp.baseline ,
adp.entity_abbr ,
adp.patch_id
from
ad_pm_patches adp ,
ad_appl_tops adt ,
ad_pa_analysis_run_bugs adb ,
ad_pm_product_info appi ,
ad_snapshots ads
,ad_hidden_patches adh
where
1=1--adb.analysis_run_id = 402352 --<request_id>
--and adb.analysis_status in ('MISSING','READY','APPLIED')
--and ads.SNAPSHOT_TYPE = 'G'
--and adt.NAME ='GLOBAL'
and adb.appl_top_id = adt.APPL_TOP_ID
and ads.appl_top_id = adt.APPL_TOP_ID
and appi.product_abbreviation = adp.product_abbreviation
and adb.bug_number = adp.BUG_NUMBER
and adb.baseline = adp.baseline
--and adp.patch_metadata_key = 'DEFAULT'
--and adp.is_code_level = 'Y'
and adh.bug_number(+) = adp.BUG_NUMBER
order by patch_name;