Query to monitor BPEL processes.

The following query can be used to monitor BPEL processes.

select count(1), RTRIM(process_id),
case state when 0 then 'intiated'
when 1 then 'open and running'
when 2 then 'open and suspended'
when 3 then 'open and faulted'
when 4 then 'closed and pending'
when 5 then 'closed and completed'
when 6 then 'closed and faulted'
when 7 then 'closed and canceled'
when 8 then 'closed and aborted'
when 9 then 'closed and stale'

else 'unknown' end as status
from ORABPEL.CUBE_INSTANCE
where trunc(creation_date) >= :B1 and state <> 5
group by process_id, state

CUBE_INSTANCE state column

Recently, I have started using soa and want to get stat on all processes with different state. I have started looking at ways of getting this data from dehydration store but there seems to be no much info on the columns of CUBE_INSTANCE. I was interested in the process name and state.

I came across the following values for state and want to share with you.
0 --> initiated
1 --> open and running
2 --> open and suspended
3 --> open and faulted
4 --> closed and pending
5 --> closed and completed
6 --> closed and faulted
7 --> closed and canceled
8 --> closed and aborted
9 --> closed and stale

Instances created in ESB Console

Instances created in ESB Console not showing up.

I have seen a situation where an instances created is not showing in esb console. At the first instance I thought something was wrong with esb. After further investigation, nothing broke as all the BPEL processes which is invoked by esb are all completed with out error.

By default the open_cursor parameter is set to 300. If you have got many processes, this can be exceeded and that was the problem and was able to fix by increasing this database parameter.

Long running Operations in Oracle

From time to time you want to monitor long running queries in oracle. I have recently given a task of purging dehydration database and wanted to find out how far much of the work has been done. I came across dynamic performance view v$session_longops. For me a simple query something like :javascript:void(0)

select round( SOFAR/TOTALWORK * 100,2) ||'% completed' from v$session_longops where sid = 487

produced the result I needed. But there is much more you can do with this view. TIME_REMAINING is very interesting column as it will give you an estimated time remaining for the query to be completed.

Oracle SOA Suite High Availability

If you are using Oracle SOA for business critial integaration, oracle have posted various resources on Oracle SOA suite high availability.

To view resources click Oracle SOA high availability