Patch Wizard
Download
Report
Transcript Patch Wizard
Patch Wizard for the Masses:
An APEX Version of the Patch
Impact Analysis Reports
Prepared by:
Chad Johnson
DBA
Polk County Florida
What Are Talking About?
■ What is Patch Wizard?
▪ Benefits
▪ Drawbacks
■ What we wanted to change
■ How we extended the functionality
What is Patch Wizard?
■ Patch Wizard is a Web-based utility in Oracle
Applications Manager (OAM).
■ One of the best kept secrets in Oracle
Applications
■ A great tool for System Administrators and staff
for planning and executing patch application
■ Our main goal is to identify only the modules
and features to be affected by the patches we
plan to apply in order to reduce the amount of
testing
▪ Focus on Patch Impact Analysis
What is Patch Wizard?
▪ We will focus on Patch Impact Analysis
▪ Pull down Karen Brownfield’s session #13937
- R12 Patch Wizard for Sysadmins and
Functional Super Users for a more
complete coverage of the tool
http://oaug.org/education-events/cpd
Karen is collecting complaints and
suggestions for the ATG Customer Advisory
Board. Please contribute.
Patch Wizard MOS Note 1077813.1
■ Patch Wizard cannot analyze non-Applications
patches
▪ CPU/PSU security patches
▪ Other database patches
■ Patch Wizard cannot analyze password protected patches
■ Occasionally a Patch Wizard analysis runs to completion,
reports no errors but is empty.
▪ Usually fixed by using adadmin to “Update current view
snapshot”
Parts of Patch Wizard
■ Executes concurrent programs to perform the steps
■ Uses the current system snapshot and an Information
Bundle to determine patches needed for new
codelevels and to determine recommended patches not
currently applied
■ Requires valid MOS credentials and access to MOS for
full features (i.e. download patches, download the
Information Bundle, etc.)
▪ Can work around this requirement if the production server is
secured
—
Download patches manually
– /staging/ad staging/nonad directories
—
Download the Information Bundle manually
Patch Wizard MOS Note 1077813.1
■ The manual download of the Information Bundle is now
available at the following URLs:
For release 11i,
https://updates.oracle.com/download/InfoBundle11i.zip
For release 12,
https://updates.oracle.com/download/InfoBundleR12.zip
■ --Contains Readmes, LDT files and Metadata about the
patches
Concurrent Programs Run in Analysis
Purging Patch Wizard
Patch Wizard uses 3 different programs
Submit Analyze Patches (Wrapper) - Short name: PAANALYZEPATCHES
Submit Download Patches (Wrapper) - Short Name: PADOWNLOADPATCHES
Submit Recommend Patches (Wrapper) - Short Name: PARECOMMENDPATCHES
Related Tables
AD_PA_*
AD_PM_*
AD_PA_ANALYSIS_RUNS
AD_PA_ANALYSIS_RUN_BUGS
AD_PA_ANAL_BUG_DEPS
AD_PA_ANAL_RUN_BUG_CODELEVELS
AD_PA_ANAL_RUN_BUG_PREREQS
AD_PA_ANAL_RUN_PREFERENCES
AD_PA_CRITERIA
AD_PA_CRITERIA_PRODUCTS
AD_PA_CRITERIA_PROD_FAMS
AD_PA_PATCH_ENTITY_INFO
AD_PA_ENTITY_INFO
AD_PA_PATCH_COND_REQUIRES_INFO
AD_PA_PATCH_REQUIRES_INFO
AD_PM_MASTER
AD_PM_PATCHES
AD_PM_PATCH_TYPES
AD_PM_PATCH_TYPE_MAP
AD_PM_PREFERENCES
AD_PM_PRODUCT_INFO
AD_PM_PROD_FAMILY_MAP
AD_UMS_LDT_LOADS
Related Tables
FND_IMP_*
FND_IMP_AFFECTEDFILES
FND_IMP_BUGSET
FND_IMP_BUGSET_TEMP
FND_IMP_DEPMODIFIEDTIME
FND_IMP_DEPOBJECTS
FND_IMP_DEPRELATIONS
FND_IMP_MENU_DEP2
FND_IMP_MENU_DEP_SUMMARY2
FND_IMP_MENU_DEP_SUMMARY3
FND_IMP_DIAGMAP
FND_IMP_LANG_SUMMARY
FND_IMP_MONITOR
FND_IMP_PFILEINFO
FND_IMP_PFILEINFO2
FND_IMP_PISUMMARY
FND_IMP_PSCOMMON
FND_IMP_PSMASTER2
FND_IMP_PSNEW
FND_IMP_SFILEDEP
Related Files
OA_HTML/oam/
sql
patch
advisor
pia
UIX (User Interface XML) is a set of technologies that
constitute a framework for building web applications.
The main focus of UIX is the user presentation layer of
an application.
Patch Wizard
Patch Wizard Preferences
Patch Wizard Preferences
AD_PM_PREFERENCES
Patch Wizard Preferences
Per User
Patch Filters
Can create your own filter(s).
Impact Summaries
FND_IMP_PISUMMARY
Files Introduced, Changed, and Unchanged
Total:
select TYPEID, count(*)
from FND_IMP_PSMASTER2
where BUG_NO = 16084364
group by TYPEID;
923
Files: Total In Patch (Detail)
select *
from FND_IMP_PSMASTER2
where BUG_NO = 16084364;
Indirect Summary - Patch Wizard
SELECT * FROM FND_IMP_MENU_DEP_SUMMARY3 ;
17
35
Patch Descriptions
select *
from AD_PM_PATCHES;
Patch Analysis (Header and Detail)
AD_PA_ANALYSIS_RUNS;
AD_PA_ANALYSIS_RUN_BUGS
Drawbacks
■ Requires System Administrator, but used by
functional staff
■ No readily available ‘semi-big’ picture
▪ Summaries are good, but not enough detail
▪ Details are plentiful but drilldown is limiting
■ Redundant data
■ Irrelevant data (i.e. non-US responsibilities)
■ No list of effect on individual testers
Indirect Summary – Patch Wizard
Solutions
■ Requires System Administrator, but used by
functional staff
Create a custom responsibility
■ No readily available ‘semi-big’ picture
▪ Summaries are good, but not enough detail
▪ Details are plentiful but drilldown is limiting
■ Redundant data
■ Irrelevant data (i.e. non-US responsibilities)
■ No list of effect on individual testers
Build an APEX App
What is APEX?
■ Application Express (APEX) is Oracle’s rapid
application development tool for web-based
applications on Oracle databases
■ Declarative (what to do rather than how to do it)
■ Uses wizards for most development tasks
■ Short learning curve
■ Fully supported by Oracle
■ Free! But check with your sales rep. Some limitations
apply.
APEX Architecture
■ Applications are stored as meta-data in the database
(Not the APPS server)
■ Procedures generate HTML
■ The meta-data is used to render pages and processing
■ Can be installed on 10gR2 and above
■ Development and runtime access is browser based (no
client software)
APEX Installation
Download APEX (4.2) and Installation Guide
from OTN
http://otn.oracle.com/apex
Install APEX (in database)
Do NOT install in SYSAUX tablespace
SYSAUX belongs to Oracle
Download and install Glassfish
Register APEX Listener with Glassfish
Installation Best Practices
■ Create custom applications in a separate
schema
■ Create the custom tables, views, triggers,
sequences in custom schema
■ Create views on APPS tables
■ Grant select privileges on APPS views to APEX
user as necessary
Programming Best Practices
■ Do NOT perform direct updates to seeded
tables!
■ Will bypass Oracle’s validation and referential
integrity
▪ Use published APIs
–with instead-of triggers
■ Can use FND_SUBMIT for intensive updates
Programming Best Practices
APEX wizards (used to) require primary keys
Wizards make life simple
Create views on seeded tables with an arbitrary
‘primary key’ (2 columns at most)
Modify APEX wizards’ row processing to call
APIs via INSTEAD OF triggers on views
Create View – Responsibilities Assigned
CREATE OR REPLACE VIEW v_polk_resps
AS
SELECT UNIQUE
g.responsibility_id
, r.application_id
, u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
, fa.application_short_name
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_application fa
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND fa.application_id = r.application_id;
Only Those Responsibilities Assigned
Inner Join on V_POLK_RESPS
Indirect Summary – Patch Wizard
Indirect Summary – Patch Wizard
Responsibilities (Interactive Report)
Responsibilities: Filtered with Control Break
<<= Selection
<<= Control Break
Responsibilities By User
Responsibilities: Download Report
Enhancement Request Created for this issue : Bug 9703082 –
PROVIDE REPORT OR EXPORT BUTTON FOR PATCH WIZARD
OUTPUT SUMMARY+IMPACT ANALYSIS
Responsibilities (Interactive Report)
Responsibilities: Download Report
Slice and dice to your heart’s content!
Responsibilities: Chart It
Responsibilities: Chart It
The Sky Is The Limit
Review Patch Wizard Report Set
-- Review Patch Wizard Report Set
select rs.application_id, rs.request_set_id, rsp.request_set_program_id,
rs.request_set_name, rst.user_request_set_name,
rsp.concurrent_program_id,
p.user_concurrent_program_name, p.description
from FND_REQUEST_SETS rs, FND_REQUEST_SETS_TL rst,
FND_REQUEST_SET_PROGRAMS rsp,
FND_CONCURRENT_PROGRAMS_TL p, fnd_user fu
where rs.request_set_id=rst.request_set_id
and rst.request_set_id = rsp.request_set_id
and rsp.concurrent_program_id = p.concurrent_program_id
and rs.owner = fu.user_id
and rst.user_request_set_name like '%Patch%Wiz%'
order by rs.request_set_id;
Responsibilities Assigned to User
select distinct vpr.user_name, fim.application_short_name,
fim.responsibility_name, fim.path, fim.form_name
from fnd_imp_menu_dep_summary2 fim
inner join v_polk_resps vpr
on fim.application_short_name = vpr.application_short_name
and fim.responsibility_id = vpr.responsibility_id
where bug_no is not null
order by vpr.user_name;
References
■ MOS Note 976188.1 Patch Wizard Utility
■ MOS Note 976688.1 Patch Wizard FAQ
■ MOS Note 1085668.1 Patch Wizard Training
■ MOS Note 1267768.1 Required Patches for Patch
Wizard
■ Collaborate Presentation Database
http://oaug.org/education-events/cpd
■ OAUG Insight Magazine (Summer 2014)