Monday 18 June 2012

Checking status of all the Concurrent Managers / Workflow from sqlplus

Checking status of all the Concurrent Managers

select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');


Checking the status of Workflow Mailer and other Workflow Components:

 SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER';

Thursday 14 June 2012

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues
Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?


SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';


Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory


Find the Failed One’s?


Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';


Check pending e-mail notification that was pending for process.


Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;


Sql> SELECT * FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'SENT'
ORDER BY begin_date DESC




Check the Workflow notification has been sent or not?


select mail_status, status from wf_notifications where notification_id=


--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference


1. Verify whether the message is processed in WF_DEFERRED queue


select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= ''
- notification id


2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue


select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '
To check what all mails have went and which all failed ?


Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';


Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';




Users complain that notifications are stuck ?


Use the following query to check to see whatever the users are saying is correct


SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;


E.g o/p of query -


MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent ---
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error




If Mail not received by User ?


select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;


Status – Active
Notification_preference-> Mailtext
Email Address should not be null


Notification not sent waiting to be mailed ?


SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?


$FND_TOP/sql
run wfmlrdbg.sql
******************************

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications

Please see (Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.

*****************

How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer For Sending Email Notifications [ID 753845.1]

This document details the steps to perform a meaningful telnet test to investigate java mailer issues related to sending workflow email notifications to the SMTP server.

As it is explained in the Oracle Workflow Administrator’s Guide, it is mandatory to have an SMTP server set up in order to send the workflow notification e-mail messages. This means that the notification mailer does not send the notification e-mails itself but completely relies on the SMTP server to achieve this task.

For this reason, it is important to be able to send an email using telnet on the SMTP server in the conditions that are used by the mailer.

Note: this test is useful in the following situations (the below list is not exhaustive):
- You are setting the workflow notification mailer up and you want to validate the outbound processing parameters.
- You suspect a connectivity issue between the java mailer node and the SMTP server node.
- You suspect that SMTP server does not process emails.
- You notice that given notifications in WF_NOTIFICATIONS have MAIL_STATUS = FAILED, and that the notification preference of the notification recipient has been switched to DISABLED (this can be seen in $FND_TOP/sql/wfmlrdbg.sql output againt the given notification id).
- You want to validate a given email address.
...
More particularly this test will provide relevant output when the following strings are seen in mailer log:
- javax.mail.SendFailedException: 550 5.7.1 Unable to relay
- javax.mail.MessagingException
- Invalid Address
- Relay access denied
- Unable to relay
- Relaying denied
- Client does not have permission to submit mail to this server
- Validation failed for the following parameters -> {OUTBOUND_SERVER=Unable to make a network connection.}
- EXCEPTION:[SVC-GSM-WFMLRSVC-12848-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.read]:Not sending notification {3939936}because the notification mail status is null OR not 'MAIL'
- Updating notification {3939936} status {FAILED} {WFMLRSND_FAILED_UNDELIVERABLE} {{SYSADMIN}}
etc...

Solution

It is very important to perform all the steps mentioned below; they will allow to perform the telnet SMTP test from the right node and to pass the correct values during the test.

1) Identify the concurrent tiers node where mailer runs
by running script below:
select target_node 
from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%'; 
It will return for example:
TARGET_NODE                    
------------------------------ 
ebiz1                     

In this example ebiz1 is the node where java mailer runs.

2) Gather other parameters values necessary for the SMTP telnet test:
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer "outbound server" parameter), and what is the reply to address that is set up for the java mailer (this is mailer "reply to" parameter).

To get these values run the following:
SELECT b.component_name, 
       c.parameter_name, 
       a.parameter_value 
FROM fnd_svc_comp_param_vals a, 
     fnd_svc_components b, 
     fnd_svc_comp_params_b c 
WHERE b.component_id = a.component_id 
     AND b.component_type = c.component_type 
     AND c.parameter_id = a.parameter_id 
     AND c.encrypted_flag = 'N' 
     AND b.component_name like '%Mailer%' 
     AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO') 
ORDER BY c.parameter_name; 

It will return for example:
COMPONENT_NAME                  PARAMETER_NAME                 PARAMETER_VALUE        
------------------------------- ------------------------------ -----------------------
Workflow Notification Mailer    OUTBOUND_SERVER                mitini1                          
Workflow Notification Mailer    REPLYTO                        jmailer1@dummy_domain.com  

In this example the outbound server is on mitini1 node and the reply to address is set to jmailer1@dummy_domain.com.


3) Perform the SMTP telnet test as follows:
3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)
This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.
In our example you should log to ebiz1 node.

3.2) From mailer node, issue the following commands one by one:
telnet [outbound server] 25 
EHLO [mailer node] 
MAIL FROM: [reply_to address] 
RCPT TO: [my_test_email_address]
DATA 
Subject: Test message 

Test message body 

quit 

Notes:

a) Very important, the commands needs to be entered one by one
b) Replace [outbound server] by the value retrieved for OUTBOUND_SERVER in step 2.
c) Replace [mailer node] by the value retrieved in step 1.
d) Replace [reply_to address] by the value retrieved for REPLYTO in step 2.
e) Replace [my_test_email_address] by the email address that you need to test.
f) By default SMTP server runs on port 25. If another port is used you'll have to modify the port accordingly in the syntax below.
g) Enter a blank line after the email subject and after the text 'Test message body'.
h) The end of the message is signaled by a "." on a line by itself.
i) To exit the telnet session, type 'quit' and then hit 'enter'.


So the commands to enter in the context of our example are:
telnet mitini1 25  
EHLO ebiz1  
MAIL FROM: jmailer1@dummy_domain.com  
RCPT TO: robert.king@dummy_domain.com
DATA  
Subject: Test message  

Test message body  
.  
quit 
(let's assume robert.king@dummy_domain.com is the given address you want to test)

3.3) Then verify the following:

a) Has an error message been thrown during the test?
To compare the output you get with the normal output received during telnet SMTP test, please refer to section 8a (Verify SMTP Server ) in note 242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G.

b) Check the INBOX of the email address used for the test (my_test_email_address)
Is there an email here with Subject: Test message and with sender corresponding to reply_to_address email address?
In our example, you should check robert.king@dummy_domain.com  INBOX and research a message with subject "Test message" and that is from sender jmailer1@dummy_domain.com.
4) Test interpretation

If an error message has been thrown during the test, or if the test email has not been received, this means the SMTP telnet test is not successful. Because the java mailer uses the SMTP server to send notification emails, it is a prerequisite for a correct behavior of the java mailer that this test is successful.
If the test is not successful, depending on the message received, the problem has to be addressed by the SMTP Server administrator or the Network administrator.  Commonly, relaying needs to be enabled.
5) Finishing tasks
a) Confirm that the SMTP server defined within the Workflow Notification mailer setup reflects the correct address.
b) Once the mail server tests are complete and successful, to re-send any failed notifications, please run the Resend Failed Notifications concurrent request.

Workflow Information Center, Diagnostics, & Community

  • Please reference the Workflow Product Information Center Document for Top Workflow Resources: Document 1320509.1
  • For additional help, please refer to one of the following documents on diagnostics to address current needs. Providing diagnostic output on an issue for support when logging a service request is very helpful.

    Document 179661.1 for 11i or Document 421245.1 for Rel 12.x




Wednesday 13 June 2012

After clone DISPLAY REP-3000 Oracle Toolkit Error

Solution:
1.  Ensure the correct DISPLAY environment variable is set on the E-Business server(s)
In the main environment file, as pointed to by $APPLFENV, verify / set the DISPLAY environment variable to an active and available X display server.
DISPLAY=:.0 ; export DISPLAY
i.e. DISPLAY=acme.com:0.0 ; export DISPLAY
Note 2: Some notes, such as 200474.1, advocates placing the DISPLAY environment variable in startup scripts: adrepctl.sh, adcmctl.sh, gsmstart.sh. Caution should be observed; these scripts override the DISPLAY value in the main environment file and may add a level of confusion if different values are present on each server or script.
On Autoconfig ready instances, make any environment variable changes via the Autoconfig Context Editor or Oracle Applications Manager (OAM) and re-run Autoconfig on all nodes in order to ensure that all changes are properly synchronized across all tiers and to prevent the lost of the current environment changes next time Autoconfig runs.
2. Invoke or re-invoke the xhost command as root.
On the server where the X display server resides, as root, execute xhost ++… Where are the E-Business server names requiring X display server access, such as the Concurrent Processing Server, Oracle Reports Server, etc.
Typically, this action needs to be done any time the host with the X display server is bounced.
3. As the “root” user, bounce (restart) the X display server and/or any required window manager (i.e. mwm, olwm, fvwm, twm, etc.) associated with the X display server or window system.
Make sure to perform the xhost + command again.
Note 3: Please reference the X display server’s guide or contact the vendor for assistance on stopping or starting the X sever or window manager in used.
4. Validate that the Concurrent Processing Server sees the correct DISPLAY value.
Run the Sysadmin report “Prints environment variable values” with the “DISPLAY” parameter and check that the correct DISPLAY value is present. If this value is incorrect, then the concurrent processing server is not aware of the proper DISPLAY setting. In short, this environment variable needs to be defined before starting the concurrent processing server.
The Sysadmin report “Generate concurrent processing environment information” also lists all environment variables visible to the concurrent processing server.
5. Verify that the DISPLAY variable is usable by running a concurrent report in Postscript format.
Run the Sysadmin report “CP Postscript Report Regression Test” with the parameter BASIC. If the reports completes successfully, the concurrent processing server is aware of the DISPLAY value and the current variable value is usable.
Note 4: If Pasta is being utilized and the IX_RENDERING variable is set without a corresponding displayfontpath entry, a REP-3000 can occur–see Note 361639.1 “Cannot Open Display Font File: … REP-3000: Internal Error Starting Oracle Toolkit”
6. If bitmap (Postscript, PDF, etc) reports fail from within Oracle Applications, does a report in Postscript format run successfully from the OS command line?
a) Login as the applmgr on the host where the concurrent processing server resides and source the main environment file.
b) Set the DISPLAY environment variable, if not already set by the main environment file.
echo $DISPLAY
DISPLAY=:0.0 ; export DISPLAY
c) Run the “Active Users” report in Postscript format with the ar60runb executable and the following options. Provide the apps password an populate the parameters with the full path of $FND_TOP and $APPLTMP.
— 12.0 Example —
appsrwrun.sh userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ActUsr.ps desformat=/<10.1.2.$ORACLE_HOME>/reports/printers/psl132.prt errfile=/<$APPLTMP>/ActUsr.log
Note: The full path to appsrwrun.sh is contained within the $APPLORB environment variable
— 11.5 Example —
ar60runb userid=apps/apps report=/<$FND_TOP>/reports/US/FNDSCURS.rdf batch=yes destype=file mode=bitmap desname=/<$APPLTMP>/ActUsr.ps desformat=/<8.0.6.$ORACLE_HOME>/reports60/admin/printer/psl132.prt errfile=/<$APPLTMP>/ActUsr.log
7. If bitmap reports still fail with a REP-3000 from Oracle Applications or from the OS command line, the most likely cause of the error is that the X display server and/or window manager is not accessible, usable, or started.
a) Review the following documents for further insight and additional recommended checks:
Note 200474.1 “Comprehensive REP-3000 Troubleshooting and Overview Guide”, particularly the startup script examples at section “VI-6. GENERAL ISSUES:”
Note 207532.1 “Troubleshooting Tips for REP-3000 Error when Running PDF/POSTSCRIPT/HTML Reports”
Note 153960.1 “FAQ: X Server testing and troubleshooting”
Note 181244.1 “Configuring VNC Or XVFB As The X Server For Applications 11i(“Configuring an X Display Server for Applications on Unix Platforms”)”
b) Update any on-going Oracle service request with the results of the previous steps.
c) Use the command xdpyinfo -display :.0 to inspect and verify the display settings. Please consult the vendor’s documentation for detailed instructions on modifying any settings.
d) Contact the vendor of the third party X display server or VNC product for assistance on configuring their product for use.

Adapcctl.Sh: Exiting With Status 1 (Service can not be stopped using this script) [ID 788414.1]

On 11.5.10.2 in Production:
When attempting to stop Apache using adapcctl.sh, the following error occurs.

ERROR
Service can not be stopped using this script

STEPS
The issue can be reproduced at will with the following steps:
1. Run adapcctl.sh stop

BUSINESS IMPACT
The issue has the following business impact:
Due to this issue, unable to stop Apache successfully.

.

Cause

The issue is caused by the following setup:
$IAS_ORACLE_HOME/Apache/Apache/logs/ directory contained both httpd.pid and
apache_runmode.properties

Files httpd.pid and httpd_pls.pid gets created when Apache is started in NORMAL mode.
File apache_runmode.properties gets created when Apache is started in RESTRICTED mode.
As both kind of files are present in Logs directory, adapcctl.sh script thinks Apache cannot be
started in both modes and fails to stop.

.

Solution

To implement the solution, please execute the following steps:

1. Stop Apache / Kill all httpd processes if exists any.
2. Backup $IAS_ORACLE_HOME/Apache/Apache/logs
3. Delete all files under $IAS_ORACLE_HOME/Apache/Apache/logs/ directory.
4. Re-start Apache.
5. Retest the issue.


Monday 4 June 2012

AutoPatch error:ATTENTION: All workers either have failed or are waiting:FAILED: file xxxx on worker x.

AutoPatch error:ATTENTION: All workers either have failed or are waiting:FAILED: file xxxx on worker x.



Here what you are going to do if you face the below error while applying patches using adpatch

ATTENTION: All workers either have failed or are waiting:
FAILED: file xxxx on worker x.
ATTENTION: Please fix the above failed worker(s) so the manager can continue


Check the worker ID log file ( adworkerxx.log ) from $APPL_TOP/admin/$SID/log , the exact issue should be mentioned in it, take an action and resolve the issue then do the following to proceed with the adpatch:


Run adctrl
Choose option 1 "Show Worker Status" which should show the status failed.
Then choose option "2. Tell worker to restart a failed job". Enter the number of worker which you want to restart.
The status will change to "fixed,restart" and the patch will progress.

My issue was like:

Issue:

ERROR at line 1:
ORA-24001: cannot create QUEUE_TABLE, APPLSYS.WF_SMTP_O_1_TABLE already exists
ORA-06512: at "APPS.WF_QUEUE", line 2665
ORA-06512: at line 22

Solution:
exec dbms_aqadm.drop_queue_table(queue_table => 'APPLSYS.WF_SMTP_O_1_TABLE', force => TRUE); 


Then restart the failed worker.



Saturday 2 June 2012

apps clone after cloning Im getting FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06508

Please check Doc 146038.1.

For me I solved my issue by copying $AU_TOP/resource from the production to the cloned environment.

Number of Visitors