Friday, January 8, 2010

Spurious entries are seen in the concurrent manager tables

.mgr log shows the following error - CONC-SM TNS FAIL, yet all concurrent requests complete successfully without any error.Ping to FNDSM_node_sid entry is successful
No other problems are being experienced
The cause of this problem is undetermined.
To implement the solution, please execute the following steps:1. Run the CMCLEAN.sql script to safely clean up the concurrent manager tables.
N.B. The script can be obtained from Metalink
Note 134007.1 - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

Tuesday, January 5, 2010

Oracle Workflow Release 12 Diagnostics

Ganeric Workflow Data Collection
Run the script from
Note 275379.1 to determine your current R12/ATG version.
Spool, run and upload the output of $FND_TOP/sql/wfver.sql from your Concurrent Manager Server that is running the Workflow Services.
Workflow Mailer
The Workflow Mailer is a key component of E-Business (EBS). The ability to send emails of many types to customers notifying them of key pieces of information is highly appreciated by all stakeholders that interact with EBS. The Workflow Mailer uses SMTP, IMAP and HTTP protocols for processing notifications by email. Run the Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test from
Note 748421.1 to do an initial data collection of your Workflow Mailer configuration.
Workflow has been enhanced to provide more diagnostics for EBS Administrators to use to diagnose notification mailer failures as it uses the protocols. The Mailer.class file is provided to collect diagnostic information when the Workflow Mailer uses these protocols.
Command Line Diagnostics:
Testing IMAP Connectivity
source APPS_.env

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=.dbc \ -Dserver= \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount= -Dpassword= \ -DdebugMailSession=Y \
Testing default IMAP Port 143:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=$FND_SECURE/VIS.dbc \ \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount=orlncatst02 -Dpassword=orlncatst02 \ -DdebugMailSession=Y \

Testing default IMAPSSL Port 993:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=$FND_SECURE/VIS.dbc \-Dport=993 -Dssl=Y \ -Dtruststore=/home/applmgr/.keystore \ \ -Daccount=orlncatst02 -Dpassword=orlncatst02 \ -Dconnect_timeout=120 -Ddebug=Y \ -DdebugMailSession=Y

NOTE: Capture the output echo'd to the screen so you can upload it and upload the default "test.log" file the tests will create.
Testing SMTP Connectivity
source APPS_.env

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=.dbc \ -Dserver= \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount= \ -Dpassword= \ -DdebugMailSession=Y \
Testing default SMTP Port 25:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=$FND_SECURE/VIS.dbc \ \ -Dconnect_timeout=120 -Ddebug=Y \ -Daccount=NoSmtpUser -Dpassword=NoSmtpPasswd \ -DdebugMailSession=Y \

Testing SMTPSSL Port 465:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \ -Ddbcfile=$FND_SECURE/aoldev-pc_vis.dbc \ -Dport=465 -Dssl=Y \ -Dtruststore=/home/applmgr/.keystore \ \ -Daccount=NoSmtpUser -Dpassword=NoSmtpPasswd \ -Dconnect_timeout=120 -Ddebug=Y \ -DdebugMailSession=Y
NOTE: Capture the output echo'd to the screen so you can upload it and upload the default "test.log" file the tests will create.
Testing HTTP Connectivity for notifications with framework message attributes
NON-SSL TEST: Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification, the protocol (-Dhtp=http), and the dbc filename (-Ddbcfile).
==============Begin source APPS_.env

$AFJVAPRG -classpath /tmp:
${AF_CLASSPATH} -Dnid=841969 -Ddbcfile=$FND_TOP/secure/orlncatst-02_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=http -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true

Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification, the protocol (-Dhtp=https), the name of your and the dbc filename (-Ddbcfile).

source APPS_.env export LD_LIBRARY_PATH=${AF_LD_LIBRARY_PATH} $AFJVAPRG -classpath $AF_CLASSPATH -Dnid=841969 -Ddbcfile=$FND_TOP/secure/aoldev-pc_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=https -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true

Java Mailer Diagnostics for HTTP Framework Region Screen Output: Mailer : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started. URL {} Connection to application server was successful and retrieved 3392 characters plus 4 referenced objects. Time taken to establish the connection and obtain the content was 31.627 seconds Content ID File name and Content Type
========== ========================== 24537094 /OA_HTML/cabo/images/errorl.gif image/gif 5612344 /OA_HTML/cabo/images/cache/cmbte-1.gif image/gif 8344960 /OA_HTML/cabo/images/t.gif image/gif 16625677 /OA_HTML/cabo/images/cache/cmbbn-1.gif image/gif

NOTE: Upload the information echoed to your session and the -DAFLOG_FILENAME=mailerTest.log and "test.log" which contains quite a bit of Statement log level data.

Additional Diagnostics:
Increase the Workflow Mailer's Log Level to Statement in order to get additional diagnostic information for the Workflow Mailer whenever you see issues sending (SMTP) notifications by email and processing (IMAP) responses from the dedicated inbox assigned to the Workflow Mailer.

Changing the Workflow Mailer Log Level:
Login to E-Business as a user assigned the System Administrator responsibility and navigate:
System Administrator > Oracle Applications Manager > Workflow > Notification Mailers > Edit > Advanced
Reproduce the issue and review the Workflow Mailer Service log which is now being written to with the Log Level set to Statement. The log can be viewed in multiple locations from within E-Business and from the file system.
Login to E-Business as a user assigned the System Administrator responsibility and navigate: System Administrator > Oracle Applications Manager > Workflow > Notification Mailers > View Log
System Administrator > Concurrent: Manager > Administer
Select Workflow Mailer Service > Processes > Manager Log
File System:
The Workflow Mailer Service (WFMLRSVC) is a java concurrent manager; therefore, the log is stored in the same location as the other concurrent manager logs on the Concurrent Manager Server node assigned to the Workflow Mailer Service.

$APPLCSF/$APPLLOG is default location. FNDCPGSC.txt is the naming convention of the Workflow Service logs. You can find the concurrent_process_id for the currently active Workflow Mailer Service log using the following query:

set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15; SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC') AND fcq.concurrent_queue_id = fcp.concurrent_queue_id AND fcq.application_id = fcp.queue_application_id AND flkup.lookup_code=fcp.process_status_code AND lookup_type ='CP_PROCESS_STATUS_CODE' AND meaning='Active';
Currently the Retention Time is set to 0 on the WF_ERROR queue. This causes all events on the queue to be removed by the Advanced Queue Time Manager (aq_tm_process) as soon as all events on the WF_ERROR queue is processed. Set the Retention Time to 1 day so you can easily determine if your notification issue is due to a workflow error.
declare l_wf_schema varchar2(200); lagent$_agent; l_new_queue varchar2(30); begin l_wf_schema := wf_core.translate('WF_SCHEMA'); l_new_queue := l_wf_schema'.WF_ERROR'; lagent :=$_agent('WF_ERROR',null,0); dbms_aqadm.alter_queue(queue_name=> l_new_queue, retention_time => 60*60*24 /* one day in seconds */);


Run $FND_TOP/sql/wfmlrdbg.sql to collect information about a particular notification based on its notification_id (NID). It will spool a wfmlrdbg.html file that can be reviewed for any issues that is preventing a notification from being sent or its response from being processed.
sqlplus apps/ @$FND_TOP/sql/wfmlrdbg.sql
Run $FND_TOP/sql/wfsmrdbg.sql to collect information about sending Summary notifications based on the user which have his notification_preference set to receive a summary of his open notifications. It will spool a wfsmrdbg.html file that can be reviewed for issues preventing the summary notification from being emailed.
sqlplus apps/ @$FND_TOP/sql/wfsmrdbg.sql
sqlplus apps/apps @$FND_TOP/sql/wfsmrdbg.sql SYSADMIN
Workflow Agent Listeners

Oracle E-Business has evolved to use the latest components of the Oracle Database. One of these components that are used extensively in R12 is Advanced Queuing (AQ) as a core component of Oracle Workflow's Business Event System (BES). Many events loaded into the BES provide core functionality such as for processing notifications by email, XML Gateway transactions, User Maintenance/Administration, and other product specific business logic. These events are processed through our AQ by seeded listeners or by custom listeners. A listener simply pings the queue it is configured to check periodically to see if there are any events enqueued on it that need to be processed or in the case of the mailer for inbound processing, it will check the IMAP user account for any responses in the inbox and enqueue proper responses on the Notification Inbound queue. An event is only raised if there is a subscription assigned to it and the queue used is based on the agent that is assigned to the subscription. Events can be enqueued on our default WF_DEFERRED AQ if the phase on the subscription is greater than 99 for asynchronous processing with or without an agent assigned to the subscription. If the phase is less than or equal to 99, the event is processed synchronously by the agent to which is is assigned. The exception this rule is for Java subscriptions used for middle tier java processing. When a java subscription is assigned to an event, all deferred subscriptions and java subscription are enqueued on the WF_JAVA_DEFERRED queue for asynchronous processing by the Workflow Java Deferred Agent Listener.
Run $FND_TOP/sql/wfbesdbg.sql to collect information about the processing of an event based on the event_name and event_key used when it is raised. It will spool a wfevtdbg.html output file. You may need to rename the in order to be able to review the output in a browser.
Due to the complexity of the naming convention of event keys you must run this diagnostics in this manner to avoid failures.
sqlplus apps/ @$FND_TOP/sql/wfbesdbg.sql
Enter value for 1: Enter value for 2:
sqlplus apps/apps @$FND_TOP/sql/wfbesdbg.sql Enter value for 1: Enter value for 2: PER:25FND_RESP:216672454484:54394

Workflow Directory Services (WFDS)
The Workflow Directory Services infrastructure is used to store workflow user and roles. This has been enhanced to directly store E-Business users and their responsibility assignments. Each Product group store their own data in WFDS table range partitions that is based on the origination of the data. As the providers of the infrastructure used by the product groups, Workflow Support and Development investigate the following potential failure points when determining if the reported issue can be addressed as a workflow infrastructure issue.
User/responsibility assignments are maintained by the business event system. The events are linked to subscriptions that are either synchronous or asynchronous. Asynchronous subscriptions will be enqueued on either the Workflow Deferred (WF_DEFERRED) or on Workflow Java Deferred (WF_JAVA_DEFERRED) AQ.
Run the Workflow Directory Services (WFDS) Data Collection Script in
Note 742062.1 to collect information about an EBS user and their responsibility assignments.
sqlplus apps/
$FND_TOP/sql/wfdirchk.sql to obtain a report of potential directory services data problems. Be sure to spool the file to obtain an output for review.
sqlplus apps/
spool wfdirchk.LST
Workflow Purging (WF_PURGE)
WF_PURGE is a workflow server package used to maintain the workflow infrastructure tables containing obsolete runtime data (data generated at the time a workflow process runs) and design time data (workflow design data that is now obsolete due to the workflow design being changed). The Purge Obsolete Workflow Runtime Data concurrent request calls this package to maintain these tables by periodically deleting completed workflow processes from all the tables used to store the meta-data.
Run the WF Purge Data Collection Script in
Note 750497.1 to collect information about the size of the workflow runtime tables and the condition of the workflow processes.
sqlplus apps/