How to Perform a Healthcheck on the E-biz Suite

How to Perform a Healthcheck on the E-biz Suite


A complete Oracle health check certifies every aspect of your Oracle database including disk, network, server environment and over 200 individual Oracle health metrics.


Check Web Server is Up: Check in browser http://hostname.domainname:port where hostname.domain name is Machine Name on which Web Server(OHS) is running. If you are using load balancer or Proxy Server in front of Web Server it will be loadbalancer / Proxy Server Name. Port is port on which Web Server is Listening. It is defined in $IAS_ORACLE_HOME /Apache /Apache /conf /httpd.conf. If you can’t access this check error_log at $IAS_ORACLE_HOME /Apache /Apache/ logs

Check Jserv is Up: Check if you can access OAM (Oracle Application Manager) page. http://hostname.domainname:port_number/servlets/weboam/oam/oamLogin If you can’t access login screen for OAM, check logs at

Check mod_pls is working fine( for 11i only) : http://hostname.domainname:port/pls/$SID so if you installed vision instance with SID as VIS11I your SID here is VIS11II this sometime in documents also called as TWO_TASK. You should see output from procedure FND_WEB.PING. If not check that -
A) database listener is Up
B) GUEST User is not end dated & password is correct.
C) dbc (database connect descriptor) file is corerct at $FND_TOP/secure
D) apps password is correct in wdbsvr.app at $IAS_ORACLE_HOME/Apache/modplsql/cfg

Check Aoljtest for IAS Availability :
Verify the Application Tier Node Configurations using aoljtest.jsp

(http://[hostname.domainname]:[port]/OA_HTML/jsp/fnd/aoljtest.jsp)


Login to Application: Login to application with Application User to confirm login is working fine , if not check mod_pls for any issues.

Check the apache service is up or not using
$IAS_ORACLE_HOME/Apache/Apache/logs/httpd_pls.pid

this contains the pid of the http process handling the pl/sql requests


Check Form Server is working : Click on any Core Responsibility like “System Administrator”, If you are facing any problem opening form Applet
A) Check jinitiator is installed, if not do you have access to install jinitiator
B) Try clearing cache from jinitiator
C) Check forms server parameters are configured properly in appsweb_$CONTEXT_NAME.cfg
D) Check if form server listener is Up : netstat -an | grep form_server_port
Check Concurrent Manager is Up & Running: Select “Administer Concurrent Manager” & check if “Actual” & “Target” are same. If not check $SID_MM_DD.mgr at $APPLCSF/$APPLLOG .

Check Reports can display properly : Change “Active Users” reports output from “Text file” to “PDF” from Define Concurrent Request Screen. Submit Active Users request & click on Output, check if you can se report in PDF format. If you encounter error like REP-3000 Oracle Toolkit, issue is with DISPLAY,

Check Apps Listener is Up: Apps listener is different from database listener, Apps Listener is used for various purpose one is to check output & log files in concurrent requests. Check if apps listener is fine by clicking on log file & output file for Active Users request you submitted in Step 7 on previous page. If you encounter any issues while trying to access Log & Output file issue might be with Oracle Apps Listener. Check if adalnctl.sh (where ad is apps dba, aln is apps listener and ctl is control) on application tier started successfully. If yes then check this query (select logfile_name, logfile_node_name, outfile_name, outfile_node_name from fnd_concurrent_requests where request_id=[your request id number].

Check if Workflow is Up: Login to OAM (Oracle Application Manager). After login, On top right of page from “Nevigate to” select “Workflow Manager” from drop down menu and click on GO button. Check “Notification Mailers” is Up.

Check if Discoverer 4i is Up: There are two type of discoverer 4i
Discoverer Viewer – Check http://yourhostname: port/discoverer4i/viewer
Discoverer Plus

Check http://yourhostname: port/discwb4/html/english/welcome.htm

Custom applications: Any custom application configured with oracle apps R12, OEM any other monitoring software. We should have complete details of each and every customization.

Oracle application has a health check script called RDA (check it out in oracle metalink note: 250262.1)

General Health Checks:
· Invalid Objects

· General DB

· User Load Checks

· Application Users

· Profiles

· Module Specific


R12 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)

++++++++++++++++++++++++++++++++++++++++++++++++
++ Warning Section:

++++++++++++++++++++++++++++++++++++++++++++++++

Confirm not in Maintenance Mode

User Security AND Access Issues section:

Show any logins whose password is NOT set to expire in 90 days

Non-DBAs who have System Administrator or Applic Develop Responsibility

Terminated employees who still have an active login to Oracle Apps

USER_NAME TERM_DATE

——————– ——————–

Zhann 06-Jan-09

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


Non-DBAs/Sys Admins who have System Administrator responsibility

USER_NAME END_DATE TODAY RESPONSIBILITY

——— ——– ——— ————————————–

Ajay 14-MAR-09 System Administrator


Terminated employees with active FND login account

USER_NAME TERM_DATE

——————– —————-

AMIT 06-Jan-09



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


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-04 13-JAN-19



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

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

Tablespace Total MB Used MB Avail MB Percent Free

———— ——— ——— ——— ———–

APPS_UNDOTS1 3,518. 00 389.19 3,128.81 88.94



General Health Checks
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



General RDBMS Checks
Show count of failed DB logins (this does not include failed R12 logins)

Note: Popular with auditors so better take care of this


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



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


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



Profile General Checks : We should check Site level Profiles changed in last 30 days ( we should have a complete detail and history of eac profile changed)

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


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




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


R12 Collecting Configuration Information for HTTP,OC4j and OPMN (take the backup every week of these files mentioned below in case if you require to sort out any issue)

-Do the following on all middle tiers as the Application tier user


zip -r /tmp/’uname -n’-'date + %m%d%y,%H%M’_iAS_CONFIG.zip \

$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf\

$ORA_CONFIG_HOME/10.1.3/config\

$INST_TOP/pids/10.1.3/config\

$ORA_CONFIG_HOME/10.1.3/j2ee/\

$ORA_CONFIG_HOME/10.1.3/javacache/admin \

$ORA_CONFIG_HOME/10.1.3/network/admin \

$ORA_CONFIG_HOME/10.1.3/opmn