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 performance? 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 Version	LINUX 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 Server	ermanhost
orachk Version	12.1.0.2.3_20150305
Collection	orachk_ermanhost_PROD_031015_105413.zip
Duration	9 mins, 53 seconds
Executed by	oracle
Collection Date	10-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

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

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