Using SFTP with Oracle SOA

A common requirement in integration projects is to transfer files in/out of a system in a secured manner. FTP is the usual protocol for transferring files and if additional security is required then SFTP (Secured FTP) is the way to go. In this post I would cover some of the ways FTP adapter can be configured in SOA 10g and 11g to make use of SFTP.

For additional details on FTP adapter configuration you can refer to the Oracle link
http://download.oracle.com/docs/cd/B31017_01/integrate.1013/b28994/adptr_file.htm

SFTP supports couple of authentication mechanisms to ensure additional security on top of the FTP protocol.

• Password authentication
• Public Key authentication

In password authentication, the external site/vendor which hosts the FTP server shares a username/password combination which has to be configured on the SOA server. At runtime when a SFTP connection is attempted, the username/password is made use of for establishing the connection.

Similarly in Public key authentication, a private-public key pair is generated. The public key is shared with the external site/vendor which hosts the FTP server. At runtime when a SFTP connection is attempted, Fusion process will try to match the private key stored locally on SOA server with the public key on Remote FTP Server and do the authorization first before sending/posting the files.

The configuration information in either case is stored in SOA_HOME/j2ee/OC4J_SOA/application-deployments/default/FtpAdapter/oc4j-ra.xml  (SOA 10g) or MW_HOME/Oracle_SOA1/soa/connectors/FtpAdapter.rar/weblogic-ra.xml (SOA 11g).

For Password authentication below are the properties which you need to set (oc4j-ra.xml sample shown below)

<config-property name="host" value="XXXXX"/>
<config-property name="port" value="22"/>
<config-property name="username" value="xxxxx"/>
<config-property name="password" value="xxxxx"/>
<config-property name="useSftp" value="true"/>
<config-property name="authenticationType" value="password"/>

For Public Key authentication below are the properties which you need to set (weblogic-ra.xml sample shown below)

<wls:property>
<wls:name>host</wls:name>
<wls:value>XXXX</wls:value>
</wls:property>

<wls:property>
<wls:name>port</wls:name>
<wls:value>22</wls:value>
</wls:property>

<wls:property>
<wls:name>useSftp</wls:name>
<wls:value>true</wls:value>
</wls:property>

<wls:property>
<wls:name>authenticationType</wls:name>
<wls:value>publickey</wls:value>
</wls:property>

<wls:property>
<wls:name>privateKeyFile</wls:name>
<wls:value>path of private key file</wls:value>
</wls:property>

Apart from above configuration steps incase of Public Key authentication we can follow below additional steps to generate the private-public key pair and do corresponding setup for that.

1. On Remote FTP Server ensure that /etc/ssh/sshd_config has below parameters set
              RSA Authentication Yes
              PubKey Authentication Yes
2. On SOA server, generate the Public/Private Key pair using below command
            ssh -keygen –t  rsa
3. Once the public and private key are generated make a note of file path, file name etc.
4. Then copy the public key content to the Remote FTP Server . Login as the account with which the FTP has to be performed and put the public key content into file ~/.ssh/authorized_keys.
5. For modifying the weblogic-ra.xml in SOA 11g, extract the file from the FtpAdapter.rar file and after making changes repackage it using command (jar cvf FtpAdapter.rar .)

Incase you run into errors like below work with your network administrator to unblock the port 22 at firewall.
sftp xxxxx
Connecting to xxxxx...
ssh: connect to host xxxx port 22: Connection refused
Couldn't read packet: Connection reset by peer

Thats it ! Now you should be able to securely transfer files..

Configuring FTP Adapter in SOA 10g Cluster for High Availability

If you have a 2 Node cluster environment for SOA 10g, it is essential that the FTP adapter is configured in an Active/Passive manner. Otherwise you may run into situations where both nodes are trying to read the same file from the remote FTP server and this will lead to duplicate files entering the system.

The reason for duplicates is because FTP adapter on each node will maintain its own control file(locally) where it stores the last read time of the file. So if a file has already been read by Node1, there are chances that after server restart the same file may be picked by Node2 as well because the time maintained in control files is out-of-sync on both nodes.

There are couple of good articles published by Oracle for this specific configuration. The base idea of the solution is to have the control file on a shared folder which is accessible by both nodes.

Step1:
=====
Make sure the FTP adapters are configured in Singleton mode i.e BPEL clusterName value specified in $ORACLE_HOME/bpel/system/config/collaxa-config.xml should be different from the Adapters clusterGroupId property set inside bpel.xml of the BPEL project.

Also the Multicast host and port in jgroups-properties.xml file should be same on both nodes.

Step2:
=====
Once step1 has been ensured, you should create a folder on a shared file system. Either use an external share storage or you can create a shared directory on one node and use NFS share to mount it on the other node. Either ways make sure the folder has write permissions from both nodes. This folder will store the control files.

Next backup and edit the $ORACLE_HOME/bpel/system/service/config/pc.properties file on each node and set the below property oracle.tip.adapter.file.controldirpath to the shared folder name

Restart the servers for the change to take effect and test the scenario.

ORABPEL-05215 Error while deploying BPEL processes

Was working on some SOA 10g deployments recently and came across this error.

ORABPEL-05215

Error while loading process. The process domain encountered the following errors while loading the process "XXXXXX" (revision "1.0"): null.

If you have installed a patch to the server, please check that the bpelcClasspath domain property includes the patch classes.

Tried looking into the opmn logs and nothing additional was mentioned there. This was a simple BPEL process and didn't have any embedded java code. So definitely wasn't an issue with classpath settings.

On further checking found that the ORACLE_HOME and PATH used by my build scripts were pointing to Jdev 10.1.3.5 directory. However the SOA Suite version was 10.1.3.4. 

Downloading 10.1.3.4 Jdev and pointing ORACLE_HOME and PATH to this directory worked ! So when you encounter this error ensure that SOA Suite version matches your JDeveloper version.

java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session

While trying to call a stored procedure from Oracle SOA 11g BPEL it throws below error
"java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session".

If you are using Global Transactions(XA) in your DB Adapter then the commit will happen only after the BPEL process completes. To avoid the above error :

1. Make sure you don't have explicit commits within the stored procedure as BPEL tries to manage the transaction commit and there is a conflict if the stored proc. has an explicit commit inside it.

2. Also you can use local transactions(non-XA) in your DB Adapter if you don't want to wait till the process is over for the commit to happen.

flowN for parallel processing in BPEL

Recently I came across a requirement where I had to implement parallel processing in my BPEL. Basically had to post some data to SAP system and was asked to work with multiple connections to increase the throughput. FlowN activity is ideal for this scenario as I had to perform similar processing on different messages/payload. Also this gave me the flexibility to increase the number of parallel flows incase a higher throughput was desired later without making any change to code (set the number of flows desired as a BPEL preference and assign the same to the FlowN variable).

However I did one mistake, which was not creating a Scope inside FlowN and have the variables defined locally inside that scope. When FlowN gets executed this scope should be processed parallely but with different message/payload based on the IndexVariable. Since I had declared the variables globally so at runtime it wasn't executing the parallel flows correctly and always used the payload/message of 1st flow.

<assign name="Set_Counter">
  <copy>
     <from expression="ora:getPreference('NumberOfFlows')"/>
        <to variable="NumOfFlowsToBeProcessed"/>
    </copy>
</assign>

<bpelx:flowN name="Parallel_Flow"
  N="bpws:getVariableData('NumOfFlowsToBeProcessed')"
  indexVariable="Parallel_Flow_Variable">
   <scope name="FlowN_Scope">
       <variables>
           <variable name="Invoke_WS_InputVariable"/>
              ...
       </variables>       
  <sequence name="Sequence_1">
           <assign name="Assign_Input">
               <copy>
                 <from variable="Fetch_Variable"
                   part="part1"
                   query="/ns1:ListOfData/ns1:Data[$Parallel_Flow_Variable]/ns1:name"/>
                  <to variable="Invoke_WS_InputVariable"
                   part="payload"
                   query="/ns2:DataList/ns2:Data/ns2:name"/>
                </copy>

Correct usage is as shown in above code snippet. Using local variables inside the FlowN scope should allow the parallel flows to execute correctly.

A nice read about True Parallelism in BPEL FlowN activity.
true-parallellism-of-the-oracle-bpel-pm-flow-activity

SAP Adapter : Configuring Database Repository

SAP Adapter(JCA config) supports both file based as well as database based repository. I have had some bad experiences with file based repository where maintaining the repository.xml has been more or less a nightmare. I have covered some of these errors in earlier posts.

targets-for-mysap-no-targets-configured.html

We sometimes end up with duplicate repository.xml files or 0kb repository.xml files after server restarts. This causes most BPEL processes to fail with Adapter exceptions. So I finally decided to migrate all file based repositories to DB repositories (Not sure why I didn't do this earlier :-( ). Now Oracle doesn't provide any migration tools as such. But this isn't a huge effort and we just need to create the targets/channels afresh and it is a one-time task.

Anyways below mentioned is the list of steps to be followed to configure DB repository for SAP adapter.

1. Create a new schema user on database where you want the repository to reside. (Eg: saprepo_user/xxxxx)

2. Execute the iwse.ora SQL script on the machine where the database is installed. The iwse.ora SQL script is located in the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\etc

This script creates the required tables that are used to store the adapterconfiguration information in the database. These tables are used by Application Explorer and by adapters during design time and runtime. It is recommended that you use the same credentials to create the database repository and also in the ra.xml file for database user credentials.

3. Create the jcatransport.properties file and save it in the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\config\Config_Name

4. Enter values for iwafjca.repo.url, iwafjca.repo.user and iwafjca.repo.password fields in the newly created jcatransport.properties file, as shown in the following example:

iwafjca.repo.url=jdbc:oracle:thin:@dbhostname:port:sid
iwafjca.repo.user=saprepo_user
iwafjca.repo.password=xxxx

5. Navigate to the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\iwafjca.rar\META-INF

6. Open the ra.xml file and provide the JDBC connection information: (Same values as step3)
IWAYRepo_URL property.
IWAYRepo_User property.
IWAYRepo_Password property.

Save your changes to the ra.xml file.

7. Copy the ojdbc14.jar file to the directory $MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\lib

8. Redeploy the iwafjca rar file from Admin console->deployments section and Restart Weblogic server.

9. Now open Application Explorer and create the SAP target again with similar details as before. You can then connect to the DB repository and check the af_config table to find your details stored there.

NOTE:
The above steps are for SOA 11g. For SOA 10g the directory structures will be different.
* jcatransport.properties file in following directory \adapters\application\config\
* copy ojdbc14.jar to \adapters\application\lib\
* Update oc4j-ra.xml (\j2ee\oc4j_soa\application-deployments\default\jca-app-adapter)

I haven't faced any issues so far after migrating from File to DB repository. Will definitely post if I find any discrepancies. Till then some relief :-)

Custom XSLT Function in SOA 11g

Its been a while since I blogged and has been busy few months. I recently got a requirement to strip all special characters from an input string in XSLT. Tried checking the list of pre-defined XSLT functions in Jdeveloper 11g to see if we had something similar. Though we have few string functions to normalize whitespaces and others which match against regex patterns. But none which replaced/stripped special characters/regex patterns from a string. So decided to write some custom XSLT function which could do this.

First we create the java class which implements the logic

package customxslt;
import java.io.PrintStream;
public class AdditionalStringFunctions
{
  public static String replaceChars(String input, String regex, String replaceWith)
  {
    String output = input.replaceAll(regex, replaceWith);
    return output;
  }
  public static void main(String[] args)
  {
    AdditionalStringFunctions aSF = new AdditionalStringFunctions();
    String input = "(+91)80-55555555";
    System.out.println(replaceChars(input, "[^0-9]", ""));
  }
}

All the above code does is, check for input string and any characters apart from 0-9 is stripped.

Next create a configuration file.Name the file as ext-mapper-xpath-functions-config.xml. Sample file is provided below

<soa-xpath-functions version="11.1.1"
                     xmlns="http://xmlns.oracle.com/soa/config/xpath"                 xmlns:regex="http://www.oracle.com/XSL/Transform/java/customxslt.AdditionalStringFunctions">
   <function name="regex:replaceChars">
    <classname>customxslt.AdditionalStringFunctions</className>
    <return type="string"/>
    <params>
      <param name="input" type="string"/>
      <param name="regex" type="string"/>
      <param name="replaceWith" type="string"/>
    </params>
    <desc/>
    <detail>
      <![CDATA[This function returns the value after stripping special characters.]]>
    </detail>
  </function>
 </soa-xpath-functions>

Create a folder called META-INF under the JDeveloper\mywork\Regex\TestRegex\classes directory and place the configuration file there.

Now create a jar file and make sure the class file and the ext-mapper-xpath-functions-config.xml are included in it.

Next register the JAR file in Jdeveloper designtime
This can be done via "Tools > Preferences… > SOA" add the JAR file and restart JDeveloper.

The newly created extension function will be available in the component palette under user defined functions page. You can make use it from there in your XSL code.

To make this function available at SOA runtime put the jar file under MW_Home/user_projects/domains/domain_name/lib

Restart the server and test your code.