1 / 31

How Healthy is your Apps Environment? Easy Health Checks for Apps 11i

How Healthy is your Apps Environment? Easy Health Checks for Apps 11i . Lynne Paulus Oracle Applications DBA Fair Isaac. 11i Health Checks: Objectives. Describe ‘Health’ conditions to check in 11i environment Highlight issues you may want to monitor

chatham
Download Presentation

How Healthy is your Apps Environment? Easy Health Checks for Apps 11i

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. How Healthy is your Apps Environment? Easy Health Checks for Apps 11i Lynne Paulus Oracle Applications DBA Fair Isaac

  2. 11i Health Checks: Objectives • Describe ‘Health’ conditions to check in 11i environment • Highlight issues you may want to monitor • Supply SQL statements that do health checks

  3. Fair Isaac and Oracle Apps • Fair Isaac: Decision Support Software - approx 3,000 employees • Bay Area = San Rafael • 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 • Modules = Financials, Projects, HR, OM, many CRM modules • All employees use 11i for Time and Expenses (OTL/iExpense) • Just went live on Project Resource Management

  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 • Does not replace DB monitoring Software • For critical DB monitoring we use Quest Foglight - notify immediately of critical conditions • Examples: DB down, low disk space

  5. 11i Health Check Characteristics • Use format which is easy to extend • I use 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 • May be on different version of 11i with different logic • You may not have HR fully implemented so some employee information may not be available

  6. 11i Health Checks: Two Categories • 1) Warnings: You normally expect no rows returned • General, User Security, Profiles, Custom Direct Chgs, Module Specific, Std DB Checks • 2) General Health Checks: (categories overlap with Warnings but general checks often return data) • Invalid Objects • General DB • User Load Checks • Application Users • Profiles • Module Specific

  7. 11i Health Checks: Two Frequencies Frequencies: 1) Daily: Includes both Warnings and General Checks • Will cover these checks in more detail 2) Weekly: • Objects created in last month (DBAs should usually know why objects got created, if not research them) • Count users whose password will expire in next several weeks (look for bubbles of high password expiration)

  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 Applic Develop Responsibility no rows selected Terminated employees who still have an active login to Oracle Apps USER_NAME            TERM_DATE                             -------------------- --------------------                             LYNNEPAULUS             06-Jan-06 

  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 • Users lacking password expiration setup • For automating new employee login accounts see Jordan Kraft’s paper OAUG June 2005 • Non-DBAs/Sys Admins who have System Administrator responsibility USER_NAME END_DATE TODAY RESPONSIBILITY --------- -------- --------- ------------------------ JASONBURGOS 14-JAN-06 System Administrator • Terminated employees with active FND login account USER_NAME TERM_DATE -------------------- --------- LYNNEPAULUS 06-Jan-06

  12. Warning Health Checks • Profile Warnings • Confirm Examine function is password protected • Profile = ‘Utilities:Diagnostics’ • When ‘Y’ then allows Help->Diagnostics->Examine without requiring Apps password – Also protects other Help->Diagnostics actions • Cryptic Logic so helpful to explain logic of setting in SQL script

  13. Warning Health Checks • Custom Direct Changes Warnings • 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 you that you may have lost your change • 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

  14. Warning Health Checks • Module Specific Warnings • 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 • Projects (PA) • Orphaned rows in pa_unbilled_rec_reporting table • Caused by 'MGT: Unbilled Receivables Aging' ending in Error • Check with Support on how to correct

  15. Warning Health Checks • General DB Warnings • Locked db user accounts • We have setup some DB accounts to ‘lock’ after 3 invalid password tries • We only have invalid password logic on custom schemas • Expansion in Undo Tablespace • Can go unnoticed and take a lot more disk space (ours grew from 3 GB to 11 GB during upgrade) Tablespace Total MB Used MB Avail MB Percent Free ------------ --------- --------- --------- ----------- APPS_UNDOTS1 3,518.00 389.19 3,128.81 88.94

  16. 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 Applic Develop Responsibility no rows selected Terminated employees who still have an active login to Oracle Apps USER_NAME            TERM_DATE                             -------------------- --------------------                             LYNNEPAULUS             06-Jan-06           

  17. Section General Health Checks

  18. General Health Checks • General Health Checks - normally expect to return rows • Invalid Objects: • Apps (we currently have 4 that are 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 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

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

  20. 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-06 05:45 229 132 722 994 359

  21. 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_DATE ENCRYPTED_PASSWORD -------------------- --------- --------- ---------------- JAMESADAMS 20-May-05 04-Apr-05 INVALID JOHNNYGABRON 04-Jan-06 06-Jan-06 INVALID LYNNEPAULUS 30-Dec-05 06-Jan-06 INVALID ROBERTBOLTON 22-Dec-05 06-Jan-06 INVALID ROBERTKRENKE 22-Dec-05 23-Sep-05 INVALID RONPERALTA 08-Jan-06 22-Dec-05 INVALID SKALSTABAKTON 22-Dec-05 06-Jan-06 INVALID TRACEYMARSHALL 20-Dec-05 07-Oct-05 INVALID USMANCHADRY 22-Dec-05 22-Dec-05 INVALID

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

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

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

  25. Section Health Check Tips

  26. 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 • I add additional checks at least once a month – takes about 5 minutes to expand • Any time new condition ‘bites’ us, I add to health check

  27. 11i Health Check Tips: • Good place to store logic behind certain issues • Captures SQL logic used to find data and reason why we care • Acts as repository of Apps exception conditions – better than using own memory to remember 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) • Consider including ‘as time allows’ reminders • We have more tasks than can get to so include queries that remind of outstanding issues (e.g. GL Consolidation tables)

  28. 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 our Change Control process since script modifies no data • Better to error on side of having too many checks than too few • Put less important queries near end of script • Must change my health check script to work at your site • 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

  29. Conclusion: • My health check SQL script available on NorCal OAUG Web site: http://www.norcaloaug.org/2006_training_day/TDpresentations.htm • Filename = ck_apps_health.sql • Many Apps Health Checks can be done automatically • If you don’t implement automated Health Checks • Modify my SQL script for your site and run manually • Review output - look for changes in 11i you want to make • For Example - Password Protect ‘Examine’ feature

  30. 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 so it seems ‘bleeding edge’ • Re-assign Fnd User to employee – caused all user’s responsibility assignments to be duplicated and corrupted • Be very cautious of direct updates to fnd_user table • Lots of Lock contention - Eventually crashed DB after killing lock holder

  31. Q and A: • General Questions: • Who is using DataGuard with 11i? • 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? • How often do you do routine maintenance of Concurrent Manager Tables? • What other 11i health checks do you run? • Who has implemented Project Resource Management?

More Related