CODEWITHSUNDEEP
oracle-databaseoracle11gmessage-queuedatabase-deadlocks
Mussa
Mussa

Reputation: 312

ora-24033 : no recipent for message has been detected in FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT

I am using Oracle E-business Suite R12.1 , 11G database version.

Error I encountered when trying to create or modify a user or responsibility:
ora-24033 : no recipent for message has been detected in FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT

My workaround: I have followed this oracle metalink doc (Doc ID 358151.1)to fix the error. Whenever I try to do the first step (dropping the existing subscriper), it gives this error:

ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object 30x0C2ABE5980x0C798B3F80x0D9CA49D8
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7541
ORA-06512: at "SYS.DBMS_AQADM", line 441
ORA-06512: at line 5

So, I have to fix ora-04020 so I can later be able to recreate the existing subscriper (by dropping the the existing one ,then adding a new one).

  1. I tried to bounce the applications and the database.
  2. I compiled the apps schema.

  3. I tried to find out where is the lock and which schema is blocking the other so I can kill that session:

    sql> SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a , v$session b, dba_objects c WHERE b.SID = a.session_id AND a.object_id = c.object_id;

    OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL# STATUS ----- ------------- ---------- ------ ------- ----------- APPLSYS FND_CONCURRENT_QUEUES TABLE 152 3 INACTIVE APPLSYS FND_CONCURRENT_REQUESTS TABLE 466 7 INACTIVE

    Then tried to find out which session is blocking the other to kill it:

    sql>SELECT l1.SID ||' IS BLOCKING '|| l2.SID FROM v$lock l1, v$lock l2 WHERE l1.BLOCK =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2;

But I got no returned rows.

Some of the suggestions in the internet state that deadlock occurs when there are invalid objects in the database.
So, what I did next is: I re-compiled the invalid objects by running $ORACLE_HOME/rdbms/admin/utlrp.sql

That script returned an output. The following is a part of the output:

ERROR at line 1:
ORA-04063: package body "SYS.UTL_RECOMP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.UTL_RECOMP"
ORA-06512: at line 4

and this was part of the output also:


OBJECTS WITH ERRORS
-------------------
1

Note: I am facing this issue after applying a patch. And it's been over 5 days since the deadlock was detected, even though most of the suggestions regarding to the deadlock says that oracle will manage the release itself. Explicit release not required.

Any idea on how to solve this issue? Your suggestion would be much appreciated.

Upvotes: 0

Views: 938

Answers (1)

Mussa
Mussa

Reputation: 312

I will write the solution here for those who might encounter such error in the future.

I solved the error by re-compiling invalid objects.

Upvotes: 0

Related Questions