Oracle EBS Production SupportRecommendations

• One should implement Oracle monitoring tools/utilities like “Oracle Enterprise Manager with performance tuning packs” for Oracle database management and performance enhancements.

• There is a need to have strong process in place for monitoring critical processes for error handling and resolutions at application, database and system level.

• Daily checklist needs to be maintained and followed religiously on regular basis, hard copy of the same should be maintained on daily basis, which will help in adopting proactive approach and better decision making with complete check on the system.

• The detailed configuration setup is not documented and available for reference, this will lead to longer downtime in case of any hardware failure/reconfiguration requirements.

• Strong password management policy should be in place for system and oracle database password.• The operational best practices in partner with service management are fundamental to avoiding,

minimizing and reacting to operational outages as well as reducing the time to recover from an outage, i.e., the MTTR.

Monitoring tools, Best practices & Support procedure

Contd.

• Best practices for maintaining High availability are divided into the following categories: – Service level management – Change management – Backup, restore, and recovery plans

• Create recovery plans • Validate backups periodically • Automate backup, restore, and recovery procedures • Choose correct backup frequency • Maintain updated documentation for backup and recovery plans

– Scheduled outages planning – Staff training – Documentation – Ensure documentation is kept up to date

• Approve documentation changes through change management process • Document lessons learned and problem resolutions • Protect the documentation

Monitoring tools, Best practices & Support procedure

Contd .

• Following reports for all systems should be maintained by DBA on weekly basis– Server Availability and Utilization Report (For CPU and Memory)– Server Uptime Report– Call Analysis Report– Severity wise Call Report– Disk space monitoring report– su log, access of the system as root user– Key Performance statistics on CPU, memory utilization and I/O– User id creation, deletion, profile change report– Exception Report if any should be sent to vendors

• One should also run the OS utilities and tools to gather I/O and system statistics this will help in identifying bottlenecks and proactive approach

Monitoring tools, Best practices & Support procedure

Generic Policies

• The passwords of key users of Oracle Applications like System and Apps has to be changed. It was observed that APPS schema password is kept to default. It is highly recommended to change to the APPS schema password using FNDCPASS utility.

• Run Gather Schema Statistics Concurrent request Periodically• Run Purge Workflow Tables Concurrent Request Periodically• Run Purge Concurrent Table concurrent Request Periodically

Concurrent Manager Best Practices

• As a general practice, it is recommended to configure high load and low load Concurrent Manager for long slow jobs & short fast jobs. Set up Concurrent Request Types (High Load/Low Load) and use these in specialization rules to force specific concurrent programs to the relevant manager.– –< 5min runtime = Low Load– –>1 hour runtime = High Load

• Additional Configuration Option– Set up work shifts for High Load manager so that it has few or zero processes running during the business day, and more

processes running ‘overnight’. Heavy (long run) jobs are captured and trickle through or only run overnight.– Create Premium manager(s) to accept highly critical recurring concurrent jobs. By this you could prevent the blocking of

critical jobs on High/Low/Standard managers.

• Cache Size - The Cache Size is the number of concurrent requests that the manager picks up from the FND_CONCURRENT_REQEUSTS table when the manager wakes up. The really interesting thing about this parameter is that if the manager is busy processing work, it doesn't enter a sleep cycle and then it doesn't poll for work. It is recommended to set & increase the cache size for all the managers

Contd .

Concurrent Manager Best Practices

• Sleep Seconds– Sleep time is the number of seconds your manager waits between checking the list of pending concurrent requests (concurrent

requests waiting to be started). If you have a very large number for rows in FND_CONCURRENT_REQUESTS this poses a burden on the system as the system constantly polls for new work to do.

– Work shifts should be defined for moving and distributing the batch-processing load to non-peak hours. Intensive processing activities should not be carried out during the day (business hours); this can be implemented by restricting concurrent managers.

• Workflow Data (Purging obsolete data) – Important WORKFLOW tables whose size has to be checked before going for a purge:

• select count(*) from WF_ITEM_ATTRIBUTE_VALUES;• select count(*) from WF_ITEM_ACTIVITY_STATUSES;• select count(*) from WF_NOTIFICATION_ATTRIBUTES;

– Following approach can be followed to purging Workflow Runtime data. The persistence type of an item type controls when Oracle Workflow purges runtime status information for work items.

– The persistence values are:* Temporary: Item will be deleted in 'n' days.* Permanent: Item will be deleted only when forced.

Note: • For a work item to be considered eligible for purging, all activities must be complete. This includes FYI notifications, which must be closed.• The following steps combined to form one approach that has been used with a fairly good success rate. It is by no means the only method.• The script bde_wf_data.sql available in Note 165316.1. (Available from metalink.oracle.com) This queries all data that is considered to be eligible for purging. This is a useful and

highly recommended script.Contd .

Concurrent Manager Best Practices

• Submit the Purge Obsolete Workflow Runtime Data concurrent request (FNDWFPR) – You can do this at any time. This is the first thing to do, as it will purge any and all data related to Workflow processes that have

terminated normally. If process data does not get purged using this method, then move on to the next step.– Use the standard concurrent program FNDWFPR ”Purge Obsolete Workflow Runtime Data” to purge old data from the Oracle Workflow

runtime tables regularly. – You can supply the following parameters for the ”Purge Obsolete Workflow Runtime Data” concurrent program:

• Item Type — The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.• Item Key — The item key to purge. Leaving this field blank defaults to purging the runtime data for all item keys.• Age — Minimum age of data to purge, in days.

• Run wfstat.sql– From within SQL, run the wfstat.sql script to list any particular process item type that is not being purged. – If it returns a status of 'ACTIVE' in the WF_ITEM_ACTIVITY_STATUSES table, yet the WF_NOTIFICATIONS table shows the status for this

item as being 'CLOSED', then there may be a problem with how the process was executed.– The process may have never run to completion for whatever reason, or it may have terminated abnormally. If only a few are not being

purged, try updating the table status, or move on to the next step.

• Manually update the affected table– Updating the table manually is usually not usually recommended. We recommend opening a SR with Oracle support for resolving the

issue. It is also advisable that you backup the table concerned beforehand. – This method should only be used after proper backup and if you know for a fact that all Workflow processes that you know of, have run

to completion. Contd .

Concurrent Manager Best Practices

• Bug Fixes / Patch Promotions Policy – As standard and best practices for patch management it is recommended to have three separate environments for promoting any

patch on production system (application/database/OS). One should have a policy in place to apply patch for the key application system on the three environments so that there is no impact on the production system uptime/availability. The patches should be applied in following order :• Development/User acceptance test (DEV)• Pre-production (TEST)• Production (PROD)

• Archival of Log & Output Files– Log files are generated for the following components:

• RDBMS (Oracle Database)• Concurrent Manager• Administration utilities (like adpatch, adconfig, adctrl, adadmin, and so on)• Application services startup and shutdown• Backup logs

– The log files are very critical as they provide valuable information on the success/failure of the jobs at the same time they consume lot of precious disk space. The retention policy for log files is purely a business decision. One should have a proper policy in place for retention of the log and output files. These files may be used/requested by support analyst for application/database diagnosis and troubleshooting.

– The retention period for these should be reviewed every 6 months due to new business requirements and configuration changes happening on the system.