Mark CraigGroup Manager Oracle Workflow
Oracle WorkflowSystem Management Hints and Tips
Important Patches • OWF H Patchset • Metalink Note 258312.1 • Released November 4 • OWF G Rollup 7: 3868138 • Simplified prereqs • Released November 17
Agenda • Is everything up and running? • Are the Agent Listeners falling behind? • Why did the Mailer go down? • Why didn’t I receive my email? • When can I drop and recreate workflow queues? • When can I “skip” errored workflow activities? • What’s the latest on the Workflow Directory Service? • Any news on cloning?
Oracle Workflow Manager • Dashboard for System Administrators and DBAs • Component of Oracle Applications Manager • Accessible from Workflow Administrator resp • Track Workflow System Status • Manage Workflow Background Processes • Manage Notification Mailer • Drill Down Capability
Oracle Workflow Manager • Unified Logging • Diagnostics • Notification User Preferences • GSM Setup • Mailer Component Test • Mailer Component Parameter Test • Mailer Diagnostic Test
Workflow Service Instances • There are two workflow service containers which must be running:
Workflow Manager: Metrics • Access metrics either via graph or Related Links: Throughput:Work Items(View)
Drill Down on an Item Type • Error Count per activity per item type
Drill Down per Activity Stage • Launch Monitor, Abort (All), Retry (All)
Workflow Agent Listeners • Business Event System requires agent listeners to be run to process event messages • An agent listener monitors incoming messages and dequeues messages • When an event message is dequeued, the Event Manager begins subscription processing for the event. • Oracle Workflow provides 3 seeded agent listener service components to process messages: • Workflow Deferred Agent Listener (WF_DEFERRED) • Workflow Error Agent Listener (WF_ERROR) • Workflow Inbound Notifications Agent Listener (WF_NOTIFICATION_IN)
Workflow Agent Listeners • Workflow Deferred /Java DeferredAgent Listener • for deferred subscription processing (Local subscription) • Can run multiple Workflow Agent Listeners on WF_DEFERRED • MUST BE RUNNING • Workflow Error/Java Error Agent Listener • for error handling (Error subscription) • MUST BE RUNNING • Workflow Inbound Notifications Agent Listener • inbound agent for e–mail notification responses (External subscription) • can run multiple Workflow Agent Listeners on WF_NOTIFICATION_IN
Tip: Custom SQL • Example SQL to check status of messages select count(*) from applsys.aq$wf_deferred where msg_state = ‘READY’ select count(*) from applsys.aq$wf_notification_out where msg_state = ‘READY’ • (example only) • Queue message states also available from wfver.sql
Tip: Custom SQL • Simple custom SQL to check status of service components select 'Service component ' || COMPONENT_NAME || decode (COMPONENT_STATUS, 'RUNNING', ' is running', ' is not running') from fnd_svc_components where concurrent_queue_id = (select concurrent_queue_id from fnd_concurrent_queues where concurrent_queue_name = 'WFALSNRSVC') order by COMPONENT_TYPE, COMPONENT_ID • (example only) • Also available from wfver.sql
Oracle Workflow Java Mailer • Based on standard Java Mail APIs • SMTP for outbound • Optional IMAP for inbound OR users respond via hyperlink included in email • One Workflow Mailer for detail and summary emails • Leverages Generic Service Management feature to manage JVM • Leverages Generic Service Components feature to monitor java threads
What type of errors? • Not configured correctly • See Workflow Manager Online Help • Not configured when system was started • Complete configuration • Run • (for owf g, run wfntfqup.sql after contacting support) • Invalid Email Addresses • mark.craig instead or email@example.com
What type of errors? • SMTP Server uncontactable • UNIX SA doesn’t talk to Apps DBA • Content Generation errors • Uncompiled Packages • Oracle/Java Errors • IMAP Server uncontactable • Mail Admin doesn’t talk to Apps DBA • Unexpected Errors: Oracle/Java • Unsolicited Emails
Mailer Status says System Deactivated • Maximum error count for Mailer is 10 (default) • Service Component Level • MAX_ERROR_COUNT=10 • 10 consecutive fatal errors = Stopped with Error • Maximum restart count is 5 (default) • Service Instance Level • SVC_COMP_MAX_ERROR_COUNT=5 • 5 sets of 10 consecutive errors = System Deactivated
Workflow Service Components • Workflow Notification Mailer service components • use for inbound and outbound mail processing • can run multiple outbound thread for scalability. • can only run one inbound thread • Workflow Agent Listener service components • to perform deferred subscription processing • error handling for the Business Event System • inbound e–mail processing for notification mailers
Possible Causes • Email traffic – be patient • Check Notification Preference • “Do not send mail” • “Summary” • Email Address • Not set • Wrong • Invalid
Troubleshooting • Send a Test Email to that Role using Workflow Manager, note down the NID. • wfmlrdbg.sql: for a given NID: • Notification Status and Mail Status • Role Details: preference and email address • Existence/Status of Messages on Queues • Depends on Queue Retention Time (processed messages may have already been purged), default retention is zero • WF_DEFERRED • WF_ERROR • WF_NOTIFICATION_OUT • WF_NOTIFICATION_IN
Troubleshooting – wfmlrdbg.sql Notification User Comments Deferred Queue Status Error Queue Status Notification OUT Queue Status Notification IN Queue Status Message Templates Generate Notification Message GSC Mailer Component Parameters Mailer Tags Notification Item Information Notification Recipient Role Members Notification Recipient Role Information Notification Recipient Routing Rules Notification More Info Role Information Notification Message Attribute Values Notification Attribute Values
Troubleshooting • Metalink Note 242941.1 • How to Troubleshoot Java-based Workflow Notification Mailer in 11.5.9 and OWF G
#5 The Agent Listeners are running slowly, should I drop and recreate the queues?
Dropping Queues • No!!! • Not supported unless you are using a SQL script provided by Oracle Workflow Development • Not all Queues can be recreated without loss of data • WF_ERROR • WF_JAVA_ERROR • WF_DEFERRED • WF_JAVA_DEFERRED • WF_NOTIFICATION_IN
Investigate Performance Issues • Number of messages on queue (wfver.sql) • Run more Agent Listeners • OK to run in parallel • Review STATSPACK Reports • Review DB File I/O • Temporary Tablespace used heavily for LOB Operations
#6My Order Management workflow has errored….can I just skip that activity?
Skipping Activities Not Supported • Workflows are business processes • For a product to function properly, all activities within the workflow process need to be executed, for example: • Activity 1 – creates business document • Activity 2 – inserts record into table • Activity 3-6 update other tables • Activity 7 – updates record created by activity 2 • If Activity 2 errors and you “skip” it, Activity 7 will error, but activity 3-6 have already been executed……data is corrupted!!!
Workflow Directory Service v4 • Patch 3942438 • Additional Bug Fixes • Enhancements to support Role Based Access Control • Role Hierarchies • Included in OWF H and AOL Data Security Rollups • And any product which prereqs those patches • Bulk Synchronization disabled for partitions which are hierarchy enabled
OWF H + OAM H • Automated Cloning • No manual steps required • Global Preferences, Instance specific runtime data updated to be correct for target instance • More details • Oracle Applications Manager Online Help • Oracle Applications System Administrator Guide
Future Directions • Basic vs Advanced • Keeping the Mailer Up in unfriendly environments • SMTP Server not contactable • IMAP Server not contactable • Many Email Addresses wrong or invalid • Integration with System Alerts feature of OAM • Official hooks to get status of service components for 3rd party management tools eg. Database view • Enterprise Manager Integration • Raise alerts based on queue backlog • Override Address vs Test Mail address
Q & Q U E S T I O N S A N S W E R S A