ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION"

Approval Workflow Notification Mailer Error :

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "APPS.WF_NOTIFICATION", line 5130 ORA-06512: at line 1

Solution:

Sql> Select do.owner, do.object_name, do.object_type, dl.session_id, vs.serial#, vs.program, vs.machine, vs.osuser

from dba_locks dl, dba_objects do,v$session vs

where do.object_name ='WF_NOTIFICATIONS' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

Issue the command Alter system kill sessions 'Sid, serial#' immediate;

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=<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()= '<nid>'

<nid> - 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 = '<nid'>

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

Change Mail Preferences for all Application Users (FND USERS)

How to mass change Mail Preferences for FND USERS [ID 451929.1]

Solution :

Using sqlplus as the apps user:

1^st Method:

 1.

update wf_local_roles

set notification_preference = 'MAILHTML'

where user_flag='Y'

2.

update fnd_user_preferences

set preference_value = 'MAILHTML'

where module_name = 'WF'

and preference_name = 'MAILTYPE'

commit;

2nd method:

Navigation: Connect to Applications as Sysadmin user and select responsibility

 Workflow Administrator  Web Applications --> Administration --> Global  Preferences

Temp Tablespace Usage/Free

TEMP Usage per session ?

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

         COUNT(*) sort_ops

FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE    T.session_addr = S.saddr

AND      S.paddr = P.addr

AND      T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

         S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

Temp Space used/free?

select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;

Users Using Temp tablespace?

select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p

where p.name=b.session_addr;

To get which user is using temp tablespace ?

select username,tablespace,extents from v$sort_usage;

Free and Used Space in Temp Tablespace ?

SELECT   A.tablespace_name tablespace, D.mb_total,

         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM     v$sort_segment A,

         (

         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

         FROM     v$tablespace B, v$tempfile C

         WHERE    B.ts#= C.ts#

         GROUP BY B.name, C.block_size

         ) D

WHERE    A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

Temp Usage By statement ?

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,

         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

         T.sqladdr address, Q.hash_value, Q.sql_text

FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE    T.session_addr = S.saddr

AND      T.sqladdr = Q.address (+)

AND      T.tablespace = TBS.tablespace_name

ORDER BY S.sid;