1 / 42

What’s Up Doc? Easy Data Health Checks for Oracle Apps

Learn how to perform easy data health checks for Oracle Apps 11i, including user security, profile settings, and module-specific issues.

hirams
Download Presentation

What’s Up Doc? Easy Data Health Checks for Oracle Apps

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. What’s Up Doc? Easy Data Health Checks for Oracle Apps Lynne Paulus Oracle Applications DBA Fair Isaac NorCal OAUG Jan 2007

  2. 11i Health Checks: Objectives • Describe some ‘Health’ conditions to check in 11i • Highlight issues that have caused us problems • Share Health Check maintenance tips • Describe how leverage our SQL statements in your environment

  3. Fair Isaac and Oracle Apps • Fair Isaac: Decision Support Software - approx 3,000 employees • Bay Area = San Rafael, San Jose, Emeryville • San Diego, Minnesota and many other locations (Int’l) • Live on Oracle Apps since 1992 • Currently on 11.5.10.2 • Upgraded Nov 2005 from 11.5.8 • Migrated to Oracle Sales from Sales Online Oct 2006 • Upgraded to ATG RUP4 Dec 2006 • Modules = Financials, Projects, HR, OM, many CRM modules • All employees use 11i for Time and Expenses (OTL/iExpense)

  4. 11i Health Check Characteristics • Overview of your environment’s health at a point-in-time • Not event driven - No alert as soon as condition occurs • Still timely enough that negative impact often avoided • Reduces Prod Support and Problem analysis time • Does not replace DB monitoring Software • For critical DB monitoring, we use Quest Foglight - notified immediately of critical conditions • Examples: DB down, low disk space

  5. 11i Health Check Characteristics • Use format which is easy to extend • Example: simple SQL script run as unix Cron job • Script emails the spool file to DBAs and Sys Admins • Some checks apply to all 11i - some may not apply to your environment • Examples: • Different versions of 11i have different logic • HR not fully implemented, may lack some employee info for selected checks

  6. 11i Health Checks: Two Categories • 1) Warnings: Normally expect no rows returned • General, User Security, Profile Settings, Direct Customizations, Module Specific Issues, Std DB Checks • Often most important checks so at top of daily health check • 2) General Health Checks: (categories overlap with Warnings but general checks often return data) • Invalid Objects • General DB • User Load Checks • Application Users • Profile Settings • Module Specific

  7. 11i Health Checks: Two Frequencies Frequencies: 1) Daily: Includes both Warnings and General Checks • Currently monitor over 75 data conditions • Will cover some of these checks in more detail 2) Weekly: • Tables/Views created in last month (DBAs usually know why objects got created, if not, research) • Count users whose password will expire in next several weeks (look for bubbles of high password expiration) • Lower priority checks

  8. Section Warning Health Checks

  9. Sample output from Warning Section +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++ Warning Section: +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Confirm not in Maintenance Mode - should see no rows no rows selected User Security AND Access Issues section: Show any logins whose password is NOT set to expire in 90 days no rows selected Non-DBAs who have System Administrator or other Powerful responsibilities no rows selected Terminated employees who still have an active login to Oracle Apps USER_NAME            TERM_DATE                             -------------------- --------------------                             LYNNEPAULUS             06-Jan-07  

  10. Warning Health Checks • Confirm DB not in Maintenance Mode • Profile Option = 'Applications Maintenance Mode‘ • When Profile set to ‘NORMAL’ means not in Maint Mode so application should function normally • If start Forms and Apache while in Maint Mode, users have access problems but startup looks successful • We added this Profile check to our Application Start shell script • We get a warning and application DOES NOT start • Makes obvious dbas forgot to turn Maint Mode off

  11. Warning Health Checks – User Warnings • Non-DBAs/Sys Admins who have System Administrator and other powerful or restricted responsibilities • We monitor following Responsibilities that have special capabilities (e.g. ability to change Site Profile Options): • 'System Administrator' • 'Application Developer' • 'Application Developer Common Modules' • ‘CRM HTML Administration' • 'Functional Administrator' -- can chg Profiles • 'Functional Developer' • 'Knowledge Administrator' • 'Oracle Sales Administrator' • 'Sales Administrator' • 'SFM System Administrator' • 'TeleSales Administration'

  12. Warning Health Checks – User Warnings (cont) • Users lacking password expiration setup • Adjust script to your password expiration standard • Auditors like this check • For automating new employee login accounts see Jordan Kraft’s paper OAUG June 2005 • Terminated employees with active FND login account • May need HR fully implemented for this logic USER_NAME TERM_DATE -------------------- --------- LYNNEPAULUS 06-Jan-07

  13. Warning Health Checks – User Warnings (cont) • Problems with email addresses stored in FND_USER table: • FND Users who have the same password • Out of synch email address: • FND email address different than HR • Find any employee who is attached to more than one FND USER • Can happen during name change, name correction or similar names

  14. Warning Health Checks – Workflow Warnings • Workflow users or adhoc roles with wrong notification preference • Problems if user needs to WF approval but their notif pref set wrong • We require all notif pref set to ‘MAILHTML’ • Avoids lots of workflow issues • Confirm Workflow Mailer PROCESSOR_READ_TIMEOUT_CLOSE set to ‘Yes’ • Mailer less reliable and notifications delayed when set to ‘No’ • ATG RUP4 switched this setting from ‘Yes’ to ‘No’ so added this health check (our newest check)

  15. Warning Health Checks • Special Check for fnd_oracle_userid table values • Confirm table has correct value, see MetaLink note # 235634.1 • When 0, causes ORA-1403 errors in CRM • Encountered this error in 2005 (early in 11.5.10 upgrade project) • Adjusted upgrade tasks, incorporated Oracle provided script to switch to 1 for ‘oracle_username = APPS’ • Problem re-occurred year later in 2006, after live on 11.5.10 • Lost weeks doing problem analysis since problem is subtle and over a year since problem bit us • Problem bit us twice so added to daily health checks • Now if happens again, better chance of determining cause

  16. Warning Health Checks – Profile Settings • Profile Setting Warnings • Confirm Examine function is password protected • Profile = ‘Utilities:Diagnostics’ • When ‘Y’ then allows Help->Diagnostics->Examine without requiring Apps password (risky since can change data without Form protections) • Protects other Help, Diagnostics actions • When clone, we switch Profile on in Non-Prod environments • Cryptic Logic so helpful to explain logic of setting in SQL script • Include comments in your health check script since quickly forget why these conditions matter

  17. Warning Health Checks – Concurrent Programs • Concurrent Programs with Trace On • Show any program with Trace turned on • Set temporarily on in Prod to research problem but forget to turn off • Confirm Periodic Alert Scheduler is scheduled • Confirm selected Programs are disabled • Have a few custom programs only enabled as needed • Cause problems if users able to submit anytime • Switch to enabled as needed BUT hard to remember to disable later • We define Program with Description including words ‘USUALLY DISABLED’ for health check search filter

  18. Warning Health Checks • Direct Customization Change Warnings • Definition = Standard objects you customized (not ‘extensions’) • Keep to absolute minimum - avoid like plague • Lose your changes when Oracle patches or upgrades object • Health Check to see whether object changed recently – Alerts that change may be lost • Examples: • Views customized for better performance or logic (e.g. ADP Views) • Package Changes: iExpense Approval - emergency customization to allow CEO to enter Expense Report Owner Object Name Created Last DDL ---- -------------------- --------- --------- APPS AP_WEB_DB_HR_INT_PKG 08-OCT-99 13-JAN-06

  19. Warning Health Checks • Direct Customization Change Warnings (cont) • Changed Standard Concurrent Program parameter setting • Business need to Change Project Gen Draft Revenue parm default • Made change as part of Change Request • Documented as ‘Custom Direct Change’ in our master doc • Lost change when applied small PA patch to Prod (result = angry Business Analyst) • Added to Health Check warnings since too hard to remember to check after every PA patch

  20. Warning Health Checks • Module Specific Warnings (many relate to Oracle data fixes) • Receivables (AR) • Orphaned rows in ar_journal_interim_all table • Caused by AR ‘Journal Entries Report' ending in Error • Check with Support on how to correct • Trading Community Arch (TCA) • Parties incorrectly set to Inactive when account is Active • Projects (PA) • Duplicate PA resource attributes • Projects that have schedule gaps

  21. Warning Health Checks • General DB Warnings • Locked db user accounts • We have setup some DB accounts to ‘lock’ after 3 invalid password tries and for password to expire after ‘N’ days • We only have invalid password logic on custom schemas, NOT APPS schema • Datafiles autoextended to their Max • We have set select datafiles to autoextend (e.g. FND, PA) • Can reach max without warning and cause out of space errors • Health check shows when max is reached • Need to set higher max or do data cleanup (e.g. rebuild indexes)

  22. Warning Health Checks • General DB Warnings • Expansion in Undo Tablespace • Can go unnoticed and take lots of space • Ours grew from 3 GB to 11 GB during 11.5.10 upgrade • Confirm it has not expanded past your norm • We now have new norm of 8 GB since 3 GB was too small Tablespace Total MB Used MB Avail MB Percent Free ------------ --------- --------- --------- ----------- APPS_UNDOTS1 3,518.00 389.19 3,128.81 88.94

  23. Sample output from Warning Section ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++ Warning Section: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Confirm not in Maintenance Mode - should see no rows no rows selected User Security AND Access Issues section: Show any logins whose password is NOT set to expire in 90 days no rows selected Non-DBAs who have System Administrator or other Powerful Responsibilities no rows selected Terminated employees who still have an active login to Oracle Apps USER_NAME            TERM_DATE                             -------------------- --------------------                             LYNNEPAULUS             06-Jan-06           

  24. Section General Health Checks

  25. General Health Checks • General Health Checks - often expect rows returned • Invalid Objects: • Apps (we currently have 6 always invalid) • We do not expect more than our ‘normal’ invalids (compile apps) • Custom Schema (we normally have zero invalids) OWNER OBJECT_NAME OBJECT_TYPE CREATED -------- ------------------------------ ------------ --------- APPS FND_OID_DIAG PACKAGE BODY 12-APR-06 APPS FND_TS_SIZE PACKAGE BODY 16-DEC-06 APPS MRP_AN_ONHAND_SUPPLIES_V VIEW 23-AUG-01 APPS MRP_AP_ONHAND_SUPPLIES_V VIEW 23-AUG-01 APPS MRP_AP_OPERATION_RESOURCES_V VIEW 23-AUG-01 APPS MRP_SN_OPR_RESS_T2 TRIGGER 23-AUG-01

  26. General Health Checks • General RDBMS Checks • Show count of failed DB logins (this does not include failed 11i logins) • Popular with auditors • List expired Schema accounts USERNAME CREATED DEFAULT TS TEMP STATUS LOCK_DATE -------------- --------- ---------- ---- ------ -------------- AD_MONITOR 12-NOV-05 TOOLS TEMP EXPIRE 11/12/05 12:34

  27. General Health Checks • User Load Checks: • Current Forms user count • Self-Service User count (in last hour since lack logoff time) • High Water Mark for Oracle Connections since last DB start • Current Total sessions • JDBC Thin Client connections – sometimes not released TIME OF DAY Forms connects Self Service Current Sess HighWater JDBC Thin ----------------------- ------------------------ ----------------- ------------------- --------------- --------------- 10-JAN-07 05:45 229 132 722 994 359

  28. General Health Checks • User Checks • Users whose FND Login Account password has become ‘INVALID’ (11.5.10.2) • Caused by too many failed login attempts USER_NAME LASTLOGON PASS_DATEENCRYPTED_PASSWORD -------------------- --------- --------- ---------------- JOHNNYGABRON 04-Jan-07 06-Jan-07 INVALID LYNNEPAULUS 30-Dec-06 06-Jan-07 INVALID ROBERTBOLTON 22-Dec-06 06-Jan-07 INVALID ROBERTKRENKE 22-Dec-06 INVALID RONPERALTA 08-Jan-07 22-Dec-06 INVALID SKALSTABAKTON 22-Dec-06 06-Jan-07 INVALID TRACEYMARSHALL 20-Dec-06 07-Oct-06 INVALID USMANCHADRY 22-Dec-06 22-Dec-06 INVALID

  29. General Health Checks • Profile General Checks • Site Level Profile settings changed in last 30 days USER_PROF_NAME value USER_NAME UPDATE_DATE ---------------------------------------- --------------- ---------- ----------- Applications Maintenance Mode NORMAL ANONYMOUS 08-Jan-2007 ICX: Discoverer End User Layer Language US LYNNEPAULU 07-Jan-2007 PA: Percentage Of Resource's Capacity 0 NATEKRUNNE 05-Jan-2007 PA: Resource Utilization Calculation Met CAPACITY NATEKRUNNE 06-Jan-2007 PA: Resource Utilization Period Type GL NATEKRUNNE 06-Jan-2007 PA: Starting Assignment Status 104 NATEKRUNNE 11-Jan-2007 PA: Utilization Calculation Begin Date 01-DEC-2005 NATEKRUNNE 06-Jan-2007 PA: Utilization Records Per Set 1000 NATEKRUNNE 06-Jan-2007 WF: Mailer Cancellation Email N NATEKRUNNE 03-Jan-2007

  30. General Health Checks • Profile General Checks • Non-User Level Profile Settings relating to Trace or Debug • User Level Profile Settings relating to Trace or Debug prof_id LEVEL_ID USER_NAME PROF_NAME value ------- ---------- --------------- ------------------------- ----- 4176 10004 KIRKMEITZ FND: Debug Log Enabled Y 1528 10004 DOUGHAYES PA: Debug Mode Y 1528 10004 MIKEMARTIN PA: Debug Mode Y

  31. General Health Checks • Module Specific Checks • Vary widely from site to site • General Ledger: GL consolidation tables that may be orphaned • iProcurement: Check for stuck Web requisitions • Research ‘Purge System Saved Requisition’ Con Program • OM: Check OE Processing Msgs for Number AND types of rows • Research ‘Message Purge’ Concurrent Program to clean

  32. Section Health Check Tips

  33. 11i Health Check Tips: • How much time needed to monitor Health Check Report? • Warning section first - Expect no rows so scan is quick • General Section - Scan for differences • Use easy to maintain format - Keep it Simple • My unix shell script emails SQL spool file to Apps DBAs • Independent of monitoring Software • Easy to expand • Add additional checks at least once a month – takes about 5 minutes to expand • Any time new condition ‘bites’ us, add to health check

  34. 11i Health Check Tips: • Reduce Prod Support and problem analysis time • Vigilant adding health checks to detect past problems • Mental filter running during problem solving • How avoid problem in future? Would health check work? • Oracle supplies ‘fix-it’ scripts to correct data that was ‘broken’ • Add same logic to health check so trap when/if happens again • When trap problem again, know user action was in last 24 hours • Much easier to determine root cause

  35. 11i Health Check Tips: • New/Changed Policies often imply new health check • Example: Decided to turn off ‘Debug’ link on Self Service from all but DBAs -> added warning health check for exceptions • Attitude: Never rely on ‘honor system’ to keep data right. Easy to forget to change something back • Examples: • Turn debug mode on for user but forget to turn off • Enable concurrent program that usually disabled • Temporarily assign someone a restricted responsibility

  36. 11i Health Check Tips: • Good place to store logic behind issues • Shows what conditions matter, what SQL detects them • Includes reasons why we care about conditions • SQL Logic often very cryptic and join conditions complex • Acts as repository of Apps exception conditions • Better than own memory for details of issues • Use as Reference as to where certain data stored • Add comments about when a check will change (e.g. after migrate to 11.5.10, this condition can be resolved by Con Pgm)

  37. 11i Health Check Tips: • Treat Health Check script as important source code • Archive off current version before making any changes • Add Change Log entry at top of script for audit trail • We do not include in Change Control process since modifies no data • Better to error on side of having too many checks than too few • Put less important queries near end of script or in less frequent script • Consider including ‘as time allows’ reminders in weekly or monthly checks • We have more tasks than can get to so include queries that remind of outstanding issues (e.g. cleanup GL Temp Consolidation tables)

  38. 11i Health Check Tips: • My health check SQL script available on NorCal OAUG Web site: 2007 Training Day Presentations. • Tentative file name = 5.07_paulus_sup.sql • Many of your health checks will be different • Use mine to jump start health checks or as reference to expand yours • Must edit script, at least change who allowed restricted responsibilities, when passwords should expire, etc. • ‘Warnings’ should retrieve few rows, otherwise adjust • Remove logic for modules not used and HR checks if lack data

  39. 11i Health Check Tips: • Best if Automat Health Checks Scripts • Your health check driver logic will probably vary from mine • My driver is unix shell script - runs from Unix Cron • Could use Oracle Alerts, 3rd party software, etc • More important that they be run rather than delay until elegant and/or proactive. Keep it simple for expansion • At minimum, if don’t implement automated Health Checks • Modify my SQL script for your site and run manually • Review output - look for changes, ‘low hanging fruit’ • Example - Password Protect ‘Examine’ feature

  40. Summary: • Running daily Health Checks reduces Prod Support and Problem analysis time • Often detect conditions before negative impact • Expand health checks as new issues arise • Trap conditions fixed in past but reoccur • Health checks do not need to be time consuming to review • Protect health check scripts since important source code • At minimum: try script manually, see what it finds at your site

  41. Off-Topic Comments: • We found 11.5.10.2 pretty stable for application modules • We’ve had lots of issues with Users, Responsibility Assignments and Workflow Role tables? Redesign of this logic was extensive, still seems ‘bleeding edge’. • Additional issues with this when we upgraded to ATG RUP4. • We’ve patched Workflow Directory Services several times since 11.5.10.2 • Lots of Lock contention when users changed password using Self Service preferences. Resolved by making ‘Known As’ view only • Built ‘Sys Admin View’ responsibility for viewing Profile settings, User Setup, Concurrent Program Definition, etc

  42. Q and A: • Question Ticklers: • How do you monitor who is currently logged into Forms and Self-Service? • Do you have your users time-out when inactive? • How quickly? • How do you get semi-exclusive access to 11i? • Found any disadvantage to adpatch options=nocompilejsp? • Who is using DataGuard with 11i? • Anyone doing Single Sign On and OID with Oracle Apps? • How often do you do routine maintenance of Concurrent Manager Tables? • What other 11i health checks do you run?

More Related