Monitoring HAFileAdapter

if a file has already been processed but not purged from the FILEADAPTER_IN table,
the second time it's submitted you will see

INSERT INTO FILEADAPTER_IN (FULL_PATH, ROOT_DIRECTORY,FILE_DIRECTORY, FILE_NAME, FILE_ENDPOINT_GUID, FILE_LAST_MODIFIED,FILE_READONLY, CREATED, UPDATED, FILE_PROCESSED) VALUES (?,?,?,?,?,?,?,?,?,?)

where FILE_PROCESSED = "0"

and you will get a

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PL1_SOAINFRA.SYS_C0026314) violated

because the file is already in the DB.

So why the file is not being purged? Because it's still with FILE_PROCESSED = "0".

I see very often:

DELETE FROM FILEADAPTER_IN WHERE FILE_PROCESSED='2' AND FILE_READONLY='N' AND ROOT_DIRECTORY=?)


RECOVER_UNPICKED:

DELETE FROM FILEADAPTER_IN WHERE UPDATED <= ? AND FILE_PROCESSED='0' AND ROOT_DIRECTORY=?

where the first ? is:
System.currentTimeMillis() - (oracle.tip.adapter.file.timeout.recoverunpicked.minutes * 60 * 1000)

(default is 3600 000 = 1 hour)

and in the log:
FileListDAO::Recovered [0] unpicked instances diff time=[1340299775855], recoverytime=[120000]


and also

DELETE FROM FILEADAPTER_IN WHERE UPDATED <= ? AND FILE_PROCESSED='1' AND ROOT_DIRECTORY=?)

where the first ? is:
System.currentTimeMillis() - (oracle.tip.adapter.file.timeout.recoverpicked.minutes * 60 * 1000)


and also

DELETE FROM FILEADAPTER_IN WHERE FILE_ENDPOINT_GUID= ? AND FILE_READONLY='N' AND FILE_PROCESSED='0'

Setting up HAFileAdapter with Oracle RAC DB

Create these DataSources:

the first is :

dsname=SOADataSource (local, non XA)
dsjndi=jdbc/SOALocalTxDataSource
dstype=GridLink
dsSupportsGlobalTransactions=true
dsXAType=OnePhaseCommit
dsDriverName=oracle.jdbc.OracleDriver
dsFanEnabled=true
dsOnsNodeList=myrachost.com:6200

<?xml version='1.0' encoding='UTF-8'?>
<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd">
  <name>SOADataSource</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myrachost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=srv_osb)))</url>
    <driver-name>oracle.jdbc.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>pp1_soainfra</value>
      </property>
    </properties>
    <password-encrypted>{AES}rnUdIazVyMYKqcFXg55eXdPGyxefK6Mr9fqUglWFhm8=</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1  FROM DUAL</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>jdbc/SOALocalTxDataSource</jndi-name>
  </jdbc-data-source-params>
  <jdbc-oracle-params>
    <fan-enabled>true</fan-enabled>
    <ons-node-list>myrachost:6200</ons-node-list>
    <ons-wallet-file></ons-wallet-file>
  </jdbc-oracle-params>
</jdbc-data-source>

and the second is:

dsname=SOAXADataSource (global (XA))
dsjndi= jdbc/SOADataSource
dstype=GridLink
dsXAType=TwoPhaseCommit
dsDriverName=oracle.jdbc.xa.client.OracleXADataSource
dsFanEnabled=true
dsOnsNodeList=myrachost.com:6200

<?xml version='1.0' encoding='UTF-8'?>
<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd">
  <name>SOAXADataSource</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myrachost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=srv_osb)))</url>
    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>pp1_soainfra</value>
      </property>
    </properties>
    <password-encrypted>{AES}LjuMmp4BvBf23878ydOfK2XcBMyZTjGDf2lP1NTn2raU=</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1  FROM DUAL</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>jdbc/SOADataSource</jndi-name>
    <global-transactions-protocol>TwoPhaseCommit</global-transactions-protocol>
  </jdbc-data-source-params>
  <jdbc-oracle-params>
    <fan-enabled>true</fan-enabled>
    <ons-node-list>myrachost:6200</ons-node-list>
    <ons-wallet-file></ons-wallet-file>
  </jdbc-oracle-params>
</jdbc-data-source>

now you can move to the eis/HAFileAdapter and configure it like this:

controlDir=/opt/oracle/domains/myydomain/shared/apps/fileadapter/controlDir (this is on a shared drive)

inboundDataSource=jdbc/SOADataSource
outboundDataSource=jdbc/SOADataSource
outboundDataSourceLocal=jdbc/SOALocalTxDataSource
outboundLockTypeForWrite=oracle


The mutex - allowing the HAFileAdapters to coordinate with each other and avoid picking up the same file - is based on 2 DB tables:

describe FILEADAPTER_IN
Name Null Type
------------------ -------- --------------
FULL_PATH NOT NULL VARCHAR2(4000)
ROOT_DIRECTORY NOT NULL VARCHAR2(3000)
FILE_DIRECTORY NOT NULL VARCHAR2(3000)
FILE_NAME NOT NULL VARCHAR2(1000)
FILE_ENDPOINT_GUID NOT NULL VARCHAR2(2000)
FILE_LAST_MODIFIED NUMBER
FILE_READONLY CHAR(1)
FILE_PROCESSED CHAR(1)
CREATED NOT NULL NUMBER
UPDATED NUMBER


describe FILEADAPTER_MUTEX
Name Null Type
------------------ -------- --------------
MUTEX_ID NOT NULL VARCHAR2(4000)
MUTEX_CREATED TIMESTAMP(6)
MUTEX_LAST_UPDATED TIMESTAMP(6)
MUTEX_SEQUENCE NUMBER



It's all, folks! Don't forget to test!

See also here for first approach