Hi, in this topic we learn how to configure Dedicated workflow mailer for alerts using Correlation id
To check all topics click below link:
################################################################################################################
################################################################################################################
Workflow mailer Important MOS Docs.
How to Purge WFERROR (System: Error) Workflow Items? (Doc ID 804622.1)
Workflow Queues Creation Scripts (Doc ID 398412.1)
How To Rebuild Mailers Queue When it is Inconsistent or Corrupted? (Doc ID 736898.1)
How To Manually Recreate WF_NOTIFICATION_OUT (Doc ID 400493.1)
How To Set %AGENT% Profile Options Correctly To Use Workflow Notifications With Framework Content Regions And Advanced Configuration settings (Doc ID 1943176.1)
Seeded WFVER.sql How To Run, Check The Version, and Health Of Workflow Installed On The Server (Doc ID 1011379.102) (Doc ID 1364295.1)
How to Change the System Name in Workflow? (Doc ID 387337.1)
Configuring Oracle Workflow for OAuth 2.0 in Oracle E-Business Suite Release 12.2 and Release 12.1.3 (Doc ID 2884072.1)
Rebuild mailer's queue:
sqlplus apps/<apps_pwd> @$FND_TOP/patch/115/sql/wfntfqup APPS <apps_pwd> APPLSYS
Workflow Mailer Profile: WF: Workflow Mailer Framework Web Agent
Context Variable for WFM Profile: grep s_wfmail_agent $CONTEXT_FILE (for WFM Profile) (Doc ID 2493004.1)
grep -ie s_webhost $CONTEXT_FILE
grep -ie s_domainname $CONTEXT_FILE
grep -ie s_webport $CONTEXT_FILE
echo "http://"$(cat $CONTEXT_FILE | grep s_webhost | cut -d '>' -f2 | cut -d '<' -f1)"."$(cat $CONTEXT_FILE | grep s_domainname | cut -d '>' -f2 | cut -d '<' -f1)":"$(cat $CONTEXT_FILE | grep s_webport | cut -d '>' -f2 | cut -d '<' -f1)
grep s_wfmail_agent $CONTEXT_FILE
################################################################################################################
################################################################################################################
https://blogs.oracle.com/ebstech/post/scheduling-workflow-mailer-stop-and-start-events-as-good-practice
In-order to achieve this make sure you make all the WFM Component Container is in Activated state.
Use the corresponding link/button to create two events, one for stopping the mailer and one for starting it up:
In the screen shot above the stopping event is set to happen at 11:50 PM and the starting event at 11:55 PM, and these are set to repeat every using 1440 minutes as the interval. If no interval value is specified the stop/start events are raised just once.
################################################################################################################
################################################################################################################
TO CREATE INDEX FOR WORKFLOW MAILERS TABLES
select component_name, inbound_agent_name from fnd_svc_components;
select * from all_tables a where a.table_name in ('WF_NOTIFICATIONS','WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');-- to check tablespace for tables
select * from dba_indexes where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');-- to check index over tables
select tablespace_name,index_name from dba_indexes where index_name like 'WF_%_N1';-- to check index over tables
--A good index should return 2 rows, 1 for CORRID, 1 for MSGID on tables-column level per table(IN, OUT)
select * from all_ind_columns where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');--,'WF_NOTIFICATIONS'); --to check index over table columns
--NOTE: The index tablespace can be determined by running the following select. The index names are all listed in the script:
select tablespace_name,table_name,index_name from dba_indexes where index_name like 'WF_NOTI%_N1';
--To create index for column WF_NOTIFICATION_OUT(CORRID) (Doc ID 367483.1)
Use the same tablespace_name as the one that belongs to WF_NOTIFICATION_IN_N1:
[applmgr@ebsprodapp3 ~]$ ls -ltrh $FND_TOP/patch/115/sql/wfqidxc*
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspswd @$FND_TOP/patch/115/sql/wfqidxc2 APPLSYS appspswd APPS_TS_QUEUES
################################################################################################################
################################################################################################################
How to Run Script wfmlrdbg.sql and Collect its Output? (Doc ID 1364300.1)
To Generate html for WFM with email NID=13701629:
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspswd @$FND_TOP/sql/wfmlrdbg.sql 13701629
[applmgr@ebsprodapp3 ~]$ ls -ltrh *.html
- To collect much information about a given notification,
- To review the setup of the Workflow Notification Mailer.
The script above will generate an output file with a name of wfmlrdbg<notification id>.html, in the same directory the command was executed from.
################################################################################################################
################################################################################################################
TO check connectivity
Workflow Mailer Configuration SSL Implementation [Video] (Doc ID 1324799.1)
How To Create A Dedicated Mailer For Alerts (Doc ID 743344.1)
E-Business Suite Workflow Java Mailer Fails With Error 'UNEXPECTED:[SVC-GSM-WFMLRSVC' And 'oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag' Due To Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException (Doc ID 367483.1)
Workflow Notification Mailer Not Sending Framework (FMK) Emails like POAPPRV, REQAPPRV, APEXP, OA Framework etc and Email Fails with "Problem getting the HTML content" in the Workflow Mailer log file (Doc ID 2493004.1)
select * from fnd_svc_components;-- to check all workflow mailers/components status
-- Deatiled info of Pending WF_Mails
select * from WF_NOTIFICATIONS where status='OPEN' and mail_status='MAIL' --and message_type='WFTESTS' --and RECIPIENT_ROLE='MAREEF'
select count(*) from WF_NOTIFICATIONS where status='OPEN' and mail_status='MAIL'
-- EMAIL Preference of user Query
select * from fnd_user u, wf_local_roles r where r.name = u.user_name and r.NOTIFICATION_PREFERENCE not like 'MAILHTML' --to check which users have pref. issue
-- Current Workflow Mailer log
SELECT fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
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'
--To check connectivity of Mailer for a office365 account
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \
-Ddbcfile=$FND_SECURE/$TWO_TASK.dbc \
-Dport=587 \
-Dsecurity=STARTTLS \
-Dtruststore=$FMW_HOME/keystores \ -----------------------> Keystore location
-Dserver=smtp.office365.com \
-Daccount=XXXX -Dpassword=XXXX \
-Dconnect_timeout=120 -Ddebug=Y \
-Dlogfile=/tmp/smtp1.log \
-DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer > /tmp/smtp2.log
--To check JRE
[applmgr@ebsprodapp3 ~]$ $AFJVAPRG -version
java version "1.7.0_321"
Java(TM) SE Runtime Environment (build 1.7.0_321-b08)
Java HotSpot(TM) 64-Bit Server VM (build 24.321-b08, mixed mode)
[applmgr@ebsprodapp3 ~]$
--WF_DEFERRED, wf_notification_out details
WF_DEFERRED queue will hold all the deferred messages that gets triggered through business events and are processed by WF deferred agent listener.
Out of these, if any messages are to be delivered as mails those messages get moved to wf_notification_out to be processed by WF mailer
You can validate the messages status and verify the count in ready status
a. SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',3,'3 = Exception',to_char(substr(wfe.state,1,12))) State, count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;
b. SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',3,'3 = Exception',to_char(substr(wfe.state,1,12))) State, count(*) COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;
################################################################################################################
################################################################################################################
TO More information regarding Workflwo Mailer like (10489 Java Mail API Version)
#
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspswd @$FND_TOP/sql/afsvcpup.sql
Enter Component Id: 10008
Enter Component Id: 1xxxx
10489 Java Mail API Version 1.5.4 1.5.4 Y N
10125 Max Error Count 10 1000 Y N
################################################################################################################
################################################################################################################
How to Run Script wfmlrdbg.sql and Collect its Output? (Doc ID 1364300.1)
To check Workflow System name:
You may check the system name through below steps
Login to oracle applications
Workflow Administrator Web Applications responsibility --> Administrator Workflow --> Administration --> Check system name field
As per script $FND_TOP/sql/wfver.sql
The local System is ASSIGNED to XXXXXX
* System Name XXXXXXX
################################################################################################################
################################################################################################################
To Create A Dedicated WFM Mailer For Alerts:
Correlation ID ALR%
################################################################################################################
###############################################################################################################
If wfntfqup failed then perform steps manually also some with scripts
Stop Workflow and all component services
select * from apps.fnd_svc_components
select * from apps.fnd_svc_components WHERE COMPONENT_TYPE='WF_MAILER'
##############################
DESCRIPTION: Purges the outbound message queue
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfntfqup APPS appspwsd APPLSYS
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:05:58 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
Commit complete.
Elapsed: 00:00:00.00
**** TEMPORARY TABLES / AQs created ****
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
**** Messages backed up ****
Commit complete.
Elapsed: 00:00:00.00
**** wfaqback.sql completed, executing SQLs from wfntfqup
declare
*
ERROR at line 1:
ORA-20000: Oracle Error = -4020 - ORA-04020: deadlock detected while trying to
lock object APPLSYS.AQ$_WF_NOTIFICATION_OUT_F
ORA-06512: at line 318
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
How To Manually Recreate WF_NOTIFICATION_OUT (Doc ID 400493.1)
[applmgr@ebsprodapp3 ~]$ sqlplus applsys/appspwsd
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:10:39 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> DROP VIEW applsys.AQ$WF_NOTIFICATION_OUT;
View dropped.
SQL> DROP VIEW applsys.AQ$_WF_NOTIFICATION_OUT_F;
View dropped.
SQL> Exec dbms_aqadm.drop_queue_table( queue_table => 'WF_NOTIFICATION_OUT', force => TRUE);
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfntfqup APPS appspwsd APPLSYS
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:11:26 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
declare
*
ERROR at line 1:
ORA-20000: Oracle Error = 100 - ORA-01403: no data found
ORA-06512: at line 104
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
##############################
DESCRIPTION: Creates all Advanced Queues with JMS Text structures.
[applmgr@ebsprodapp3 ~]$ sqlplus applsys/appspwsd @$FND_TOP/patch/115/sql/wfjmsqc2.sql APPLSYS appspwsd
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:16:24 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL>
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
##############################
DESCRIPTION: Creates Subscribers for Multiconsumer Queues
[applmgr@ebsprodapp3 ~]$ sqlplus applsys/appspwsd @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS appspwsd
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:19:11 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
##############################
select * from all_ind_columns where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');
DESCRIPTION: Create Index on Notifications Table OUT "CORRID" coloum
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfqidxc2.sql APPLSYS appspwsd APPS_TS_TX_DATA :May be this is correct to provide apps password:
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 12:50:01 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Connected.
Index created.
CREATE INDEX WF_NOTIFICATION_IN_N1 ON WF_NOTIFICATION_IN(CORRID)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
select * from all_ind_columns where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');
##############################
DESCRIPTION: Creates required grants/synonyms for Workflow Advanced Queue tables
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS appspwsd APPLSYS appspwsd
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:27:00 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Connected.
PL/SQL procedure successfully completed.
Connected.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
#############################################
DESCRIPTION: Work Flow Histograms Creation Script
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfhistc.sql APPLSYS
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:28:21 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
#########################
DESCRIPTION: WorkFlow Message Queue Clean - Purges the outbound message queue
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfntfqup APPS appspwsd APPLSYS
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:29:00 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
Commit complete.
Elapsed: 00:00:00.00
**** TEMPORARY TABLES / AQs created ****
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
**** Messages backed up ****
Commit complete.
Elapsed: 00:00:00.00
**** wfaqback.sql completed, executing SQLs from wfntfqup
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.59
*** invoking wfaqrenq.sql ******
*** Re-enqueing messages
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30
Commit complete.
Elapsed: 00:00:00.00
***** Objects related to wf_queue_temp_evt_table and wf_queue_temp_jms_table are dropped *****
***** Re-enqueue OF Alerts completed *****
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
#########################
DESCRIPTION: Create Index on WF Queue Tables
select distinct tablespace_name from dba_indexes,dba_queues where index_name='WF_NOTIFICATION_OUT_N1' and table_name=queue_table and name like 'WF%';
select * from all_ind_columns where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');
[applmgr@ebsprodapp3 ~]$ sqlplus apps/appspwsd @$FND_TOP/patch/115/sql/wfqidxc2 APPLSYS appspwsd APPS_TS_TX_DATA
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Apr 4 11:36:07 2022
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Connected.
Index created.
CREATE INDEX WF_NOTIFICATION_IN_N1 ON WF_NOTIFICATION_IN(CORRID)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
[applmgr@ebsprodapp3 ~]$
select * from all_ind_columns where table_name in ('WF_NOTIFICATION_IN','WF_NOTIFICATION_OUT');
select * from WF_NOTIFICATION_OUT
Start Workflow Services and send a test email;
Thanks for Reading
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux | DATABASE | RMAN | RAC | EBS |
| R1229 M7 Clone | ||||
| RAC DataGuard | Pluggable DB Clone | |||
| appsutil for DB | ||||
| JDK JRE upgrade | ||||
| Add EBS Node | ||||
No comments:
Post a Comment