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