Daily Queries in SOAINFRA & XREF

:

Find BRM poid & CRM row_id from XREF :

SELECT MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,3), 'BRM', XREF_COLUMN_NAME, NULL)) SOURCE_ID,
    MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,3), 'BRM', VALUE, NULL)) BRM,
    MAX(DECODE(XREF_COLUMN_NAME, 'COMMON', VALUE,NULL)) COMMON,
    MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,4), 'SEBL', VALUE,NULL)) SIEBEL
  FROM (select * from xref_data where row_number in
(select row_number from xref_data where value in ('0.0.0.1 /billinfo 31XXXXXXXX 0')))
  WHERE IS_DELETED     ='N'
  AND (XREF_TABLE_NAME ='oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_BILLPROFILEID.xref')
  GROUP BY row_number,
    XREF_TABLE_NAME;

Find Instances in SOA_INFRA :

select b.ID "Instance ID",b.title "Name",a.composite_name"Composite",Decode(a.state,5,'SUCCESS','FAILED')"Instance State",a.modify_date "Time",TO_CHAR(SUBSTR((a.modify_date-a.creation_date),18,6)) "Execution_Time" from cube_instance a,composite_instance b where
a.cmpst_id=b.ID and b.title like '%97430037778%' order by 5 desc;

Find instance of some composite in SOA_INFRA :

select b.ID,Decode(a.state,5,'SUCCESS','FAILED') "STATUS",a.component_name,b.title,
TO_CHAR(a.creation_date,'MM/DD/YYYY HH24:MI:SS') "CREATION_TIME",TO_CHAR(SUBSTR((a.modify_date-a.creation_date),18,6)) "EXECUTION_TIME"
from VFQ_SOAINFRA.cube_instance a,VFQ_SOAINFRA.composite_instance b where
a.cmpst_id=b.ID and component_name like '%QueryRealTimeBalanceSurePayProvABCSImpl%'
and a.creation_date between
tO_DATE('01/13/2015 11:00:00','MM/DD/YYYY HH24:MI:SS') and
TO_DATE('01/13/2015 12:00:00','MM/DD/YYYY HH24:MI:SS') order by creation_date desc ;

RDA to run on 8.X version

Copy the RDA from below URL :

1. IMPORTANT: Download latest RDA from Note 314422.1 , alternatively you can use below link:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=390193761380843&id=314422.1&_afrWindowMode=0&_adf.ctrl-state=17ze6fiyoc_4

important: if you don't have perl you can use the binary version as described in this note

2. Unzip the the file into the SOA Server machine on a location of your preference.

3. From a command prompt enter the command:

./rda.sh -ySCRP -e'RDA.BEGIN.D_ORACLE_PARENT=<MIDDLEWARE_HOME>,OFM.SOA.D_ORACLE_HOME=<SOA_HOME_PATH>,OFM.SOA.D_DOMAIN_ROOT=<DOMAIN_PARENT_PATH>,OFM.SOA.T_DOMAINS=<SOA_DOMAIN_NAME>' -p FM11g_SoaMin

For example the command might look like this:

#PROJECT Specific command

./rda.sh -ySCRP -e'RDA.BEGIN.D_ORACLE_PARENT=/app/oracle/product/fmw/soa,OFM.SOA.D_ORACLE_HOME=/app/oracle/product/fmw/soa/Oracle_SOA1,OFM.SOA.D_DOMAIN_ROOT=/app/oracle/admin/vfq_domain/mserver,OFM.SOA.T_DOMAINS=vfq_domain' -p FM11g_SoaMin


4. Provide the output zip file generated, usually into the same rda folder where above command is ran in the name of: RDA_output_*.zip

Daily Queried to perform purging OSB DB

+++++++++++++++++++++++

p11676998_111130_Generic

+++++++++++++++++++++++

BEGIN

CMS_ORASDPM.UMS_CLEANUP.PURGE(200);

END;

/

+++++++++++++++++++++++

CR325307utility

+++++++++++++++++++++++

DECLARE

amount NUMBER;

BEGIN

REMOVE_REPORT_DATA_OLDER_THAN(50, 1000 ,amount);

dbms_output.put_line(amount);

END;

ORA-02049: timeout: distributed transaction waiting for lock

Problem :

The issue what we were getting while Product sync. Whenever any product sync got failed due to any valid issue (product already exists, Product not found) the transaction is not getting closed. As a result even if after correcting, if we try to sync the same or other products all of them getting failed with the same error. We found one workaround that is after restarting SOA Manage Server & DB restart issue is not coming anymore. But again once there is any valid error while product sync, subsequent all request are getting failed.

Cause :

Trnsactions was not getting closed after Throw. As a result all the subsequent request are causing DB_Lock.

Solution :

Ideally timeout parameters should be set as below :

syncMaxWaitTime < BPEL EJB's transaction timeout < Global Transaction Timeout (JTA timeout) < DISTRIBUTED_LOCK_TIMEOUT

1. SyncMaxWaitTime already tune to 240sec.

 


2. EJB level timeout setting :

BPELActivityManagerBean (300sec)
BPELDeliveryBean (300sec)
BPELDispatcherBean (300sec)
BPELEngineBean (300sec)
BPELFinderBean (300sec)
BPELInstanceManagerBean (300sec)
BPELProcessManagerBean (300sec)
BPELSensorValuesBean (300sec)
BPELServerManagerBean (300sec)

3. JTA time out already set as 360sec also Abandon Timeout Seconds as 400sec.

4. We can increase this more than 360 (more than JTA timeout). We made it 405sec which is just more than Abandon Timeout.

Remove unused files/artifacts from MDS

Whenever we want to delete some files/artifacts from MDS by using below steps we can delete data directly from MDS :

1. Execute wlst.cmd :
--------------------------------------
cd ${ORACLE_HOME}/oracle_common/common/bin

Execute wlst.sh file:
----------------------
wlst.sh


2. Connect to WL Server :
--------------------------------------
connect('weblogic','welcome1','t3://hostname:port')


3. Delete from MDS :
--------------------------------------
deleteMetadata(application='soa-infra', server='soa_server1',docs='/apps/AIAMetaData/##Artifacts Path##')

java.lang.NoClassDefFoundError

java.lang.NullPointerException at oracle.apps.aia.core.eh.InvokeBusinessErrorHandler.process(InvokeBusinessErrorHandler.java:24)

To work around the exception and missing /classes issue associated with the java.lang.NoClassDefFoundError, please take the following steps:  

Firstly check below steps :

> Check to see if aia.jar exists in the SOA CLASSPATH.
> Verify whether or not aia.jar also resides under the "domain/lib" folder.
If aia.jar is present in either location, please remove the aia.jar file from each location and then retest.
If issue is resolved , the following 5 steps is not required.


1) Copy aia.jar located under $AIAHOME/lib to $SOA_HOME/soa/modules/oracle.soa.ext.11.1.1/
2) Use command - jar -xvf aia.jar to extract all of its contents.
3) Create a directory named "classes" under $SOA_HOME/soa/modules/oracle.soa.ext.11.1.1 directory and copy the extracted contents of aia.jar into newly created  "classes" directory.
4) After this you need to run the build.xml file located in the same directory.
5) Remove the aia.jar FROM this location $SOA_HOME/soa/modules/oracle.soa.ext.11.1.1 that is copied in Step 1 (If it exists)
6) Bounce SOA server and then retest.
This should correct the java.lang.NoClassDefFoundError.

Once the above is complete, to address the email notification portion, the following changes are required:

Update the AIAConfiguraitonProperties.xml file with the following changes:

1) Under EH Module properties section
<Property name="EH.HOSTNAME">managedserver1.hostname</Property> <!-- Use one of the SOA Servers instead of Load balancer hostname for this property-->
<Property name="EH.PORT">managedserver1.port</Property>   <!-- Used the SOA Port and not the LBR port for this property-->
2) Upload to MDS
3) Reload using AIA UI.
4) Retest.

==================================================================================================================================================================

If still issue not got resolved, please check whether the class file got corrupted not (try to decompile). If got corrupted, please load a new class file 

Add Security Header

Namespace add :

xmlns:ns7="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd

Variable Declaration :

    <variable name="securityContext" element="ns7:Security"/>
    <variable name="userNameToken" element="ns7:UsernameToken"/>
    <variable name="pswd" element="ns7:Password"/>

Assign Security Header :

    <assign name="Assign_WS_Header">

      <copy>

        <from expression="aia:getServiceProperty('{http://xmlns.oracle.com/ABCSImpl/Siebel/Industry/Comms/ProcessPayableInvoiceXXXXProvABCSImpl}ProcessPayableInvoiceXXXXProvABCSImpl','Routing.billformatter.pwd',false())"/>

        <to variable="pswd" query="/ns7:Password"/>

      </copy>

      <copy>

        <from expression="aia:getServiceProperty('{http://xmlns.oracle.com/ABCSImpl/Siebel/Industry/Comms/ProcessPayableInvoiceXXXXProvABCSImpl}ProcessPayableInvoiceXXXXProvABCSImpl','Routing.billformatter.username',false())"/>

        <to variable="userNameToken" query="/ns7:UsernameToken/ns7:Username"/>

      </copy>

      <bpelx:insertAfter>

        <bpelx:from variable="pswd" query="/ns7:Password"/>

        <bpelx:to variable="userNameToken"

        query="/ns7:UsernameToken/ns7:Username"/>

      </bpelx:insertAfter>

      <bpelx:append>

        <bpelx:from variable="userNameToken" query="/ns7:UsernameToken"/>

      <bpelx:to variable="securityContext" query="/ns7:Security"/>

      </bpelx:append>

    </assign>

</sequence>

Invocation :

<invoke name="Invoke_test" bpelx:invokeAsDetail="no"

                  inputVariable="Invoke_test_InputVariable"

                  outputVariable="Invoke_test_OutputVariable"

                  partnerLink="billformatter" portType="ns1:Billformatter"

                  bpelx:inputHeaderVariable="securityContext"

                  operation="resendBill"/>

          <assign name="Reply_ResendBillOutput">