Hi All,
I do have a problem in using the DB adapter poller with SQLServer. The below steps describes my setup.
1. DB adapter is configured with SQLServer for polling every 15 seconds.
2. BPEL process receives the polled records and updates another db(oracle)
3. Both oracle and sqlserver (with the required SPs) are configured with XA datasource
4. db adapter datasource is set to xa for both the outbound connections
5. bpel.config.transcation is set to required
product version: 11.1.1.7
"Set XA Transaction Timeout" is checked in datasource transcation tab.
Problem: When the oracle db is not available, I am throwing the rollback exception from BPEL process but the change is committed on sqlserver.
I have enabled the log configuration for oracle.soa.adapter to FINEST(32) and found the commit is done internally before the process is complete.
the below is the trace
ctivationSpec: oracle.tip.adapter.db.DBActivationSpec@36d92b02[[
]]
[2014-07-18T14:04:35.616+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] Database Adapter test_delete <oracle.tip.adapter.db.DBEndp
oint start> Kicked off 1 threads.[[
]]
[2014-07-18T14:04:35.618+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [u
serId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] Database Adapter test_delete <oracle.tip.adapter.db.DBAdapte
rConstants getUniqueProcessIdentifier> Unique process identifier will be test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0[[
]]
[2014-07-18T14:04:35.619+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa
nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor basicExecuteCall> SELECT REQUEST_NUM_ID, REQUES
T_STATUS, OPPORTUNITY_ID, CUSTOMER_CODE, PROJECT_CODE, REQUEST_HEADING, REQUEST_COMMENT, REQUEST_PROCESSED, REQUEST_FAILURE_COUNT FROM dbo.REQUEST WHERE (REQUEST_PROCESSED
= ?)[[
bind => [1]
]]
[2014-07-18T14:04:35.623+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo
gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController beginTransaction> TX beginTransaction, stat
us=NO_TRANSACTION[[
]]
[2014-07-18T14:04:35.623+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo
gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController beginTransaction> TX Internally starting[[
]]
[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo
gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.sessions.AbstractSession beginExternalTransaction> external transaction has
begun internally[[
]]
[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa
nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <oracle.tip.adapter.db.ox.O_XParser parse> Transforming the row(s) [<Request 1 />] read from the database into xm
l.[[
]]
[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa
nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <oracle.tip.adapter.db.DBAdapterConstants isElementFormDefaultQualified> Element is Request namespace is http://x
mlns.oracle.com/pcbpel/adapter/db/top/testsqlserver[[
]]
[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa
nagerImpl] [SRC_METHOD: log] JCABinding=> testsqlserver shouldProcessMessage: error = false --> true
[2014-07-18T14:04:35.702+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana
gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> Be
gin batch statements[[
]]
[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager
Impl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> UPDAT
E dbo.REQUEST SET REQUEST_PROCESSED = ? WHERE (REQUEST_NUM_ID = ?)[[
]]
[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager
Impl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> b
ind => [0, 1][[
]]
[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana
gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> En
d Batch Statements[[
]]
[2014-07-18T14:04:35.717+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] JCABinding=> test_delete JCA Binding Component instance:
SCA_AS11R1 - successfully completed endpointActivation for portType=testsqlserver_ptt, operation=receive
[2014-07-18T14:04:35.717+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']
[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] JCABinding=> [test_delete.testsqlserver/1.0] :init Succes
sfully initialized testsqlserver_db.jca
[2014-07-18T14:04:35.720+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana
gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController commitTransaction> TX commitTransaction, status=
STATUS_ACTIVE[[
]]
[2014-07-18T14:04:35.721+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana
gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController commitTransaction> TX Internally committing[[
]]
[2014-07-18T14:04:35.829+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI
d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana
gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.sessions.AbstractSession commitExternalTransaction> external transaction has comm
itted internally[[
]]
[2014-07-18T14:04:35.830+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:
weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e9,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager
Impl] [SRC_METHOD: log] JCABinding=> testsqlserver onBatchBegin: Batch 'test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.test
sqlserver.Request1405656275830' (test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830) starting
...
[2014-07-18T14:04:35.830+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e9,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManagerImpl] [SRC_METHOD: log] JCABinding=> testsqlserver onBatchComplete: Batch 'test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830' (test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830) has completed - final size = 1
[2014-07-18T14:04:35.878+10:00] [soa_server1] [ERROR] [] [oracle.soa.bpel.engine] [tid: orabpel.invoke.pool-4.thread-7] [userId: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,1:33349] [APP: soa-infra] [composite_instance_id: 110029] [component_instance_id: 110022] [composite_name: test_delete!1.0] [component_name: testsqlserverbpel] This exception was caused by a bpelx:rollback fault being thrown or a ROLLBACK_ONLY FabricInvocationException being thrown. BPEL engine is directed to roll back the current JTA transaction. Root cause : [[
com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.oracle.com/bpel/extension}rollback}
messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage}