1 / 48

Patch Wizard for the Masses: An APEX Version of the Patch Impact Analysis Reports

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?.

aiko
Download Presentation

Patch Wizard for the Masses: An APEX Version of the Patch Impact Analysis Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Patch Wizard for the Masses: An APEX Version of the Patch Impact Analysis Reports Prepared by: Chad Johnson DBA Polk County Florida

  2. What Are Talking About? • What is Patch Wizard? • Benefits • Drawbacks • What we wanted to change • How we extended the functionality

  3. What is Patch Wizard? • PatchWizard 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

  4. 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.

  5. 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”

  6. 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

  7. 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.zipFor release 12, https://updates.oracle.com/download/InfoBundleR12.zip • --Contains Readmes, LDT files and Metadata about the patches

  8. Concurrent Programs Run in Analysis

  9. 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

  10. 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

  11. 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

  12. 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.

  13. Patch Wizard

  14. Patch Wizard Preferences

  15. Patch Wizard Preferences AD_PM_PREFERENCES

  16. Patch Wizard Preferences Per User

  17. Patch Filters Can create your own filter(s).

  18. Impact Summaries FND_IMP_PISUMMARY

  19. Files Introduced, Changed, and Unchanged Total: 923 select TYPEID, count(*) from FND_IMP_PSMASTER2 where BUG_NO = 16084364 group by TYPEID;

  20. Files: Total In Patch (Detail) select * from FND_IMP_PSMASTER2 where BUG_NO = 16084364;

  21. Indirect Summary - Patch Wizard SELECT * FROM FND_IMP_MENU_DEP_SUMMARY3 ; 17 35

  22. Patch Descriptions select * from AD_PM_PATCHES;

  23. Patch Analysis (Header and Detail) AD_PA_ANALYSIS_RUNS; AD_PA_ANALYSIS_RUN_BUGS

  24. 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

  25. Indirect Summary – Patch Wizard

  26. 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

  27. 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.

  28. 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)

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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_applicationfa , 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;

  34. Only Those Responsibilities Assigned Inner Join on V_POLK_RESPS

  35. Indirect Summary – Patch Wizard

  36. Indirect Summary – Patch Wizard

  37. Responsibilities (Interactive Report)

  38. Responsibilities: Filtered with Control Break <<= Selection <<= Control Break

  39. ResponsibilitiesBy User

  40. Responsibilities: Download Report Enhancement Request Created for this issue : Bug 9703082 – PROVIDE REPORT OR EXPORT BUTTON FOR PATCH WIZARD OUTPUT SUMMARY+IMPACT ANALYSIS

  41. Responsibilities (Interactive Report)

  42. Responsibilities: Download Report Slice and dice to your heart’s content!

  43. Responsibilities: Chart It

  44. Responsibilities: Chart It

  45. The Sky Is The Limit

  46. 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_userfu 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;

  47. 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_respsvpr 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;

  48. 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 Databasehttp://oaug.org/education-events/cpd • OAUG Insight Magazine (Summer 2014)

More Related