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. 
    

OBPM INTRO

                                            OBPM

What ?

Oracle BPM is short Oracle Business Process Management, a powerful set of tools for creating, executing, and optimizing business processes that can great improve efficiency, data sharing, and customer service for your company.

Oracle BPM is part of Oracle Fusion Middleware Family built on top of very successful Oracle SOA suite 11g.

Oracle BPM is around in the market from 2009.

IBM BPM and PEGA are the main competitors.

Why?

1. The suite enables unparalleled collaboration between business and IT to automate and optimize business processes.

2. Today's businesses operate in a rapidly changing climate that demands an unprecedented level of adaptation and innovation. These conditions place equally unprecedented burdens on IT departments, which must now deliver solutions in months rather than years. And those solutions must themselves be flexible enough to support continuous adaptation to an ever-evolving, ever-expanding marketplace.

3.Today’s organizations face increasing business pressures due to globalization, changing competitors, stricter regulations, faster pace of
innovation, and demand for better customer service.

4. Trends such as mobile, social, cloud, and analytic s have a profound impact on a company’s business and its core business processes. Companies expect business driven rapid process automation, seamless collaboration among all stakeholders, deep insights for better business decisions, unprecedented agility and operational excellence.

How ?

OBPM provides standard set of tools to achieve the objectives defined earlier. 

Complete set to of tools from Modelling , Design, Development, Execution and Monitoring.

Can be used to Achieve Round trip integration of a process.

Round trip integration is used to improve process through cycles of design and development.

Agile and DEVOPS complement this naturally.

Tools  are described below 

Process composer :

1. Primary designed for Business analyst ,Process experts and Functional experts.
2. A Web based tool.
3. Create , Import and Modify(limited) BPM processes.
4. After creation of Process , publish it to the Repository, where IT developer can pick it and take over the development activity.

BPM studio :

1. A Jdeveloper Addon. Jdeveloper is a standard tool for Application development in Oracle Fusion Middleware family.
2. Complete development features like  Code, compile and packaging .
3. Primarily targeted on IT developer.
4. One stop for all  OBPM development.

Weblogic Server :

1.  A runtime platform for the BPM process.
2.  An excellent Enterprise application server.
3.  Excellent load balancing and load handling capabilities.

Enterprise manager :

1. Web application to Administrators to monitor and control all BPM processes.
2. View each instance details and current state of all BPM process.

BPM workspace :

1.  A web application to end users to act on task assigned via BPM process.
2. Any task assigned to logged in user appears on his bpm workspace task list.
3. Admin can also use to create, update and delete process roles etc.

BAM :

1. View realtime metrics derived from the running bpm processes.
2. Metrics has to be configured during design time.
3. Metrics are used to modify the business process.

 BPMN : 

1. Business Process Model and Notation (BPMN) is a graphical representation for specifying business processes in a business process model.
2. A standard Business Process Model and Notation (BPMN) will provide businesses with the capability of understanding their internal business procedures in a graphical notation and will give organizations the ability to communicate these procedures in a standard manner.
3. The graphical notation will facilitate the understanding of the performance collaborations and business transactions between the organizations. This will ensure that businesses will understand themselves and participants in their business and will enable organizations to adjust to new internal and B2B business circumstances quickly.
4. OBPM suite uses BPMN 2.0

Runtime architecture

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 : 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 BPM :Get list of Holiday Calendars and Working Calendar using BPM api's

Oracle BPM : Get Extended User Properties using BPM API's

The below code retrieves the list of parametric values and its properties.

Libraries :

Oracle BPM : Get List of Parametric roles using BPM api's

Libraries :

Oracle BPM : Assign or Remove user from Application role using BPM api's

Below is the java code to assign or remove user from a role , same functionality is present in bpm workspace admin.

Libraries

Oracle BPM : Get list of roles and its corresponding users using BPM Api

Get list of Roles and users using the below code.

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