ORA-00060

[latifa]

Hi Erman,

Somme requests failed and one of the error is ORA-00060 (interblocking detection, while waiting for resource, produced when  …)

********************************************
REP-0069: Erreur interne
Travail en cours de traitement terminé :Terminé avec erreur :
REP-1419: MSG-00101: Error occured in BATCH_APPROVAL()
MSG-00102: Error Code : -20001
MSG-00103: Error Message : ORA-20001: APP-SQLAP-10000: L'erreur ORA-00060: détection d'interblocage pendant l'attente d'une ressource s'est produite lors de
**************************************************************************


In trace files of the database, we can see :

Current Wait Stack:
 0: waiting for 'enq: TX - row lock contention'
    name|mode=0x54580006, usn<<16 | slot=0xff0011, sequence=0x31b20f
    wait_id=720 seq_num=721 snap_id=1
    wait times: snap=13.884800 sec, exc=13.884800 sec, total=13.884800 sec
    wait times: max=infinite, heur=13.884800 sec
    wait counts: calls=30 os=30
    in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
  inst: 2, sid: 4054, ser: 55892
Dumping final blocker:
  inst: 2, sid: 4054, ser: 55892
There are 20 sessions blocked by this session.
Dumping one waiter:
  inst: 1, sid: 4457, ser: 12768


So as I can't track the blocking sessions, how can we solve this issue ?

Regards,
Latifa

[ErmanArslansOracleBlog]

Hmm.. It is a deadblock.
Probably due to a bug.
Does this log belong to Invoice Validation Program or something?
What is your exact EBS version?

[ErmanArslansOracleBlog]

Please also check the following ->

What is the session with SID 4054 doing ?  check it from v$session and v$process. What is the module name and program name, what is its wait, what is its current sql and so on..

[latifa]

Erman,

Yes it's INVOICE validation program :
Program: Invoice Validation  (APPRVL)
Application: Payables
Executable type: Oracle Reports  (P)
Executable file name or procedure: APXAPRVL

Our Oracle EBS is 12.2.4

For the session I can't find any thing in v$session. It was ran during batch so no session left now.

Here it is AWR reports .  I Don't know how to find pertinent informations.

awrrpt_1_26303_26304.html
awrrpt_2_26303_26304.html

Thank you in advance
Latifa

[ErmanArslansOracleBlog]

1)Please check wheter another program was running at the same time with INVOICE validation program. or not.. If there was a program running during that time, we need to check that.. Maybe there is an incompatability between that program and INVOICE Validate program.. So this is an important thing to check.
2)Please check wheter you are executing APXAPRVL correctly or not.. Check its parameters with your functionals.. Check the profile options related with it and check the setup that is done for making it work.. Any problem in those are may also create this issue.
3)We need to check the cause of this deadlock.. The details of the session that causes it.. Check the alert log of the database, it will give you some clues.. By taking those clues into considerations, analyze further in the db level.
4)Deadlock is an application related error. it is the problem of the application actually. So we need to check the patches.. I see multiple bugs for 12.1, but I don't see any bugs publically available for 12.2...
But, still it is better to check patches available for versions 12.2. You can be check it on: My Oracle support > Patches & Updates > Select Product: Payable >  Release: 12.2

[latifa]

Hi Erman,

1. I couldn't find any request running same time as our request "APPRVL".
there were three other programs:

RAXTRX (Autoinvoice Import Program) between  19:09:58 and 19:11:09
RBMATCHG (XXRB - Rapprochement) between 09:13:58-09:13:59 and 16:24:02-16:24:02 !
RBMATSIM (XXRB - Simulation rapprochement automatique) between  09:13:57-09:13:57 and 16:24:00-16:24:17)

2. APXAPRVL correctly exceuted or not: I'll ask finctionnal admin for this point later when I check all that I can do (point out details from alert_log and trace files that make them working on them).

3. Alert log reported the ORA-00060 error and give us trace file in both instances of the RAC.
I Don't know how to interprete the messages. They look normal !!!

I check out Following messages :
        --> Global Wait-For-Graph(WFG) for GES Deadlock ID=[4_1_162]
               End of Global WFG for GES Deadlock ID=[4_1_162]

       --> *** 2020-11-18 21:28:37.057
         Global blockers dump start:---------------------------------
         DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x350018][0x23a45b],[TX][ext 0x0,0x1]
          ----------resource 3c6c30820----------------------

       --> APPRVL,RAXMTR Calling RAXTRX, RACUST


4. I think that there is no actual patch for payable for 12.2 ! and I Don't know :-( ...

If you have any advice please let me know. Please find attached my trace files, hope it's not too much for you because they are heavy in my opinion :)

Thank you in advance for your help.

Regards
Latifa

DBOFINP11_lmd0_22912.7z

PS: second trace file in a second post :)

[latifa]

DBOFINP12_lmd0_22252.7z

[ErmanArslansOracleBlog]

It seems it is caused by RACUSTSB..

    application name: e:AR:cp:ar/RACUSTSB, hash value=846504457
    action name: AR/TOUT_AR_ADMINISTRATEUR, hash value=1118212072
  current SQL:
  update hz_cust_accounts c  set payment_term_id=(select min(standard_terms)  from hz_customer_profiles p where ((p.cust_account_id=c.cust_account_id and p.status='A') and p.request_id=:b0)) where cust_account_id in (select cust_account_id  from hz_cust_accounts cust ,ra_customer_profiles_interface i where (i.orig_system_customer_ref=cust.orig_system_reference and i.orig_system_address_ref is null ))

[ErmanArslansOracleBlog]

Here it shows the deadlock ->
It seems 2 RACUSTSBNS  lock eachother..

  Global Wait-For-Graph(WFG) for GES Deadlock ID=[4_0_1]
------------------------------------------------------------------------
                       Victim : (instance=1, lock=0x8ef29c9d0)
      Start (master) Instance : 1
     Number of Locks involved : 4
  Number of Sessions involved : 2

User session identified by:
{
                    User Name : opfinbac
                 User Machine : udkfinp04
             OS Terminal Name :
                OS Process ID : 7206
              OS Program Name : RACUST@udkfinp04 (TNS V1-V3)
             Application Name : e:AR:cp:ar/RACUSTSBNS V1-V3)work.OAFatalErrorr.LookUpAMMtAM
                  Action Name : AR/TOUT_AR_ADMINISTRATEURCEPTIONTES
                  Current SQL : update hz_cust_accounts c  set payment_term_id=(select min(standard_terms)  from hz_customer_profiles p where ((p.cust_account_id=c.cust_account_id and p.status='A') and p.request_id=:b0)) where cust_account_id in (select cust_account_id  from hz_cust_accounts cust ,ra_customer_profiles_interface i where (i.orig_system_customer_ref=cust.orig_system_reference and i.orig_system_address_ref is null ))
               Session Number : 515
        Session Serial Number : 32615
        Server Process ORAPID : 606
         Server Process OSPID : 15149
                     Instance : 1
}
waiting for Lock 0x8ef29c9d0 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0x10c001a.0x127228(ext 0x0,0x1b)
        GES Transaction ID : 1025-025E-000000DD
}
which is blocked by Lock 0x8eb7c2638 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0x10c001a.0x127228(ext 0x0,0x1b)
        GES Transaction ID : 2028-0288-00000020
}
owned by the
User session identified by:
{
                    User Name : opfinbac
                 User Machine : udkfinp04
             OS Terminal Name :
                OS Process ID : 7209
              OS Program Name : RACUST@udkfinp04 (TNS V1-V3)
             Application Name : e:AR:cp:ar/RACUSTSBNS V1-V3)3)MAILion.sendigatePortletAMletAM
                  Action Name : AR/TOUT_AR_ADMINISTRATEURCEPTIONEL
                  Current SQL : update hz_cust_accounts c  set payment_term_id=(select min(standard_terms)  from hz_customer_profiles p where ((p.cust_account_id=c.cust_account_id and p.status='A') and p.request_id=:b0)) where cust_account_id in (select cust_account_id  from hz_cust_accounts cust ,ra_customer_profiles_interface i where (i.orig_system_customer_ref=cust.orig_system_reference and i.orig_system_address_ref is null ))
               Session Number : 4039
        Session Serial Number : 22031
        Server Process ORAPID : 648
         Server Process OSPID : 24374
                     Instance : 2
}
waiting for Lock 0x3c6e60c50 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0x100007.0xff4f3(ext 0x0,0x1)
        GES Transaction ID : 2028-0288-00000020
}
which is blocked by Lock 0x89b08b710 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0x100007.0xff4f3(ext 0x0,0x1)
        GES Transaction ID : 1025-025E-000000DD
}
owned by the first user session of the WFG.

[ErmanArslansOracleBlog]

Same updates .. Same sql.. Same program from 2 different sessions..

[ErmanArslansOracleBlog]

Well.. Check it with your Functionals..
Trace shows the deadlock.. Tell them the story, the RACUST and that update.
Ensure everyting is done properly in the user level..

If everyting is done properly, then create an Oracle Support SR .. There may be a need to patch RACUST.

[latifa]

Thank you very much Erman for your reply.

However, I would like to learn how do find it :) ;

I have a question, why did you choose this bloc, how about APPRVL with this one for example :

--> same update … sql … program … and it's do the the job three time ?


===============================================================
  Global Wait-For-Graph(WFG) for GES Deadlock ID=[4_1_17]
------------------------------------------------------------------------
                       Victim : (instance=2, lock=0x8ef69d340)
      Start (master) Instance : 2
     Number of Locks involved : 6
  Number of Sessions involved : 3

User session identified by:
{
                    User Name : opfinbac
                 User Machine : udkfinp04
             OS Terminal Name :
                OS Process ID : 9441
             OS Program Name : ora_rw20_run@udkfinp04 (TNS V1-V3)
             Application Name : e:SQLAP:cp:sqlap/APPRVL(TNS V1-V3)ournal.completeImportlItemAMM
                  Action Name : SQLAP/TOUT_AP_ADMINISTRATEURTEURULE
                  Current SQL : UPDATE AP_HOLDS_ALL AHA SET AHA.VALIDATION_REQUEST_ID = NULL WHERE AHA.VALIDATION_REQUEST_ID IN ( SELECT FCR.REQUEST_ID FROM FND_CONCURRENT_REQUESTS FCR WHERE FCR.CONCURRENT_PROGRAM_ID IN ( SELECT FCP.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS FCP WHERE FCP.APPLICATION_ID = 200 AND FCP.CONCURRENT_PROGRAM_NAME IN ('APPRVL','WORKERAPPRVL') ) AND FCR.PHASE_CODE = 'C')
               Session Number : 2356
        Session Serial Number : 1320
        Server Process ORAPID : 328
         Server Process OSPID : 12438
                     Instance : 2
}
waiting for Lock 0x3c396e9c8 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe70020.0x274161(ext 0x0,0x1b)
        GES Transaction ID : 2014-0148-00000C3A
}
which is blocked by Lock 0x8eef66100 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe70020.0x274161(ext 0x0,0x1b)
        GES Transaction ID : 2011-011B-00000B2E
}
owned by the
User session identified by:
{
                    User Name : opfinbac
                 User Machine : udkfinp04
             OS Terminal Name :
                OS Process ID : 8877
              OS Program Name : ora_rw20_run@udkfinp04 (TNS V1-V3)
             Application Name : e:SQLAP:cp:sqlap/APPRVL(TNS V1-V3)GES1-V3)l.completeImportMmAMMa
                  Action Name : SQLAP/TOUT_AP_ADMINISTRATEURTEURTES
                  Current SQL : UPDATE AP_HOLDS_ALL AHA SET AHA.VALIDATION_REQUEST_ID = NULL WHERE AHA.VALIDATION_REQUEST_ID IN ( SELECT FCR.REQUEST_ID FROM FND_CONCURRENT_REQUESTS FCR WHERE FCR.CONCURRENT_PROGRAM_ID IN ( SELECT FCP.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS FCP WHERE FCP.APPLICATION_ID = 200 AND FCP.CONCURRENT_PROGRAM_NAME IN ('APPRVL','WORKERAPPRVL') ) AND FCR.PHASE_CODE = 'C')
               Session Number : 3628
        Session Serial Number : 17567
        Server Process ORAPID : 283
         Server Process OSPID : 12011
                     Instance : 2
}
waiting for Lock 0x8ef69d340 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe30019.0xeb244(ext 0x0,0x1b)
        GES Transaction ID : 2011-011B-00000B2E
}
which is blocked by Lock 0x8e44d2fe8 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe30019.0xeb244(ext 0x0,0x1b)
        GES Transaction ID : 200F-00F2-00000E85
}
owned by the
User session identified by:
{
                    User Name : opfinbac
                 User Machine : udkfinp04
             OS Terminal Name :
                OS Process ID : 8765
              OS Program Name : ora_rw20_run@udkfinp04 (TNS V1-V3)
             Application Name : e:SQLAP:cp:sqlap/APPRVL(TNS V1-V3)g.preaccountinggkUpAMetAMmAMM
                  Action Name : SQLAP/TOUT_AP_ADMINISTRATEURTIONTES
                  Current SQL : UPDATE AP_HOLDS_ALL AHA SET AHA.VALIDATION_REQUEST_ID = NULL WHERE AHA.VALIDATION_REQUEST_ID IN ( SELECT FCR.REQUEST_ID FROM FND_CONCURRENT_REQUESTS FCR WHERE FCR.CONCURRENT_PROGRAM_ID IN ( SELECT FCP.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS FCP WHERE FCP.APPLICATION_ID = 200 AND FCP.CONCURRENT_PROGRAM_NAME IN ('APPRVL','WORKERAPPRVL') ) AND FCR.PHASE_CODE = 'C')
               Session Number : 189
        Session Serial Number : 13163
        Server Process ORAPID : 242
         Server Process OSPID : 11983
                     Instance : 2
}
waiting for Lock 0x3c6e5e168 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe70020.0x274161(ext 0x0,0x1b)
        GES Transaction ID : 200F-00F2-00000E85
}
which is blocked by Lock 0x3c396e9c8 (Transaction):
{
                Lock Level : KJUSEREX
             Resource Name : TX 0xe70020.0x274161(ext 0x0,0x1b)
        GES Transaction ID : 2014-0148-00000C3A
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
      End of Global WFG for GES Deadlock ID=[4_1_17]

[ErmanArslansOracleBlog]

Yes, this is another deadlock..
Same thing applies for it as well..
I also see some chained deadlocks.. I mean it seems like a session is a blocker and also blocked in 2 different deadlock schemas. For instance, I see SID 3368 is locked by SID 2247 in one deadlock graph.. Then I see SID 2247 is blocked by SID 2541 in another deadlock graph..The traces are very large, we need a parser for this.
But we don't need to lose our time.. The issue is clear from the DBA perspective..
So as suggested, you need to check the usage of these programs and you need to log an Oracle SR.

[latifa]

Hi Erman

Thank you for your reply. Let's see today in the meeting with Customer.

Regards,
Latifa

[latifa]

Hi Erman,

Feedback of our functionnals is that:

Failing requests APPRVL started 4 hours after these RACUST requests have finished.  
They ask if these sessions stay up despite the requests have finished !!
Also, there is no lynk between  hz_cust_accounts (AR) table and Invoice validation (AP) request.

I Don't think we can find hanging sessions to reply that "yes" they didn't finish !! (Do you think I can find that in the trace file of the database ?)

Anyway, I think I should make an SR for this …
What is your opinion ?

Regards,
Latifa

[ErmanArslansOracleBlog]

Hi Latifa,

You may parse that log using a script to gain more insight about the sessions and the deadlocks. 

The session ids are there, the module names are there. The dml statements are there. You may show them(functionals) the dmls statements and the modules executing those statements. They are all in the trace file. In parallel to that, you should raise a SR.

26 Kas 2020 Per 20:40 tarihinde latifa [via Erman Arslan's Oracle Forum] <[hidden email]> şunu yazdı:

Hi Erman,

Feedback of our functionnals is that:

Failing requests APPRVL started 4 hours after these RACUST requests have finished.  
They ask if these sessions stay up despite the requests have finished !!
Also, there is no lynk between  hz_cust_accounts (AR) table and Invoice validation (AP) request.

I Don't think we can find hanging sessions to reply that "yes" they didn't finish !! (Do you think I can find that in the trace file of the database ?)

Anyway, I think I should make an SR for this …
What is your opinion ?

Regards,
Latifa

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/ORA-00060-tp9024p9065.html

To start a new topic under EBS 12.2, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[latifa]

Thank you Erman for your reply.

Ok I'll raise an SR tomorrow.

Do you have a script to parse these trace files ? I tried tkprof but it results Nothing …

Regards,
Latifa

[ErmanArslansOracleBlog]

Deadlock graphs shows that session 1 is waiting for session 2 and session 2 is waiting for session 1. The exact same dmls are shown for each session .. strange.. the same pattern in different modules and dmls.. it looks like the exact same processes were executed at the same time, they got the same necessary locks in a random order and then normally they locked eachother ( deadlock )

26 Kas 2020 Per 20:40 tarihinde latifa [via Erman Arslan's Oracle Forum] <[hidden email]> şunu yazdı:

Hi Erman,

Feedback of our functionnals is that:

Failing requests APPRVL started 4 hours after these RACUST requests have finished.  
They ask if these sessions stay up despite the requests have finished !!
Also, there is no lynk between  hz_cust_accounts (AR) table and Invoice validation (AP) request.

I Don't think we can find hanging sessions to reply that "yes" they didn't finish !! (Do you think I can find that in the trace file of the database ?)

Anyway, I think I should make an SR for this …
What is your opinion ?

Regards,
Latifa

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/ORA-00060-tp9024p9065.html

To start a new topic under EBS 12.2, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML