Tuesday, May 12, 2020

12c SOA - How to check BPEL recovery Pool from SOAInfra DB

Sometimes EM Composite BPEL instances have failures and thus they are moved to BPEL recovery pool. We can check  them from SOAINFRA DB and delete the struck instances if not needed.

Query 1: based on date

select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'

Query2: based on a period
select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date 
between TO_DATE('2020-01-01 00:00:01', 'YYYY-MM-DD HH24:Mi:SS') and TO_DATE('2020-05-31 23:40:59', 'YYYY-MM-DD HH24:Mi:SS')

To delete:
delete from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'

12c SOA - Business Rules - use of decision table

Here I will show you how to create and use decision table using business rule.

Input: id, name, designation (like PAT, A, SA, M etc.).

Output: eligible_trutime_topup_per_fortnight (like 10,20,0 etc.)

Step1: Create SOA Project.


Now project created.

Step2: Drag and drop bpel process and select synchronous process.

Now bpel created as like below.

Step3: Prepare schema as per your requirement.

Step4: Drag and drop business rules component.

Now you can import schema input and output.

Import input schema.

Input schema imported successfully, now import output schema.


Both schema’s imported successfully.

Step5: Double click business rules.

Create decision tables.

Now decision table like below.

Select process Designation like below.


Click Actions and hard code the top up value.


After hard coding the value diagram like below.

Create another rule and give remaining conditions like below.

After creating all condition rules diagram like below.

Step6: Double click bpel process and add BusinessRules.

Double click rules and and select Business rules.

After selection now showing like below image.

Click ok, now composite automatically invoke business rules and created activities.

Step7:  Deploy the composite and test.

 

Testing:

Input:

<?xml version="1.0" encoding="UTF-8"?><inputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <ns1:process xmlns:ns1="http://xmlns.oracle.com/POC/DecisionTable_Poc/BPELProcess1">

            <ns1:ID>12345</ns1:ID>

            <ns1:NAME>Suri</ns1:NAME>

            <ns1:DESIGNATION>A</ns1:DESIGNATION>

        </ns1:process>

   </part>

</inputVariable>


Output:

<?xml version="1.0" encoding="UTF-8"?><outputVariable>

   <part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="payload">

      <processResponse xmlns="http://xmlns.oracle.com/POC/DecisionTable_Poc/BPELProcess1">

         <EligibleTopup>15</EligibleTopup>

      </processResponse>

   </part>

</outputVariable>


Saturday, May 9, 2020

Database - create package and procedure

Here showing you an insert events package and procedure creation.

Package creation:
create or replace PACKAGE "PCK_PROVISIONING"
AS
  /* enter package declarations (types, exceptions, methods etc) here */
PROCEDURE INSERT_EVENT_IN_PROGRESS(
    EMP_NUMBER     VARCHAR2,
    EMP_COUNTRY    VARCHAR2,
    BPEL_INST_ID   VARCHAR2,
    EMP_LAST_NAME  VARCHAR2,
    EMP_FIRST_NAME VARCHAR2,
    EV_ID OUT NUMBER );
END pck_provisioning;

Package body:
create or replace PACKAGE BODY "PCK_PROVISIONING"
AS
PROCEDURE INSERT_EVENT_IN_PROGRESS
  (
    EMP_NUMBER     VARCHAR2,
    EMP_COUNTRY    VARCHAR2,
    BPEL_INST_ID   VARCHAR2,
    EMP_LAST_NAME  VARCHAR2,
    EMP_FIRST_NAME VARCHAR2,
    EV_ID OUT NUMBER
  )
AS
BEGIN
/* calling the Insert_Event() procedure*/
  INSERT_EVENT
  (
    EV_TYPE => 'T', EMP_NUMBER => EMP_NUMBER , EMP_COUNTRY => EMP_COUNTRY, EV_STATUS => 2,            -- 2.- In Progress
    BPEL_INST_ID => BPEL_INST_ID, LAST_NAME => EMP_LAST_NAME, FIRST_NAME => EMP_FIRST_NAME, STS => 1, -- 1.- Active
    EV_ID => EV_ID
  );
END INSERT_EVENT_IN_PROGRESS;
/* called the Insert_Event() procedure body with Table.field%type */
PROCEDURE INSERT_EVENT(
    EV_TYPE EMPLOYEE_EVENT.EVENT_TYPE%type, 
    EMP_NUMBER EMPLOYEE_EVENT.EMPLOYEE_NUMBER%type,
    EMP_COUNTRY VARCHAR2,
    EV_STATUS EMPLOYEE_EVENT.EVENT_STATUS%type,
    STS EMPLOYEE_EVENT.STATUS%type,
    BPEL_INST_ID EMPLOYEE_EVENT.BPEL_INSTANCE_ID%type,
    LAST_NAME EMPLOYEE_EVENT.EMPLOYEE_LAST_NAME%type,
    FIRST_NAME EMPLOYEE_EVENT.EMPLOYEE_FIRST_NAME%type,
    EV_ID OUT EMPLOYEE_EVENT.EMPLOYEE_EVENT_ID%type )
AS
BEGIN
--Calling a sequence to get the event id.
  SELECT EMPLOYEE_EVENT_SEQ.NEXTVAL INTO EV_ID FROM DUAL;
--Inserting data to Employee_event table
  INSERT INTO EMPLOYEE_EVENT(
      EVENT_DATETIME,
      EVENT_TYPE,
      EMPLOYEE_NUMBER,
      EMPLOYEE_COUNTRY,
      EVENT_STATUS,
      STATUS,
      BPEL_INSTANCE_ID,
      EMPLOYEE_FIRST_NAME,
      EMPLOYEE_LAST_NAME,
      EMPLOYEE_EVENT_ID
    )
    VALUES(
      SYSDATE,
      EV_TYPE,
      EMP_NUMBER,
      EMP_COUNTRY,
      EV_STATUS,
      STS,
      BPEL_INST_ID,
      LAST_NAME,
      FIRST_NAME,
      EV_ID
    );
END INSERT_EVENT;

END pck_provisioning;

Unix or AIX box - remove zero size files from a directory


In our AIX box, we observed, when the files system got 100% utilized, it used to create files with zero size for all the incoming files via file adapter. so need to clear all the zero size files at a fly.

This works for plain BSD so it should be universally compatible with all flavors.

Command:
find . -size 0 |  xargs rm

12c SOA - Weblogic - delete JMS Queue messages from persistent store level

Sometimes we are facing a situation where a gigantic amount of messages are stored in the JMS queue but not processing at all and we are also not able to remove them from the queue itself. There is one alternate option to clear the messages from the persistent store level itself.

Solution steps:

Step1: Check the persistent store path, go to the path and delete SOA_JMSFILESTORE.DAT file
cd app/Oracle/Middleware/Oracle_Home/user_projects/domains/soa-osbdomain/servers/soa_server1/data/store/default
 ll
total 4112
-rw-r-----    1 oracle   dba         1052672 Apr 09 21:21 SOA_JMSFILESTORE1000000.DAT

rm SOA_JMSFILESTORE1000000.DAT

Step2: Restart the affected the manage servers. Like in this case, I have restarted the SOA1 Manage Server.



Wednesday, March 18, 2020

https service call - java.security.cert.CertPathValidatorException: Certificate chaining error

While we try to call a https external service from SOA composites, we  face following kind of certificate error:
Error:
oracle.fabric.common.FabricInvocationException: Unable to invoke endpoint URI "https://external-service/soap/V2.ASMX" successfully due to: javax.xml.soap.SOAPException: javax.xml.soap.SOAPException: Message send failed: com.ibm.jsse2.util.h: PKIX path building failed: java.security.cert.CertPathBuilderException: PKIXCertPathBuilderImpl could not build a valid CertPath.; internal cause is:
java.security.cert.CertPathValidatorException: The certificate issued by CN=DigiCert High Assurance EV Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US is not trusted; internal cause is:
java.security.cert.CertPathValidatorException: Certificate chaining error

Solution:
We need to import the certificate of this service to weblogic server lib location and then restart the impacted servers.

check steps here 12c-soa-weblogic-certificate-import.

12c - keytool - Delete certificate from Java Keystore using alias name

Solution
We need alias name to delete a specific certificate from java keystore.

Please use the following command:
Syntax:
keytool -delete
 -alias keyAlias
 -keystore keystore-name
 -storepass password

Example:
/usr/java8_64/bin/keytool -delete -alias cavent  -keystore SOAKeysTrust.jks -storepass CUSTOMPASS

here /usr/java8_64/bin/ is the java bin path added.


Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...