SOA 11g – Useful SOA Infra DB Queries [Advanced users]


Sometimes you want to identify all the faults for the messages that were sitting in BPEL Engine Level recovery as undelivered Invokes.

We can run this SQL on the SOA Infra Schema to obtain this info.

select wif.*

FROM WI_FAULT WIF

WHERE wif.cikey IN

(SELECT ci.cikey

FROM Dlv_Message dlv, cube_instance ci

WHERE dlv.State = 0

AND dlv.Dlv_Type = 1

AND dlv.ecid= ci.ecid

AND dlv.conv_id = ci.conversation_id

AND dlv.Receive_Date >TRUNC(Sysdate));

Once you have investigated them you can then either recover these instances from the em console application or can utilise the SOA API  to call the

BPELServiceEngine.recoverInvokeMessages() operation by passing the message GUID’s of the instances returned by the above query.

I will share the details in another post soon.

 

For details about the various states Please see my post https://nitinaggarwal.wordpress.com/2013/06/12/soa-11g-soa-infra-db-states-for-soa-composites-and-components/

References: Oracle A team

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s