Tuesday, March 10, 2015

EBS -- Health Check , Preparing a comprehensive Health check report

In this blog post, I will go through the checks which can be done while preparing a health check report for EBS environment.. 
I will review both the custom checks which we can declare ourselves and  the standard checks which are done by the Oracle provided tools for making comprehensive  health checks..

Lets start with taking a look to what we can done ourselves, I mean what we can control,  to collect information from the environment for preparing our healtchecks.. 
DB controls:

1) Is DB configured properly? Init.ora parameters, limits, sga, pga etc..
2) Is DB working properly? Any errors, check logs.
3) What about the DB performans? Awr analysis.
4) Check backups.. How to recover the database? Is the backup policy is consistent with the Recovery policy?
5) If there is a standby db in the environment, control the standby side fully.. Is dataguard configured properly?
6) How is the configuration of Database Server's OS? Any problems in the resources? Memory,IO and CPU.. Rpm's , and kernel checks..
7) Any errors in the Database server's OS logs?
8) Are te password of Db users weak? Any password policy is in use? Are there any public synonyms or public grants? What about security in the db level? Any default passwords or users? Any unneeded db schemas present?
9) Check Db space requirements? Any tbs or mount reaching the limits? Are the datafiles autoextend? Are there space available to support an increase in the database size?
10) Check the scheduled jobs? Both cron and jobs inside the rdbms? What are their frequency and costs?
11) Are the stats gathered with regular intervals?
12) What are the top sqls? What are the sqls need tuning and what are their module information?
13) What is the database version and patchset? What about the latest PSU ?

APPLICATION(EBS) controls:

1) Are Apps web server , form servers, oacore and concurrent managers configured properly?
2) Are the connection configuratons of EBS components configured as they need to be? (RAC tns configurations, application twotask configuration and so on..)
3) Any errors or warning in the application log files?
4) Are the java configurations in place ? (in order to support the application load..)
5) Are the stats jobs scheled, are the purging scheduled?
6) What are the scheduled jobs, concurrent programs and alerts ? What is the load that they bring..?
7) Is the notification mailers parameters configured properly? Any errors in the ntf mailer logs?
8) Check the alert configurations
9) Check the workflow configurations
10) What about the EBS Application user's password policy? check the security configuration of the system.
11)  How it the performance of the EBS Application Server?
12)  Are there space available in the application filesystem to support an increase in the application size?
13) Check backups.. How to restore this application server? Is the backup policy is consistent with the Recovery policy?
14)  If standby is used in this environment? Are the application filesystems syncronized with the Standby application filesystems? Is the configuration appropriate with the switchover and failover scenarios?
15)  Check the patch levels.. What is the EBS version? What about support dates? Is the latest CPU patch patch applied? Are the latest product delta patches applied?
16)  Check the configuration of Application Server's OS .. Are there any resource problems based on the Memory , IO and CPU ?
17)  Check the OS logs of Application Server..Any errors or warnings? Are the kernel parameters ok?

Okay.. This list above consists of several checks as you see.. They may appear with one per line, but actually the things mentioned there are not so easy to check.. It takes time and effort..
Anyways;
Besides from the controls above, Oracle provides us some tools for collecting information for

Like CP analyzer..
So, you can get benefit from Cp analyzer (Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)).. 
CP analyzer is a great tool to analyze Concurrent Processing environment in EBS instances.. It gives you a detailed and clear picture in HTML format.. Thus, you gather almost all the information you need to investigate the distinct problem causes in Concurrent Processing environment, without struggling with OS commands or complicated sql statements..
















I strongly recommend to use CP analyzer in making Health Checks for EBS environments.

CP analyzer is available via the following Oracle Doc:
Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)

CP analyzer can be used in any EBS envrionment with version 11.5.10.2 to 12.2.3 [Release 11.5 to 12.2]

It does the following analysis and provide an
HTML for you to view those analysis;

Immediate Analysis and Output of Concurrent Environment
E-Business Applications Concurrent Processing Analyzer Overview
Total Purge Eligible Records in FND_CONCURRENT_REQUESTS
E-Business Suite Version
Concurrent Processing Database Parameter Settings
Applied ATG Patches
Known 1-Off Patches on top of Rollups
E-Business Applications Concurrent Request Analysis
Long Running Reports During Business Hours
Elapsed Time History of Concurrent Requests
Requests Currently Running on a System
FND_CONCURRENT_REQUESTS Totals
Running Requests
Total Pending Requests by Status Code
Count Pending Regularly Scheduled/Non Regularly-Scheduled Requests
Count of Pending Requests on Hold/Not on Hold
Listing of Scheduled Requests
Listing of Pending Requests on Hold
Listing of Pending Requests Not on Hold
Volume of Daily Concurrent Requests for Last Month
Identify/Resolve the Pending/Standby Issue, if Caused by Run Alone Flag
Tablespace Statistics for the fnd_concurrent tables
E-Business Applications Concurrent Manager Analysis
Concurrent Managers Active and Enabled
Concurrent Manager Processes by Workshift
Active Manager for Applications that are not Installed/Used
Total Target Processes for Request Managers (Excluding Off-Hours)
Request Managers with Incorrect Cache Size
Concurrent Manager Request Summary by Manager
Check Manager Queues for Pending Requests
Check the Configuration of OPP
Identifies Concurrent System Setup and configurations
Identifies and recommends Concurrent Best Practices
Easy to add Tool for regular Concurrent Maintenance
Execute Analysis anytime to compare trending from past outputs
There is also a tool called ORAChk, which replaces popular Rac Check tool.. ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.2)

ORAChk can scan the following areas:

-Oracle Database
Standalone Database
Grid Infrastructure & RAC
Maximum Availability Architecture (MAA) Validation
Upgrade Readiness Validation
Golden Gate
-Enterprise Manager Cloud Control (12c only)
Repository
Agents
-E-Business Suite
Oracle Payables (R12 only)
Oracle Workflow
Oracle Purchasing (R12 only)
Oracle Order Management (R12 only)
Oracle Process Manufacturing (R12 only)
-Oracle Hardware Systems
Oracle Solaris
Here is an example run of ORAChk.. It just needs to read the central inventory, if it cant find the inventory you can pass the inventory location using RAT_INV_LOC environment variable:
Note that: If your environment is 32 bit, you need to pass -ebs32bit argument too..
Running it interactively is very simple; just download orachk.zip, unzip orachk.zip and run ./orachk using rdbms software owner..
Here is an example report:
Oracle orachk Assessment Report

System Health Score is 88 out of 100 (detail)

Summary

OS/Kernel VersionLINUX X86 OELRHEL 5 2.6.18-194.el5
DB Home - Version - Names/r12/d01/oracle/PROD/db/tech_st/11.1.0 - 11.1.0.7.0 - PROD
Database Serverermanhost
orachk Version12.1.0.2.3_20150305
Collectionorachk_ermanhost_PROD_031015_105413.zip
Duration9 mins, 53 seconds
Executed byoracle
Collection Date10-Mar-2015 10:54:34

Note! This version of orachk is considered valid for 115 days from today or until a new version is available

Table of Contents

  • Findings Needing Attention
    • On Database Server
  • Maximum Availability Architecture (MAA) Scorecard
  • Findings needing further review
  • E-Business Suite
  • Findings Passed
    • On Database Server
  • RDBMS patch recommendation Summary report
  • RDBMS patch recommendation Detailed report
  • Top 10 Time Consuming Checks
As you see above there are numerous contents in the OraChk report..
Lets take a look at the E-Business Suite section of the report and see what do we have there..

E-Business Suite

StatusTypeMessageStatus OnDetails
FAILSQL CheckOne or more Workflow email notification preferences are disabledAll DatabasesView
FAILSQL CheckOne or more Workflow Error Items foundAll DatabasesView
FAILSQL CheckOne or more Supplier site missing service_tolerance_id after upgrade from 11i.All DatabasesView
WARNINGSQL CheckOutput post processor (OPP) heap size settings are probably too low.All DatabasesView
INFOSQL CheckReview E-Business Suite Best PracticesAll DatabasesView
PASSSQL CheckThe Java Mailer is enabled, and does not appear to have any corruptionAll DatabasesView
PASSSQL CheckThe Workflow Java Mailer Service is enabled and runningAll DatabasesView
PASSSQL CheckNo major Workflow Looping Items foundAll DatabasesView
PASSSQL CheckNo Workflow stuck activities foundAll DatabasesView
PASSSQL CheckNo invoices with out of sync accounting dates were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect payment selection status were foundAll DatabasesView
PASSSQL CheckNo invoices with out of sync accounting dates were foundAll DatabasesView
PASSSQL CheckNo orphan accounting events were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect payment selection status were foundAll DatabasesView
PASSSQL CheckNo invoices with incompletely discarded lines were foundAll DatabasesView
PASSSQL CheckNo common invoice data integrity issues were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect data in payment schedules or payment fields foundAll DatabasesView
PASSSQL CheckNo common invoice data integrity issues were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect data in payment schedules or payment fields foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect data in payment schedules or payment fields foundAll DatabasesView
PASSSQL CheckNo invoices with multiple transaction entity records in SLA were foundAll DatabasesView
PASSSQL CheckNo unaccounted events with event dates in closed periods were foundAll DatabasesView
PASSSQL CheckNo invoices with issues in amounts or base amounts were foundAll DatabasesView
PASSSQL CheckNo common invoice data integrity issues were foundAll DatabasesView
PASSSQL CheckNo invoices with different amounts in ap_invoice_distributions and xla_distribution_links were found for PRODAll DatabasesView
PASSSQL CheckNo payment records with missing accounting events were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect payment selection status were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect data in payment schedules or payment fields foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect data in payment schedules or payment fields foundAll DatabasesView
PASSSQL CheckNo checks with multiple transaction entity records in SLA were foundAll DatabasesView
PASSSQL CheckNo invoices with different amounts in ap_invoice_distributions and xla_distribution_links were found for PRODAll DatabasesView
PASSSQL CheckNo invoices with issues in amounts or base amounts were foundAll DatabasesView
PASSSQL CheckNo invoices with incorrect payment selection status were foundAll DatabasesView
PASSSQL CheckNo invoices with different amounts in ap_invoice_distributions and xla_distribution_links were found for PRODAll DatabasesView
PASSSQL CheckNo orphan accounting events were foundAll DatabasesView
PASSSQL CheckNo orphan accounting events were foundAll DatabasesView
PASSSQL CheckNo inactive vendor sites which would impact 1099 reporting were foundAll DatabasesView
PASSSQL CheckNo invoices with reversed distributions having a null amount were foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo unmatched purchase orders with invoice related amounts or quantities were foundAll DatabasesView
PASSSQL CheckNo canceled or discarded lines with non-zero invoiced quantity were foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions in functional currency with different values for amount and base_amount were foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo invalid prepay application data foundAll DatabasesView
PASSSQL CheckNo duplicate bank account records foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo common PO/Receipt matching data mismatches foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo unaccounted invoice distributions with null value for both cash and accrual posted flags were foundAll DatabasesView
PASSSQL CheckNo duplicate external payee records found in iby_external_payees_allAll DatabasesView
PASSSQL CheckNo invoices with cancellation issues foundAll DatabasesView
PASSSQL CheckNo matched PO's with invoiced quantity or amount discrepancies between the shipments and the distributions were foundAll DatabasesView
PASSSQL CheckNo discarded invoice lines with active distributions were foundAll DatabasesView
PASSSQL CheckNo upgraded invoices with null party_id and party_site_id were foundAll DatabasesView
PASSSQL CheckNo expense reports with incorrect value (Y) for the generate_dists flag were foundAll DatabasesView
PASSSQL CheckNo invoices with org_id that does not match the batch org_id were foundAll DatabasesView
PASSSQL CheckNo upgraded invoices with null invoice_type_lookup_code were foundAll DatabasesView
PASSSQL CheckNo invoices with default code combination ID equal to -1 were foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo automatic withholding tax (AWT) distributions with missing AWT_RELATED_ID foundAll DatabasesView
PASSSQL CheckNo invoices with unknown characters were foundAll DatabasesView
PASSSQL CheckNo reversing invoice distribution pairs which are not encumbered but which have BC_EVENT_ID populated were found.All DatabasesView
PASSSQL CheckNo payments with out of synch check_id values between ap_checks_all and ap_invoice_payments_all were found.All DatabasesView
PASSSQL CheckNo prepayment applications were found with incorrect accounted amounts due to incorrect final payment rounding.All DatabasesView
PASSSQL CheckNo payment clearing accounting events were found where related payment events are not correctly prorated across all invoice distributions.All DatabasesView
PASSSQL CheckNo functional currency invoices with BASE_AMOUNT populated were found.All DatabasesView
PASSSQL CheckNo checks with incorrect accounted amounts found.All DatabasesView
PASSSQL CheckNo invoices with posting status out of synch with the corresponding accounting events were foundAll DatabasesView
PASSSQL CheckNo prepayment applications were found with accounted amounts which are multiples of the actual prepayment application amount.All DatabasesView
PASSSQL CheckNo orphan invoice distributions with no line foundAll DatabasesView
PASSSQL CheckNo upgraded invoices found with missing records in XLA_DISTRIBUTION_LINKS foundAll DatabasesView
PASSSQL CheckNo payments with out of sync accounting dates foundAll DatabasesView
PASSSQL CheckNo payments with posting status out of synch with the corresponding accounting events were foundAll DatabasesView
PASSSQL CheckNo receiving distributions out of synch with AP distributions for quantity or amount billed foundAll DatabasesView
PASSSQL CheckNo purchase order shipments with quantity or amount data which does not match the values on the distributions foundAll DatabasesView
PASSSQL CheckNo unaccounted accounting events having event dates in closed periods foundAll DatabasesView
PASSSQL CheckNo invoices with validation_request_id populated after invoice validation has completed with error foundAll DatabasesView
PASSSQL CheckNo missing records found in XLA_DISTRIBUTION_LINKS for migrated paymentsAll DatabasesView
PASSSQL CheckNo historical upgraded events missing records foundAll DatabasesView
PASSSQL CheckNo Incomplete Accounting Events foundAll DatabasesView
PASSSQL CheckNo approved distributions containing unprocessed budgetary control events foundAll DatabasesView
PASSSQL CheckNo missing payment history record found for accounted checksAll DatabasesView
PASSSQL CheckNo Suppliers missing individual_1099/tax_payer_id after upgrade from 11i.All DatabasesView
PASSSQL CheckNo unclearing/maturity reversal events found that not getting accounted for voided paymentsAll DatabasesView
PASSSQL CheckNo Orphan checks foundAll DatabasesView
PASSSQL CheckNo unaccounted events foundAll DatabasesView
PASSSQL CheckNo amounts or base amounts are out of synch between Payables and EBtaxAll DatabasesView
PASSSQL CheckNo payments found in a SPOILED status which would leave PPR in Formatting statusAll DatabasesView
PASSSQL CheckNo Refund checks found with maturity rate information populatedAll DatabasesView
PASSSQL CheckNo Voided checks found with incorrect statuses in ap_checks_allAll DatabasesView
PASSSQL CheckNo Incomplete Accounting Events foundAll DatabasesView
PASSSQL CheckNo checks with exchange rate information populated in AP Views FoundAll DatabasesView
PASSSQL CheckNo operating units with missing system parameter values foundAll DatabasesView
PASSSQL CheckNo supplier contacts with missing job titles foundAll DatabasesView
PASSSQL CheckNo invoices with invalid liability accounts foundAll DatabasesView
PASSSQL CheckNo partially cancelled invoices foundAll DatabasesView
PASSSQL CheckNo Payables application accounting definitions (AAD) were found with a status of "Invalid", "Not Validated", or "Validating".All DatabasesView
PASSSQL CheckNo orphan Cash Management bank accounts without assignments were found.All DatabasesView
PASSSQL CheckThe output post processor (OPP) timeout settings are appropriate.All DatabasesView
PASSSQL CheckNo duplicate primary account owners were found.All DatabasesView
PASSSQL CheckThe java heap space execution setting for Payments concurrent programs is set appropriately.All DatabasesView
PASSSQL CheckNo orphan bank accounts with no owner were found.All DatabasesView
PASSSQL CheckNo orphan Receivables bank accounts with no assignments were foundAll DatabasesView

Okay.. We have functional checks related to EBS in this section..
Ofcourse, there are OS , database and performance related checks in the report (see Table of Contents section above) , but I m interested in the EBS findings because Db,Os and performance specific controls can be done by us using the checks in DB controls and App Controls that I have mentioned above, anyways..

So in conclusion; we can say that a comprehensive report for an EBS environment may be prepared by combining the findings in :

  • APPS Controls(Mentioned in this document)
  • DB Controls(mentioned in this document)
  • Cp Analyzer Report
  • ORAChk Report 

2 comments :

  1. Great post. The information given here, is very useful.

    ReplyDelete
  2. eToro is the most recommended forex trading platform for novice and advanced traders.

    ReplyDelete