Oracle SOA BPM notes : This is the digital notes that I have taken from various sources.

1. Composite :
2. Component :
3. Service :
4. Reference :
5. Adapters :
6. Mediator :
7. BPEL
8. BPMN
9. Spring
10. Human task
11. Business Rules
12. Task Api's 
13. Facade Api's
14. BPM api's
15. JMX api's
16. XA and non XA datasource
17. Purging ;
18. EDN 
19. OSB
20. ADF
21. BAM
22. XML
23. XSD
24. WSDL
25. XSLT
26. WSDL
27. Weblogic Server
28. SOA security
29. Instance patching
30. BPA
31. 
    

Oracle SOA BPM : Get jca reference properties using api

Some times we found out while using config plans to change schema for the package or procedure it was not reflecting.  In order to check in all the code, we had to manually go into EM console and check the JCA reference for schema name.

After some exploration, i found out that we can check schema names for all composites quickly  using soa facade api's ,below is the java code.

Work around when Audit trail is not visible in EM : Unable to display Flowtrace due to exception while initializing object, please review logs for detailed information

Some times when you open an instance in EM console, you might not be able to view the audit trail.

The error might be as below

Unable to display Flowtrace due to exception while initializing object, please review logs for detailed information.

>> Exception occured while retrieving the Flowtrace XML for the Composite Instance; ECID: 41343d22bdb09a74:-2967886c:144cf30c70b:-8000-000000000296dd8a
>> java.lang.NullPointerException
  at oracle.sysman.emai.model.sca.share.composite.FlowTraceModel.setFloatraceModelInfo(FlowTraceModel.java:185)
  at oracle.sysman.emai.model.sca.share.composite.FlowTraceModel.initialize(FlowTraceModel.java:90)
  at oracle.sysman.emai.model.sca.EMEntityModel._initializeEntityData(EMEntityModel.java:28)
  at oracle.sysman.emai.model.sca.common.SCAEntityObject.initializeSCAData(SCAEntityObject.java:124)
  at oracle.sysman.emai.model.sca.SCAObject.initializeData(SCAObject.java:74)
  at oracle.sysman.core.model.util.ModelUtil$EMObjectResetter.initOrResetEMObject(ModelUtil.java:319)
  at oracle.sysman.core.model.util.ModelUtil$EMObjectResetter.access$000(ModelUtil.java:280)
  at oracle.sysman.core.model.util.ModelUtil.initializeMBEMObjects(ModelUtil.java:231)
  at oracle.sysman.core.model.util.ModelUtil.initializeAllEMObjects(ModelUtil.java:128)
  at oracle.sysman.core.model.util.ModelUtil.initializeAllEMObjects(ModelUtil.java:101)
  at oracle.sysman.emSDK.adfext.ctlr.EMPagePhaseListener.doPrePrepareModel(EMPagePhaseListener.java:867)
  at oracle.sysman.emSDK.adfext.ctlr.EMPagePhaseListener.beforePhase(EMPagePhaseListener.java:795)
  at oracle.adfinternal.controller.lifecycle.ADFLifecycleImpl$PagePhaseListenerWrapper.beforePhase(ADFLifecycleImpl.java:558)
  at oracle.adfinternal.controller.lifecycle.LifecycleImpl.internalDispatchBeforeEvent(LifecycleImpl.java:100)
  at oracle.adfinternal.controller.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:191)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.startPageLifecycle(ADFPhaseListener.java:203)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener$1.after(ADFPhaseListener.java:377)
  at oracle.adfinternal.controller.faces.lifecycle.ADFPhaseListener.afterPhase(ADFPhaseListener.java:88)
  at oracle.adfinternal.controller.faces.lifecycle.ADFLifecyclePhaseListener.afterPhase(ADFLifecyclePhaseListener.java:53)
  at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:520)
  at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:207)
  at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
  at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
  at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)
  at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
  at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
  at oracle.sysman.emSDK.license.LicenseFilter.doFilter(LicenseFilter.java:164)


I had to debug the issue, so i tried a new way.

1. Get all the components with id associated with the instance.
2. Now i had list of components with component instance id and i will get list of error messages

Now i know which component has error.

Whether its BPEL, BPMN, MEDIATOR, HUMAN TASK etc I will goto the respective service engines in EM console, put the component instance id and will be able to view the faults with audit trail of that particular component.

Oracle SOA BPM : Task definition not found using namespace error

If you get  the below error

Task definition not found using namespace.[[
There is no active SCA composite with the referenced task definition. The provided task definition namespace is default/xyz!9.40/userTaskHT.
Verify that the composite containing the task definition is active.
 ORABPEL-30069

Task definition not found using namespace.
There is no active SCA composite with the referenced task definition. The provided task definition namespace is default/xyz!9.40/userTaskHT.
Verify that the composite containing the task definition is active.

at oracle.bpel.services.workflow.task.impl.WorkflowUtil.getTaskDefinitionId(WorkflowUtil.java:5038)
at oracle.bpel.services.workflow.task.impl.WorkflowUtil.setTaskDefinitionIdentifiers(WorkflowUtil.java:4943)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:1286)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:751)
at oracle.bpel.services.workflow.task.impl.TaskService.initiateTask(TaskService.java:679)
at sun.reflect.GeneratedMethodAccessor42858.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

Solution : Redeployment with the same revision resolved the issue.

Also refer to one of the post i found recently on invalid task definition : here 

Oracle SOA BPM Retrieve ldap user properties using expression.

If you want to retrieve ldap user properties like email, first name , last name etc, SOA suite provides Identity service functions to retrieve the same. The ldap is the security provider configured in weblogic server- Security realms.

You can retrieve some the properties below :

1.    name
2.    displayName
3.    description
4.    firstName
5.    middleName
6.    lastName
7.    workNumber
8.    homePhone
9.    mobile
10.    pager
11.    fax
12.    email
13.    im
14.    title

To use it in Jdeveloper use assign activity, select Expression Builder.

You can use the getUserproperty expression as below :


ids:getUserProperty($inputVariable.payload/ns3:userId,'lastName','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'name','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'title','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'email','jazn.com')
ids:getUserProperty($inputVariable.payload/ns3:userId,'firstName','jazn.com')

Oracle SOA BPM : Queries for SOA INFRA

1. Query to find number of purgable instances and respective states according to dates
--version 1
select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state,
 1,'1 - Completed',
 2,'2 - Running with faults',
 3, '3 - Completed with faults',
 16, '16 - Running with terminated',
 17, '17 - Completed with terminated',
 19, '19 - Completed with faults and terminated',
 64, '64 - ?') "State",
count(distinct ecid)
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
group by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;

-- version 2 group by date

select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
DECODE(STATE,
 0,'0-Running',
 1,'1-Completed',
 2,'2-Running with faults',
 3, '3-Completed with faults',
 4, '4-Running with recovery required',
 5,'5-Completed with recovery required',
 6,'6-Running with faults and recovery required',
 7,'7-Completed with faults and recovery required',
 8,'8-Running with suspended',
 9,'9-Completed with suspended',
 10,'10-Running with faults and suspended',
 11,'11-Completed with faults and suspended',
 12,'12-Running with recovery required and suspended',
 13,'13-Completed with recovery required and suspended',
 14,'14-Running with faults, recovery required, and suspended',
 15,'15-Completed with faults, recovery required, and suspended',
 16,'16-Running with terminated',
 17,'17-Completed with terminated',
 18,'18-Running with faults and terminated',
 19,'19-Completed with faults and terminated',
 20,'20-Running with recovery required and terminated',
 21,'21-Completed with recovery required and terminated',
 22,'22-Running with faults, recovery required, and terminated',
 23,'23-Completed with faults, recovery required, and terminated',
 24,'24-Running with suspended and terminated',
 25,'25-Completed with suspended and terminated',
 26,'26-Running with faulted, suspended, and terminated',
 27,'27-Completed with faulted, suspended, and terminated',
 28,'28-Running with recovery required, suspended, and terminated',
 29,'29-Completed with recovery required, suspended, and terminated',
 30,'30-Running with faulted, recovery required, suspended, and terminated',
 31,'31-Completed with faulted, recovery required, suspended, and terminated',
 32,'32-Unknown',
 34,'34-Open and Faulted',
 36,'36-Running with recovery required and unknown state',
 64,'64-Stale') "State",
count(distinct ecid)
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
GROUP BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;

--version 3 group by count of instances

select
DECODE(STATE,
 1,'1-Completed',
 2,'2-Running with faults',
 3, '3-Completed with faults',
 4, '4-Running with recovery required',
 5,'5-Completed with recovery required',
 6,'6-Running with faults and recovery required',
 7,'7-Completed with faults and recovery required',
 8,'8-Running with suspended',
 9,'9-Completed with suspended',
 10,'10-Running with faults and suspended',
 11,'11-Completed with faults and suspended',
 12,'12-Running with recovery required and suspended',
 13,'13-Completed with recovery required and suspended',
 14,'14-Running with faults, recovery required, and suspended',
 15,'15-Completed with faults, recovery required, and suspended',
 16,'16-Running with terminated',
 17,'17-Completed with terminated',
 18,'18-Running with faults and terminated',
 19,'19-Completed with faults and terminated',
 20,'20-Running with recovery required and terminated',
 21,'21-Completed with recovery required and terminated',
 22,'22-Running with faults, recovery required, and terminated',
 23,'23-Completed with faults, recovery required, and terminated',
 24,'24-Running with suspended and terminated',
 25,'25-Completed with suspended and terminated',
 26,'26-Running with faulted, suspended, and terminated',
 27,'27-Completed with faulted, suspended, and terminated',
 28,'28-Running with recovery required, suspended, and terminated',
 29,'29-Completed with recovery required, suspended, and terminated',
 30,'30-Running with faulted, recovery required, suspended, and terminated',
 31,'31-Completed with faulted, recovery required, suspended, and terminated',
 32,'32-Unknown',
 34,'34-Open and Faulted',
 36,'36-Running with recovery required and unknown state',
 64,'64-Stale') "State",
count(distinct ecid) as CNT
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
GROUP BY STATE
ORDER BY CNT DESC;


2.  Query to find number of Running instances and respective states according to dates

--version 1
select to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state, 1,'1 - Completed',
 2,'2 - Running with faults',
 3, '3 - Completed with faults',
 16, '16 - Running with terminated',
 17, '17 - Completed with terminated',
 19, '19 - Completed with faults and terminated',
 64, '64 - ?', state) "State", count(distinct ecid)
FROM COMPOSITE_INSTANCE
group by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
order by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state asc;


--version 2

select
DECODE(STATE,
 0,'0-Running',
 1,'1-Completed',
 2,'2-Running with faults',
 3, '3-Completed with faults',
 4, '4-Running with recovery required',
 5,'5-Completed with recovery required',
 6,'6-Running with faults and recovery required',
 7,'7-Completed with faults and recovery required',
 8,'8-Running with suspended',
 9,'9-Completed with suspended',
 10,'10-Running with faults and suspended',
 11,'11-Completed with faults and suspended',
 12,'12-Running with recovery required and suspended',
 13,'13-Completed with recovery required and suspended',
 14,'14-Running with faults, recovery required, and suspended',
 15,'15-Completed with faults, recovery required, and suspended',
 16,'16-Running with terminated',
 17,'17-Completed with terminated',
 18,'18-Running with faults and terminated',
 19,'19-Completed with faults and terminated',
 20,'20-Running with recovery required and terminated',
 21,'21-Completed with recovery required and terminated',
 22,'22-Running with faults, recovery required, and terminated',
 23,'23-Completed with faults, recovery required, and terminated',
 24,'24-Running with suspended and terminated',
 25,'25-Completed with suspended and terminated',
 26,'26-Running with faulted, suspended, and terminated',
 27,'27-Completed with faulted, suspended, and terminated',
 28,'28-Running with recovery required, suspended, and terminated',
 29,'29-Completed with recovery required, suspended, and terminated',
 30,'30-Running with faulted, recovery required, suspended, and terminated',
 31,'31-Completed with faulted, recovery required, suspended, and terminated',
 32,'32-Unknown',
 34,'34-Open and Faulted',
 36,'36-Running with recovery required and unknown state',
 64,'64-Stale') "State",
count(1)
from composite_instance
GROUP BY STATE
ORDER BY STATE ASC;




3.  Table queries

SELECT COUNT(1) FROM COMPOSITE_INSTANCE;--
SELECT COUNT(1) FROM CUBE_INSTANCE;--
SELECT COUNT(1) FROM WFTASK;--
select count(1) from DLV_MESSAGE;--

--Count from important tables
SELECT
(SELECT COUNT(*) FROM CUBE_INSTANCE) AS CUBE_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE) AS COMP_INST,
(SELECT COUNT(*) FROM COMPONENT_INSTANCE) AS COMPNT_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE_FAULT) AS COMPST_INST_FLT,
(SELECT COUNT(*) FROM REFERENCE_INSTANCE) AS REF_INST,
(SELECT COUNT(*) FROM CUBE_SCOPE) AS CUBE_SCP,
(SELECT COUNT(*) FROM COMPOSITE_SENSOR_VALUE) AS COMP_SEN_VAL,
(SELECT COUNT(*) FROM XML_DOCUMENT) AS XML_DOC,
(SELECT COUNT(*) FROM XML_DOCUMENT_REF) AS XML_DOC_REF,
(SELECT COUNT(*) FROM AUDIT_TRAIL) AS AUDIT_TRAIL,
(SELECT COUNT(*) FROM AUDIT_COUNTER) AS AUDIT_CNTR,
(SELECT COUNT(*) FROM AUDIT_DETAILS) AS AUDIT_DET,
(SELECT COUNT(*) FROM INSTANCE_PAYLOAD) AS INST_PYLD,
(SELECT COUNT(*) FROM HEADERS_PROPERTIES) AS HDRS_PROPS,
(SELECT COUNT(*) FROM WFTASK) AS WFTASK,
(SELECT COUNT(*) FROM WI_FAULT) AS WI_FLT,
(SELECT COUNT(*) FROM DLV_MESSAGE) AS DLV_MSSG,
(SELECT COUNT(*) FROM DLV_SUBSCRIPTION) AS DLV_SUBC,
(SELECT COUNT(*) FROM DOCUMENT_DLV_MSG_REF) AS DOC_DLV_MSG_REF,
(SELECT COUNT(*) FROM DOCUMENT_CI_REF) AS DOC_CI_REF,
(SELECT COUNT(*) FROM WORK_ITEM) AS WRK_ITEM
FROM DUAL;

3. Calculate increase in size of database schema's

select * from(select c.segment_name "Object Name",b.object_type,sum(space_used_delta) / 1024 / 1024 "Growth (MB)"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate)-&days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner ='*_SOAINFRA'
GROUP BY C.TABLESPACE_NAME,C.SEGMENT_NAME,B.OBJECT_TYPE)
ORDER BY 3 DESC;



4. Current Size of each tables

select segment_name,segment_type,bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE' AND OWNER='*_SOAINFRA' order by 3 desc;

5. Size allocated to soa infra and current size


SELECT A1.TABLESPACE_NAME TABLE_SPACE, B.AUTOEXTENSIBLE, B.INCREMENT_BY,
SUM (A1.BYTES)/1024/1024 FREE_SPACE_MB, SUM (A1.BLOCKS) FREE_BLOCKS, SUM(B.BYTES)/1024/1024 ALLOCATED_SPACE_MB
FROM SYS.DBA_FREE_SPACE A1, SYS.DBA_DATA_FILES B
WHERE A1.TABLESPACE_NAME=B.TABLESPACE_NAME AND A1.TABLESPACE_NAME ='*_SOAINFRA'
GROUP BY A1.TABLESPACE_NAME, B.AUTOEXTENSIBLE, B.INCREMENT_BY;

--All tables

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) -
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY A.TABLESPACE_NAME, C.FREE/1024
ORDER BY USED_PCT desc;


----------------------------------------------------------------------------------------------------
 Table space allocated to all schema's

select tablespace_name,used_percent, TABLESPACE_SIZE from dba_tablespace_usage_metrics order by USED_PERCENT desc;

----------------------------------------------------------------------------------------------------

6.  Performance of BPEL or BPM component in a day

SELECT DOMAIN_NAME,
COMPONENT_NAME,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') AVERG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') MINI,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE - CREATION_DATE),18,4))),'999990.000') MAXI,
COUNT(1) CNT
FROM CUBE_INSTANCE
WHERE CREATION_DATE >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
--AND COMPOSITE_NAME LIKE '%%'
GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE
ORDER BY COMPONENT_NAME, STATE;


7. Performance of Mediator component in a day.


SELECT SUBSTR(COMPONENT_NAME, 1, INSTR(COMPONENT_NAME,'/')-1)
PARTITON,
SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,
INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2',
'FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') STATE,
TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') AVERG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MINI,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MAXI,
COUNT(1) CNT
FROM MEDIATOR_INSTANCE
WHERE CREATED_TIME >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
GROUP BY COMPONENT_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, COMPONENT_STATE;

8. Performance of each composite.

SELECT CMPST_ID,

TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,

COMPONENT_NAME,

COMPONENTTYPE,

DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,

TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4)),'999990.000') DURTION

FROM CUBE_INSTANCE

WHERE TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')

AND (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + 

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4)) > 10

--AND COMPONENT_NAME LIKE '%%'

--AND COMPOSITE_NAME LIKE '%%'

ORDER BY COMPONENT_NAME, CREATION_DATE;

SELECT COMPOSITE_INSTANCE_ID,

TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,

SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,

INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,

DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2','FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') STATE,

TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4)),'999990.000') DURTION

FROM MEDIATOR_INSTANCE

WHERE TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')

AND (TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) + 

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) + 

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4)) > 10

-- AND COMPONENT_NAME LIKE '%%'

ORDER BY COMPONENT_NAME, CREATION_DATE;

8. Slow performing queries in the database

SELECT SQL_FULLTEXT, SQL_ID, CHILD_NUMBER, DISK_READS, EXECUTIONS,
FIRST_LOAD_TIME, LAST_LOAD_TIME, ELAPSED_TIME FROM V$SQL ORDER BY
ELAPSED_TIME DESC;

9. select * from user_tab_modifications order by timestamp desc;

Other queries can be found here 

Oracle SOA BPM javax.xml.soap.SOAPException: Bad response: 504 Gateway Timeout from url

If you are getting "Unable to invoke endpoint URI "http://xxxx.com:80/sdfsfsdfersionServicePort" successfully due to: javax.xml.soap.SOAPException: javax.xml.soap.SOAPException: Bad response: 504 Gateway Timeout from url http://xxxx.com:80/sdfsfsdfersionServicePort" in a BPEL instance when you invoke a Synchronous web service, then you need to set "HTTP Read Timeout (ms)" in the reference property of that particular Synchronus webservice.

This can be performed both at design time and runtime.


1. At Runtime

Navigate to the particular composite in EM console

Click on the Reference for which you need to change the timing

Reference property page opens

Click on the properties tab

Change the HTTP Read time out setting to required timing.


2. At design time


1. Navigate to composite in Jdeveloper and click on the service for which you want to change the time out setting

Go to properties for the particular service.

Expand properties and set the  Binding property oracle.webservices.httpReadTimeout to the required value

For more detailed information on Time out setting in Oracle SOA 11g please visit  link

Removing a particular file from MDS using MBean-EM console

1.       Login to EM console

2.       Right click on SOA-INFRA

3.       Click on Administration.

4.       Click on MDS Configuration.

Navigate to Application Defined MbeansàOracle.mds.lcmàServer : soa_server1àApplication :soa-infraàMDSAppRuntime

Click on Operations tab

Click on deleteMetadata with 6 input parameters

Click on edit button on docs tab

Click on Add

Add file to removed

Check the  MDS before removing.

Click on Invoke

Check the return value

Refresh MDS connection.

Refresh the MDS and file should not be present

Oracle SOA : Get number of instances using SOA Facade api's

Libraries

Oracle SOA : Get list of all faults in the SOA domain

Below is the code to get all faults given the filter criteria in the entire soa infra.



Libraries

Oracle SOA : Get Number of messages in JMS using JMX api's

Below is the java code to get Number of messages in JMS using JMX api's

Libraries

Oracle SOA : Get server health data using JMX api's

Below is the java code to retrieve server health data using JMX api's

Libraries

Oracle SOA BPM : Get list of faulted BPMN instances using SOA managment API's

Below is the code to get list of BPMN instances which are faulted.

Libraries :

Oracle SOA : Get list of instances that are recoverable using SOA managment api's Ver2

There are five message states

1. Undelivered
2. Resolved
3. Delivered
4. Cancelled
5. Exhausted

The int value for the above state is as below
0 for Undelivered
1 for Resolved
2 for delivered
3 for Cancelled
4 for exhausted
This is set in the mfilter.setState(0);




Jdev libraries

Note :
If you get "org.omg.CORBA.MARSHAL:   vmcid: 0x0  minor code: 0  completed: No" exceptions then use weblogic.jar instead of wlclient.jar

Oracle SOA : Get Composite instance details based on Sensor value

Below is the java code to retrieve composite instance data based on sensor value.

Libraries

Oracle SOA : Getting default revision and deployment times from SOA managment api's

Below is the code to retrieve default revision and deployment dates for a given composite.
Other operations that you can perform is
1. activate();
2. getFaultCount();
3. getInstanceCount();
4. isShutdown();
5.retire();
6.setAsDefaultRevision();
7.start();
8.stop();

Libraries

Oracle SOA : Get composite instance state using SOA Management api's

The below code is used to get composite instances state

Libraries

Note : If the state comes as STATE_UNKNOWN then enable the instance capture option in EM console 

Oracle SOA : Get instance details for composites using SOA management api's

Below is the code to get instance details for composites and by applying filters you can retrieve at composite level and sensor level.

Libraries :

Oracle SOA : Get list of composites deployed using SOA management api's

Use the below code to get the list of composites and other information about the composites deployed into the server.

Libraries

Oracle SOA : Get Task service and Task Query service

Below is the java code and screenshot for getting task query service and Task service to query and update tasks

Libraries